Pandas：合并数据帧但求和重叠列

Question

我读了很多关于merge()和join()方法的帖子，并尝试用这些方法解决自己的问题，但没有找到解决方案。在

我有一个非常大的数据文件（.csv），其中包含各种ID的每小时消耗量。我想把每个月每个身份证的消费总额加起来。在

由于内存限制，我需要使用read_csv分块处理每小时消耗的文件（使用chunk_size选项），并在几个月内为ID加载消耗的数据帧，例如：

df1 = 
 Month  Dec  Nov
ID             
XXX    4.0  1.0
YYY    8.0  3.0
ZZZ    4.0  1.0 

df2 = 
 Month  Dec  Nov  Oct
ID                  
AAA    1.0  7.0  9.0
BBB    0.0  NaN  2.0
YYY    5.0  5.0  0.0

此帖子的生成者：

^{pr2}$

请注意，0.0和{}的消耗是不同的。0.0表示当月至少有一个消耗读数{}，但{}表示根本没有记录消耗值，在这种情况下，不能假定为0。就我的目的而言，这种差异必须保持鲜明。

因为数据文件是分块处理的，所以有一些ID出现在多个数据帧中，例如YYY，对于这些ID，有时月份也会重叠，例如，Nov表示ID YYY。在这种情况下，上半月的消费在df1中，下半月在{}中。在

因此，为了合计消耗量，我需要按“ID”和重叠的“Months”中的sum值合并这些数据帧。在

直接求和数据帧产生许多nan：

df1 + df2 = 
 Month   Dec  Nov  Oct
ID                   
AAA     NaN  NaN  NaN
BBB     NaN  NaN  NaN
XXX     NaN  NaN  NaN
YYY    13.0  8.0  NaN
ZZZ     NaN  NaN  NaN

我假设这是因为当对df1的id/Months进行求和时，它返回一个NaN。在

外部合并生成重叠月份的后缀列：

df1.merge(df2,how='outer',on='ID') = 
 Month  Dec_x  Nov_x  Dec_y  Nov_y  Oct
ID                                    
XXX      4.0    1.0    NaN    NaN  NaN
YYY      8.0    3.0    5.0    5.0  0.0
ZZZ      4.0    1.0    NaN    NaN  NaN
AAA      NaN    NaN    1.0    7.0  9.0
BBB      NaN    NaN    0.0    NaN  2.0

我也不能让combine_first做我想做的事。在

我想要的是中间的东西，看起来像这样：

 Month   Dec  Nov  Oct
ID                   
XXX      4.0  1.0  NaN
YYY     13.0  8.0  0.0
ZZZ      4.0  1.0  NaN
AAA      1.0  7.0  9.0
BBB      0.0  NaN  2.0

其中重叠月份的总和为x + NaN = x、NaN + y = y和{}。

---

我可以看到一个解决方案，即进行合并，然后求和重叠列，忽略nan：

df3 = df1.merge(df2,how='outer',on='ID',suffixes=['','_x'])
overlapping_months_sufx = df3.columns.values[df3.columns.str.endswith('_x')]
for mnth_sufx in overlapping_months_sufx:
    mnth = mnth_sufx[:-2]
    df3[mnth][df3[mnth_sufx].notnull()] = df3[mnth].fillna(0) + df3[mnth_sufx]
    df3=df3.drop(columns=mnth_sufx)
df3 = 
 Month   Dec  Nov  Oct
ID                   
XXX     4.0  1.0  NaN
YYY    13.0  8.0  0.0
ZZZ     4.0  1.0  NaN
AAA     1.0  7.0  9.0
BBB     0.0  NaN  2.0

考虑到这个数据集的大小，最好能用最有效的方法来聚合所有这些数据。有没有更好的方法，也许是一步到位？在

谢谢， 克里斯

Answer 1

这是一个尝试。如果我理解正确，请留言。在

给予：

