Merging & Combining Data

Python tutorial · PySpark.in

What is Merging & Combining Data?

Merging and Combining Data is the process of bringing together multiple DataFrames into a single DataFrame based on rows, columns, or common keys.

You can think like Exactly like SQL JOIN

Why Do We Need It?

✔ Data from different sources
✔ Database-like operations
✔ Real-world datasets are never in one table
✔ Feature enrichment

Sample DataFrames

Students Table

```

import pandas as pd

students = pd.DataFrame({

"ID": [1, 2, 3, 4],

"Name": ["Megha", "Amit", "Riya", "Neha"]

})

print(students)

```

Marks Table

```

marks = pd.DataFrame({

"ID": [1, 2, 3],

"Marks": [85, 90, 88]

})

print(marks)

```

concat() :combines DataFrames vertically (rows) or horizontally (columns). No matching key required

Vertical Concatenation (Row-wise)

```

df1 = pd.DataFrame({"A": [1, 2]})

df2 = pd.DataFrame({"A": [3, 4]})

print(pd.concat([df1, df2]))

```

Horizontal Concatenation (Column-wise)

```

print(pd.concat([students, marks], axis=1))

```

Important Parameters

axis=0 means: rows (default)
axis=1 means :columns
ignore_index=True

merge(): combines DataFrames based on a common column (key). Most used in real projects.It is just equivalent to SQL JOIN

Practical (Default = Inner Join)

```

merged = pd.merge(students, marks, on="ID")

print(merged)

```

Join Types

(inner, left, right, outer)

INNER JOIN (Default)

Keeps only matching rows.

```

T=pd.merge(students, marks, on="ID", how="inner")

print(T)

```

LEFT JOIN

It keeps all rows from left table.

```

X=pd.merge(students, marks, on="ID", how="left")

print(X)

```

RIGHT JOIN

```

Y=pd.merge(students, marks, on="ID", how="right")

print(Y)

```

OUTER JOIN

Keeps all rows from both tables.

```

Z=pd.merge(students, marks, on="ID", how="outer")

print(Z)

```

join(): combines DataFrames based on index.Easier to apply when index is common key

Practical

```

students_indexed = students.set_index("ID")

marks_indexed = marks.set_index("ID")

print(students_indexed.join(marks_indexed))

```

Difference: merge() vs join()

merge	join
Column-based	Index-based
More flexible	Simpler
SQL-style	Faster for index

Working with Multiple DataFrames

Combining more than two DataFrames.

Practical using concat

```

df3 = pd.DataFrame({"ID": [5], "Name": ["Rahul"]})

print(pd.concat([students, df3], ignore_index=True))

```

Multiple merge

```

attendance = pd.DataFrame({