I have a dataframe
:
order_creationdate orderid productid quantity prod_name price Amount 0 2021-01-18 22:27:03.341260 1 SnyTV 3.0 Sony LED TV 412.0 1236.0 1 2021-01-18 17:28:03.343089 1 AMDR5 1.0 AMD Ryzen 5 313.0 313.0 2 2021-01-18 13:19:03.343842 1 INTI0 8.0 Intel I10 146.0 1168.0 3 2021-01-18 10:24:03.344399 1 INTI0 5.0 Intel I10 146.0 730.0 4 2021-01-18 12:29:03.344880 1 CMCFN 4.0 coolermaster CPU FAN 675.0 2700.0
Index 2 and 3 have the same product id’s, hence its the same order, so i am trying to combine the rows into one single row, to get :
INTI0 13 .0 146.0 1898.0
the final df
being :
order_creationdate orderid productid quantity prod_name price Amount 0 2021-01-18 22:27:03.341260 1 SnyTV 3.0 Sony LED TV 412.0 1236.0 1 2021-01-18 17:28:03.343089 1 AMDR5 1.0 AMD Ryzen 5 313.0 313.0 2 2021-01-18 13:19:03.343842 1 INTI0 13.0 Intel I10 146.0 1898.0 3 2021-01-18 12:29:03.344880 1 CMCFN 4.0 coolermaster CPU FAN 675.0 2700.0
I have tried using df.groupby
function :
df2['productid'] =df2['productid'].astype('str') arr = np.sort(df2[['productid','quantity']], axis=1) df2 = (df2.groupby([arr[:, 0],arr[:, 1]]) .agg({'price':'sum', 'Amount':'sum'}) .rename_axis(('X','Y')) .reset_index()) print(df2)
But it throws datatype error
File "/home/anti/Documents/db/create_rec.py", line 65, in <module> arr = np.sort(df2[['productid','quantity']], axis=1) File "<__array_function__ internals>", line 5, in sort File "/home/anti/.local/lib/python3.8/site-packages/numpy/core/fromnumeric.py", line 991, in sort a.sort(axis=axis, kind=kind, order=order) TypeError: '<' not supported between instances of 'float' and 'str'
Advertisement
Answer
df2.groupby(['productid', 'orderid'], as_index=False).agg( {'quantity': sum, 'Amount': sum, 'order_creationdate': min, 'prod_name': min, 'price': min} )
The output is:
productid orderid quantity Amount order_creationdate prod_name price 0 AMDR5 1 1.0 313.0 2021-01-18 17:28:03.343089 AMD Ryzen 5 313.0 1 CMCFN 1 4.0 2700.0 2021-01-18 12:29:03.344880 coolermaster CPU FAN 675.0 2 INTI0 1 13.0 1898.0 2021-01-18 10:24:03.344399 Intel I10 146.0 3 SnyTV 1 3.0 1236.0 2021-01-18 22:27:03.341260 Sony LED TV 412.0