How to add a column in pandas with the value depending on the date

Question

I am trying to digitise my portfolio. I have a dataframe with the daily stock closing information since a certain period (01-01-2020) and I want to add a column showing how many of the stocks I am holding, so for example on 01-01-2020, I have zero APPL and from 25-06-2020 I have 2 and from 01-09-2021 I have 1.

I have another dataframe that keeps track of the balance of my stocks when it changes (but not the dates in between). This looks like this for this example;

Date        Balance
25-06-2020  2
01-09-2021  1

I know that df['balance'] = 0 will add a column that is all zero but how can I add conditions based on the date?

In summary, I have this

Date        Price 
01-01-2020  $100
01-02-2020  $100
...
25-06-2020  $120
26-06-2020  $130
...
01-09-2021  $145
02-09-2021  $146

and I want to get to this

Date        Price  Balance
01-01-2020  $100   0
01-02-2020  $100   0
...
25-06-2020  $120   2
26-06-2020  $130   2
...
01-09-2021  $145   1
02-09-2021  $146   1

jezrael · Accepted Answer · 2021-12-27 13:58:47Z

2

If possible specify first values of Balance in dictionary is possible use Series.map with forward filling missing values:

d = {'01-01-2020':0,'25-06-2020':2,'01-09-2021':1}

df['Balance'] = df['Date'].dt.strftime('%Y-%m-%d').map(d).ffill().astype(int)

EDIT: Use left join for new column Balance, then forward filling values after df1['Date'], first replace by 0:

df['Date'] = pd.to_datetime(df['Date'])
df1['Date'] = pd.to_datetime(df1['Date'])

df2 = df.merge(df1[['Date','Balance']], on='Date', how='left')
df2['Balance'] = df2['Balance'].ffill().fillna(0).astype(int)

edited Dec 27, 2021 at 13:58

answered Dec 27, 2021 at 13:45

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1234 Over a year ago

That would work if I only had three dates to add, but I have some stocks where the balance changes much more often

jezrael Over a year ago

@user1234 - hmm, can you explain more how and when are Balance change?

user1234 Over a year ago

As in everytime I bought or sold some stocks, my total balance of the stock changes. So one day I could be holding 100 shares and a week later 20 and another week later 250

jezrael Over a year ago

@user1234 - how is count it? From sample data not clear for me.

jezrael Over a year ago

@user1234 - Answer was edited.

Collectives™ on Stack Overflow

How to add a column in pandas with the value depending on the date

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related