python-3.x - 如何使用 pandas python 对特定列进行操作第 3 部分

Question

我有以下两个数据框，我想创建第三个 df3 数据框:

df1

  Monday_One     Monday_Two Tuesday
       Water          Water     Ice
        Cold Hot        Hot    Cold
0          4   1          1      10
1          5   2          2       9
2          6   3          7       8
3          7   3          4       7
4          8   5          5       6

df2

   Area         
  Water      Ice
   Cold Hot Cold
0     4   1   10
1     5   2    9
2     6   3    8
3     7   4    7
4     8   5    6

df3

  Monday_One     Monday_Two Tuesday
       Water          Water     Ice
        Cold Hot        Hot    Cold
0         16   1          1     100
1         25   4          4      81
2         36   9         21      64
3         49  12         16      49
4         64  25         25      36

这是生成它的代码:

idx = pd.IndexSlice
data = {'Col1': [4, 5, 6, 7, 8], 'Col2': [1, 2, 3, 3, 5], 'Col3': [1, 2, 7, 4, 5], 'Col4': [10, 9, 8, 7, 6]}
col = pd.MultiIndex.from_tuples([('Monday_One', 'Water', 'Cold'), ('Monday_One', 'Water', 'Hot'),
                                 ('Monday_Two', 'Water', 'Hot'), ('Tuesday', 'Ice', 'Cold')])
df1 = pd.DataFrame(data)
df1.columns = col

data = {'Col1': [4, 5, 6, 7, 8], 'Col2': [1, 2, 3, 4, 5], 'Col3': [10, 9, 8, 7, 6]}
col = pd.MultiIndex.from_tuples([('Area', 'Water', 'Cold'), ('Area', 'Water', 'Hot'),
                                 ('Area', 'Ice', 'Cold')])
df2 = pd.DataFrame(data)
df2.columns = col

data = {'Col1': [0, 0, 0, 0, 0]}
col = pd.MultiIndex.from_tuples([('One', 'One', 'One')])
df3 = pd.DataFrame(data)
df3.columns = col

df3[('Monday_One', 'Water', 'Cold')] = df1[('Monday_One', 'Water', 'Cold')] * df2[('Area', 'Water', 'Cold')]
df3[('Monday_One', 'Water', 'Hot')] = df1[('Monday_One', 'Water', 'Hot')] * df2[('Area', 'Water', 'Hot')]
df3[('Monday_Two', 'Water', 'Hot')] = df1[('Monday_Two', 'Water', 'Hot')] * df2[('Area', 'Water', 'Hot')]
df3[('Tuesday', 'Ice', 'Cold')] = df1[('Tuesday', 'Ice', 'Cold')] * df2[('Area', 'Ice', 'Cold')]

df3 = df3.drop(df3.columns[0], axis=1)

目标是将 df1 中的每一列与 df2 中的相应列相乘，忽略多索引的第一级。

Answer 1

使用DataFrame.rename_axis用于设置 MultiIndex 名称，因此可能按 DataFrame.align 对齐 DataFrames删除第一级 df2 然后删除多个:

df2 = df2.rename_axis(('a','b','c'), axis=1)
df1 = df1.rename_axis(('a','b','c'), axis=1)

df5, df6 = df2.droplevel(0, axis=1).align(df1)
print (df5)
b     Ice      Water                      
c    Cold       Cold        Hot           
a Tuesday Monday_One Monday_One Monday_Two
0      10          4          1          1
1       9          5          2          2
2       8          6          3          3
3       7          7          4          4
4       6          8          5          5


df = df1.mul(df5)
print (df)
a Monday_One     Monday_Two Tuesday
b      Water          Water     Ice
c       Cold Hot        Hot    Cold
0         16   1          1     100
1         25   4          4      81
2         36   9         21      64
3         49  12         16      49
4         64  25         25      36

multiple 的另一个想法是设置列名称，添加原始订单 DataFrame.reorder_levels与 DataFrame.reindex :

df2 = df2.rename_axis(('a','b','c'), axis=1)
df1 = df1.rename_axis(('a','b','c'), axis=1)


df = (df1.mul(df2.droplevel(0, axis=1))
         .reorder_levels(['a','b','c'], axis=1)
         .reindex(df1.columns, axis=1))
print (df)
a Monday_One     Monday_Two Tuesday
b      Water          Water     Ice
c       Cold Hot        Hot    Cold
0         16   1          1     100
1         25   4          4      81
2         36   9         21      64
3         49  12         16      49
4         64  25         25      36