Pandas Learning Guide for Users from the R Side

# Python/Pandas
s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
s['a']  # Access by index label

# Pandas - Index is integral to the structure
s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
s['a']     # Access by index label
s.iloc[0]  # Access by position (0-indexed, must use .iloc)

# Pandas - Index alignment in operations
s1 = pd.Series([10, 20], index=['a', 'b'])
s2 = pd.Series([30, 40], index=['b', 'a'])  # Different order
s1 + s2  # Aligns by index: a=10+40=50, b=20+30=50 (not position-based!)

# In R, this would be position-based:
# s1 + s2 would give: 10+30=40, 20+40=60 (position-based, not name-based)

# Series always has an index - if not specified, uses default RangeIndex
s1 = pd.Series([10, 20, 30])  # Index: 0, 1, 2 (default)
s2 = pd.Series([10, 20, 30], index=None)  # Same as above - still creates default index
# Note: You cannot create a Series truly without an index

# Create Series with custom index
s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])

# Method 1: reset_index() - converts index to a column (returns DataFrame!)
s_reset = s.reset_index()
print(s_reset)
# Output:
#    index    0
# 0      a   10
# 1      b   20
# 2      c   30
# Note: reset_index() on Series returns a **DataFrame**, not a Series!

# Method 2: Using reset_index(drop=True) - returns Series with default integer index
# (drop=True drops the old index instead of making it a column)
s_reset = s.reset_index(drop=True)  # Returns Series with default RangeIndex (0, 1, 2, ...)

# Method 3: Reset index while keeping as Series - reassign index directly
s.index = range(len(s))  # Reset to 0, 1, 2, ...
# Or create new Series with default index
s_new = pd.Series(s.values)  # Uses default RangeIndex (0, 1, 2, ...)
# Or explicitly
s_new = pd.Series(s.values, index=pd.RangeIndex(len(s)))

# Series index does NOT have to be unique - duplicates are allowed
s_dup = pd.Series([10, 20, 30, 40], index=['a', 'b', 'a', 'c'])
print(s_dup)
# Output:
# a    10
# b    20
# a    30
# c    40
# dtype: int64

# Accessing with duplicate index returns multiple values
print(s_dup['a'])  # Returns Series with both 'a' values: a=10 and a=30

# Check if index is unique
s_dup.index.is_unique  # Returns: False

# Make index unique (keeps first occurrence by default)
s_unique = s_dup[~s_dup.index.duplicated(keep='first')]
# Or
s_unique = s_dup.groupby(s_dup.index).first()  # Keep first value for each index

# Warning: Some operations may fail or behave unexpectedly with duplicate indices
# For example, reindexing or certain alignment operations

s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
# Index: ['a', 'b', 'c']
# Values: [10, 20, 30]
# No columns - Series are 1D structures

import pandas as pd
import numpy as np

# From a list, using default index
s1 = pd.Series([1, 3, 5, 7, 9])
print(s1)
# Output:
# 0    1
# 1    3
# 2    5
# 3    7
# 4    9
# dtype: int64

# With custom index
s2 = pd.Series([10, 20, 30, 40], index=['a', 'b', 'c', 'd'])
print(s2)
# Output:
# a    10
# b    20
# c    30
# d    40
# dtype: int64

# From a dictionary
data = {'apple': 3, 'banana': 2, 'orange': 5}
s3 = pd.Series(data)
print(s3)
# Output:
# apple     3
# banana    2
# orange    5
# dtype: int64

# From NumPy array
arr = np.array([1.5, 2.5, 3.5, 4.5])
s4 = pd.Series(arr, index=['x', 'y', 'z', 'w'])
print(s4)

# Mixed types (not recommended - stored as object dtype)
s5 = pd.Series([1, 'two', 3.0, True])
print(s5)
print(s5.dtype)  # Output: object
# Note: Mixed-type Series are less efficient. Keep Series homogeneous when possible.

# By index label
s2['b']  # Returns: 20

# By position (iloc)
s2.iloc[1]  # Returns: 20

# By position (integer index)
s1[1]  # Returns: 3

# Slicing with index labels or integer indices
s2['b':'d']  # Returns Series with b, c, d
s2.iloc[1:3]  # Returns Series with positions 1, 2

# Arithmetic operations
s1 * 2  # Multiply all values by 2
s1 + s1  # Element-wise addition (if same index)

# Statistical operations
s1.mean()  # Mean value
s1.std()   # Standard deviation
s1.sum()   # Sum of values
s1.min()   # Minimum value
s1.max()   # Maximum value
s1.median()  # Median value

# Boolean indexing
s1[s1 > 5]  # Returns Series with values > 5

# Check for null values
s1.isna()   # Returns boolean Series
s1.notna()  # Returns boolean Series
s1.dropna() # Remove null values

# Value counts
s = pd.Series(['a', 'b', 'a', 'c', 'b', 'a'])
s.value_counts()
# Output:
# a    3
# b    2
# c    1
# dtype: int64