>>> df1                                                                                                                
Month  Dec  Nov
ID             
XXX    4.0  1.0
YYY    8.0  3.0
ZZZ    4.0  1.0
>>> df2                                                                                                                
Month  Dec  Nov  Oct
ID                  
AAA    1.0  7.0  9.0
BBB    0.0  NaN  2.0
YYY    5.0  5.0  0.0

解决方案：

^{pr2}$

---

说明：

串联只是将df2放在df1之下。在

>>> cat = pd.concat([df1, df2])                                                                                        
>>> cat                                                                                                                
     Dec  Nov  Oct
ID                
XXX  4.0  1.0  NaN
YYY  8.0  3.0  NaN
ZZZ  4.0  1.0  NaN
AAA  1.0  7.0  9.0
BBB  0.0  NaN  2.0
YYY  5.0  5.0  0.0

reset_index将索引移到列中。在

>>> cat = cat.reset_index()                                                                                            
>>> cat                                                                                                                
    ID  Dec  Nov  Oct
0  XXX  4.0  1.0  NaN
1  YYY  8.0  3.0  NaN
2  ZZZ  4.0  1.0  NaN
3  AAA  1.0  7.0  9.0
4  BBB  0.0  NaN  2.0
5  YYY  5.0  5.0  0.0

我这样做的目的是有一个名为'ID'的列，我可以根据该列对其他值进行分组。groupby('ID', sort=False)在'ID'列中创建共享相同值的行组（并且sort=False确保最终结果中的行排序不匹配您的输出）。在

我们可以这样检查组大小：

>>> cat.groupby('ID', sort=False).size()                                                                               
ID
XXX    1
YYY    2
ZZZ    1
AAA    1
BBB    1
dtype: int64

如您所见，我们只有一组大小为2的组，因为'YYY'ID是唯一重复的。在

sum(min_count=1)的工作原理是这样的：每个组中的值根据其列进行汇总。参数min_count=1确保所有NaN值的一系列相加后得到NaN。在

>>> cat.groupby('ID', sort=False).sum(min_count=1)                                                      
      Dec  Nov  Oct
ID                 
XXX   4.0  1.0  NaN
YYY  13.0  8.0  0.0
ZZZ   4.0  1.0  NaN
AAA   1.0  7.0  9.0
BBB   0.0  NaN  2.0

演示min_count：

>>> s = pd.Series([np.nan, np.nan])                                                                                    
>>> s                                                                                                                  
0   NaN
1   NaN
dtype: float64
>>>                                                                                                                    
>>> s.sum()                                                                                                            
0.0
>>> s.sum(min_count=1)                                                                                                 
nan
>>> s[0] = 1                                                                                                           
>>> s                                                                                                                  
0    1.0
1    NaN
dtype: float64
>>> s.sum()                                                                                                            
1.0
>>> s.sum(min_count=1)                                                                                                 
1.0
>>> s.sum(min_count=2)                                                                                                 
nan

Answer 2

我的方法是插入那些只存在于一个数据帧中的值，然后在这些索引处插入求和结果为NaN的值：

result = df1 + df2

Month   Dec  Nov  Oct
ID                   
AAA     NaN  NaN  NaN
BBB     NaN  NaN  NaN
XXX     NaN  NaN  NaN
YYY    13.0  8.0  NaN
ZZZ     NaN  NaN  NaN

result = result.where(~result.isna(), df1)

Month   Dec  Nov  Oct
ID                   
AAA     NaN  NaN  NaN
BBB     NaN  NaN  NaN
XXX     4.0  1.0  NaN
YYY    13.0  8.0  NaN
ZZZ     4.0  1.0  NaN

result = result.where(~result.isna(), df2)

Month   Dec  Nov  Oct
ID                   
AAA     1.0  7.0  9.0
BBB     0.0  NaN  2.0
XXX     4.0  1.0  NaN
YYY    13.0  8.0  0.0
ZZZ     4.0  1.0  NaN