Data Analysis with Pandas: Working with DataFrames

# Creating and Accessing DataFrames

import pandas as pd
import numpy as np

# === Creating DataFrames from dictionaries ===

# Dictionary with lists (columns)
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'Age': [25, 30, 35, 28, 32],
    'City': ['New York', 'San Francisco', 'Los Angeles', 'Chicago', 'Boston'],
    'Salary': [70000, 85000, 95000, 72000, 88000]
}

df = pd.DataFrame(data)
print("DataFrame from dictionary:")
print(df)
print()

# === Creating from lists ===

# List of lists
data_list = [
    ['Alice', 25, 'New York'],
    ['Bob', 30, 'San Francisco'],
    ['Charlie', 35, 'Los Angeles']
]

df_list = pd.DataFrame(data_list, columns=['Name', 'Age', 'City'])
print("DataFrame from list:")
print(df_list)
print()

# === Creating from NumPy array ===

array_data = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
df_array = pd.DataFrame(array_data, columns=['A', 'B', 'C'])
print("DataFrame from NumPy array:")
print(df_array)
print()

# === Creating with custom index ===

df_custom = pd.DataFrame(
    data,
    index=['emp1', 'emp2', 'emp3', 'emp4', 'emp5']
)
print("DataFrame with custom index:")
print(df_custom)
print()

# === Reading from CSV ===

# Create sample CSV
df.to_csv('sample_data.csv', index=False)

# Read CSV
df_csv = pd.read_csv('sample_data.csv')
print("DataFrame from CSV:")
print(df_csv.head())
print()

# === Basic DataFrame information ===

print("DataFrame shape:", df.shape)  # (rows, columns)
print("DataFrame size:", df.size)    # Total elements
print("Column names:", df.columns.tolist())
print("Index:", df.index.tolist())
print("Data types:")
print(df.dtypes)
print()

# === DataFrame statistics ===

print("Describe (numeric columns):")
print(df.describe())
print()

print("Info:")
print(df.info())
print()

# === Accessing data with head() and tail() ===

print("First 3 rows:")
print(df.head(3))
print()

print("Last 2 rows:")
print(df.tail(2))
print()

# === Selecting columns ===

# Single column (returns Series)
names = df['Name']
print("Names (Series):")
print(names)
print(type(names))
print()

# Multiple columns (returns DataFrame)
subset = df[['Name', 'Salary']]
print("Name and Salary:")
print(subset)
print()

# === Accessing with loc[] (label-based) ===

# Single row by index
row = df.loc[0]
print("First row (loc):")
print(row)
print()

# Multiple rows
rows = df.loc[0:2]  # Includes endpoint
print("Rows 0-2 (loc):")
print(rows)
print()

# Specific row and column
value = df.loc[0, 'Name']
print(f"Value at [0, 'Name']: {value}")
print()

# Multiple rows and columns
subset = df.loc[0:2, ['Name', 'Age']]
print("Subset with loc:")
print(subset)
print()

# All rows, specific columns
subset = df.loc[:, ['Name', 'Salary']]
print("All rows, Name and Salary:")
print(subset)
print()

# === Accessing with iloc[] (position-based) ===

# Single row by position
row = df.iloc[0]
print("First row (iloc):")
print(row)
print()

# Multiple rows
rows = df.iloc[0:3]  # Excludes endpoint
print("Rows 0-2 (iloc):")
print(rows)
print()

# Specific row and column by position
value = df.iloc[0, 1]  # First row, second column
print(f"Value at [0, 1]: {value}")
print()

# Multiple rows and columns by position
subset = df.iloc[0:3, [0, 3]]  # First 3 rows, columns 0 and 3
print("Subset with iloc:")
print(subset)
print()

# === Iterating through DataFrame ===

print("Iterating through rows:")
for index, row in df.iterrows():
    print(f"{row['Name']} is {row['Age']} years old")
print()

# === Setting values ===

# Set single value
df.loc[0, 'Age'] = 26
print("After updating age:")
print(df.head())
print()

