Pandas Fundamentals

import pandas as pd
import numpy as np

data = {
    'name': ['Amara', 'Emeka', 'Chisom', 'Fatima'],
    'country': ['Nigeria', 'Ghana', 'Nigeria', 'Kenya'],
    'revenue': [1200, 450, 870, 320],
    'orders': [5, 2, 4, 1]
}
df = pd.DataFrame(data)

df = pd.read_csv('sales_data.csv')

# With options
df = pd.read_csv(
    'sales_data.csv',
    parse_dates=['order_date'],    # automatically parse date columns
    index_col='order_id'           # use order_id as the row index
)

df = pd.read_excel('sales_report.xlsx', sheet_name='Data')

df.shape           # (rows, columns) -- e.g., (50000, 12)
df.head(5)         # first 5 rows
df.tail(5)         # last 5 rows
df.info()          # column names, data types, non-null counts
df.describe()      # statistical summary for numeric columns
df.columns         # list of column names
df.dtypes          # data type of each column
df.isnull().sum()  # count of missing values per column

# Single column (returns a Series)
df['revenue']
df.revenue          # dot notation (avoid if column name has spaces)

# Multiple columns (returns a DataFrame)
df[['name', 'revenue', 'orders']]

# .loc: label-based selection
df.loc[0]              # row with index label 0
df.loc[0:5]            # rows with index labels 0 to 5 (inclusive)
df.loc[0, 'revenue']   # specific cell by row label and column name
df.loc[:, 'revenue']   # all rows, revenue column

# .iloc: position-based selection
df.iloc[0]             # first row
df.iloc[0:5]           # rows 0 to 4 (exclusive end)
df.iloc[0, 3]          # row 0, column 3 (4th column)

# Single condition
df[df['revenue'] > 500]

# Multiple conditions (use & for AND, | for OR)
df[(df['country'] == 'Nigeria') & (df['revenue'] > 500)]
df[(df['country'] == 'Nigeria') | (df['country'] == 'Ghana')]

# Using isin() for multiple values
df[df['country'].isin(['Nigeria', 'Ghana', 'Kenya'])]

# Checking for NaN
df[df['email'].isna()]
df[df['email'].notna()]

# Add a new column
df['revenue_per_order'] = df['revenue'] / df['orders']
df['is_high_value'] = df['revenue'] > 1000

# Apply a function to create a new column
def categorise(revenue):
    if revenue >= 1000:
        return 'VIP'
    elif revenue >= 500:
        return 'Regular'
    else:
        return 'Low'

df['segment'] = df['revenue'].apply(categorise)

# Using numpy where (faster for simple conditions)
df['tier'] = np.where(df['revenue'] >= 500, 'High', 'Low')

df.rename(columns={'revenue': 'total_revenue', 'orders': 'order_count'}, inplace=True)

df.drop(columns=['column_to_drop'], inplace=True)
df.drop(columns=['col1', 'col2'], inplace=True)

df.sort_values('revenue', ascending=False)

# Sort by multiple columns
df.sort_values(['country', 'revenue'], ascending=[True, False])

# Group by one column
df.groupby('country')['revenue'].sum()

# Group by one column, multiple aggregations
df.groupby('country').agg(
    total_revenue=('revenue', 'sum'),
    avg_revenue=('revenue', 'mean'),
    order_count=('orders', 'sum'),
    customer_count=('name', 'count')
).reset_index()

# Group by multiple columns
df.groupby(['country', 'segment'])['revenue'].sum().reset_index()

# INNER JOIN
merged = pd.merge(orders, customers, on='customer_id', how='inner')

# LEFT JOIN
merged = pd.merge(customers, orders, on='customer_id', how='left')

# Merge on columns with different names
merged = pd.merge(
    orders,
    customers,
    left_on='cust_id',
    right_on='customer_id',
    how='inner'
)

# Save to CSV
df.to_csv('output.csv', index=False)

# Save to Excel
df.to_excel('output.xlsx', sheet_name='Analysis', index=False)

# Save multiple sheets to one Excel file
with pd.ExcelWriter('report.xlsx') as writer:
    summary.to_excel(writer, sheet_name='Summary', index=False)
    details.to_excel(writer, sheet_name='Details', index=False)

import pandas as pd

# Load sample data
url = 'https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv'
df = pd.read_csv(url)

# 1. Explore: shape, info, describe, isnull().sum()
print(df.shape)
print(df.isnull().sum())

# 2. Select only Name, Age, Fare, Survived columns
subset = df[['Name', 'Age', 'Fare', 'Survived']]

# 3. Filter to passengers who survived and paid more than $50
survivors_high_fare = df[(df['Survived'] == 1) & (df['Fare'] > 50)]

# 4. Add a column categorising fare as 'Low', 'Medium', 'High'
df['fare_tier'] = pd.cut(df['Fare'], bins=[0, 20, 100, 1000], labels=['Low', 'Medium', 'High'])

# 5. Group by survival status and calculate mean age and fare
df.groupby('Survived').agg(avg_age=('Age', 'mean'), avg_fare=('Fare', 'mean'))