将两个Pandas分组对象相加

Question

我有两个基于pandas的分组对象，我想将它们的值求和。我无法弄清楚如何合并这两个数据框，以便列CALL_BLOCK具有该DOW下的所有十个调用块，并且还要对值进行求和。我尝试了几种方法，例如重置索引和合并两个数据框，但仍然无法获取CALL_BLOCKS列中的所有十个调用块。非常感谢您的帮助。谢谢！

df1 = {('1-100019B', 'a_8:00AM to 9:00AM'): 0.6493506493506493,
 ('1-100019B', 'b_9:00AM to 10:00AM'): 0.7272727272727273,
 ('1-100019B', 'c_10:00AM to 11:00AM'): 0.16883116883116883,
 ('1-100019B', 'd_11:00AM to 12:00PM'): 0.025974025974025976,
 ('1-100019B', 'e_12:00PM to 1:00PM'): 0.38961038961038963,
 ('1-100019B', 'f_1:00PM to 2:00PM'): 0.14285714285714285,
 ('1-100019B', 'g_2:00PM to 3:00PM'): 0.0,
 ('1-100019B', 'h_3:00PM to 4:00PM'): 0.12987012987012986,
 ('1-100019B', 'i_4:00PM to 5:00PM'): 0.0,
 ('1-100019B', 'j_After 5PM'): 0.0}

df2 = 
{('1-100019B', 0, 'a_8:00AM to 9:00AM'): 0.5,
 ('1-100019B', 0, 'b_9:00AM to 10:00AM'): 0.6666666666666666,
 ('1-100019B', 0, 'c_10:00AM to 11:00AM'): 0.25,
 ('1-100019B', 0, 'e_12:00PM to 1:00PM'): 0.3333333333333333,
 ('1-100019B', 0, 'f_1:00PM to 2:00PM'): 0.0,
 ('1-100019B', 0, 'h_3:00PM to 4:00PM'): 1.0}

期望输出：

df = 
CONTACT_ID  DOW  CALL_BLOCKS         
1-100019B   0    a_8:00AM to 9:00AM      1.149
                 b_9:00AM to 10:00AM     1.380
                 c_10:00AM to 11:00AM    0.410
                 d_11:00AM to 12:00PM    0.026
                 e_12:00PM to 1:00PM     0.710
                 f_1:00PM to 2:00PM      0.140
                 g_2:00PM to 3:00PM      0.000
                 h_3:00PM to 4:00PM      1.120
                 i_4:00PM to 5:00PM      0.000
                 j_After 5PM             0.000

- Krishnang K Dalal

---

你能把 df1.to_dict() 和 df2.to_dict() 加到这个问题里吗？ - Scott Boston

嗨，斯科特，已编辑。这有帮助吗？ - Krishnang K Dalal

Answer 1

从您的第二个数据框中删除未使用的MultiIndex级别，然后使用pd.Series.add：

df2.index = df2.index.droplevel(1)

res = df1.add(df2, fill_value=0)

print(res)

                                0
idx1      idx3                          
1-100019B a_8:00AM to 9:00AM    1.149351
          b_9:00AM to 10:00AM   1.393939
          c_10:00AM to 11:00AM  0.418831
          d_11:00AM to 12:00PM  0.025974
          e_12:00PM to 1:00PM   0.722944
          f_1:00PM to 2:00PM    0.142857
          g_2:00PM to 3:00PM    0.000000
          h_3:00PM to 4:00PM    1.129870
          i_4:00PM to 5:00PM    0.000000
          j_After 5PM           0.000000

设置

这是我用来从您的输入字典到MultiIndex系列的代码，这就是您在groupby操作的输出中看到的内容。

df1 = pd.DataFrame.from_dict(df1, orient='index').reset_index()
df1 = df1.join(pd.DataFrame(df1['index'].values.tolist(), columns=['idx1', 'idx3'])).drop('index', 1)
df1 = df1.set_index(['idx1', 'idx3'])

df2 = pd.DataFrame.from_dict(df2, orient='index').reset_index()
df2 = df2.join(pd.DataFrame(df2['index'].values.tolist(), columns=['idx1', 'idx2', 'idx3'])).drop('index', 1)
df2 = df2.set_index(['idx1', 'idx2', 'idx3'])

- jpp

---

谢谢您的回答。我不能删除 level=1 (DOW)，因为我想要与我在期望输出下描述的类似于 DOW 列的特定值。 - Krishnang K Dalal

使用 reset_index() 在这些 group by 对象上进行转换，然后在数据帧中处理它们会更简单，这种情况下输出将是所描述的数据帧格式吗？ - Krishnang K Dalal

Answer 2

使用 @jpp setup。

df1.merge(df2.reset_index('DOW'), on=['CONTACTS_ID','CALL_BLOCKS'], how='outer')\
   .set_index('DOW', append=True).sum(1)

输出：

CONTACTS_ID  CALL_BLOCKS           DOW
1-100019B    a_8:00AM to 9:00AM    0.0    1.149351
             b_9:00AM to 10:00AM   0.0    1.393939
             c_10:00AM to 11:00AM  0.0    0.418831
             d_11:00AM to 12:00PM  NaN    0.025974
             e_12:00PM to 1:00PM   0.0    0.722944
             f_1:00PM to 2:00PM    0.0    0.142857
             g_2:00PM to 3:00PM    NaN    0.000000
             h_3:00PM to 4:00PM    0.0    1.129870
             i_4:00PM to 5:00PM    NaN    0.000000
             j_After 5PM           NaN    0.000000
dtype: float64

- Scott Boston

---

这很有帮助。谢谢。 - Krishnang K Dalal

@KrishnangKDalal 我很高兴这有所帮助。不用谢。祝你编程愉快！ - Scott Boston