# Set entire column
df['Country'] = 'USA'
print("After adding Country column:")
print(df.head())
print()

# Set values conditionally
df.loc[df['Age'] > 30, 'Category'] = 'Senior'
df.loc[df['Age'] <= 30, 'Category'] = 'Junior'
print("After adding Category:")
print(df)

# Filtering and Data Manipulation

import pandas as pd
import numpy as np

# Create sample DataFrame
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank'],
    'Age': [25, 30, 35, 28, 32, 27],
    'City': ['New York', 'San Francisco', 'Los Angeles', 'Chicago', 'Boston', 'Seattle'],
    'Salary': [70000, 85000, 95000, 72000, 88000, 76000],
    'Department': ['Sales', 'Engineering', 'Sales', 'Engineering', 'Sales', 'Marketing']
}

df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)
print()

# === Boolean indexing (filtering) ===

# Single condition
high_salary = df[df['Salary'] > 80000]
print("Employees with salary > 80000:")
print(high_salary)
print()

# Multiple conditions (AND)
young_high_earners = df[(df['Age'] < 30) & (df['Salary'] > 75000)]
print("Young high earners:")
print(young_high_earners)
print()

# Multiple conditions (OR)
sales_or_marketing = df[(df['Department'] == 'Sales') | (df['Department'] == 'Marketing')]
print("Sales or Marketing:")
print(sales_or_marketing)
print()

# NOT condition
not_sales = df[~(df['Department'] == 'Sales')]
print("Not in Sales:")
print(not_sales)
print()

# Using isin() for multiple values
selected_cities = df[df['City'].isin(['New York', 'Boston', 'Chicago'])]
print("Selected cities:")
print(selected_cities)
print()

# === String filtering ===

# Contains
contains_francisco = df[df['City'].str.contains('Francisco')]
print("Cities containing 'Francisco':")
print(contains_francisco)
print()

# Starts with
starts_with_s = df[df['City'].str.startswith('S')]
print("Cities starting with 'S':")
print(starts_with_s)
print()

# Case-insensitive filtering
contains_sales = df[df['Department'].str.lower() == 'sales']
print("Sales department (case-insensitive):")
print(contains_sales)
print()

# === Query method ===

# SQL-like filtering
filtered = df.query('Age > 28 and Salary < 90000')
print("Query result:")
print(filtered)
print()

# Using variables in query
min_age = 30
filtered = df.query('Age >= @min_age')
print(f"Age >= {min_age}:")
print(filtered)
print()

# === Adding columns ===

# Simple assignment
df['Bonus'] = df['Salary'] * 0.1
print("After adding Bonus column:")
print(df)
print()

# Conditional column
df['Senior'] = df['Age'] >= 30
print("After adding Senior column:")
print(df[['Name', 'Age', 'Senior']])
print()

# Using apply() with function
def categorize_salary(salary):
    if salary < 75000:
        return 'Low'
    elif salary < 85000:
        return 'Medium'
    else:
        return 'High'

df['Salary_Category'] = df['Salary'].apply(categorize_salary)
print("After adding Salary_Category:")
print(df[['Name', 'Salary', 'Salary_Category']])
print()

# Using lambda with apply
df['Name_Length'] = df['Name'].apply(lambda x: len(x))
print("After adding Name_Length:")
print(df[['Name', 'Name_Length']])
print()

# Combining multiple columns
df['Full_Info'] = df['Name'] + ' (' + df['City'] + ')'
print("After adding Full_Info:")
print(df[['Full_Info']].head())
print()

# === Removing columns ===

# Drop single column
df_dropped = df.drop('Bonus', axis=1)
print("After dropping Bonus:")
print(df_dropped.columns.tolist())
print()

# Drop multiple columns
df_dropped = df.drop(['Senior', 'Name_Length'], axis=1)
print("After dropping multiple columns:")
print(df_dropped.columns.tolist())
print()

# Drop in place
df.drop('Full_Info', axis=1, inplace=True)
print("Columns after in-place drop:")
print(df.columns.tolist())
print()

# === Renaming columns ===