# Sort
s2.sort_values()  # Sort by values
s2.sort_index()  # Sort by index

# Reset index
s_custom = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
# Reset to default integer index (returns DataFrame - index becomes a column)
s_custom.reset_index()  # Returns DataFrame with 'index' column
# To reset index and keep as Series, reassign index directly:
s_custom.index = range(len(s_custom))  # Now has index 0, 1, 2

# Apply function
s1.apply(lambda x: x**2)  # Square each value

# String operations (for string Series)
s_str = pd.Series(['apple', 'banana', 'cherry'])
s_str.str.upper()  # Convert to uppercase
s_str.str.len()    # Get length of each string

# Convert Series to NumPy array
arr = s1.to_numpy()  # Returns NumPy array (recommended)
arr = s1.values      # Alternative (legacy, still works)
# Note: Index is lost when converting to NumPy array

# Check and handle duplicate indices
s_dup = pd.Series([10, 20, 30], index=['a', 'b', 'a'])
s_dup.index.is_unique  # Check if index is unique (returns False if duplicates exist)
s_dup.index.duplicated()  # Returns boolean array indicating duplicate indices
# Remove duplicates (keep first occurrence)
s_unique = s_dup[~s_dup.index.duplicated(keep='first')]
# Or group by index and aggregate
s_unique = s_dup.groupby(s_dup.index).first()  # Keep first value for each index

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['NYC', 'LA', 'NYC']
}, index=['a', 'b', 'c'])

print(df.index)    # Index(['a', 'b', 'c'], dtype='object') - Row index
print(df.columns)  # Index(['name', 'age', 'city'], dtype='object') - Column labels

# Pandas - Index is always present and integral
df = pd.DataFrame({'x': [1, 2, 3], 'y': [4, 5, 6]}, index=['a', 'b', 'c'])
df.loc['a']    # Access by index label
df.iloc[0]     # Access by position (0-indexed)
# Operations automatically align by index

# From a dictionary of lists
data = {
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [25, 30, 35, 28],
    'city': ['New York', 'London', 'Tokyo', 'Paris']
}
df = pd.DataFrame(data)
print(df)
# Output:
#       name  age      city
# 0    Alice   25  New York
# 1      Bob   30    London
# 2  Charlie   35     Tokyo
# 3    David   28     Paris

# From a list of dictionaries
data = [
    {'name': 'Alice', 'age': 25, 'city': 'New York'},
    {'name': 'Bob', 'age': 30, 'city': 'London'},
    {'name': 'Charlie', 'age': 35, 'city': 'Tokyo'}
]
df = pd.DataFrame(data)

# From a NumPy array
arr = np.random.randn(4, 3)
df = pd.DataFrame(arr, columns=['A', 'B', 'C'], index=['row1', 'row2', 'row3', 'row4'])

# From CSV file
df = pd.read_csv('data.csv')

# From Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# From TSV file
df = pd.read_csv('data.tsv', sep='\t')

# First few rows
df.head()      # First 5 rows (default)
df.head(10)    # First 10 rows

# Last few rows
df.tail()      # Last 5 rows
df.tail(10)    # Last 10 rows

# Basic information
df.info()      # Data types, non-null counts, memory usage (like str() in R)
df.describe()  # Statistical summary (like summary() in R)
df.shape       # (rows, columns) - like dim() in R
df.columns     # Column names - like colnames() in R
df.index       # Index - like rownames() in R
df.dtypes      # Data types of each column - like sapply(df, class) in R

# Value counts for categorical columns
df['city'].value_counts()  # Like table(df$city) in R

# Select a column (returns Series)
df['name']        # Like df$name or df[["name"]] in R
df.name           # Alternative syntax (like df$name)

# Select multiple columns (returns DataFrame)
df[['name', 'age']]  # Like df[, c("name", "age")] in R

# Select rows by index label and/or column index
df.loc[0]           # First row (by label) - like df[1, ] in R (if index is 0)
df.loc[0:2]         # Rows 0 to 2 (inclusive) - like df[1:3, ] in R
df.loc[0:2, 'name'] # Rows 0-2, column 'name' - like df[1:3, "name"] in R

# Select rows by position (0-indexed!)
df.iloc[0]          # First row - like df[1, ] in R (but 0-indexed)
df.iloc[0:3]        # Rows 0, 1, 2 (exclusive end) - like df[1:3, ] in R
df.iloc[0:3, 0:2]   # Rows 0-2, columns 0-1 - like df[1:3, 1:2] in R

# Boolean indexing
df[df['age'] > 30]              # Rows where age > 30 - like df[df$age > 30, ] in R
df[(df['age'] > 30) & (df['city'] == 'Tokyo')]  # Multiple conditions - like df[df$age > 30 & df$city == "Tokyo", ] in R

# Query method (alternative to boolean indexing) - like subset() in R
df.query('age > 30 and city == "Tokyo"')  # Like subset(df, age > 30 & city == "Tokyo") in R

