Excel中使用Python的多列多对一映射

Question

我有一个映射表，它在Excel中看起来像下面的表，其中列B-L每个都包含来自两个独立数据集的列标题的名称，这些数据集需要映射到由两个独立的数据集组成的新的串联数据集中a列中的新列标题。

A    B    C    D    E    F    G    H    I    J    K    L       
Ant  Car            Bin   
Bat  Bat            Gat  Rat  Mat  Sat  Pat  Fat  Cat  Hat

在上面的例子中，来自数据集1(Col B(的Bat需要映射到新的级联数据集(Col A(中的Bat，并且来自数据集2的列E-L中的列标题需要全部映射到Bat(Col A(，使得数据集2中这些列下面的行中的数据被添加在一起，即

Bat = Gat + Rat + Mat + Sat + Pat + Fat + Cat + Hat

当用户将来可以选择将数据集1或数据集2的列分别添加到C-D列或F-L列时，就会产生上述增加的复杂性，并且希望Python代码能够识别这一点，并以上述添加形式连接数据。

我知道pandas中的groupby功能可以用于对Gat, Mat, Sat etc进行附加分组，但我一直在处理如何允许用户更改映射表的问题。

我曾考虑过使用for循环反复检查每个单元格的值，以检查用户在第C-D列和第F-L列下的输入列标题，并忽略NaN单元格，但我该如何处理映射？

感谢您的帮助。

Answer 1

您需要创建一个辅助映射，它是原始映射的转置，只包括具有多对一映射的列的子集(即Col e-L(。

为具有多个条目的行过滤此映射，即

min_nas = 1
map_sub = map_sub.loc[:, (map_sub.notnull().sum(axis=0) > min_nas)]

转换回正常，使用pd.melt()，并将其附加回原始映射，仅保留Col A、B和E。然后在一对一的基础上进行映射，如下所示：

map_sub = map_sub.T
map_sub.reset_index(level='[insert name of Col A here]', inplace=True)
keys = [c for c in map_sub if c.startswith('[insert Col E-L name prefix here]')]
map_sub = pd.melt(map_sub, id_vars='[insert name of Col A here]', value_vars=keys, value_name='[insert name of Col E here]')

清理：

map_sub = map_sub.dropna() # drop all rows and columns with nan
del map_sub['variable'] # remove variable column

附加到原始映射：

newmapping = mapping.append(map_sub, sort=False, ignore_index=True)
newmapping = newmapping[['[insert name of Col A here]','[insert name of Col B here]', '[insert name of Col E here]']] # keep just 3 relevant columns for one-to-one mapping.

继续在一对一的基础上绘制地图。