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

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

CodePudding user response：

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)