# Sample DataFrame with custom index
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'age': [25, 30, 35, 28, 32],
    'city': ['NYC', 'LA', 'NYC', 'LA', 'NYC']
}, index=['a', 'b', 'c', 'd', 'e'])

# .loc - Label-based selection
# Uses index labels (names), slicing is INCLUSIVE
df.loc['a']              # Row with index 'a'
df.loc['a':'c']          # Rows 'a', 'b', 'c' (INCLUSIVE - includes 'c')
df.loc['a', 'name']      # Value at row 'a', column 'name'
df.loc['a':'c', 'name']  # Rows 'a' to 'c', column 'name'
df.loc[['a', 'c'], ['name', 'age']]  # Specific rows and columns

# .iloc - Position-based selection
# Uses integer positions, slicing is EXCLUSIVE (like Python lists)
df.iloc[0]               # First row (position 0)
df.iloc[0:3]             # Rows 0, 1, 2 (EXCLUSIVE - excludes position 3)
df.iloc[0, 0]            # Value at row 0, column 0
df.iloc[0:3, 0:2]        # Rows 0-2, columns 0-1
df.iloc[[0, 2], [0, 1]]  # Specific rows and columns by position

# Important: Slicing behavior difference
df.loc['a':'c']   # Returns 3 rows: 'a', 'b', 'c' (INCLUSIVE)
df.iloc[0:3]      # Returns 3 rows: positions 0, 1, 2 (EXCLUSIVE, like Python)

# With integer index - be careful!
df_int = pd.DataFrame({'A': [1, 2, 3, 4, 5]}, index=[10, 20, 30, 40, 50])

# .loc uses the index labels (10, 20, 30, etc.)
df_int.loc[10:30]  # Returns rows with index 10, 20, 30 (INCLUSIVE)

# .iloc uses positions (0, 1, 2, etc.)
df_int.iloc[0:3]   # Returns rows at positions 0, 1, 2 (EXCLUSIVE)
# This is different from df_int.loc[10:30]!

# Setting values
df.loc['a', 'age'] = 26        # Set value using label
df.iloc[0, 1] = 26             # Set value using position

# Boolean indexing with .loc
df.loc[df['age'] > 30, 'name']  # Select 'name' column where age > 30
df.loc[df['age'] > 30, ['name', 'city']]  # Multiple columns

# Setting with conditions
df.loc[df['age'] > 30, 'salary'] = 50000  # Set salary where age > 30

# Pitfall 1: Confusing .loc and .iloc with integer index
df = pd.DataFrame({'A': [1, 2, 3]}, index=[10, 20, 30])

# This uses index labels (10, 20, 30)
df.loc[10:30]  # Returns all 3 rows

# This uses positions (0, 1, 2)
df.iloc[0:3]   # Returns all 3 rows, but conceptually different!

# Pitfall 2: Slicing behavior
df = pd.DataFrame({'A': [1, 2, 3, 4, 5]}, index=['a', 'b', 'c', 'd', 'e'])

df.loc['a':'c']   # Returns 3 rows: 'a', 'b', 'c' (INCLUSIVE)
df.iloc[0:3]      # Returns 3 rows: positions 0, 1, 2 (EXCLUSIVE)

# To get same result with .iloc, use:
df.iloc[0:3]      # Positions 0, 1, 2 (excludes 3)

# Pitfall 3: Using .loc with integer positions when index is not integer
df = pd.DataFrame({'A': [1, 2, 3]}, index=['x', 'y', 'z'])
# df.loc[0]  # ERROR! Index '0' doesn't exist
df.iloc[0]   # Correct - uses position

# Add new column
df['salary'] = [50000, 60000, 70000, 55000]

# Modify existing column
df['age'] = df['age'] + 1  # Add 1 to all ages

# Rename columns
df.rename(columns={'name': 'full_name', 'age': 'years'}, inplace=True)

# Drop columns
df.drop('city', axis=1)           # Returns new DataFrame
df.drop('city', axis=1, inplace=True)  # Modifies in place

# Drop rows
df.drop(0)  # Drop row with index 0
df.drop([0, 2])  # Drop multiple rows

# Set index: column named 'name' becomes index
df.set_index('name', inplace=True)

# Reset index: revert the above operation
df.reset_index(inplace=True)

# Handle missing values
df.isna()           # Check for missing values - like is.na(df) in R
df.isnull()         # Same as isna()
df.dropna()         # Drop rows with any missing values - like df[complete.cases(df), ] in R
df.dropna(axis=1)   # Drop columns with any missing values
df.fillna(0)        # Fill missing values with 0 - like df[is.na(df)] <- 0 in R
df.fillna(df.mean())  # Fill with mean - like df$col[is.na(df$col)] <- mean(df$col, na.rm=TRUE) in R

# Remove duplicates
df.drop_duplicates()  # Like df[!duplicated(df), ] in R
df.drop_duplicates(subset=['name'])  # Based on specific columns - like df[!duplicated(df$name), ] in R

# Data type conversion
df['age'] = df['age'].astype(float)  # Like as.numeric(df$age) in R
df['date'] = pd.to_datetime(df['date'])  # Like as.Date(df$date) in R

