CODEWITHSUNDEEP
pythonpandasdataframeloops
Stunedt Studencheskiy
Stunedt Studencheskiy

Reputation: 13

Total Column from Changes Column

Date Change in Quantity Stock Name Total Quantity of Stock held on this Day
2020-01-06 10 TSLA 10
2020-01-06 25 AAPL 25
2020-01-06 5 ORCL 5
2020-02-10 15 AAPL 40
2020-02-10 -5 TSLA 5
2020-02-10 15 ORCL 20

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

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

What I need is the total quantity of Stock held on this day - column 4 on the 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 a newbie in python (and programming in general) and all dozens of attempts 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 a much less complicated copy of the actual database in excel and attached the screenshot of it lower.

Another less complicated copy, just to understand the logic

Upvotes: 0

Views: 49

Answers (1)

user17242583
user17242583

Reputation:

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

Upvotes: 2

Related Questions