Some of the most interesting studies of data come from combining different data sources.
These operations can involve anything from very straightforward concatenation of two different datasets, to more complicated database-style joins and merges that correctly handle any overlaps between the datasets.
DataFrames are built with this type of operation in mind, and Pandas includes functions and methods that make this sort of data wrangling fast and straightforward.
Here we'll take a look at simple concatenation of
DataFrames with the
pd.concat function; later we'll dive into more sophisticated in-memory merges and joins implemented in Pandas.
We begin with the standard imports:
For convenience, we'll define this function which creates a
DataFrame of a particular form that will be useful below:
In addition, we'll create a quick class that allows us to display multiple
DataFrames side by side. The code makes use of the special
_repr_html_ method, which IPython uses to implement its rich object display:
The use of this will become clearer as we continue our discussion in the following section.
DataFrame objects is very similar to concatenation of Numpy arrays, which can be done via the
np.concatenate function as discussed in The Basics of NumPy Arrays.
Recall that with it, you can combine the contents of two or more arrays into a single array:
array([1, 2, 3, 4, 5, 6, 7, 8, 9])
The first argument is a list or tuple of arrays to concatenate.
Additionally, it takes an
axis keyword that allows you to specify the axis along which the result will be concatenated:
array([[1, 2, 1, 2], [3, 4, 3, 4]])
Pandas has a function,
pd.concat(), which has a similar syntax to
np.concatenate but contains a number of options that we'll discuss momentarily:
pd.concat() can be used for a simple concatenation of
DataFrame objects, just as
np.concatenate() can be used for simple concatenations of arrays:
1 A 2 B 3 C 4 D 5 E 6 F dtype: object
It also works to concatenate higher-dimensional objects, such as
By default, the concatenation takes place row-wise within the
pd.concat allows specification of an axis along which concatenation will take place.
Consider the following example:
We could have equivalently specified
axis=1; here we've used the more intuitive
One important difference between
pd.concat is that Pandas concatenation preserves indices, even if the result will have duplicate indices!
Consider this simple example:
Notice the repeated indices in the result.
While this is valid within
DataFrames, the outcome is often undesirable.
pd.concat() gives us a few ways to handle it.
If you'd like to simply verify that the indices in the result of
pd.concat() do not overlap, you can specify the
With this set to True, the concatenation will raise an exception if there are duplicate indices.
Here is an example, where for clarity we'll catch and print the error message:
ValueError: Indexes have overlapping values: [0, 1]
Sometimes the index itself does not matter, and you would prefer it to simply be ignored.
This option can be specified using the
With this set to true, the concatenation will create a new integer index for the resulting
Another option is to use the
keys option to specify a label for the data sources; the result will be a hierarchically indexed series containing the data:
The result is a multiply indexed
DataFrame, and we can use the tools discussed in Hierarchical Indexing to transform this data into the representation we're interested in.
In the simple examples we just looked at, we were mainly concatenating
DataFrames with shared column names.
In practice, data from different sources might have different sets of column names, and
pd.concat offers several options in this case.
Consider the concatenation of the following two
DataFrames, which have some (but not all!) columns in common:
By default, the entries for which no data is available are filled with NA values.
To change this, we can specify one of several options for the
join_axes parameters of the concatenate function.
By default, the join is a union of the input columns (
join='outer'), but we can change this to an intersection of the columns using
Another option is to directly specify the index of the remaininig colums using the
join_axes argument, which takes a list of index objects.
Here we'll specify that the returned columns should be the same as those of the first input:
The combination of options of the
pd.concat function allows a wide range of possible behaviors when joining two datasets; keep these in mind as you use these tools for your own data.
Because direct array concatenation is so common,
DataFrame objects have an
append method that can accomplish the same thing in fewer keystrokes.
For example, rather than calling
pd.concat([df1, df2]), you can simply call