Pandas¶

# Import Pandas
import pandas as pd
pd.__version__ 

# only used in this cheat sheet to display the DataFrames
# from IPython.display import display 

# Create a Series from a list
s = pd.Series([10, 20, 30, 40], index=['a', 'b', 'c', 'd'])
print("Series s:")
display(s)

# Create a Series from a dictionary
data_dict = {'x': 100, 'y': 200, 'z': 300}
s_dict = pd.Series(data_dict)
print("\nSeries s_dict:")
display(s_dict)

# Accessing elements
print("\nAccess element by label s['b']:", s['b'])
print("Access element by position s.iloc[2]:", s.iloc[2])

# Create DataFrame from a dictionary of lists
df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [25, 30, 35],
    'City': ['New York', 'Paris', 'London']
})
print("DataFrame df:")
display(df)

# Create DataFrame from a list of lists
df2 = pd.DataFrame(
    [[1, 2, 3],
     [4, 5, 6],
     [7, 8, 9]],
    columns=['ColA', 'ColB', 'ColC']
)
print("\nDataFrame df2:")
display(df2)

# Check basic attributes
print("\nShape of df2:", df2.shape)
print("Columns of df2:", df2.columns)
print("Index of df2:", df2.index)

import numpy as np

# Create a MultiIndex from tuples
tuples = [('Group1', 'Row1'), ('Group1', 'Row2'),
          ('Group2', 'Row1'), ('Group2', 'Row2')]
multi_index = pd.MultiIndex.from_tuples(tuples, names=['Group', 'Row'])

# Create a DataFrame using the MultiIndex
df_multi = pd.DataFrame(
    np.random.randn(4, 2),
    index=multi_index,
    columns=['ColX', 'ColY']
)
print("MultiIndex DataFrame:\n")
display(df_multi)
print("\nIndex levels:", df_multi.index.levels)

# Using the df we created above
print("Original df:")
display(df)

# Select a single column by label
print("\nSingle column df['Name']:")
display(df['Name'])

# Using .loc (label-based)
print("\nUsing df.loc[0, 'Age']:")
display(df.loc[0, 'Age'])
print("\nUsing df.loc[0:1, ['Name', 'City']]:")
display(df.loc[0:1, ['Name', 'City']])

# Using .iloc (integer position-based)
print("\nUsing df.iloc[1, 2]:")  # second row, third column
display(df.iloc[1, 2])
print("\nUsing df.iloc[0:2, 0:2]:")
display(df.iloc[0:2, 0:2])

# Boolean indexing
print("\nRows where Age > 25:")
display(df[df['Age'] > 25])

# Example DataFrame
df_wide = pd.DataFrame({
    'id': [1, 2, 3],
    'varA': [10, 20, 30],
    'varB': [40, 50, 60]
})
print("Wide DataFrame:")
display(df_wide)

# Melt (wide -> long)
df_long = pd.melt(df_wide, id_vars='id', var_name='variable', value_name='value')
print("\nMelted (long) DataFrame:")
display(df_long)

# Pivot (long -> wide)
df_pivoted = df_long.pivot(index='id', columns='variable', values='value')
print("\nPivoted (wide) DataFrame:")
display(df_pivoted)

# Concat examples
df_part1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df_part2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})
df_concat = pd.concat([df_part1, df_part2], ignore_index=True)
print("\nConcatenated DataFrame:")
display(df_concat)

# Using df_wide again
print("df_wide:")
display(df_wide)

# Subset columns
subset_columns = df_wide[['id', 'varA']]
print("\nSubset of columns [id, varA]:")
display(subset_columns)

# Subset rows by index slicing
subset_rows = df_wide.iloc[0:2]  # first two rows
print("\nSubset of rows (first two rows):")
display(subset_rows)

# Subset using a condition
condition_subset = df_wide[df_wide['varA'] > 10]
print("\nSubset where varA > 10:")
display(condition_subset)

import numpy as np

df_stats = pd.DataFrame({
    'Col1': np.random.randint(0, 100, 5),
    'Col2': np.random.randint(0, 100, 5),
    'Col3': np.random.randint(0, 100, 5),
    'Category': ['A', 'B', 'A', 'C', 'B'],
    'Status': ['Active', 'Inactive', 'Active', 'Active', 'Inactive']
})
print("DataFrame with mixed types:")
display(df_stats)

# Basic stats (numeric columns only)
print("\nNumerical Statistics (describe):")
display(df_stats.describe())

# Basic stats for object columns
print("\nCategorical Statistics (describe):")
display(df_stats.describe(include=['object']))

# Value counts for categorical columns
print("\nCategory value counts:")
display(df_stats['Category'].value_counts())
print("\nStatus value counts:")
display(df_stats['Status'].value_counts())

