Pandas 添加两个多索引数据帧 [英] Pandas adding two Multiindex Dataframes

问题描述

我正在尝试将两个具有多索引列和不同索引大小的数据帧添加在一起.什么是最优雅的解决方案.示例是:

I'm trying to add two dataframes with Multiindex Columns and different index sizes together. What is the most elegant solution. And example is:

names = ['Level 0', 'Level 1']
cols1 = pd.MultiIndex.from_arrays([['A', 'A', 'B'],['A1', 'A2', 'B1']], names = names)
cols2 = pd.MultiIndex.from_arrays([['A', 'A', 'B'],['A1', 'A3', 'B1']], names = names)
df1 = pd.DataFrame(np.random.randn(1, 3), index=range(1), columns=cols1)
df2 = pd.DataFrame(np.random.randn(5, 3), index=range(5), columns=cols2)
print(df1)
print(df2)

Level 0         A                   B
Level 1        A1        A2        B1 
0       -0.116975 -0.391591  0.446029

Level 0         A                   B
Level 1        A1        A3        B1
0        1.179689  0.693096 -0.102621
1       -0.913441  0.187332  1.465217
2       -0.089724 -1.907706 -0.963699
3        0.203217 -1.233399  0.006726
4        0.218911 -0.027446  0.982764

现在我尝试将 df1 添加到 df2，其逻辑是刚刚添加了缺失的列，并将 df1 的索引 0 添加到 df2 中的所有索引.

Now I try to add df1 to df2 with the logic that missing columns are just added and that the index 0 of df1 is added to all indices in df2.

所以我希望上面的数字:

So I would expect with the above numbers:

  Level 0          A                                   B
  Level 1         A1           A2          A3         B1
  0         1.062714    -0.391591    0.693096   0.343408
  1        -1.030416    -0.391591    0.187332   1.911246 
  2        -0.206699    -0.391591   -1.907706   -0.51767
  3         0.086242    -0.391591   -1.233399   0.452755
  4         0.101936    -0.391591   -0.027446   1.428793

速度和内存效率最高的解决方案是什么?任何帮助表示赞赏.

What is the most speed and memory efficient solution? Any help appreciated.

推荐答案

设置

In [76]: df1
Out[76]: 
Level 0        A                   B
Level 1       A1        A2        B1
0       -0.28667  1.852091 -0.134793

In [77]: df2
Out[77]: 
Level 0         A                   B
Level 1        A1        A3        B1
0       -0.023582 -0.713594  0.487355
1        0.628819  0.764721 -1.118777
2       -0.572421  1.326448 -0.788531
3       -0.160608  1.985142  0.344845
4       -0.184555 -1.075794  0.630975

这将对齐框架并用 0 填充 nan但不广播

This will align the frames and fill the nan's with 0 but not broadcast

In [63]: df1a,df2a = df1.align(df2,fill_value=0)

In [64]: df1a+df2a
Out[64]: 
Level 0         A                             B
Level 1        A1        A2        A3        B1
0       -0.310253  1.852091 -0.713594  0.352561
1        0.628819  0.000000  0.764721 -1.118777
2       -0.572421  0.000000  1.326448 -0.788531
3       -0.160608  0.000000  1.985142  0.344845
4       -0.184555  0.000000 -1.075794  0.630975

这是第一个广播的方式

In [65]: df1a,df2a = df1.align(df2)

In [66]: df1a.ffill().fillna(0) + df2a.fillna(0)
Out[66]: 
Level 0         A                             B
Level 1        A1        A2        A3        B1
0       -0.310253  1.852091 -0.713594  0.352561
1        0.342149  1.852091  0.764721 -1.253570
2       -0.859091  1.852091  1.326448 -0.923324
3       -0.447278  1.852091  1.985142  0.210052
4       -0.471226  1.852091 -1.075794  0.496181

这篇关于Pandas 添加两个多索引数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！