Combining DataFrames and Creating Pivot Tables

Real-world analysis often involves data spread across multiple tables. Pandas supports combining datasets and reshaping them into summary formats.

Combining Data with merge()

The merge() function joins DataFrames using one or more common columns.

customers = pd.DataFrame({

"Customer_ID": [101, 102, 103],

"Name": ["Aman", "Priya", "Ravi"]

})

orders = pd.DataFrame({

"Customer_ID": [101, 103, 101],

"Amount": [2500, 4000, 1800]

})

merged_data = pd.merge(

orders,

customers,

on="Customer_ID",

how="left"

)

print(merged_data)

The left join retains every row from orders and adds matching customer information where available.

Common join types include:

• inner: Keeps only matching keys.
• left: Keeps all rows from the left DataFrame.
• right: Keeps all rows from the right DataFrame.
• outer: Keeps keys from both DataFrames, including unmatched records.

Choosing the correct join type is important because it determines which records remain in the result.

Appending Rows with concat()

Use pd.concat() to combine DataFrames vertically or horizontally.

january_sales = pd.DataFrame({

"Product": ["Laptop", "Phone"],

"Sales": [50000, 30000]

})

february_sales = pd.DataFrame({

"Product": ["Tablet", "Monitor"],

"Sales": [25000, 18000]

})

all_sales = pd.concat(

[january_sales, february_sales],

ignore_index=True

)

print(all_sales)

Using ignore_index=True creates a fresh sequential index for the combined rows.

Creating a Pivot Table

A pivot table summarizes data by arranging categories into rows and columns.

sales_data = pd.DataFrame({

"Region": ["North", "North", "South", "South"],

"Product": ["Laptop", "Phone", "Laptop", "Phone"],

"Sales": [50000, 30000, 65000, 40000]

})

pivot = pd.pivot_table(

sales_data,

values="Sales",

index="Region",

columns="Product",

aggfunc="sum",

fill_value=0

)

print(pivot)

The result displays total sales for each product within each region. Pivot tables are useful for comparing categories and preparing summary data for charts and reports.