# Pandas string operations (using .str accessor)
df['name'].str.upper()       # Convert to uppercase
df['name'].str.lower()       # Convert to lowercase
df['name'].str.len()         # String length
df['name'].str.strip()       # Remove whitespace
df['name'].str.split(' ')    # Split strings
df['email'].str.replace('@', '[at]')  # Replace
df['email'].str.contains('gmail')  # Contains pattern

# Sample data
df = pd.DataFrame({
    'name': ['Alice Smith', 'Bob Johnson', 'Charlie Brown'],
    'email': ['alice@email.com', 'BOB@EMAIL.COM', 'charlie@email.com'],
    'phone': ['123-456-7890', '234-567-8901', '345-678-9012']
})

# Case conversion
df['name'].str.upper()      # Convert to uppercase
df['name'].str.lower()        # Convert to lowercase
df['name'].str.title()      # Title case (first letter of each word)
df['name'].str.capitalize() # Capitalize first letter only

# String length
df['name'].str.len()        # Length of each string

# Strip whitespace
df['name'].str.strip()      # Remove leading/trailing whitespace
df['name'].str.lstrip()    # Remove leading whitespace
df['name'].str.rstrip()    # Remove trailing whitespace

# Split strings
df['name'].str.split(' ')   # Split by space, returns list
df['name'].str.split(' ', expand=True)  # Split into separate columns
df['name'].str.split(' ', n=1)  # Split into max n parts

# Extract parts
df['name'].str[0]           # First character
df['name'].str[:5]          # First 5 characters
df['name'].str[-5:]         # Last 5 characters

# Replace
df['email'].str.replace('@', '[at]')  # Replace substring
df['phone'].str.replace('-', '')     # Remove dashes

# Contains, startswith, endswith
df['email'].str.contains('gmail')    # Boolean: contains substring
df['email'].str.startswith('alice')  # Boolean: starts with
df['email'].str.endswith('.com')      # Boolean: ends with

# Count occurrences
df['name'].str.count('a')  # Count occurrences of substring

# Find position
df['email'].str.find('@')  # Position of first occurrence (-1 if not found)
df['email'].str.rfind('@') # Position of last occurrence

# Extract patterns
df['email'].str.extract(r'(\w+)@(\w+)\.(\w+)')  # Extract groups
# Returns DataFrame with columns for each group

# Extract all matches
df['text'].str.extractall(r'(\d+)')  # Extract all numbers

# Replace with regex
df['phone'].str.replace(r'\D', '')  # Remove all non-digits

# Match pattern
df['email'].str.match(r'^[a-z]+@')  # Boolean: matches pattern from start

# Contains pattern
df['text'].str.contains(r'\d+')  # Contains digits

# Concatenate columns
df['full_info'] = df['name'].str.cat(df['email'], sep=' | ')

# Concatenate with separator
df['name'].str.cat(sep=', ')  # Join all values with separator

# Concatenate multiple columns
df['full'] = df[['name', 'email']].apply(lambda x: ' | '.join(x), axis=1)

# Extract first and last name
df[['first_name', 'last_name']] = df['name'].str.split(' ', n=1, expand=True)

# Normalize email to lowercase
df['email'] = df['email'].str.lower()

# Extract domain from email
df['domain'] = df['email'].str.extract(r'@(\w+\.\w+)')

# Clean phone numbers (remove non-digits)
df['phone_clean'] = df['phone'].str.replace(r'\D', '')

# Check if email is valid format
df['valid_email'] = df['email'].str.match(r'^[\w\.-]+@[\w\.-]+\.\w+$')

# Pad strings
df['id'].str.zfill(5)  # Zero-pad to 5 digits: '123' -> '00123'
df['name'].str.pad(width=20, side='right', fillchar='-')  # Pad with dashes

# Pandas datetime operations
df['date'] = pd.to_datetime(df['date'])  # Convert to datetime
df['date'].dt.year                       # Extract year
df['date'].dt.month                      # Extract month
df['date'].dt.day                        # Extract day
df['date'] + pd.Timedelta(days=7)       # Add days
df['date'] - other_date                 # Date difference

# Convert to datetime
df['date'] = pd.to_datetime(df['date'])
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')  # Specify format
df['date'] = pd.to_datetime(df['date'], errors='coerce')  # Convert errors to NaT

# Create datetime from components
pd.to_datetime({'year': [2024], 'month': [1], 'day': [15]})

# Current timestamp
pd.Timestamp.now()
pd.Timestamp.today()

# Create date range
pd.date_range('2024-01-01', periods=10, freq='D')  # Daily
pd.date_range('2024-01-01', '2024-01-31', freq='D')

# Set datetime as index
df.set_index('date', inplace=True)

# Access by date
df.loc['2024-01-15']           # Specific date
df.loc['2024-01']              # Entire month
df.loc['2024-01-15':'2024-01-20']  # Date range

