Pandas merge non multi-index df and df with multi-index columns

Viewed 33

I have one df called la_codes with these columns:

Index(['LADCD', 'LAD18NM'], dtype='object')

and I have a second df called overall_absences with these columns:

MultiIndex([('overall_absences',        'LADCD'),
            ('overall_absences',        'Asian'),
            ('overall_absences',        'Black'),
            ('overall_absences',      'Chinese'),
            ('overall_absences',        'Mixed'),
            ('overall_absences',        'Other'),
            ('overall_absences', 'Unclassified'),
            ('overall_absences',        'White')],
           names=[None, 'Ethnicity'])

I can merge them correctly if overall_absences doesn't have a multi-index using:

pd.merge(la_codes, overall_absences, how="left", on="LADCD")

However, I need to add the multi-index as I will be merging in multiple datasets with the same column names, so 'overall_absences' is needed to differentiate (I don't want to use suffixes). I have tried merge with the below which errors, but I am struggling to find a correct solution.

pd.merge(la_codes, overall_absences, how="left", left_on="LADCD", right_on=["overall_absences", "LADCD"])

Also tried added a multi-index to la_codes which didn't work

pd.merge(la_codes, overall_absences, how="left", left_on=["la_codes", "LADCD"], right_on=["overall_absences", "LADCD"])

How can I do this correctly?

0 Answers
Related