# Rename single column
df_renamed = df.rename(columns={'Salary': 'Annual_Salary'})
print("After renaming:")
print(df_renamed.columns.tolist())
print()

# Rename multiple columns
df_renamed = df.rename(columns={
    'Name': 'Employee_Name',
    'Age': 'Employee_Age'
})
print("After renaming multiple:")
print(df_renamed.columns.tolist())
print()

# Rename all columns
df.columns = ['Emp_Name', 'Emp_Age', 'Emp_City', 'Emp_Salary', 'Emp_Dept', 'Emp_Bonus', 'Emp_Senior', 'Emp_Category']
print("After renaming all:")
print(df.columns.tolist())

# Reset column names
df.columns = ['Name', 'Age', 'City', 'Salary', 'Department', 'Bonus', 'Senior', 'Salary_Category']
print()

# === Sorting ===

# Sort by single column
sorted_df = df.sort_values('Age')
print("Sorted by Age (ascending):")
print(sorted_df[['Name', 'Age']])
print()

# Sort descending
sorted_df = df.sort_values('Salary', ascending=False)
print("Sorted by Salary (descending):")
print(sorted_df[['Name', 'Salary']].head())
print()

# Sort by multiple columns
sorted_df = df.sort_values(['Department', 'Salary'], ascending=[True, False])
print("Sorted by Department and Salary:")
print(sorted_df[['Name', 'Department', 'Salary']])
print()

# === Resetting index ===

df_reset = df.sort_values('Age').reset_index(drop=True)
print("After sorting and resetting index:")
print(df_reset[['Name', 'Age']].head())

# Grouping and Aggregation

import pandas as pd
import numpy as np

# Create sample DataFrame
data = {
    'Department': ['Sales', 'Sales', 'Engineering', 'Engineering', 'Sales', 'Marketing', 'Marketing', 'Engineering'],
    'Employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Henry'],
    'Salary': [70000, 85000, 95000, 72000, 88000, 76000, 82000, 90000],
    'Experience': [2, 5, 8, 3, 6, 4, 5, 7],
    'Region': ['East', 'West', 'East', 'West', 'East', 'West', 'East', 'West']
}

df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)
print()

# === Basic groupby ===

# Group by single column
grouped = df.groupby('Department')

# Get group names
print("Groups:", grouped.groups.keys())
print()

# Get specific group
sales_group = grouped.get_group('Sales')
print("Sales department:")
print(sales_group)
print()

# === Single aggregation ===

# Mean salary by department
mean_salary = df.groupby('Department')['Salary'].mean()
print("Mean salary by department:")
print(mean_salary)
print()

# Sum by department
total_salary = df.groupby('Department')['Salary'].sum()
print("Total salary by department:")
print(total_salary)
print()

# Count employees per department
count = df.groupby('Department').size()
print("Employee count by department:")
print(count)
print()

# === Multiple aggregations ===

# Multiple functions on single column
stats = df.groupby('Department')['Salary'].agg(['mean', 'min', 'max', 'sum'])
print("Salary statistics by department:")
print(stats)
print()

# Multiple columns, multiple functions
stats = df.groupby('Department').agg({
    'Salary': ['mean', 'sum'],
    'Experience': ['mean', 'max']
})
print("Multiple aggregations:")
print(stats)
print()

# Custom aggregation function
def salary_range(x):
    return x.max() - x.min()

range_stats = df.groupby('Department')['Salary'].agg([
    ('Average', 'mean'),
    ('Range', salary_range),
    ('Count', 'count')
])
print("Custom aggregations:")
print(range_stats)
print()

# === Group by multiple columns ===

# Group by department and region
multi_group = df.groupby(['Department', 'Region'])['Salary'].mean()
print("Mean salary by department and region:")
print(multi_group)
print()

# Unstack for better view
multi_group_unstacked = df.groupby(['Department', 'Region'])['Salary'].mean().unstack()
print("Unstacked view:")
print(multi_group_unstacked)
print()

# === Transform (add aggregated values to original DataFrame) ===