# Partial string indexing
df.loc['2024']                  # Entire year
df.loc['2024-01':'2024-03']     # Quarter

df['date'].dt.year              # Year
df['date'].dt.month             # Month (1-12)
df['date'].dt.day               # Day of month
df['date'].dt.dayofweek         # Day of week (0=Monday)
df['date'].dt.day_name()        # Day name ('Monday', 'Tuesday', etc.)
df['date'].dt.quarter           # Quarter (1-4)
df['date'].dt.week              # Week number
df['date'].dt.is_month_start    # Boolean: is first day of month
df['date'].dt.is_month_end      # Boolean: is last day of month
df['date'].dt.is_quarter_start  # Boolean: is first day of quarter
df['date'].dt.is_year_start     # Boolean: is first day of year

# Time components (if datetime includes time)
df['datetime'].dt.hour          # Hour (0-23)
df['datetime'].dt.minute         # Minute
df['datetime'].dt.second         # Second
df['datetime'].dt.time           # Time component only

# Date differences
df['date'] - pd.Timestamp('2024-01-01')  # Timedelta
(df['date'] - pd.Timestamp('2024-01-01')).dt.days  # Days difference

# Add/subtract time
df['date'] + pd.Timedelta(days=7)        # Add 7 days
df['date'] + pd.Timedelta(weeks=2)       # Add 2 weeks
df['date'] + pd.Timedelta(months=1)      # Add 1 month (approximate)
df['date'] - pd.Timedelta(hours=24)      # Subtract 24 hours

# Date offsets (more precise)
from pandas.tseries.offsets import Day, MonthEnd, BDay
df['date'] + Day(7)                      # Add 7 days
df['date'] + MonthEnd()                  # Move to end of month
df['date'] + BDay(5)                     # Add 5 business days

# Convert to string
df['date'].dt.strftime('%Y-%m-%d')      # Format as string
df['date'].dt.strftime('%B %d, %Y')     # 'January 15, 2024'
df['date'].dt.strftime('%A, %B %d')    # 'Monday, January 15'

# Common format codes:
# %Y - 4-digit year, %y - 2-digit year
# %m - Month (01-12), %B - Full month name, %b - Abbreviated month
# %d - Day (01-31), %A - Full weekday, %a - Abbreviated weekday
# %H - Hour (00-23), %M - Minute, %S - Second

# Set datetime index
df.set_index('date', inplace=True)

# Resample to different frequencies
df.resample('D').mean()         # Daily mean
df.resample('W').sum()          # Weekly sum
df.resample('M').mean()          # Monthly mean
df.resample('Q').mean()          # Quarterly mean
df.resample('Y').sum()          # Yearly sum

# Common frequency aliases:
# 'D' - Daily, 'W' - Weekly, 'M' - Month end, 'MS' - Month start
# 'Q' - Quarter end, 'QS' - Quarter start, 'Y' - Year end, 'YS' - Year start
# 'H' - Hourly, 'T' or 'min' - Minute, 'S' - Second

# Resample with different methods
df.resample('D').agg({
    'value': ['mean', 'sum', 'count'],
    'price': 'last'  # Last value in period
})

# Resample with label and convention
df.resample('M', label='right', convention='end').mean()

# Rolling mean
df['value'].rolling(window=7).mean()        # 7-day rolling mean
df['value'].rolling(window='30D').mean()   # 30-day rolling mean (datetime index)

# Rolling statistics
df['value'].rolling(7).sum()               # Rolling sum
df['value'].rolling(7).std()                # Rolling standard deviation
df['value'].rolling(7).min()               # Rolling minimum
df['value'].rolling(7).max()               # Rolling maximum

# Expanding window
df['value'].expanding().mean()              # Expanding mean (cumulative)
df['value'].expanding().sum()               # Cumulative sum

# Calculate age from birthdate
df['age'] = (pd.Timestamp.now() - df['birthdate']).dt.days / 365.25

# Extract year-month for grouping
df['year_month'] = df['date'].dt.to_period('M')

# Find records from last 30 days
recent = df[df['date'] >= pd.Timestamp.now() - pd.Timedelta(days=30)]

# Group by month
df.groupby(df['date'].dt.month).sum()

# Calculate days between dates
df['days_diff'] = (df['end_date'] - df['start_date']).dt.days

# Business days between dates
from pandas.tseries.offsets import BDay
business_days = len(pd.bdate_range(df['start_date'], df['end_date']))

# Group by
df.groupby('city')['age'].mean()  # Mean age by city
df.groupby('city').agg({
    'age': 'mean',
    'salary': ['mean', 'sum', 'count']
})

# Multiple groupby columns
df.groupby(['city', 'department']).mean()

# Pivot tables
df.pivot_table(values='salary', index='city', columns='department', aggfunc='mean')

# Python/Pandas - Count (like R table())
df.pivot_table(values='salary', index='city', columns='department', aggfunc='count')

# Python/Pandas - Mean (like R aggregate())
df.pivot_table(values='salary', index='city', columns='department', aggfunc='mean')

