Conditional Values In A Pandas Data Frame Based On Dates

I have the following Pandas data frame: import pandas as pd df= pd.DataFrame({'type':['Asset','Liability','Asset','Liability','Asset'],'Amount':[10,-10,20,-20,5],'Maturity Date':['

Solution 1:

User input the date , then we can get it base on this

df['2018-12-31']=(df['Maturity Date']>pd.to_datetime('2018-12-31'))*df.Amount
df
Out[356]: 
   Amount Maturity Date       type  2018-12-31
0      10    2018-01-22      Asset           0
1     -10    2018-01-22  Liability           0
2      20    2018-06-22      Asset           0
3     -20    2018-06-22  Liability           0
4       5    2019-01-22      Asset           5

---

Another solution using np.where + df.insert

date = '2018-01-31'
df.insert(0, date, np.where(df['Maturity Date'] > '2018-01-31', df.Amount, 0))

df

   2018-01-31  Amount Maturity Date       type
0           0      10    2018-01-22      Asset
1           0     -10    2018-01-22  Liability
2          20      20    2018-06-22      Asset
3         -20     -20    2018-06-22  Liability
4           5       5    2019-01-22      Asset

Solution 2:

Let's use assign and mask:

print(df)AmountMaturityDatetype0102018-01-22      Asset1-102018-01-22  Liability2202018-06-22      Asset3-202018-06-22  Liability452019-01-22      Asset

Add, first column,

input_date = '2018-01-31'df = df.assign(input_date=df.Amount.mask(df["Maturity Date"] <= input_date,0)).rename(columns={'input_date':input_date})
print(df)

   Amount Maturity Date       type  2018-01-31
0      10    2018-01-22      Asset           0
1     -10    2018-01-22  Liability           0
2      20    2018-06-22      Asset          20
3     -20    2018-06-22  Liability         -20
4       5    2019-01-22      Asset           5

Add, second column,

input_date = '2018-12-31'df = df.assign(input_date=df.Amount.mask(df["Maturity Date"] <= input_date,0)).rename(columns={'input_date':input_date})
print(df)

   Amount Maturity Date       type  2018-01-31  2018-12-31
0      10    2018-01-22      Asset           0           0
1     -10    2018-01-22  Liability           0           0
2      20    2018-06-22      Asset          20           0
3     -20    2018-06-22  Liability         -20           0
4       5    2019-01-22      Asset           5           5