# Add department average to each row
df['Dept_Avg_Salary'] = df.groupby('Department')['Salary'].transform('mean')
print("With department average:")
print(df[['Employee', 'Department', 'Salary', 'Dept_Avg_Salary']])
print()

# Calculate difference from department average
df['Diff_From_Avg'] = df['Salary'] - df['Dept_Avg_Salary']
print("Difference from average:")
print(df[['Employee', 'Salary', 'Dept_Avg_Salary', 'Diff_From_Avg']])
print()

# === Filter groups ===

# Keep groups with more than 2 employees
filtered = df.groupby('Department').filter(lambda x: len(x) > 2)
print("Departments with > 2 employees:")
print(filtered)
print()

# Keep groups where average salary > 80000
high_paying = df.groupby('Department').filter(lambda x: x['Salary'].mean() > 80000)
print("High-paying departments:")
print(high_paying)
print()

# === Pivot tables ===

# Simple pivot table
pivot = df.pivot_table(
    values='Salary',
    index='Department',
    aggfunc='mean'
)
print("Pivot table - average salary:")
print(pivot)
print()

# Pivot with multiple dimensions
pivot_multi = df.pivot_table(
    values='Salary',
    index='Department',
    columns='Region',
    aggfunc='mean'
)
print("Pivot table - department x region:")
print(pivot_multi)
print()

# Pivot with multiple aggregations
pivot_agg = df.pivot_table(
    values='Salary',
    index='Department',
    columns='Region',
    aggfunc=['mean', 'count'],
    fill_value=0
)
print("Pivot with multiple aggregations:")
print(pivot_agg)
print()

# === Value counts ===

# Count occurrences
dept_counts = df['Department'].value_counts()
print("Department value counts:")
print(dept_counts)
print()

# Value counts with percentage
dept_pct = df['Department'].value_counts(normalize=True) * 100
print("Department percentages:")
print(dept_pct)
print()

# === Cumulative aggregations ===

# Cumulative sum
df_sorted = df.sort_values('Experience')
df_sorted['Cumulative_Salary'] = df_sorted['Salary'].cumsum()
print("With cumulative salary:")
print(df_sorted[['Employee', 'Experience', 'Salary', 'Cumulative_Salary']])
print()

# Rolling mean (moving average)
df_sorted['Rolling_Avg_Salary'] = df_sorted['Salary'].rolling(window=3).mean()
print("With rolling average:")
print(df_sorted[['Employee', 'Salary', 'Rolling_Avg_Salary']])

# Handling Missing Values

import pandas as pd
import numpy as np

# Create DataFrame with missing values
data = {
    'Name': ['Alice', 'Bob', None, 'David', 'Eve'],
    'Age': [25, np.nan, 35, 28, np.nan],
    'Salary': [70000, 85000, np.nan, 72000, 88000],
    'City': ['New York', 'San Francisco', 'Los Angeles', None, 'Boston']
}

df = pd.DataFrame(data)
print("DataFrame with missing values:")
print(df)
print()

# === Detecting missing values ===

# Check for missing values
print("Missing values (isna):")
print(df.isna())
print()

# Count missing values per column
print("Missing value counts:")
print(df.isna().sum())
print()

# Percentage of missing values
print("Missing value percentages:")
print((df.isna().sum() / len(df)) * 100)
print()

# Check if any value is missing
print("Any missing values?", df.isna().any().any())
print()

# Rows with any missing value
rows_with_missing = df[df.isna().any(axis=1)]
print("Rows with missing values:")
print(rows_with_missing)
print()

# === Filling missing values ===

# Fill with specific value
df_filled = df.fillna(0)
print("Filled with 0:")
print(df_filled)
print()

# Fill with different values per column
df_filled = df.fillna({
    'Name': 'Unknown',
    'Age': df['Age'].mean(),
    'Salary': df['Salary'].median(),
    'City': 'Not Specified'
})
print("Filled with column-specific values:")
print(df_filled)
print()

# Forward fill (use previous value)
df_ffill = df.fillna(method='ffill')
print("Forward fill:")
print(df_ffill)
print()

