Conditional values in a Pandas data frame based on dates

Question

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':['2018-01-22','2018-01-22','2018-06-22','2018-06-22','2019-01-22']})

Depending on user input, I wanted to modify the Pandas data frame to only show certain values. For a "date" of 2018-01-31, I would like the data frame to be:

df1= pd.DataFrame({'2018-01-31':[0,0,20,-20,5],'type':['Asset','Liability','Asset','Liability','Asset'],'Amount':[10,-10,20,-20,5],'Maturity Date':['2018-01-22','2018-01-22','2018-06-22','2018-06-22','2019-01-22']})

Similarly for a "date" of 2018-12-31, I would like the data frame to be:

df2= pd.DataFrame({'2018-12-31':[0,0,0,0,5],'2018-01-31':[0,0,20,-20,5],'type':['Asset','Liability','Asset','Liability','Asset'],'Amount':[10,-10,20,-20,5],'Maturity Date':['2018-01-22','2018-01-22','2018-06-22','2018-06-22','2019-01-22']})

Any suggestions for the most efficient way to achieve this?

cs95 · Accepted Answer · 2018-01-10 17:12:55Z

3

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

edited Jan 10, 2018 at 17:12

cs95

406k106 gold badges744 silver badges797 bronze badges

answered Jan 10, 2018 at 17:00

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

cs95 Over a year ago

Willing to bet this is the fastest one actually. Also, that was my last vote for the day.

BENY Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ a ha , thank you ~ :-) also, np.where looks great !

Scott Boston · Accepted Answer · 2018-01-10 17:08:40Z

Let's use assign and mask:

print(df)

   Amount Maturity Date       type
0      10    2018-01-22      Asset
1     -10    2018-01-22  Liability
2      20    2018-06-22      Asset
3     -20    2018-06-22  Liability
4       5    2019-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

Collectives™ on Stack Overflow

Conditional values in a Pandas data frame based on dates

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related