Merge one dataframe value based on another following the hierarchic order

user_8410477 0
Viewed 64

Given two dataframes which you may download from this link:

df1:

  category  category_id big_class  big_class_id small_class  small_class_id
0        A            1        a1            11         aa1             111
1        A            1        a2            12         aa2             112
2        B            2        b1            21         bb1             221
3        B            2        b2            22         bb2             222
4        B            2        b3            23         bb3             223

df2:

   item small_class  small_class_id  ... big_class_id  category category_id
0  aaa1         aa1           111.0  ...         12.0         B         2.0
1  aaa2         aa2           112.0  ...         11.0       NaN         NaN
2  bbb3         NaN             NaN  ...          NaN         B         2.0
3  bbb4         bb3           223.0  ...          NaN         A         1.0
4  bbb1         NaN             NaN  ...         23.0         A         1.0

You can find data in df2 doesn't follow the hierarchic order of df1, which means ['small_class', 'small_class_id', 'big_class', 'big_class_id', 'category', 'category_id'] ie., for row of aaa1, its small_class is aa1, then, big_class and category should be a1 and A, instead of a2 and B based on df1, same logic for row of bbb1 in df2, if its big_class is b2 then category should be B instead of A.

My trial code:

df = pd.merge(df2, df1, on = 'small_class', how = 'left')

df['category'] = df['category_y'].fillna(df['category_x'])
df['big_class'] = df['big_class_y'].fillna(df['big_class_x'])
    
df.drop(['big_class_x', 'category_x', 'category_y',
       'big_class_y'], axis=1, inplace=True)

df3 = pd.merge(df, df1[['big_class', 'category']], on = 'big_class', how = 'left')
df3['category'] = df3['category_y'].fillna(df3['category_x'])

df3.drop(['category_y', 'category_x'], axis=1, inplace=True)

Any ideas how could acheive this in more concise way? Thanks.

The expected result:

   item small_class  small_class_id  ... big_class_id     category category_id
0  aaa1         aa1           111.0  ...         11.0            A           1
1  aaa2         aa2           112.0  ...         12.0            A           1
2  bbb3         NaN             NaN  ...          NaN            B           2
3  bbb4         bb3           223.0  ...         23.0            B           2
4  bbb1         NaN             NaN  ...         22.0            B           2
pandas dataframe python-3.x
2 Answers

If I understand correctly - assuming that df1 contains the most up-to-date data and you'd like to use it to update the values in df2, using the pandas update method:

df1 = df1.set_index('small_class')
df2 = df2.set_index('small_class')

df2.update(df1)

df1 = df1.set_index('big_class')
df2 = df2.set_index('big_class')

df2.update(df1[['category', 'category_id', 'big_class_id']])
df2.reset_index()

Out:

  small_class  item  small_class_id big_class  big_class_id category  \
0         aa1  aaa1           111.0        a1          11.0        A   
1         aa2  aaa2           112.0        a2          12.0        A   
2         NaN  bbb3             NaN       NaN           NaN        B   
3         bb3  bbb4           223.0        b3          23.0        B   
4         NaN  bbb1             NaN        b2          23.0        A   

   category_id  
0          1.0  
1          1.0  
2          2.0  
3          2.0  
4          1.0
user_93373040 
condition_1_mask = (~df2['small_class'].isnull())
df2.loc[condition_1_mask, ['big_class', 'category', 'category_id', 'big_class_id']] = df2[['small_class']][condition_1_mask].reset_index().merge(df1, how='left', on=['small_class'])[['big_class', 'category', 'category_id', 'big_class_id', 'index']].set_index('index')
condition_2_mask = ((df2['small_class'].isnull()) & (~df2['big_class'].isnull()))
df2.loc[condition_2_mask, ['big_class', 'category', 'category_id', 'big_class_id']] = df2[['small_class', 'big_class']][condition_2_mask].reset_index().merge(df1, how='left', on=['big_class'])[['big_class', 'category', 'category_id', 'big_class_id' ,'index']].set_index('index')
print(df2)

Out:

   item small_class  small_class_id big_class  big_class_id category  \
0  aaa1         aa1           111.0        a1          11.0        A   
1  aaa2         aa2           112.0        a2          12.0        A   
2  bbb3         NaN             NaN       NaN           NaN        B   
3  bbb4         bb3           223.0        b3          23.0        B   
4  bbb1         NaN             NaN        b2          22.0        B   

   category_id  
0          1.0  
1          1.0  
2          2.0  
3          2.0  
4          2.0
user_107207230
Related