pandas need to merge two data frames by column

Question

I have 2 dataframes, first many more rows than the second, and has a column like:

A
A
B
B
C
C

Second is like:

A, blue, 1
B, red, 2
C, yellow, 3

I want to merge them so that I get one dataframe that looks like:

A, blue, 1
A, blue, 1
B, red, 2
B, red, 2
C, yellow, 3
C, yellow, 3

How would I do this?

score 1 · Accepted Answer · answered Nov 16 '20 at 01:04

1

df_left.merge(df_right, left_on=left_column_name, right_on=right_column_name)

Check out the docs

answered Nov 16 '20 at 01:04

Nabil Daoud

221
1
10

Did that work for you? I tried it, did not work for me. – Max Gebhard Nov 16 '20 at 01:12

score 1 · Answer 2 · answered Nov 16 '20 at 01:09

You want all the keys in one dataframe, so you should use merge with the how='left' parameter, as follows.

import pandas as pd

if __name__ == '__main__':
    df = pd.DataFrame(data=['A', 'A', 'B', 'B', 'C', 'C'], columns=['alpha'])
    df2 = pd.DataFrame(data=[['A', 'blue', 1], ['B', 'red', 2], ['C', 'yellow', 3]], columns=['alpha', 'color', 'idx'])
    print(df.merge(df2, how='left', on='alpha'))

outputs

  alpha   color  idx
0     A    blue    1
1     A    blue    1
2     B     red    2
3     B     red    2
4     C  yellow    3
5     C  yellow    3

pandas need to merge two data frames by column

2 Answers2