CODEWITHSUNDEEP
pythonpandas
neenertronics
neenertronics

Reputation: 61

New pandas columns based on date condition

I have a dataframe with a series of floats called balance and a series of timestamps called due_date. I'd like to create a new column called current that displays the balance if the due_date is >= today (all else ""), a column called 1-30 Days that displays the balance if the due_date is 1 to 30 days ago (all else ""), and a column called >30 Days that displays the balance if the due_date is more than 30 days ago (all else "").

Here are some example rows:

    balance due_date
0   250.00  2017-10-22
1   400.00  2017-10-04
2   3000.00 2017-09-08
3   3000.00 2017-09-08
4   250.00  2017-08-05

Any help would be greatly appreciated.

Upvotes: 0

Views: 932

Answers (1)

BENY
BENY

Reputation: 323226

Using pd.cut and pd.crosstab

df['diff']=(pd.to_datetime('today')-df.due_date).dt.days
df['New']=pd.cut(df['diff'],bins = [0,1,30,99999],labels=["current","1-30","more than 30"])
pd.concat([df,pd.crosstab(df.index.get_level_values(0),df.New).apply(lambda x: x.mul(df.balance))],axis=1)


Out[928]: 
       balance   due_date  diff           New  more than 30
row_0                                                      
0        250.0 2017-01-22   261  more than 30         250.0
1        400.0 2017-02-04   248  more than 30         400.0
2       3000.0 2017-02-08   244  more than 30        3000.0
3       3000.0 2017-02-08   244  more than 30        3000.0
4        250.0 2017-02-05   247  more than 30         250.0

Upvotes: 1

Related Questions