pandas - how to merge tables with multiindexes

Question

I have two tables that have similar multiindex structure: date and country__name. Indexes are not identical: some of the countries might be missing from one or another table.

The tables have different columns. To illustrate, here they are:

I want to merge them into one table that keeps the multiindex but has all the columns from both tables.

But when I do

pandas.concat([grouped_channel_df, grouped_tds_df], axis=1)

I get a table full of NaN:

what am I missing?

Answer 1

If you wish to merge table you need to use .merge instead of .concat. Check the difference between both concepts here

For your use case, try something like this:

merged = pandas.merge(grouped_channel_df, grouped_tds_df, how='outer', on=('date','country_name'), suffixes=('_channel','_tds'))

Read the documentation above to read other options an