# Backward fill (use next value)
df_bfill = df.fillna(method='bfill')
print("Backward fill:")
print(df_bfill)
print()

# Fill with mean for numeric columns
df_numeric_filled = df.copy()
for col in df.select_dtypes(include=[np.number]).columns:
    df_numeric_filled[col].fillna(df[col].mean(), inplace=True)

print("Numeric columns filled with mean:")
print(df_numeric_filled)
print()

# === Interpolation ===

# Linear interpolation
df_interpolated = df.copy()
df_interpolated['Age'] = df_interpolated['Age'].interpolate()
print("Age column interpolated:")
print(df_interpolated)
print()

# === Dropping missing values ===

# Drop rows with any missing value
df_dropped = df.dropna()
print("After dropping rows with any NaN:")
print(df_dropped)
print()

# Drop rows where all values are missing
df_all_na = pd.DataFrame({
    'A': [1, np.nan, 3],
    'B': [np.nan, np.nan, np.nan],
    'C': [7, 8, 9]
})
df_dropped_all = df_all_na.dropna(how='all')
print("After dropping rows where all are NaN:")
print(df_dropped_all)
print()

# Drop columns with any missing value
df_dropped_cols = df.dropna(axis=1)
print("After dropping columns with any NaN:")
print(df_dropped_cols)
print()

# Drop rows with missing values in specific columns
df_dropped_subset = df.dropna(subset=['Age', 'Salary'])
print("After dropping rows with NaN in Age or Salary:")
print(df_dropped_subset)
print()

# Drop rows with at least N non-null values
df_thresh = df.dropna(thresh=3)  # Keep rows with at least 3 non-null values
print("Rows with at least 3 non-null values:")
print(df_thresh)
print()

# === Replacing values ===

# Replace specific value with NaN
df_replace = df.copy()
df_replace['City'] = df_replace['City'].replace('New York', np.nan)
print("After replacing 'New York' with NaN:")
print(df_replace)
print()

# Replace multiple values
df_replace = df.copy()
df_replace = df_replace.replace({
    'New York': 'NYC',
    'San Francisco': 'SF'
})
print("After replacing city names:")
print(df_replace)
print()

# === Checking for duplicates ===

data_dup = {
    'Name': ['Alice', 'Bob', 'Alice', 'David'],
    'Age': [25, 30, 25, 28]
}
df_dup = pd.DataFrame(data_dup)

print("DataFrame with duplicates:")
print(df_dup)
print()

# Find duplicates
print("Duplicate rows:")
print(df_dup.duplicated())
print()

# Show duplicate rows
print("Duplicate row values:")
print(df_dup[df_dup.duplicated()])
print()

# Drop duplicates
df_no_dup = df_dup.drop_duplicates()
print("After dropping duplicates:")
print(df_no_dup)
print()

# Drop duplicates based on specific columns
df_no_dup_name = df_dup.drop_duplicates(subset=['Name'])
print("After dropping duplicates by Name:")
print(df_no_dup_name)
print()

# === Complete data cleaning pipeline ===

def clean_dataframe(df):
    """Clean DataFrame by handling missing values and duplicates."""
    df_clean = df.copy()
    
    # Fill numeric columns with mean
    numeric_cols = df_clean.select_dtypes(include=[np.number]).columns
    for col in numeric_cols:
        df_clean[col].fillna(df_clean[col].mean(), inplace=True)
    
    # Fill categorical columns with mode (most frequent)
    categorical_cols = df_clean.select_dtypes(include=['object']).columns
    for col in categorical_cols:
        df_clean[col].fillna(df_clean[col].mode()[0], inplace=True)
    
    # Drop duplicates
    df_clean.drop_duplicates(inplace=True)
    
    # Reset index
    df_clean.reset_index(drop=True, inplace=True)
    
    return df_clean

df_cleaned = clean_dataframe(df)
print("Cleaned DataFrame:")
print(df_cleaned)
print("\nMissing values after cleaning:", df_cleaned.isna().sum().sum())