# Python/Pandas - Sum (like R xtabs())
df.pivot_table(values='salary', index='city', columns='department', aggfunc='sum')

df.pivot_table(
    values=None,      # Column(s) to aggregate
    index=None,       # Column(s) to use as row index
    columns=None,     # Column(s) to use as column headers
    aggfunc='mean',   # Aggregation function(s)
    fill_value=None,  # Value to replace missing values
    margins=False,    # Add row/column totals
    dropna=True       # Drop columns with all NaN
)

# Sample data
df = pd.DataFrame({
    'city': ['NYC', 'NYC', 'LA', 'LA', 'NYC', 'LA'],
    'department': ['Sales', 'IT', 'Sales', 'IT', 'Sales', 'IT'],
    'salary': [50000, 60000, 55000, 65000, 52000, 62000],
    'bonus': [5000, 6000, 5500, 6500, 5200, 6200]
})

# Basic pivot: mean salary by city and department
df.pivot_table(values='salary', index='city', columns='department', aggfunc='mean')
# Output:
# department      IT    Sales
# city
# LA           63500   55000
# NYC          60000   51000

# Multiple aggregation functions
df.pivot_table(values='salary', index='city', columns='department', 
               aggfunc=['mean', 'sum', 'count'])
# Creates multi-level columns with mean, sum, and count

# Multiple values
df.pivot_table(values=['salary', 'bonus'], index='city', columns='department', 
               aggfunc='mean')
# Aggregates both salary and bonus columns

# Custom aggregation function
df.pivot_table(values='salary', index='city', columns='department', 
               aggfunc=lambda x: x.max() - x.min())  # Range

# Fill missing values
df.pivot_table(values='salary', index='city', columns='department', 
               aggfunc='mean', fill_value=0)

# Add margins (row and column totals)
df.pivot_table(values='salary', index='city', columns='department', 
               aggfunc='mean', margins=True)
# Output includes 'All' row and column with totals

# Multiple index columns
df.pivot_table(values='salary', index=['city', 'year'], columns='department', 
               aggfunc='mean')
# Creates hierarchical row index

# Multiple column levels
df.pivot_table(values='salary', index='city', 
               columns=['department', 'quarter'], aggfunc='mean')
# Creates hierarchical column index

# Dictionary of aggregation functions for different values
df.pivot_table(values=['salary', 'bonus'], index='city', columns='department',
               aggfunc={'salary': 'mean', 'bonus': 'sum'})

# pivot() - no aggregation, fails with duplicates
df.pivot(index='city', columns='department', values='salary')  # May fail if duplicates exist

# pivot_table() - handles duplicates with aggregation
df.pivot_table(values='salary', index='city', columns='department', aggfunc='mean')

# Pandas apply functions
df.apply(np.mean)                # Apply to columns (axis=0, default)
df.apply(np.sum, axis=1)         # Apply to rows (axis=1)
df['col'].apply(lambda x: x**2)   # Apply to Series
df.apply(lambda x: x.mean())      # Apply to each column
df['col'].map(lambda x: x*2)     # Element-wise mapping

# On Series: applies function to each element
s = pd.Series([1, 2, 3, 4])
s.apply(lambda x: x**2)  # Square each element
s.apply(np.sqrt)         # Square root of each element

# On DataFrame: applies function along axis (default: axis=0, columns)
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# Apply to each column (axis=0, default)
df.apply(np.mean)        # Mean of each column
df.apply(lambda x: x.max() - x.min())  # Range of each column

# Apply to each row (axis=1)
df.apply(np.sum, axis=1)  # Sum of each row
df.apply(lambda row: row['A'] + row['B'], axis=1)  # Custom row operation

# Apply with multiple return values (returns DataFrame)
def stats(x):
    return pd.Series([x.mean(), x.std()], index=['mean', 'std'])
df.apply(stats)  # Returns DataFrame with mean and std for each column

# Apply with result_type
df.apply(lambda x: [x.min(), x.max()], axis=1, result_type='expand')
# Expands list into separate columns

# Map values using a dictionary
s = pd.Series(['a', 'b', 'c', 'a'])
mapping = {'a': 1, 'b': 2, 'c': 3}
s.map(mapping)  # Maps each value: a->1, b->2, c->3

# Map using a function
s.map(str.upper)  # Convert to uppercase
s.map(lambda x: x.upper())  # Same as above

# Map with Series (aligns by index)
s1 = pd.Series([1, 2, 3], index=['a', 'b', 'c'])
s2 = pd.Series(['one', 'two', 'three'], index=['a', 'b', 'c'])
s1.map(s2)  # Maps 1->'one', 2->'two', 3->'three'

# Map with NaN handling
s.map(mapping, na_action='ignore')  # Ignore NaN values

# applymap() is deprecated, use map() instead
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# Old way (deprecated)
# df.applymap(lambda x: x**2)

# New way (use map())
df.map(lambda x: x**2)  # Square each element
df.map(str)             # Convert each element to string

