How can I look through 3 diferent columns to match a common number with one column of another dataframe to merge in the data (and if no match append)?

user_6366770 0
Viewed 68

I'm stumped on how to merge df with df1 for the final output (see below). I have tried pd.merge on each of the columns individually 1 time each for a total of 3 times. Before doing a pd.merge, I change the column header to ID1, ID2 or ID2. Finally, if all values in the ID columns are NaN after trying to merge, then I will append that row of data. I'm wondering if there is a simpler way to do this.

EDIT: One rule is you cannot merge on the Account field. In my actual data, sometimes the Account fields are slightly different in the two dataframes, so I must merge on the ID field!

df:

    Account ID1 ID2 ID3 Revenue
0   A       123 789 567 900
1   B       321 234 213 400
2   C           456     700

df1:

    Account Industry    ID
0   A       Tech        123
1   B       Retail      213
2   D       Legal       111

output:

    Account Industry    ID     Revenue
0   A       Tech        123    900
1   B       Retail      213    400
2   C                   456    700
3   D       Legal       111    0
pandas dataframe python
2 Answers

Use:

# step 1a
df2 = df.melt(id_vars=['Account', 'Revenue'], value_name='ID').drop('variable', 1)
# Step 1b (Edited by David Erickson (OP), I needed the column to be a string) in order to merge. Also, I had to have NaNs for step 4, in order for it to properly bring in the ID for Account C.
df2['ID'] = df2['ID'].astype(str).replace('', np.nan, regex=True)

# step 2
df3 = pd.merge(df1, df2, on='ID', how='outer').dropna(subset=['ID'])

# step 3
df3['Account_x'] = df3['Account_x'].fillna(df3.pop('Account_y'))

# step 4
df3 = (
    df3.drop_duplicates(subset=['Account_x'])
    .rename({'Account_x': 'Account'}, axis=1)
    .sort_values(by='Account')
    .reset_index(drop=True)
)

Steps:

# step 1: df2
  Account  Revenue     ID
0       A      900  123.0
1       B      400  321.0
2       C      700    NaN
3       A      900  789.0
4       B      400  234.0
5       C      700  456.0
6       A      900  567.0
7       B      400  213.0
8       C      700    NaN

# step 2: df3
  Account_x Industry     ID Account_y  Revenue
0         A     Tech  123.0         A    900.0
1         B   Retail  213.0         B    400.0
2         D    Legal  111.0       NaN      NaN
3       NaN      NaN  321.0         B    400.0
6       NaN      NaN  789.0         A    900.0
7       NaN      NaN  234.0         B    400.0
8       NaN      NaN  456.0         C    700.0
9       NaN      NaN  567.0         A    900.0

# step 3: df3
  Account_x Industry     ID  Revenue
0         A     Tech  123.0    900.0
1         B   Retail  213.0    400.0
2         D    Legal  111.0      NaN
3         B      NaN  321.0    400.0
6         A      NaN  789.0    900.0
7         B      NaN  234.0    400.0
8         C      NaN  456.0    700.0
9         A      NaN  567.0    900.0


# step 4: df3
  Account Industry     ID  Revenue
0       A     Tech  123.0    900.0
1       B   Retail  213.0    400.0
2       C      NaN  456.0    700.0
3       D    Legal  111.0      NaN
user_128331660

You could put the ID1, ID2, ID3 columns in an ID column and replicate the entries of account and revenue.

Afterwards you can perform a left join on both dataframes

EDIT For the code part: 

import pandas as pd
import numpy as np 

df = pd.DataFrame([
    ['A', 123, 789, 567, 900],
    ['B', 321, 234, 213, 400],
    ['C', None, 456, None, 700]
], columns=['Account', 'ID1', 'ID2', 'ID3', 'Revenue'])
df1 = pd.DataFrame([
    ['A', 'Tech', 123],
    ['B', 'Retail', 213],
    ['D', 'Legal', 111]
], columns = ['Account', 'Industry', 'ID'])

df_new = pd.DataFrame(columns=['Account', 'ID', 'Revenue'])
for ix in ['ID1', 'ID2', 'ID3']:
    df_new = df_new.append(pd.DataFrame(df[['Account', ix, 'Revenue']].values, 
                                        columns=['Account', 'ID', 'Revenue']))
df_new = df_new.dropna()
df_new['ID'] = df_new['ID'].astype(int)
df_new.set_index('ID', inplace=True)
df1.set_index('ID', inplace=True)

output = df1.join(df_new, how='left', lsuffix='_from_df_new')
missing_accounts = set(df['Account'].unique()) - set(output['Account_from_df_new'].unique())  
output = output.append(df_new[df_new['Account'].isin(missing_accounts)])
output['Account'] = output.apply(
    lambda row: 
    row['Account'] 
    if not pd.isnull(row['Account'])
    else row['Account_from_df_new'], axis=1)

output.drop(columns=['Account_from_df_new']).reset_index()

Output: 

    ID Account Industry Revenue
0  123       A     Tech     900
1  213       B   Retail     400
2  111       D    Legal     NaN
3  456       C      NaN     700
user_133901390
Related