Combining data sources#

Data can come from different files and you can combine them in a single dataframe.

When working with data, storing data across multiple files or data sources is common. Combining data from different files or data sources into a single dataframe can be useful for data analysis and modelling.

In Pandas, combining data from additional files or sources is done using the merge or join functions. These functions allow us to connect data based on a common key or index, which can be helpful when analyzing related data.

This notebook will explore how to merge or join data from different files or sources into a single Pandas dataframe. We will also discuss different types of joins and merge operations and how to handle missing or duplicate data during the merging process.

How To#

import pandas as pd
df = pd.read_csv("data/housing.csv")
df.head()
longitude latitude housing_median_age total_rooms total_bedrooms population households median_income median_house_value ocean_proximity
0 -122.23 37.88 41.0 880.0 129.0 322.0 126.0 8.3252 452600.0 NEAR BAY
1 -122.22 37.86 21.0 7099.0 1106.0 2401.0 1138.0 8.3014 358500.0 NEAR BAY
2 -122.24 37.85 52.0 1467.0 190.0 496.0 177.0 7.2574 352100.0 NEAR BAY
3 -122.25 37.85 52.0 1274.0 235.0 558.0 219.0 5.6431 341300.0 NEAR BAY
4 -122.25 37.85 52.0 1627.0 280.0 565.0 259.0 3.8462 342200.0 NEAR BAY
df_geo = df[["latitude", "longitude", "ocean_proximity"]]
df_geo.head()
latitude longitude ocean_proximity
0 37.88 -122.23 NEAR BAY
1 37.86 -122.22 NEAR BAY
2 37.85 -122.24 NEAR BAY
3 37.85 -122.25 NEAR BAY
4 37.85 -122.25 NEAR BAY
df_geo.join(df["median_house_value"])
latitude longitude ocean_proximity median_house_value
0 37.88 -122.23 NEAR BAY 452600.0
1 37.86 -122.22 NEAR BAY 358500.0
2 37.85 -122.24 NEAR BAY 352100.0
3 37.85 -122.25 NEAR BAY 341300.0
4 37.85 -122.25 NEAR BAY 342200.0
... ... ... ... ...
20635 39.48 -121.09 INLAND 78100.0
20636 39.49 -121.21 INLAND 77100.0
20637 39.43 -121.22 INLAND 92300.0
20638 39.43 -121.32 INLAND 84700.0
20639 39.37 -121.24 INLAND 89400.0

20640 rows × 4 columns

df_price = df[["longitude", "latitude", "median_house_value"]]
df_geo.merge(df_price, left_on="latitude", right_on="latitude")
latitude longitude_x ocean_proximity longitude_y median_house_value
0 37.88 -122.23 NEAR BAY -122.23 452600.0
1 37.88 -122.23 NEAR BAY -122.34 350000.0
2 37.88 -122.23 NEAR BAY -122.29 216700.0
3 37.88 -122.23 NEAR BAY -122.28 261300.0
4 37.88 -122.23 NEAR BAY -122.26 391800.0
... ... ... ... ... ...
1775707 35.96 -119.04 INLAND -119.04 51700.0
1775708 35.86 -119.46 INLAND -119.46 42700.0
1775709 35.85 -119.12 INLAND -119.12 58300.0
1775710 35.89 -119.27 INLAND -119.27 53300.0
1775711 35.88 -119.27 INLAND -119.27 43700.0

1775712 rows × 5 columns

pd.concat([df_price, df_geo], join="inner", axis=1)
longitude latitude median_house_value latitude longitude ocean_proximity
0 -122.23 37.88 452600.0 37.88 -122.23 NEAR BAY
1 -122.22 37.86 358500.0 37.86 -122.22 NEAR BAY
2 -122.24 37.85 352100.0 37.85 -122.24 NEAR BAY
3 -122.25 37.85 341300.0 37.85 -122.25 NEAR BAY
4 -122.25 37.85 342200.0 37.85 -122.25 NEAR BAY
... ... ... ... ... ... ...
20635 -121.09 39.48 78100.0 39.48 -121.09 INLAND
20636 -121.21 39.49 77100.0 39.49 -121.21 INLAND
20637 -121.22 39.43 92300.0 39.43 -121.22 INLAND
20638 -121.32 39.43 84700.0 39.43 -121.32 INLAND
20639 -121.24 39.37 89400.0 39.37 -121.24 INLAND

20640 rows × 6 columns

df_geo.append(df_geo.sample(1000))
latitude longitude ocean_proximity
0 37.88 -122.23 NEAR BAY
1 37.86 -122.22 NEAR BAY
2 37.85 -122.24 NEAR BAY
3 37.85 -122.25 NEAR BAY
4 37.85 -122.25 NEAR BAY
... ... ... ...
18757 40.67 -122.38 INLAND
14540 32.92 -117.15 <1H OCEAN
19860 36.35 -119.30 INLAND
18808 40.87 -121.67 INLAND
4293 34.09 -118.30 <1H OCEAN

21640 rows × 3 columns

Exercise#

Familiarize yourself with the merge operations and try how inner, left, right, and outer change the merge results.

pd.concat([df_price, df_geo], join="inner", axis=1)
longitude latitude median_house_value latitude longitude ocean_proximity
0 -122.23 37.88 452600.0 37.88 -122.23 NEAR BAY
1 -122.22 37.86 358500.0 37.86 -122.22 NEAR BAY
2 -122.24 37.85 352100.0 37.85 -122.24 NEAR BAY
3 -122.25 37.85 341300.0 37.85 -122.25 NEAR BAY
4 -122.25 37.85 342200.0 37.85 -122.25 NEAR BAY
... ... ... ... ... ... ...
20635 -121.09 39.48 78100.0 39.48 -121.09 INLAND
20636 -121.21 39.49 77100.0 39.49 -121.21 INLAND
20637 -121.22 39.43 92300.0 39.43 -121.22 INLAND
20638 -121.32 39.43 84700.0 39.43 -121.32 INLAND
20639 -121.24 39.37 89400.0 39.37 -121.24 INLAND

20640 rows × 6 columns

Additional Resources#