# Mean of numeric columns
print("\nMean of numeric columns:")
display(df_stats.mean(numeric_only=True))

# Count of all columns (works for both numeric and object types)
print("\nCount of all columns:")
display(df_stats.count())

# Create a DataFrame with NaN values
df_missing = pd.DataFrame({
    'A': [1, np.nan, 3, np.nan],
    'B': [5, 6, np.nan, 8]
})
print("Original df_missing:")
display(df_missing)

# Detect missing values
print("\nMissing value check:")
display(df_missing.isnull())

# Drop rows with any missing values
print("\nDrop rows with any NaN:")
display(df_missing.dropna())

# Fill missing values with a constant
filled_df = df_missing.fillna(0)
print("\nFill NaN with 0:")
display(filled_df)

df_new_cols = pd.DataFrame({
    'Length': [2, 3, 4],
    'Width': [5, 6, 7]
})
print("Original df_new_cols:")
display(df_new_cols)

# Direct assignment
df_new_cols['Area'] = df_new_cols['Length'] * df_new_cols['Width']
print("\nAdded 'Area' column:")
display(df_new_cols)

# Using assign (returns a copy)
df_assigned = df_new_cols.assign(Perimeter=lambda x: 2 * (x['Length'] + x['Width']))
print("\nAssigned 'Perimeter' column:")
display(df_assigned)

df_group = pd.DataFrame({
    'Category': ['A', 'A', 'B', 'B', 'C'],
    'Values': [10, 15, 10, 25, 5]
})

print("df_group:")
display(df_group)

# Group by 'Category'
grouped = df_group.groupby('Category')
print("\nSum by Category:")
display(grouped['Values'].sum())
print("\nMean by Category:")
display(grouped['Values'].mean())

# Multiple aggregation methods at once
agg_results = grouped['Values'].agg(['min', 'max', 'mean', 'count'])
print("\nMultiple aggregations:")
display(agg_results)

# Create a Series for rolling example
s_rolling = pd.Series([1, 2, 3, 4, 5, 6, 7])
print("Original Series:")
display(s_rolling)

# Rolling window of size 3, compute mean
rolling_mean = s_rolling.rolling(3).mean()
print("\nRolling mean with window=3:")
display(rolling_mean)

# Expanding: cumulative computations
expanding_sum = s_rolling.expanding().sum()
print("\nExpanding sum:")
display(expanding_sum)

left = pd.DataFrame({'key': ['K0', 'K1', 'K2'], 'A': ['A0', 'A1', 'A2']})
right = pd.DataFrame({'key': ['K0', 'K2', 'K3'], 'B': ['B0', 'B2', 'B3']})

print("left:")
display(left)
print("\nright:")
display(right)

# Merge using an inner join
merged_inner = pd.merge(left, right, on='key', how='inner')
print("\nMerged (inner join):")
display(merged_inner)

# Merge using an outer join
merged_outer = pd.merge(left, right, on='key', how='outer')
print("\nMerged (outer join):")
display(merged_outer)

# Concat example (stacking rows)
concat_example = pd.concat([left, right], axis=0, ignore_index=True)
print("\nConcatenated (stack rows):")
display(concat_example)

# Create sample DataFrames with multiple columns
left = pd.DataFrame({
    'id_left': ['K0', 'K1', 'K2'],
    'name': ['A0', 'A1', 'A2'],
    'score': [10, 20, 30]
})
right = pd.DataFrame({
    'id_right': ['K0', 'K2', 'K3'],
    'name': ['B0', 'B2', 'B3'],
    'score': [15, 35, 45]
})

# Merge with different key columns and suffixes
merged = pd.merge(
    left, right,
    left_on=['id_left', 'name'],
    right_on=['id_right', 'name'],
    how='outer',
    suffixes=('_L', '_R')
)
print("Merged with multiple keys and suffixes:")
display(merged)

import matplotlib.pyplot as plt

df_plot = pd.DataFrame({
    'x': [1, 2, 3, 4, 5],
    'y': [3, 5, 2, 8, 7]
})

# Line plot
df_plot.plot(x='x', y='y', kind='line', title='Line Plot')
plt.show()

# Scatter plot
df_plot.plot.scatter(x='x', y='y', title='Scatter Plot')
plt.show()

print("df_plot info:")
display(df_plot.info())

print("\nDataFrame attributes:")
print("Shape:", df_plot.shape)
print("Columns:", df_plot.columns)
print("Index:", df_plot.index)
print("\nDataFrame dtypes:")
display(df_plot.dtypes)

print("\nDescriptive statistics:")
display(df_plot.describe())

print("df_stats")
display(df_stats)

# Apply a function to each column
print("Applying sum to each column:")
display(df_stats[['Col1', 'Col2']].apply(sum))

