Joining dataframes by keeping common names on the same rows with no nans

Question

I want to join these two data frames in this particular way.

df:

    name ... time
0   Tom  ...  13
1   Tom  ...  16
2   Ben  ...  10
3   Ben  ...  12

and df1:

   name ... time
0   Tom  ...  8
1   Ben  ...  6

to make...

    name ... time time1
0   Tom  ...  13    8
1   Tom  ...  16    8
2   Ben  ...  10    6
3   Ben  ...  12    6

Basically df1 contains the fastest times of Tom and Ben and df contains all other times. I want to merge the dataframes so the fastest time is always on the same row as the other times. Any help would be appreciated. Thanks in advance..

Why have name repeated, since that seems to be the merge key? — ALollz, Jan 07 '19 at 15:35

score 2 · Accepted Answer · answered Jan 07 '19 at 15:51

You simply do that with merge and then rename the column names as desired.

>>> df = pd.merge(df1, df2, how='outer', on='name')
>>> df
  name  time_x  time_y
0  Tom      13       8
1  Tom      16       8
2  Ben      10       6
3  Ben      12       6

Change the column names:

>>> df.rename(columns={'time_x': 'time', 'time_y': 'time1'}, inplace=True)
>>> df
  name  time  time1
0  Tom    13      8
1  Tom    16      8
2  Ben    10      6
3  Ben    12      6

Joining dataframes by keeping common names on the same rows with no nans

1 Answers1