如何通过多列在两个数据帧中添加不同的列（Pandas）

Question

我有一个错误数据表需要修复。你知道吗

测试df

df = pd.DataFrame({
    'store_id' : list('aaaabbbbcccc'),
    'product_id' : list('111122223333'),
    'time_create' : (1,1,1,3,1,1,2,2,10,11,12,13),
    'store_product_quantity_old' : (0,0,0,3,0,0,5,5, 0,1,2,3),
    'store_product_quantity_new' : (1,1,1,5,2,3,4,10,1,2,3,4)
})

按['store\u id'、'product\u id'、'time\u create']选择所有副本

dups = df[df.duplicated(subset=['store_id', 'product_id', 'time_create'], keep=False)].copy()

计算实际差值

dups.loc[:, 'quantity_diff'] = dups.store_product_quantity_new - dups.store_product_quantity_old
a = dups.groupby(['store_id', 'product_id', 'time_create']).agg({'quantity_diff': 'sum'} )

除去第一个

x = df.drop(df[df.duplicated(subset=['store_id', 'product_id', 'time_create'])].index)

将差异值添加回上一个例外行

x = x.set_index(['store_id', 'product_id',  'time_create'])
x.iloc[a.index].store_product_quantity_new  = x.iloc[a.index].store_product_quantity_old + a.quantity_diff

---

但最后一步失败了：

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-129-0183d1586485> in <module>()
----> 1 x.iloc[a.index].store_product_quantity_new  = x.store_product_quantity_old + a.quantity_diff

C:\Anaconda3\lib\site-packages\pandas\core\indexing.py in __getitem__(self, key)
   1476 
   1477             maybe_callable = com._apply_if_callable(key, self.obj)
-> 1478             return self._getitem_axis(maybe_callable, axis=axis)
   1479 
   1480     def _is_scalar_access(self, key):

C:\Anaconda3\lib\site-packages\pandas\core\indexing.py in _getitem_axis(self, key, axis)
   2089         # a list of integers
   2090         elif is_list_like_indexer(key):
-> 2091             return self._get_list_axis(key, axis=axis)
   2092 
   2093         # a single integer

C:\Anaconda3\lib\site-packages\pandas\core\indexing.py in _get_list_axis(self, key, axis)
   2068             axis = self.axis or 0
   2069         try:
-> 2070             return self.obj._take(key, axis=axis)
   2071         except IndexError:
   2072             # re-raise with different error message

C:\Anaconda3\lib\site-packages\pandas\core\generic.py in _take(self, indices, axis, is_copy)
   2787         new_data = self._data.take(indices,
   2788                                    axis=self._get_block_manager_axis(axis),
-> 2789                                    verify=True)
   2790         result = self._constructor(new_data).__finalize__(self)
   2791 

C:\Anaconda3\lib\site-packages\pandas\core\internals.py in take(self, indexer, axis, verify, convert)
   4524                              dtype='int64')
   4525                    if isinstance(indexer, slice)
-> 4526                    else np.asanyarray(indexer, dtype='int64'))
   4527 
   4528         n = self.shape[axis]

