How to add different column in two DataFrames by multiple columns (Pandas)

Question

I have a table with error data need fix.

Test 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)
})

Select all duplicates by ['store_id', 'product_id', 'time_create']

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

Calculate the real diff value

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'} )

Drop duplicates except first one

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

Add diff value back to previous excepted row

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

---

But it failed on last step :

---------------------------------------------------------------------------
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

Oh, this is my careless . After I finished the code, forgot iloc was used to select integer index, which can't use for multiindex. Aslo because loc failed before my code done . Which make me think my code was wrong . I really need review question before posting.

@jezrael's answer knock me up, but directly add up x.store_product_quantity_old and a.quantity_diff to x['store_product_quantity_new'] isn't a good idea I think. So I vote him up without accept.

The better one is change iloc to loc :

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

to

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

would work.

---

Knock myself again, and people who see this question:

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.

If you got any error in your pandas code, check this first before posting question.

Answer 2

Use drop_duplicates and create new column without 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  

For avoid NaN use add with parameter 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 3

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

I tried with the following code and worked.

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)