How to join one column from two dataframes to another dataframe ? pd.merge returns nan

Question

I have 3 Data Frames

df1 = pd.DataFrame({'sev_group': {0: '1&2', 1: '3', 2: '4&5'},'Age': {0: '1.0', 1: '10.0', 2: '3.0'}})

df2 = pd.DataFrame({'sev_group': {0: '1&2', 1: '3', 2: '4&5'}, 'Age': {0: '0.0', 1: '24.0', 2: '5.0'}})

df3 = pd.DataFrame({'Count': {'1&2': '7', '3': '82', '4&5': '15'}, 'SLA': {'1&2': '1', '3': '3', '4&5': '5'}, 'Open': {'1&2': '0', '3': '8', '4&5': '5'}, 'Closed/Resolved': {'1&2': '7', '3': '74', '4&5': '10'}})

All the above column values are in dtype object.

df1

df2

df3

I need to add join Age column from df1 as df1Age and df2 as df2Age to df3.

Expected output

i cant able to get it useing pd.merge, it returns NAn values — Sathish Kumar, Nov 11 '20 at 12:20
df3.merge(df1[['Age']], left_on='Closed/Resolved', right_on='Age', how='left') — Sathish Kumar, Nov 11 '20 at 12:23
Do you need `df3 = df3.merge(df1.rename(columns={'Age':'df1Age'}), left_index=True, right_on='sev_group', how='left').merge(df2.rename(columns={'Age':'df2Age'}), on='sev_group', how='left').set_index('sev_group')` ? — jezrael, Nov 11 '20 at 12:25

score 1 · Accepted Answer · answered Nov 11 '20 at 12:24

1

Use the following code:

result = df3.join(df1.set_index('sev_group').Age.rename('df1Age'))\
    .join(df2.set_index('sev_group').Age.rename('df2ge'))

The result is:

    Count SLA Open Closed/Resolved df1Age df2ge
1&2     7   1    0               7    1.0   0.0
3      82   3    8              74   10.0  24.0
4&5    15   5    5              10    3.0   5.0

By the way: Your pictures for content of df1 and df2 are in wrong order, compared to your code sample to create these DataFrames.

answered Nov 11 '20 at 12:24

Valdi_Bo

24,530
2
17
30

Yeah ! Just now noticed Thanks for the help mate – Sathish Kumar Nov 11 '20 at 12:27
better solution like my comment, super! – jezrael Nov 11 '20 at 12:38

How to join one column from two dataframes to another dataframe ? pd.merge returns nan

1 Answers1