C:\Anaconda3\lib\site-packages\numpy\core\numeric.py in asanyarray(a, dtype, order)
    542 
    543     """
--> 544     return array(a, dtype, copy=False, order=order, subok=True)
    545 
    546 

ValueError: setting an array element with a sequence.

Answer 1

使用^{}并创建不带iloc的新列：

dups = df[df.duplicated(subset=['store_id', 'product_id', 'time_create'], keep=False)].copy()
dups['quantity_diff'] = dups.store_product_quantity_new - dups.store_product_quantity_old
a = dups.groupby(['store_id', 'product_id', 'time_create']).agg({'quantity_diff': 'sum'})

x = df.drop_duplicates(subset=['store_id', 'product_id', 'time_create'])
x = x.set_index(['store_id', 'product_id',  'time_create'])
x['store_product_quantity_new'] =  x.store_product_quantity_old + a.quantity_diff
print (x)
                                 store_product_quantity_old  \
store_id product_id time_create                               
a        1          1                                     0   
                    3                                     3   
b        2          1                                     0   
                    2                                     5   
c        3          10                                    0   
                    11                                    1   
                    12                                    2   
                    13                                    3   

                                 store_product_quantity_new  
store_id product_id time_create                              
a        1          1                                   3.0  
                    3                                   NaN  
b        2          1                                   5.0  
                    2                                   5.0  
c        3          10                                  NaN  
                    11                                  NaN  
                    12                                  NaN  
                    13                                  NaN  

要避免NaN，请将^{}与参数fill_value=0一起使用：

x['store_product_quantity_new'] =  (x.store_product_quantity_old
                                    .add(a.quantity_diff, fill_value=0))
print (x)
                                 store_product_quantity_old  \
store_id product_id time_create                               
a        1          1                                     0   
                    3                                     3   
b        2          1                                     0   
                    2                                     5   
c        3          10                                    0   
                    11                                    1   
                    12                                    2   
                    13                                    3   

                                 store_product_quantity_new  
store_id product_id time_create                              
a        1          1                                   3.0  
                    3                                   3.0  
b        2          1                                   5.0  
                    2                                   5.0  
c        3          10                                  0.0  
                    11                                  1.0  
                    12                                  2.0  
                    13                                  3.0  

Answer 2

哦，这是我的粗心。在我完成代码之后，忘记了iloc是用来选择整数索引的，它不能用于多重索引。因为在我的代码完成之前loc失败了。这让我觉得我的代码错了。 我真的需要审查问题之前发布。你知道吗

@jezrael的回答让我大吃一惊，但我觉得直接把x.store_product_quantity_old和a.quantity_diff加起来x['store_product_quantity_new']不是个好主意。所以我没有接受就投了他一票。你知道吗

更好的方法是将iloc更改为loc：

x.iloc[a.index].store_product_quantity_new  = x.iloc[a.index].store_product_quantity_old + a.quantity_diff

至

x.loc[a.index].store_product_quantity_new  = x.loc[a.index].store_product_quantity_old + a.quantity_diff

会有用的。你知道吗

---

再敲一下我自己，看到这个问题的人：

iloc

Purely integer-location based indexing for selection by position.

loc

Access a group of rows and columns by label(s) or a boolean array. .loc[] is primarily label based, but may also be used with a boolean array.

如果您的熊猫代码中有任何错误，请在发布问题之前先检查此项。你知道吗

Answer 3

使用x.loc[a.index, 'store_product_quantity_new'] = x.store_product_quantity_old + a.quantity_diff

我尝试了以下代码并成功了。你知道吗

import pandas as pd

df = pd.DataFrame(columns = ['store_id','product_id','time_create', 'store_product_quantity_old', 'store_product_quantity_new'])
df.loc[len(df)] = ['5aab11da-5dd2-477f-916c-3ed7e81ec03f',    '460a49f8-7a8a-426d-8dec-d650d28035ee'  ,  1540305578301,   215,186]
df.loc[len(df)] = ['901b87fe-7a33-49ae-8730-de6f72167c8d' ,   '347ed0f2-423d-43b5-864a-654ebfab88e0'  ,  1540036103826,   10,  9]
df.loc[len(df)] = ['831b632e-12bd-4c23-a6fd-a18749d8d508' ,   'c0e48f01-7d37-433e-8c82-66621a83be58'  ,  1540770907795,   0 ,  20]
df.loc[len(df)] = ['31919fe6-bcef-483b-bc44-0fb2360993b2' ,   '2444245c-69d5-43ca-9138-2428acb368e0'  ,  1539659604914 ,  90,  89]
df.loc[len(df)] = ['901b87fe-7a33-49ae-8730-de6f72167c8d' ,   '4614ce9a-52a2-42c4-9fda-2f200231531e'   , 1538523837324 ,  115, 114]
df.loc[len(df)] = ['8f3dfc01-3a82-4fbf-a681-94cc807b41a1' ,   '484f911a-f0d9-43a9-bcaf-ef5e67c8f64c'   , 1539388385358 ,  5 ,  6]
df.loc[len(df)] = ['1148a913-7860-4525-b9c9-06c428baea4e' ,   '0e6ee8aa-f9f1-4541-ade7-04f3df6cbf71'    ,1538355213073 ,  171, 170]
df.loc[len(df)] = ['8d3527f7-8c25-4e47-8a8d-ddd6dcde439b' ,   'cdfaa486-75ed-4be6-8457-057d8708142a'    ,1539499795481 ,  156, 155]
df.loc[len(df)] = ['9e7ff8bf-6aa4-4731-96d3-68c875f843f6' ,   '6f157811-0b18-4144-8400-311a299e2386'    ,1538389333436 ,  37 , 36]
df.loc[len(df)] = ['8dc2853b-ffe9-4dc8-9ad6-85622eed0c74' ,   'f6b11510-f719-44d8-91a3-1b5cab1011ad'    ,1539735238709 ,  6 ,  5]
df.loc[len(df)] = ['05a2d25c-d04f-4b32-8678-7c3b31d45fbb' ,   'b7a2019a-eac1-4900-9c51-9576a77a0711'    ,1538540129711 ,  128, 127]
df.loc[len(df)] = ['05a2d25c-d04f-4b32-8678-7c3b31d45fbb' ,   'b7a2019a-eac1-4900-9c51-9576a77a0711'    ,1538540129711 ,  129, 128]
df.loc[len(df)] = ['05a2d25c-d04f-4b32-8678-7c3b31d45fbb' ,   'b7a2019a-eac1-4900-9c51-9576a77a0711'    ,1538540129711 ,  130, 129]

dups = df[df.duplicated(subset=['store_id', 'product_id', 'time_create'], keep=False)].copy()
dups['quantity_diff'] = dups['store_product_quantity_old'] - dups['store_product_quantity_new']
a = dups.groupby(['store_id', 'product_id', 'time_create']).agg({'quantity_diff': 'sum'} )
dups.loc[:, 'quantity_diff'] = dups.store_product_quantity_new - dups.store_product_quantity_old

x = df.drop(df[df.duplicated(subset=['store_id', 'product_id', 'time_create'])].index)
x = x.set_index(['store_id', 'product_id',  'time_create'])
x.loc[a.index, 'store_product_quantity_new'] = x.store_product_quantity_old + a.quantity_diff
print(x)