0

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.

neenertronics
  • 61
  • 1
  • 1
  • 8
  • Would this help answer your question? https://stackoverflow.com/questions/11608238/is-it-possible-to-add-a-where-clause-with-list-comprehension – Brijesh Oct 10 '17 at 20:52

1 Answers1

1

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
BENY
  • 317,841
  • 20
  • 164
  • 234