# Use apply() when:
# - Aggregating along axis (mean, sum, etc.)
df.apply(np.mean)  # Mean of each column

# - Complex row/column operations
df.apply(lambda row: row['A'] * row['B'], axis=1)  # Row-wise calculation

# - Need multiple return values
df.apply(lambda x: pd.Series([x.min(), x.max()]))

# Use map() when:
# - Simple value mapping (dictionary lookup)
df['category'].map({'A': 1, 'B': 2, 'C': 3})

# - Element-wise transformation on Series
df['name'].map(str.upper)

# - Element-wise transformation on DataFrame
df.map(lambda x: x * 2)  # Double each value

# Vectorized operations are faster than apply()
# Slow:
df['result'] = df.apply(lambda row: row['A'] + row['B'], axis=1)

# Fast:
df['result'] = df['A'] + df['B']

# Use apply() only when vectorization isn't possible

# Pandas rolling functions
df['value'].rolling(window=7).mean()   # 7-period rolling mean
df['value'].rolling(window=7).sum()     # 7-period rolling sum
df['value'].rolling(window=7).std()     # 7-period rolling std
df['value'].ewm(span=7).mean()         # Exponentially weighted moving average

# Basic rolling window
df['value'].rolling(window=3).mean()      # 3-period rolling mean
df['value'].rolling(window=7).sum()       # 7-period rolling sum
df['value'].rolling(window=5).std()       # 5-period rolling std

# Rolling window with datetime index
df.set_index('date', inplace=True)
df['value'].rolling(window='7D').mean()   # 7-day rolling mean
df['value'].rolling(window='30D').sum()   # 30-day rolling sum
df['value'].rolling(window='1M').mean()   # 1-month rolling mean

# Window types
df['value'].rolling(7, center=True).mean()  # Centered window
df['value'].rolling(7, min_periods=3).mean()  # Minimum periods required

# Multiple statistics
df['value'].rolling(7).agg(['mean', 'std', 'min', 'max'])

# Rolling on multiple columns
df[['A', 'B']].rolling(7).mean()

# Expanding mean (cumulative mean)
df['value'].expanding().mean()    # Running mean from start
df['value'].expanding().sum()     # Cumulative sum
df['value'].expanding().std()     # Expanding standard deviation
df['value'].expanding().min()    # Running minimum
df['value'].expanding().max()     # Running maximum

# Expanding with minimum periods
df['value'].expanding(min_periods=5).mean()  # Start after 5 periods

# Exponentially weighted moving average
df['value'].ewm(span=7).mean()        # 7-period EWMA
df['value'].ewm(halflife=3).mean()   # Half-life of 3 periods
df['value'].ewm(alpha=0.3).mean()    # Smoothing factor alpha

# Different adjustment methods
df['value'].ewm(span=7, adjust=True).mean()   # Adjust for bias (default)
df['value'].ewm(span=7, adjust=False).mean()  # No adjustment (faster)

# EWM statistics
df['value'].ewm(span=7).std()   # Exponentially weighted std
df['value'].ewm(span=7).var()   # Exponentially weighted variance

# Moving average crossover strategy
df['ma_short'] = df['price'].rolling(5).mean()
df['ma_long'] = df['price'].rolling(20).mean()
df['signal'] = (df['ma_short'] > df['ma_long']).astype(int)

# Rolling correlation
df['A'].rolling(30).corr(df['B'])  # 30-period rolling correlation

# Percentage change over rolling window
df['pct_change'] = df['value'].rolling(7).apply(
    lambda x: (x.iloc[-1] - x.iloc[0]) / x.iloc[0]
)

# Z-score (standardization) over rolling window
df['zscore'] = (df['value'] - df['value'].rolling(30).mean()) / \
                df['value'].rolling(30).std()

# Maximum drawdown (rolling)
df['rolling_max'] = df['value'].expanding().max()
df['drawdown'] = (df['value'] - df['rolling_max']) / df['rolling_max']

# Volatility (rolling standard deviation of returns)
df['returns'] = df['price'].pct_change()
df['volatility'] = df['returns'].rolling(30).std() * np.sqrt(252)  # Annualized

# Pandas reshaping functions
df.melt(id_vars=['id'], value_vars=['Q1', 'Q2', 'Q3'],
        var_name='quarter', value_name='sales')  # Wide to long
df.pivot_table(index='id', columns='quarter', values='sales')  # Long to wide
df.stack()  # Stack columns to index
df.unstack()  # Unstack index to columns

# Sample wide data
df = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'Q1': [100, 200, 150],
    'Q2': [110, 210, 160],
    'Q3': [120, 220, 170]
})

# Melt: convert columns to rows
df_melted = df.melt(
    id_vars=['id', 'name'],      # Columns to keep as identifiers
    value_vars=['Q1', 'Q2', 'Q3'],  # Columns to melt (optional)
    var_name='quarter',          # Name for variable column
    value_name='sales'           # Name for value column
)

