How can I join two dataframes based on matching values in one column?
I start with two dataframes:
Breakfast Lunch
1 Monday Cereal Sandwich
2 Wednesday Eggs Salad
Dinner Snack
1 Monday Ham Chips
2 Tuesday Pasta Nuts
3 Wednesday Chicken Apple
How can I join these two dataframes comparing weekdays in to something like:
Breakfast Lunch Dinner Snack
1 Monday Cereal Sandwich Ham Chips
2 Tuesday Pasta Nuts
3 Wednesday Eggs Salad Chicken Apple
You can join
(to take advantage of the fact that you're merging on index); then groupby
index level 1 (which are days of the week) and use first
(which by default drops NaN values)
out = df1.join(df2, how='outer').groupby(level=1).first().replace({None:''})
Output:
Breakfast Lunch Dinner Snack
Monday Cereal Sandwich Ham Chips
Tuesday Pasta Nuts
Wednesday Eggs Salad Chicken Apple