Table above is a simplified example, just to get the logic of what i am having an issue with. Actual database screenshot

I have database with info on trader's trades (purchases and sales)

• Trades Dates
• Names of stocks traded
• Net quantity changed (bough minus sold of that particualr stock on that day).

What i need is total quantity of Stock held on this day - column 4 on tab above

My plan: Create column 4, and refer

1. To the amount held of that stock, last time it appeared in dataframe
2. Add the amount changed, however i am an newbie in python (and programming in general) and all dozens attemps of mine in creating any loop have failed.

An idea i have got is:

1. I get the list of unique stock names with

UnqNms = df['Stock Name'].unique()

2. Via loop going through the entire database find lines where each unique name of the stock appears, and in the newly created column sum the value of quantity changed to the previously held total quantity.

I created much less complicated copy of the actual database in the excel and attached the screenshot of it lower.

Another less complicated copy, just to understand the logic

CodePudding user response：

I think a simple groupby cumsum will work:

df['Total Quantity of Stock held on this Day'] = df.groupby('Stock Name')['Change in Quantity'].cumsum()

Output:

>>> df
         Date  Change in Quantity Stock Name  Total Quantity of Stock held on this Day
0  2020-01-06                  10       TSLA                                        10
1  2020-01-06                  25       AAPL                                        25
2  2020-01-06                   5       ORCL                                         5
3  2020-02-10                  15       AAPL                                        40
4  2020-02-10                  -5       TSLA                                         5
5  2020-02-10                  15       ORCL                                        20