Joining tables or mapping values from a table to another table

Question

I have two dataframes where I want to map multiple columns from the smaller df to the bigger df. The bigger df is 5000 rows and I want to join based on conditions from the smaller tables. For example the bigger dataframe is:

status    type    slot    br
2         1       2       5
2         1       1       5
2         1       2       5
2         1       2       5
2         1       56       26
2         1       76       5

The second dataframe is as follows:

slot    name    from   to     br
1       4PM     16     19     5
2       7PM     19     22     5
3       10PM    10     12     5
76      1PM     13     16     5
56      Lun     12     14     26

So basically I want to map the columns in the second dataframe to the first one based one the two columns "slot" and "br" so that the end result will have a join between the two as follows:

status    type    slot    br    name    from   to
2         1       2       5     7PM     19     22
2         1       1       5     4PM     16     19
2         1       2       5     7PM     19     22
2         1       2       5     7PM     19     22
2         1       56      26    Lun     12     14
2         1       76      5     1PM     13     16

I tried using if statements but gave me an error. Though I think need a more efficient solution using joins or an if should be fine too

score 0 · Answer 1 · answered May 16 '19 at 23:33

This is a simple merge, the how parameter specifies how you want the merge to work, in this instance using the keys on your left frame.

new_df = pd.merge(df,df2,on=['slot','br'],how='left')
print(new_df)
    status  type    slot    br  name    from    to
0   2   1   2   5   7PM 19  22
1   2   1   1   5   4PM 16  19
2   2   1   2   5   7PM 19  22
3   2   1   2   5   7PM 19  22
4   2   1   56  26  Lun 12  14
5   2   1   76  5   1PM 13  16

i would caution you to first understand how merges work before going any further, check out Pandas Merging 101

score 0 · Accepted Answer · answered May 17 '19 at 00:24

This should work:

df = pd.DataFrame({'status': [2, 2, 2, 2, 2, 2],
          'type': [1, 1, 1, 1, 1, 1],
          'slot': [2, 1, 2, 2, 56, 76],
          'br': [5.0, 5.0, 5.0, 5.0, 26.0, np.nan]})
df2 = pd.DataFrame({'slot': [1, 2, 3, 76, 56],
          'name': ['4PM', '7PM', '10PM', '1PM', 'Lun'],
          'from': [16, 19, 10, 13, 12],
          'to': [19, 22, 12, 16, 14],
          'br': [5, 5, 5, 5, 26]})

print(df.merge(df2,on=['slot','br'],how='left'))

   status  type  slot    br name  from    to
0      2     1     2   5.0  7PM  19.0  22.0
1      2     1     1   5.0  4PM  16.0  19.0
2      2     1     2   5.0  7PM  19.0  22.0
3      2     1     2   5.0  7PM  19.0  22.0
4      2     1    56  26.0  Lun  12.0  14.0
5      2     1    76   NaN  NaN   NaN   NaN

Joining tables or mapping values from a table to another table

2 Answers2