Pandas Merge multiple dataframes on index and column

Question

I am trying to Merge multiple dataframes to one main dataframe using the datetime index and id from main dataframe and datetime and id columns from other dataframes

Main dataframe

DateTime | id | data
(Df.Index)
---------|----|------
2017-9-8 |  1 |  a
2017-9-9 |  2 |  b

df1

id | data1 | data2 | DateTime
---|-------|-------|---------
1  |  a    |   c   | 2017-9-8
2  |  b    |   d   | 2017-9-9
5  |  a    |   e   | 2017-9-20

df2

id | data3 | data4 | DateTime
---|-------|-------|---------
1  |  d    |   c   | 2017-9-8
2  |  e    |   a   | 2017-9-9
4  |  f    |   h   | 2017-9-20

The main dataframe and the other dataframes are in different dictionaries. I want to read from each dictionary and merge when the joining condition (datetime, id) is met

for sleep in dictOfSleep#MainDataFrame:
    for sensorDevice in dictOfSensor#OtherDataFrames:
        try:
  dictOfSleep[sleep]=pd.merge(dictOfSleep[sleep],dictOfSensor[sensorDevice], how='outer',on=['DateTime','id'])

        except:
            print('Join could not be done')

Desired Output:

DateTime | id | data | data1 | data2 | data3 | data4
(Df.Index)
---------|----|------|-------|-------|-------|-------|
2017-9-8 |  1 |  a   |  a    |   c   |   d   |   c   |
2017-9-9 |  2 |  b   |  b    |   d   |   e   |   a   |

Answer 1

I'm not sure how your dictionaries are set up so you will most likely need to modify this but I'd try something like:

for sensorDevice in dictOfSensor:
    df = dictOfSensor[sensorDevice]
    # set df index to match the main_df index
    df = df.set_index(['DateTime'])
    # try join (not merge) when combining on index
    main_df = main_df.join(df, how='outer')

Alternatively, if the id column is very important you can try to first reset your main_df index and then merging.

main_df = main_df.reset_index()
for sensorDevice in dictOfSensor:
    df = dictOfSensor[sensorDevice]
    # try to merge on both columns
    main_df = main_df.merge(df, how='outer', on=['DateTime', 'id])