# Result:
#    id     name quarter  sales
# 0   1    Alice      Q1    100
# 1   2      Bob      Q1    200
# 2   3  Charlie      Q1    150
# 3   1    Alice      Q2    110
# ...

# Melt all columns except id_vars
df.melt(id_vars=['id', 'name'], var_name='quarter', value_name='sales')

# Stack: moves column level to index level
df = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6]
}, index=['x', 'y', 'z'])

df_stacked = df.stack()  # Creates MultiIndex Series
# Result:
# x  A    1
#    B    4
# y  A    2
#    B    5
# z  A    3
#    B    6

# Stack specific level (for MultiIndex columns)
df.columns = pd.MultiIndex.from_tuples([('X', 'A'), ('X', 'B')])
df.stack(level=0)  # Stack first level

# Unstack: moves index level to column level
df = pd.DataFrame({
    'value': [1, 2, 3, 4, 5, 6]
}, index=pd.MultiIndex.from_tuples([
    ('x', 'A'), ('x', 'B'),
    ('y', 'A'), ('y', 'B'),
    ('z', 'A'), ('z', 'B')
], names=['level1', 'level2']))

df_unstacked = df.unstack()  # Moves level2 to columns
# Result:
#        value
# level2    A    B
# level1
# x         1    2
# y         3    4
# z         5    6

# Unstack specific level
df.unstack(level='level2')
df.unstack(level=1)  # By position

# Unstack with fill_value
df.unstack(fill_value=0)  # Fill NaN with 0

# Wide to long: Multiple measurements per subject
df_wide = pd.DataFrame({
    'subject': ['A', 'B', 'C'],
    'baseline': [10, 20, 15],
    'week1': [12, 22, 17],
    'week2': [14, 24, 19]
})

df_long = df_wide.melt(
    id_vars='subject',
    value_vars=['baseline', 'week1', 'week2'],
    var_name='timepoint',
    value_name='measurement'
)

# Long to wide: pivot_table alternative
df_long.pivot_table(
    index='subject',
    columns='timepoint',
    values='measurement'
)

# Stack/unstack for hierarchical data
df = pd.DataFrame({
    'Q1': {'A': 100, 'B': 200},
    'Q2': {'A': 110, 'B': 210}
})
df_stacked = df.stack()  # Wide to long
df_unstacked = df_stacked.unstack()  # Long to wide

# Merge DataFrames
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'C', 'D'], 'value2': [4, 5, 6]})

# Inner join (default)
pd.merge(df1, df2, on='key')

# Left join
pd.merge(df1, df2, on='key', how='left')

# Right join
pd.merge(df1, df2, on='key', how='right')

# Outer join
pd.merge(df1, df2, on='key', how='outer')

# Concatenate
pd.concat([df1, df2], axis=0)  # Stack vertically
pd.concat([df1, df2], axis=1)  # Stack horizontally

# Sort by column
df.sort_values('age')                    # Ascending
df.sort_values('age', ascending=False)   # Descending
df.sort_values(['city', 'age'])          # Multiple columns

# Sort by index
df.sort_index()

# Method 1: .values (legacy, still works but deprecated in favor of .to_numpy())
arr = df.values  # Returns NumPy array (may be object dtype if mixed types)

# Method 2: .to_numpy() (recommended - more explicit and flexible)
arr = df.to_numpy()  # Returns NumPy array
arr = df.to_numpy(dtype='float64')  # Specify data type
arr = df.to_numpy(na_value=0)  # Replace NaN with 0

# Convert specific columns to NumPy
arr = df[['age', 'salary']].to_numpy()  # Only selected columns

# Convert single column (Series) to NumPy
arr = df['age'].to_numpy()  # Returns 1D array
arr = df['age'].values  # Alternative (legacy)

# Important considerations:
# - Mixed data types: DataFrame with mixed types will result in object dtype array
# - Missing values: NaN values are preserved in the array (use na_value parameter)
# - Index and column names: Lost when converting to NumPy array
# - Memory: NumPy arrays are more memory-efficient for homogeneous numeric data

# Example with numeric DataFrame
df_numeric = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
arr = df_numeric.to_numpy()
print(arr)
# Output:
# [[1 4]
#  [2 5]
#  [3 6]]
print(type(arr))  # <class 'numpy.ndarray'>

# Example with mixed types (results in object array)
df_mixed = pd.DataFrame({'A': [1, 2, 3], 'B': ['x', 'y', 'z']})
arr = df_mixed.to_numpy()
print(arr.dtype)  # object (because of mixed types)

import matplotlib.pyplot as plt

# Line plot
df['age'].plot(kind='line')
df.plot(x='name', y='age', kind='line')

# Bar plot
df['age'].plot(kind='bar')
df.plot(x='name', y='age', kind='bar')

# Horizontal bar plot
df.plot(x='name', y='age', kind='barh')

# Histogram
df['age'].plot(kind='hist', bins=10)

# Box plot
df['age'].plot(kind='box')

# Scatter plot
df.plot(x='age', y='salary', kind='scatter')

# Pie chart
df['city'].value_counts().plot(kind='pie')