Merge multiple data frames with different sizes using Pandas

Question

I have the following data frames (actually there are more than 3 of them).

import pandas as pd df1 = pd.DataFrame({'head1': ['foo', 'bix', 'bar'],'val': [11, 22, 32]}) df2 = pd.DataFrame({'head2': ['foo', 'xoo', 'bar','qux'],'val': [1, 2, 3,10]}) df3 = pd.DataFrame({'head3': ['xoo', 'bar',],'val': [20, 100]}) # Note that the value in column 'head' is always unique 

What I want to do is combine them based on the head column. And whenever a head does not exist in one data frame, we assigned it NA.

In the end it will look like this:

  head1 head2 head3 ------------------------------- foo 11 1 NA bix 22 NA NA bar 32 3 100 xoo NA 2 20 qux NA 10 NA 

How can I achieve this with Pandas?

Answer 1

You can use pandas.concat by choosing axis=1 to combine multiple DataFrames.

Please note, however, that I first set the index df1, df2, df3 to use variables (foo, bar, etc.), rather than default integers.

 import pandas as pd df1 = pd.DataFrame({'head1': ['foo', 'bix', 'bar'],'val': [11, 22, 32]}) df2 = pd.DataFrame({'head2': ['foo', 'xoo', 'bar','qux'],'val': [1, 2, 3,10]}) df3 = pd.DataFrame({'head3': ['xoo', 'bar',],'val': [20, 100]}) df1 = df1.set_index('head1') df2 = df2.set_index('head2') df3 = df3.set_index('head3') df = pd.concat([df1, df2, df3], axis = 1) columns = ['head1', 'head2', 'head3'] df.columns = columns print(df) head1 head2 head3 bar 32 3 100 bix 22 NaN NaN foo 11 1 NaN qux NaN 10 NaN xoo NaN 2 20