CODEWITHSUNDEEP
pythonpandas
llssff
llssff

Reputation: 119

Pandas sum rows with nearly identical values

I am trying to organize a spreadsheet to keep track of the item flow.

The goal is to sum the amount column, for the items with the same action, price, and date.

So for example: 

Item  action amount price date

socks   buy    10    $20  5/1

socks   buy     5    $20  5/1

socks   sell    5    $20  5/1

shoes   sell    7    $25  5/2

shoes   sell    2    $25  5/2

shoes   sell    8    $30  5/2

--would turn into--

socks   buy     15   $20  5/1
socks   sell     5   $20  5/1
shoes   sell     9   $25  5/2
shoes   sell     8   $30  5/2

Is this possible using pandas?

Upvotes: 1

Views: 585

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use DataFrame.groupby to group the dataframe on 'Item', 'action', 'price', 'date' then use the agg function sum to calculate the sum for amount column for each group, then use DataFrame.reset_index to reset the index of grouped dataframe:

df = df.groupby(['Item', 'action', 'price', 'date']).sum().reset_index().reindex(columns=df.columns)

Result:

# print(df)
   Item action  amount price date
0  shoes   sell       9   $25  5/2
1  shoes   sell       8   $30  5/2
2  socks    buy      15   $20  5/1
3  socks   sell       5   $20  5/1

Upvotes: 1

Related Questions