How to combine data from multiple tables#
By default concatenation is along axis 0, so the resulting table combines the rows of the input tables. Let’s check the shape of the original and the concatenated tables to verify the operation:
In [10]: print('Shape of the ``air_quality_pm25`` table: ', air_quality_pm25.shape) Shape of the ``air_quality_pm25`` table: (1110, 4) In [11]: print('Shape of the ``air_quality_no2`` table: ', air_quality_no2.shape) Shape of the ``air_quality_no2`` table: (2068, 4) In [12]: print('Shape of the resulting ``air_quality`` table: ', air_quality.shape) Shape of the resulting ``air_quality`` table: (3178, 4)
Hence, the resulting table has 3178 = 1110 + 2068 rows.
The axis argument will return in a number of pandas methods that can be applied along an axis. A DataFrame has two corresponding axes: the first running vertically downwards across rows (axis 0), and the second running horizontally across columns (axis 1). Most operations like concatenation or summary statistics are by default across rows (axis 0), but can be applied across columns as well.
Sorting the table on the datetime information illustrates also the combination of both tables, with the parameter column defining the origin of the table (either no2 from table air_quality_no2 or pm25 from table air_quality_pm25 ):
In [13]: air_quality = air_quality.sort_values("date.utc") In [14]: air_quality.head() Out[14]: date.utc location parameter value 2067 2019-05-07 01:00:00+00:00 London Westminster no2 23.0 1003 2019-05-07 01:00:00+00:00 FR04014 no2 25.0 100 2019-05-07 01:00:00+00:00 BETR801 pm25 12.5 1098 2019-05-07 01:00:00+00:00 BETR801 no2 50.5 1109 2019-05-07 01:00:00+00:00 London Westminster pm25 8.0
In this specific example, the parameter column provided by the data ensures that each of the original tables can be identified. This is not always the case. The concat function provides a convenient solution with the keys argument, adding an additional (hierarchical) row index. For example:
In [15]: air_quality_ = pd.concat([air_quality_pm25, air_quality_no2], keys=["PM25", "NO2"]) In [16]: air_quality_.head() Out[16]: date.utc location parameter value PM25 0 2019-06-18 06:00:00+00:00 BETR801 pm25 18.0 1 2019-06-17 08:00:00+00:00 BETR801 pm25 6.5 2 2019-06-17 07:00:00+00:00 BETR801 pm25 18.5 3 2019-06-17 06:00:00+00:00 BETR801 pm25 16.0 4 2019-06-17 05:00:00+00:00 BETR801 pm25 7.5
The existence of multiple row/column indices at the same time has not been mentioned within these tutorials. Hierarchical indexing or MultiIndex is an advanced and powerful pandas feature to analyze higher dimensional data.
Multi-indexing is out of scope for this pandas introduction. For the moment, remember that the function reset_index can be used to convert any level of an index to a column, e.g. air_quality.reset_index(level=0)
Feel free to dive into the world of multi-indexing at the user guide section on advanced indexing .
More options on table concatenation (row and column wise) and how concat can be used to define the logic (union or intersection) of the indexes on the other axes is provided at the section on object concatenation .
Join tables using a common identifier#
- Add the station coordinates, provided by the stations metadata table, to the corresponding rows in the measurements table.
Warning The air quality measurement station coordinates are stored in a data file air_quality_stations.csv , downloaded using the py-openaq package.
In [17]: stations_coord = pd.read_csv("data/air_quality_stations.csv") In [18]: stations_coord.head() Out[18]: location coordinates.latitude coordinates.longitude 0 BELAL01 51.23619 4.38522 1 BELHB23 51.17030 4.34100 2 BELLD01 51.10998 5.00486 3 BELLD02 51.12038 5.02155 4 BELR833 51.32766 4.36226
Note The stations used in this example (FR04014, BETR801 and London Westminster) are just three entries enlisted in the metadata table. We only want to add the coordinates of these three to the measurements table, each on the corresponding rows of the air_quality table.
In [19]: air_quality.head() Out[19]: date.utc location parameter value 2067 2019-05-07 01:00:00+00:00 London Westminster no2 23.0 1003 2019-05-07 01:00:00+00:00 FR04014 no2 25.0 100 2019-05-07 01:00:00+00:00 BETR801 pm25 12.5 1098 2019-05-07 01:00:00+00:00 BETR801 no2 50.5 1109 2019-05-07 01:00:00+00:00 London Westminster pm25 8.0
In [20]: air_quality = pd.merge(air_quality, stations_coord, how="left", on="location") In [21]: air_quality.head() Out[21]: date.utc . coordinates.longitude 0 2019-05-07 01:00:00+00:00 . -0.13193 1 2019-05-07 01:00:00+00:00 . 2.39390 2 2019-05-07 01:00:00+00:00 . 2.39390 3 2019-05-07 01:00:00+00:00 . 4.43182 4 2019-05-07 01:00:00+00:00 . 4.43182 [5 rows x 6 columns]
Warning The air quality parameters metadata are stored in a data file air_quality_parameters.csv , downloaded using the py-openaq package.
In [22]: air_quality_parameters = pd.read_csv("data/air_quality_parameters.csv") In [23]: air_quality_parameters.head() Out[23]: id description name 0 bc Black Carbon BC 1 co Carbon Monoxide CO 2 no2 Nitrogen Dioxide NO2 3 o3 Ozone O3 4 pm10 Particulate matter less than 10 micrometers in. PM10
In [24]: air_quality = pd.merge(air_quality, air_quality_parameters, . how='left', left_on='parameter', right_on='id') . In [25]: air_quality.head() Out[25]: date.utc . name 0 2019-05-07 01:00:00+00:00 . NO2 1 2019-05-07 01:00:00+00:00 . NO2 2 2019-05-07 01:00:00+00:00 . NO2 3 2019-05-07 01:00:00+00:00 . PM2.5 4 2019-05-07 01:00:00+00:00 . NO2 [5 rows x 9 columns]
pandas supports also inner, outer, and right joins. More information on join/merge of tables is provided in the user guide section on database style merging of tables . Or have a look at the comparison with SQL page.
REMEMBER
- Multiple tables can be concatenated both column-wise and row-wise using the concat function.
- For database-like merging/joining of tables, use the merge function.
See the user guide for a full description of the various facilities to combine data tables .
How to reshape the layout of tables
How to handle time series data with ease
pandas.DataFrame.merge#
DataFrame. merge ( right , how = ‘inner’ , on = None , left_on = None , right_on = None , left_index = False , right_index = False , sort = False , suffixes = (‘_x’, ‘_y’) , copy = None , indicator = False , validate = None ) [source] #
Merge DataFrame or named Series objects with a database-style join.
A named Series object is treated as a DataFrame with a single named column.
The join is done on columns or indexes. If joining columns on columns, the DataFrame indexes will be ignored. Otherwise if joining indexes on indexes or indexes on a column or columns, the index will be passed on. When performing a cross merge, no column specifications to merge on are allowed.
If both key columns contain rows where the key is a null value, those rows will be matched against each other. This is different from usual SQL join behaviour and can lead to unexpected results.
Type of merge to be performed.
- left: use only keys from left frame, similar to a SQL left outer join; preserve key order.
- right: use only keys from right frame, similar to a SQL right outer join; preserve key order.
- outer: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically.
- inner: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys.
- cross: creates the cartesian product from both frames, preserves the order of the left keys.
Column or index level names to join on. These must be found in both DataFrames. If on is None and not merging on indexes then this defaults to the intersection of the columns in both DataFrames.
left_on label or list, or array-like
Column or index level names to join on in the left DataFrame. Can also be an array or list of arrays of the length of the left DataFrame. These arrays are treated as if they are columns.
right_on label or list, or array-like
Column or index level names to join on in the right DataFrame. Can also be an array or list of arrays of the length of the right DataFrame. These arrays are treated as if they are columns.
left_index bool, default False
Use the index from the left DataFrame as the join key(s). If it is a MultiIndex, the number of keys in the other DataFrame (either the index or a number of columns) must match the number of levels.
right_index bool, default False
Use the index from the right DataFrame as the join key. Same caveats as left_index.
sort bool, default False
Sort the join keys lexicographically in the result DataFrame. If False, the order of the join keys depends on the join type (how keyword).
suffixes list-like, default is (“_x”, “_y”)
A length-2 sequence where each element is optionally a string indicating the suffix to add to overlapping column names in left and right respectively. Pass a value of None instead of a string to indicate that the column name from left or right should be left as-is, with no suffix. At least one of the values must not be None.
copy bool, default True
If False, avoid copy if possible.
indicator bool or str, default False
If True, adds a column to the output DataFrame called “_merge” with information on the source of each row. The column can be given a different name by providing a string argument. The column will have a Categorical type with the value of “left_only” for observations whose merge key only appears in the left DataFrame, “right_only” for observations whose merge key only appears in the right DataFrame, and “both” if the observation’s merge key is found in both DataFrames.
validate str, optional
If specified, checks if merge is of specified type.
- “one_to_one” or “1:1”: check if merge keys are unique in both left and right datasets.
- “one_to_many” or “1:m”: check if merge keys are unique in left dataset.
- “many_to_one” or “m:1”: check if merge keys are unique in right dataset.
- “many_to_many” or “m:m”: allowed, but does not result in checks.
A DataFrame of the two merged objects.
Merge with optional filling/interpolation.
Similar method using indices.
Support for specifying index levels as the on , left_on , and right_on parameters was added in version 0.23.0 Support for merging named Series objects was added in version 0.24.0
>>> df1 = pd.DataFrame('lkey': ['foo', 'bar', 'baz', 'foo'], . 'value': [1, 2, 3, 5]>) >>> df2 = pd.DataFrame('rkey': ['foo', 'bar', 'baz', 'foo'], . 'value': [5, 6, 7, 8]>) >>> df1 lkey value 0 foo 1 1 bar 2 2 baz 3 3 foo 5 >>> df2 rkey value 0 foo 5 1 bar 6 2 baz 7 3 foo 8
Merge df1 and df2 on the lkey and rkey columns. The value columns have the default suffixes, _x and _y, appended.
>>> df1.merge(df2, left_on='lkey', right_on='rkey') lkey value_x rkey value_y 0 foo 1 foo 5 1 foo 1 foo 8 2 foo 5 foo 5 3 foo 5 foo 8 4 bar 2 bar 6 5 baz 3 baz 7
Merge DataFrames df1 and df2 with specified left and right suffixes appended to any overlapping columns.
>>> df1.merge(df2, left_on='lkey', right_on='rkey', . suffixes=('_left', '_right')) lkey value_left rkey value_right 0 foo 1 foo 5 1 foo 1 foo 8 2 foo 5 foo 5 3 foo 5 foo 8 4 bar 2 bar 6 5 baz 3 baz 7
Merge DataFrames df1 and df2, but raise an exception if the DataFrames have any overlapping columns.
>>> df1.merge(df2, left_on='lkey', right_on='rkey', suffixes=(False, False)) Traceback (most recent call last): . ValueError: columns overlap but no suffix specified: Index(['value'], dtype='object')
>>> df1 = pd.DataFrame('a': ['foo', 'bar'], 'b': [1, 2]>) >>> df2 = pd.DataFrame('a': ['foo', 'baz'], 'c': [3, 4]>) >>> df1 a b 0 foo 1 1 bar 2 >>> df2 a c 0 foo 3 1 baz 4
>>> df1.merge(df2, how='inner', on='a') a b c 0 foo 1 3
>>> df1.merge(df2, how='left', on='a') a b c 0 foo 1 3.0 1 bar 2 NaN
>>> df1 = pd.DataFrame('left': ['foo', 'bar']>) >>> df2 = pd.DataFrame('right': [7, 8]>) >>> df1 left 0 foo 1 bar >>> df2 right 0 7 1 8
>>> df1.merge(df2, how='cross') left right 0 foo 7 1 foo 8 2 bar 7 3 bar 8