DataFrame Basics
Creating DataFrames
import pandas as pd
# From dictionary
data = {
'name': ['Alice', 'Bob', 'Charlie'],
'age': [25, 30, 35],
'city': ['NYC', 'LA', 'Chicago']
}
df = pd.DataFrame(data)
print(df)
# name age city
# 0 Alice 25 NYC
# 1 Bob 30 LA
# 2 Charlie 35 Chicago
# From list of dicts
data = [
{'name': 'Alice', 'age': 25},
{'name': 'Bob', 'age': 30}
]
df = pd.DataFrame(data)
# From CSV
df = pd.read_csv('data.csv')
# From Excel
df = pd.read_excel('data.xlsx')
# From JSON
df = pd.read_json('data.json')
DataFrame Properties
df = pd.DataFrame({
'name': ['Alice', 'Bob', 'Charlie'],
'age': [25, 30, 35],
'salary': [50000, 60000, 70000]
})
print(df.shape) # (3, 3) - rows x columns
print(df.dtypes) # Column data types
print(df.columns) # Column names
print(df.index) # Row indices
print(df.info()) # Summary info
print(df.describe()) # Statistics
Viewing Data
print(df.head(2)) # First 2 rows
print(df.tail(2)) # Last 2 rows
print(df.sample(2)) # Random 2 rows
print(df['name']) # Single column
print(df[['name', 'age']]) # Multiple columns
Saving DataFrames
# To CSV
df.to_csv('output.csv', index=False)
# To Excel
df.to_excel('output.xlsx', index=False)
# To JSON
df.to_json('output.json', orient='records')
# To HTML
df.to_html('output.html')
Data Selection
Column Selection
df = pd.DataFrame({
'name': ['Alice', 'Bob', 'Charlie'],
'age': [25, 30, 35],
'salary': [50000, 60000, 70000]
})
# Single column (Series)
print(df['name'])
# 0 Alice
# 1 Bob
# 2 Charlie
# Multiple columns (DataFrame)
print(df[['name', 'age']])
# Access column as attribute (if no spaces)
print(df.name)
Row Selection
# iloc - integer location
print(df.iloc[0]) # First row (Series)
print(df.iloc[0:2]) # First 2 rows
print(df.iloc[[0, 2]]) # Rows 0 and 2
# loc - label location
print(df.loc[0]) # Row with index 0
print(df.loc[0:2]) # Rows 0 to 2 (inclusive!)
Filtering
# Boolean indexing
mask = df['age'] > 28
print(df[mask])
# name age salary
# 1 Bob 30 60000
# 2 Charlie 35 70000
# Direct filtering
print(df[df['age'] > 28])
# Multiple conditions
print(df[(df['age'] > 25) & (df['salary'] > 55000)])
# isin
print(df[df['name'].isin(['Alice', 'Bob'])])
# Query method
print(df.query('age > 25 and salary > 55000'))
Setting Values
# Set single value
df.loc[0, 'age'] = 26
# Set column
df['bonus'] = df['salary'] * 0.1
# Conditional setting
df['senior'] = df['age'] > 30
# Drop column
df = df.drop('bonus', axis=1)
# Drop row
df = df.drop(0, axis=0)
at and iat
# Fast scalar access
print(df.at[0, 'name']) # 'Alice'
print(df.iat[0, 0]) # 'Alice'
# Faster than loc/iloc for single values
Data Cleaning
Handling Missing Data
import pandas as pd
import numpy as np
df = pd.DataFrame({
'A': [1, 2, np.nan, 4],
'B': [5, np.nan, np.nan, 8],
'C': ['a', 'b', None, 'd']
})
# Check for missing
print(df.isnull())
print(df.isnull().sum()) # Count per column
# Drop missing
df_clean = df.dropna() # Drop rows with any NaN
df_clean = df.dropna(subset=['A']) # Drop rows where A is NaN
df_clean = df.dropna(thresh=2) # Keep rows with at least 2 non-NaN
# Fill missing
df_filled = df.fillna(0) # Fill with 0
df_filled = df.fillna({'A': df['A'].mean(), 'B': 0})
df_filled = df.fillna(method='ffill') # Forward fill
df_filled = df.fillna(method='bfill') # Backward fill
# Interpolate
df_interp = df.interpolate()
Data Types
# Check types
print(df.dtypes)
# Convert types
df['A'] = df['A'].astype(int)
df['A'] = df['A'].astype(str)
# Parse dates
df = pd.read_csv('data.csv', parse_dates=['date_column'])
# To datetime
df['date'] = pd.to_datetime(df['date'])
String Operations
df = pd.DataFrame({'name': ['Alice Smith', 'Bob Jones', 'Charlie Brown']})
# Access string methods
df['first_name'] = df['name'].str.split().str[0]
df['last_name'] = df['name'].str.split().str[1]
df['name_upper'] = df['name'].str.upper()
df['name_len'] = df['name'].str.len()
# Replace
df['name_clean'] = df['name'].str.replace('Smith', 'S')
# Contains
df['has_a'] = df['name'].str.contains('a', case=False)
Duplicates
# Check duplicates
print(df.duplicated())
print(df.duplicated().sum())
# Remove duplicates
df_unique = df.drop_duplicates()
df_unique = df.drop_duplicates(subset=['name'])
# Keep first/last
df_unique = df.drop_duplicates(keep='first')
GroupBy & Merge
GroupBy
df = pd.DataFrame({
'department': ['Engineering', 'Engineering', 'Sales', 'Sales', 'HR'],
'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'salary': [100000, 110000, 80000, 75000, 90000]
})
# Group by department
grouped = df.groupby('department')
# Aggregation
print(grouped['salary'].mean())
# department
# Engineering 105000.0
# HR 90000.0
# Sales 77500.0
# Multiple aggregations
print(grouped['salary'].agg(['mean', 'min', 'max', 'count']))
# Custom aggregation
def salary_range(x):
return x.max() - x.min()
print(grouped['salary'].agg(salary_range))
# Apply multiple functions to multiple columns
result = grouped.agg({
'salary': ['mean', 'max'],
'employee': 'count'
})
Merge
df1 = pd.DataFrame({
'id': [1, 2, 3],
'name': ['Alice', 'Bob', 'Charlie']
})
df2 = pd.DataFrame({
'id': [2, 3, 4],
'salary': [60000, 70000, 80000]
})
# Inner merge (only matching)
result = pd.merge(df1, df2, on='id', how='inner')
print(result)
# id name salary
# 0 2 Bob 60000
# 1 3 Charlie 70000
# Left merge (all from left)
result = pd.merge(df1, df2, on='id', how='left')
# Right merge (all from right)
result = pd.merge(df1, df2, on='id', how='right')
# Outer merge (all from both)
result = pd.merge(df1, df2, on='id', how='outer')
Concatenation
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})
# Vertical (stack)
result = pd.concat([df1, df2], axis=0)
# Horizontal (side by side)
result = pd.concat([df1, df2], axis=1)
# With keys
result = pd.concat([df1, df2], keys=['first', 'second'])
Pivot Tables
df = pd.DataFrame({
'department': ['Eng', 'Eng', 'Sales', 'Sales'],
'year': [2020, 2021, 2020, 2021],
'salary': [100000, 110000, 80000, 85000]
})
pivot = df.pivot_table(
values='salary',
index='department',
columns='year',
aggfunc='mean'
)
print(pivot)
# year 2020 2021
# department
# Eng 100000 110000
# Sales 80000 85000