title: Merge one dataframe value based on another following the hierarchic order
body: <p>Given two dataframes which you may download from <a href="https://www.dropbox.com/scl/fi/6md9m10f6md5y1iwvyoo4/test1.xlsx?dl=0&amp;rlkey=bl7yts55wz0y4t4t1g43rpzar" rel="nofollow noreferrer">this link</a>:</p>
  <p>df1:</p>
  <pre><code>  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
  </code></pre>
  <p>df2:</p>
  <pre><code>   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
  </code></pre>
  <p>You can find data in <code>df2</code> doesn't follow the hierarchic order of <code>df1</code>, which means <code>['small_class', 'small_class_id', 'big_class', 'big_class_id', 'category', 'category_id']</code>  ie., for row of <code>aaa1</code>, its <code>small_class</code> is <code>aa1</code>, then, <code>big_class</code> and <code>category</code> should be <code>a1</code> and <code>A</code>, instead of <code>a2</code> and <code>B</code> based on <code>df1</code>, same logic for row of <code>bbb1</code> in <code>df2</code>, if its <code>big_class</code> is <code>b2</code> then <code>category</code> should be <code>B</code> instead of <code>A</code>.</p>
  <p>My trial code:</p>
  <pre><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)
  </code></pre>
  <p>Any ideas how could acheive this in more concise way? Thanks.</p>
  <p>The expected result:</p>
  <pre><code>   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
  </code></pre>