# Apply a function to each element
df_applied = df_stats.applymap(lambda x: x * 2)
print("\nApplying lambda x: x * 2 to each element:")
display(df_applied)

# For a single column (Series)
df_stats_col1_applied = df_stats['Col1'].apply(lambda x: x + 10)
print("\nAdding 10 to each element in Col1:")
display(df_stats_col1_applied)

# Apply function to two columns row-wise
temp_df = df_stats.copy() # copyinf DataFrame
temp_df['row_sums'] = temp_df.apply(lambda x: x['Col1'] + x['Col2'], axis=1)
print("\nSum of Col1 and Col2:")
display(temp_df)

s1 = pd.Series([1, 2, 3], index=['a', 'b', 'c'])
s2 = pd.Series([4, 5, 6], index=['b', 'c', 'd'])

print("s1:")
display(s1)
print("\ns2:")
display(s2)

# Addition auto-aligns on index
s_sum = s1 + s2
print("\nAuto-aligned sum:")
display(s_sum)

# Fill missing with 0 while adding
s_sum_fill = s1.add(s2, fill_value=0)
print("\nSum with fill_value=0:")
display(s_sum_fill)

df_chain = pd.DataFrame({
    'A': [1, 2, 3, 4],
    'B': [5, 6, 7, 8]
})

# Example of method chaining: melt, rename, query
df_chain_melted = (
    df_chain
    .melt(var_name='Variable', value_name='Value')
    .rename(columns={'Variable': 'var', 'Value': 'val'})
    .query('val > 3')
)
display(df_chain_melted)

df_sample = pd.DataFrame({'ColA': [5, 2, 9, 1, 7, 3]})
print("Original Data:")
display(df_sample)

# Random sample of 3 rows
sampled = df_sample.sample(n=3)
print("\nRandom sample of 3 rows:")
display(sampled)

# 2 largest values in ColA
largest_two = df_sample.nlargest(2, 'ColA')
print("\n2 largest in ColA:")
display(largest_two)

# 2 smallest values in ColA
smallest_two = df_sample.nsmallest(2, 'ColA')
print("\n2 smallest in ColA:")
display(smallest_two)

df_dup = pd.DataFrame({
    'X': [1, 1, 2, 2, 3],
    'Y': [10, 10, 20, 30, 30]
})
print("Original Data:")
display(df_dup)

df_no_dup = df_dup.drop_duplicates()
print("\nAfter drop_duplicates:")
display(df_no_dup)

df_counts = pd.Series(['apple', 'banana', 'apple', 'orange', 'banana'])
print("Original Series:")
display(df_counts)

print("\nValue counts:")
display(df_counts.value_counts())
print("\nNumber of unique values:")
display(df_counts.nunique())

df_filter = pd.DataFrame({
    'width_cm': [10, 15, 20],
    'height_cm': [5, 8, 12],
    'depth_m': [0.5, 0.8, 1.2]
})

# Filter columns that contain '_cm'
cm_cols = df_filter.filter(regex='_cm$')
print("Original DataFrame:")
display(df_filter)
print("\nColumns that end with '_cm':")
display(cm_cols)

df_query = pd.DataFrame({
    'col1': [5, 10, 15],
    'col2': [2, 4, 6]
})
print("Original Data:")
display(df_query)

# Filter rows where col1 > 5 AND col2 < 6
filtered_query = df_query.query("col1 > 5 and col2 < 6")
print("\nFiltered via query:")
display(filtered_query)

df_pivot_example = pd.DataFrame({
    'month': ['Jan', 'Jan', 'Feb', 'Feb'],
    'category': ['A', 'B', 'A', 'B'],
    'value': [10, 20, 30, 40]
})

print("Original DataFrame:")
display(df_pivot_example)

# pivot will fail if there are duplicate entries for month & category
# pivot_table can aggregate duplicates:
df_pivoted_tbl = df_pivot_example.pivot_table(
    index='month',
    columns='category',
    values='value',
    aggfunc='sum'
)
print("\nPivot Table with aggregation:")
display(df_pivoted_tbl)

df_cum = pd.DataFrame({'vals': [100, 200, 200, 300]})
print("Original Data:")
display(df_cum)

# rank with 'dense' method
df_cum['rank'] = df_cum['vals'].rank(method='dense')
print("\nRank (dense) on 'vals':")
display(df_cum)

# shift by 1
df_cum['shifted_vals'] = df_cum['vals'].shift(1)
print("\nAfter shifting 'vals' by 1:")
display(df_cum)

# cumsum
df_cum['cumulative_sum'] = df_cum['vals'].cumsum()
print("\nCumulative sum of 'vals':")
display(df_cum)