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?