如何基于一个DataFrame的列名映射另一个DataFrame的行值？

我需要将values DataFrame的内容，按照keys DataFrame中列名对应的类别，映射到目标desired DataFrame的行中。例如：

• desired['block_1'][0]对应values['apple'][0]
• desired['block_2'][0]对应values['orange'][0]
  以此类推，目标DataFrame的每一行都要和keys对应行的类别匹配，取值则来自values的对应行和类别列。

示例数据：keys DataFrame #

import pandas as pd

block_1 = ['apple', 'apple', 'apple', 'apple', 'apple', 'apple']
block_2 = ['orange', 'berry', 'pear', 'pear', 'orange', 'berry']
block_3 = ['pear', 'pear', 'orange', 'berry', 'berry', 'orange']
block_4 = ['berry', 'orange', 'berry', 'orange', 'pear', 'pear']

keys = pd.DataFrame({'block_1': block_1, 'block_2': block_2, 'block_3': block_3, 'block_4': block_4})

示例数据：values DataFrame #

apple = [('apple_1', 'apple_3', 'apple_2'), 
         ('apple_2', 'apple_3', 'apple_1'), 
         ('apple_3', 'apple_1', 'apple_2'), 
         ('apple_3', 'apple_2', 'apple_1'), 
         ('apple_1', 'apple_2', 'apple_3'), 
         ('apple_2', 'apple_1', 'apple_3')]
pear = [('pear_1', 'pear_3', 'pear_2'), 
         ('pear_2', 'pear_3', 'pear_1'), 
         ('pear_3', 'pear_1', 'pear_2'), 
         ('pear_3', 'pear_2', 'pear_1'), 
         ('pear_1', 'pear_2', 'pear_3'), 
         ('pear_2', 'pear_1', 'pear_3')]
orange = [('orange_1', 'orange_3', 'orange_2'), 
         ('orange_2', 'orangee_3', 'orange_1'), 
         ('orange_3', 'orange_1', 'orange_2'), 
         ('orange_3', 'orange_2', 'orange_1'), 
         ('orange_1', 'orange_2', 'orange_3'), 
         ('orange_2', 'orange_1', 'orange_3')]
berry = [('berry_1', 'berry_3', 'berry_2'), 
         ('berry_2', 'berry_3', 'berry_1'), 
         ('berry_3', 'berry_1', 'berry_2'), 
         ('berry_3', 'berry_2', 'berry_1'), 
         ('berry_1', 'berry_2', 'berry_3'), 
         ('berry_2', 'berry_1', 'berry_3')]
          
values = pd.DataFrame({'apple': apple, 'pear': pear, 'orange': orange, 'berry': berry})

期望输出：desired DataFrame #

desired = pd.DataFrame({'block_1': [('apple_1', 'apple_3', 'apple_2'), 
                                     ('apple_2', 'apple_3', 'apple_1'), 
                                     ('apple_3', 'apple_1', 'apple_2'), 
                                     ('apple_3', 'apple_2', 'apple_1'), 
                                     ('apple_1', 'apple_2', 'apple_3'), 
                                     ('apple_2', 'apple_1', 'apple_3')], 
                        'block_2': [('orange_1', 'orange_3', 'orange_2'),
                                  ('berry_2', 'berry_3', 'berry_1'),
                                 ('pear_3', 'pear_1', 'pear_2'),
                                  ('pear_3', 'pear_2', 'pear_1'),
                                  ('orange_1', 'orange_2', 'orange_3'),
                                  ('berry_2', 'berry_1', 'berry_3')],
                        'block_3': [('pear_1', 'pear_3', 'pear_2'),
                                  ('pear_2', 'pear_3', 'pear_1'),
                                  ('orange_3', 'orange_1', 'orange_2'),
                                 ('berry_3', 'berry_2', 'berry_1'),
                                  ('berry_1', 'berry_2', 'berry_3'),
                                  ('orange_2', 'orange_1', 'orange_3')],
                        'block_4': [('berry_1', 'berry_3', 'berry_2'),
                                  ('orange_2', 'orangee_3', 'orange_1'),
                                  ('berry_3', 'berry_1', 'berry_2'),
                                  ('orange_3', 'orange_2', 'orange_1'),
                                  ('pear_1', 'pear_2', 'pear_3'),
                             ('pear_2', 'pear_1', 'pear_3')]})

简洁实现方法 #

可以利用Pandas的lookup方法高效完成映射，有两种简洁写法：

方法1：基于整表的lookup + 重塑形状

desired = pd.DataFrame(
    values.lookup(values.index, keys.values.ravel()).reshape(keys.shape),
    columns=keys.columns
)

方法2：逐列处理（更直观）

desired = keys.apply(lambda col: values.lookup(values.index, col), axis=0)

说明 #

• values.lookup(row_labels, col_labels)会根据提供的行索引和列名数组，提取对应位置的元素
• 方法1中，keys.values.ravel()将keys的二维值数组转成一维，一次性提取所有需要的值后再重塑成keys的形状
• 方法2则对keys的每一列单独处理，提取对应列的映射值，最终组合成目标DataFrame

内容的提问来源于stack exchange，提问作者psychcoder