Merging DataFrames that don't have unique indexes with Python and Pandas

Question

I'm presented with two dataframes. One contains school food ratings for types of foods at different campuses. The first df is student ratings, the second is teacher ratings. The order of the results and the length of the df cannot be guaranteed. Thats said, I need to join the two together.

import pandas as pd 

student_ratings = pd.DataFrame({'food': ['chinese', 'mexican', 'american', 'chinese', 'mexican', 'american'],
                                'campus': [37, 37, 37, 25, 25, 25],
                                'student_rating': [97, 90, 83, 96, 89, 82]})

teacher_ratings = pd.DataFrame({'food': ['chinese', 'mexican', 'american', 'chinese', 'mexican', 'american', 'chinese', 'mexican', 'american'],
                                'campus': [25, 25, 25, 37, 37, 37, 45, 45, 45],
                                'teacher_rating': [87, 80, 73, 86, 79, 72, 67, 62, 65]})

#...

# SOMETHING LIKE WHAT I'M AFTER...
combined_ratings = pd.DataFrame({'food': ['chinese', 'mexican', 'american', 'chinese', 'mexican', 'american', 'chinese', 'mexican', 'american'],
                                 'campus': [25, 25, 25, 37, 37, 37, 45, 45, 45],
                                 'student_rating': [96, 89, 82, 97, 90, 83, Nan, NaN, NaN],
                                 'teacher_rating': [87, 80, 73, 86, 79, 72, 67, 62, 65]})

I basically want to add columns (possibly more than one additional column) but I need to match everything up by food AND campus

simple merge `teacher_ratings.merge(student_ratings,how='outer') ` — BENY, May 09 '18 at 14:28

score 2 · Accepted Answer · answered May 09 '18 at 14:29

Seems like you need an outer merge:

res = pd.merge(student_ratings, teacher_ratings, how='outer')

print(res)

   campus      food  student_rating  teacher_rating
0      37   chinese            97.0              86
1      37   mexican            90.0              79
2      37  american            83.0              72
3      25   chinese            96.0              87
4      25   mexican            89.0              80
5      25  american            82.0              73
6      45   chinese             NaN              67
7      45   mexican             NaN              62
8      45  american             NaN              65

Merging DataFrames that don't have unique indexes with Python and Pandas

1 Answers1