Pandas#

Pandas is a powerful library for data manipulation and analysis, providing data structures like DataFrame and Series for working with structured data.

import pandas as pd

Import and Export Data#

Read CSV#

df = pd.read_csv("data.csv")
df = pd.read_csv("data.csv", sep=";", encoding="utf-8")
df = pd.read_csv("data.csv", usecols=["name", "age"])

Read Excel#

df = pd.read_excel("data.xlsx")
df = pd.read_excel("data.xlsx", sheet_name="Sheet2")

From Dictionary#

data = {
    "name": ["Alice", "Bob", "Charlie"],
    "age": [30, 25, 35],
    "city": ["London", "Paris", "Berlin"],
}
df = pd.DataFrame(data)

Read DBF from Shapefile#

import geopandas as gpd

gdf = gpd.read_file("shapefile.shp")
df = pd.DataFrame(gdf.drop(columns="geometry"))

Read from MS Access#

import pyodbc

conn_str = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\path\to\database.accdb;"
)
conn = pyodbc.connect(conn_str)
df = pd.read_sql("SELECT * FROM table_name", conn)
conn.close()

Export#

df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", index=False)

Common Functions#

DataFrame Information#

df.shape       # (rows, columns) tuple
df.columns     # Column names
df.dtypes      # Data types of each column
df.head()      # First 5 rows
df.head(10)    # First 10 rows
df.describe()  # Summary statistics
df.sample(5)   # 5 random rows

Aggregation#

df["age"].mean()    # Mean of a column
df["age"].sum()     # Sum
df["age"].median()  # Median
df["age"].max()     # Maximum value
df["age"].min()     # Minimum value
df["city"].unique() # Unique values in a column

Indexing and Values#

# Get values as a NumPy array
df["age"].values

# Get values as a Python list
df["age"].tolist()

# Unique values
df["city"].unique()

# Number of unique values
df["city"].nunique()

Rename Columns#

df = df.rename(columns={"name": "full_name", "age": "years"})

Delete Rows and Columns#

# Drop rows by index
df = df.drop([0, 1, 2])

# Drop rows where a condition is met
df = df[df["age"] >= 18]

# Drop rows with missing values
df = df.dropna()
df = df.dropna(subset=["age"])

# Reset index after dropping
df = df.reset_index(drop=True)

# Drop columns
df = df.drop('column_name', axis=1)          # one column
df = df.drop(columns=['columnA', 'columnB']) # by names
df = df.drop(df.columns[[0, 1, 3]], axis=1)  # by index

Operations#

Assign New Columns#

# Create a new column from an expression
df = df.assign(birth_year=2024 - df["age"])

# Multiple columns at once
df = df.assign(
    birth_year=2024 - df["age"],
    name_upper=df["name"].str.upper(),
)

Indexing#

loc — Label-based Indexing#

# Select row by label
df.loc[0]

# Select rows and specific columns
df.loc[0:5, ["name", "age"]]

# Select with a condition
df.loc[df["age"] > 30, "name"]

iloc — Position-based Indexing#

# Select row by position
df.iloc[0]

# Select rows 0-4, columns 0-2
df.iloc[0:5, 0:3]

# Select specific rows and columns
df.iloc[[0, 2, 4], [1, 3]]

Selecting and Filtering#

Select Columns#

# Single column (returns Series)
df["name"]

# Multiple columns (returns DataFrame)
df[["name", "age"]]

Filter Rows#

# Single condition
df[df["age"] > 30]
df.loc[df['age']=='35', 'name']

# Multiple conditions (use & for AND, | for OR)
df[(df["age"] > 25) & (df["city"] == "London")]
df[(df["age"] < 20) | (df["age"] > 60)]

Select a value#

df[df["age"] == 30].values[0]  # from a row use index

Note

Parentheses Required When combining conditions with & or |, each condition must be wrapped in parentheses.

String Filtering#

# Contains a substring
df[df["name"].str.contains("ali", case=False)]

# Starts with / ends with
df[df["name"].str.startswith("A")]
df[df["name"].str.endswith("e")]

isin#

cities = ["London", "Paris"]
df[df["city"].isin(cities)]

where#

# Keep values where condition is True, replace others with NaN
df["age"].where(df["age"] > 30)

query#

# SQL-like filtering syntax
df.query("age > 30 and city == 'London'")

Iterate#

for index, row in df.iterrows():
    print(row['ColumnA'], row['ColumnB'])

Merge and Join#

Concatenate#

# Stack DataFrames vertically
df_all = pd.concat([df1, df2], ignore_index=True)

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

Merge#

# Inner join (default)
merged = pd.merge(df1, df2, on="id")

# Left join
merged = pd.merge(df1, df2, on="id", how="left")

# Join on different column names
merged = pd.merge(df1, df2, left_on="user_id", right_on="id")

Parameter	Description
`on`	Column name to join on (must exist in both)
`left_on`	Column name in left DataFrame
`right_on`	Column name in right DataFrame
`how`	Type of join: `inner`, `left`, `right`, `outer`

Pivot Table#

Using groupby + value_counts + unstack#

# Count occurrences of each category per group
pivot = df.groupby("city")["category"].value_counts().unstack(fill_value=0)

Using pivot_table#

pivot = pd.pivot_table(
    df,
    values="sales",
    index="city",
    columns="product",
    aggfunc="sum",
    fill_value=0,
)

Group By#

Basic Aggregation#

# Sum by group
df.groupby("city")["sales"].sum()

# Multiple aggregations
df.groupby("city")["sales"].agg(["sum", "mean", "count"])

Apply a Custom Function#

# Collect values into a list per group
df.groupby("city")["name"].apply(list)

Binning with pd.cut#

# Create age bins
bins = [0, 18, 35, 50, 100]
labels = ["youth", "young_adult", "adult", "senior"]
df["age_group"] = pd.cut(df["age"], bins=bins, labels=labels)

# Count per bin
df.groupby("age_group", observed=True).size()