5

Trying to have a column be filled with today's date minus the created_date column, but getting the following error : TypeError: unsupported operand type(s) for -: 'str' and 'str'

import datetime
now = datetime.date.today()
today = '{0:%m/%d/%Y}'.format(now).format(now)
today
data['Aging'] = today
data['Aging'] = data['Aging'].sub(data['Created_Date'], axis=0)

TypeError: unsupported operand type(s) for -: 'str' and 'str'

U13-Forward
  • 69,221
  • 14
  • 89
  • 114
0004
  • 1,156
  • 1
  • 14
  • 49

2 Answers2

3

I think need subtract datetimes, so is necessary convert date in now and in Created_Date column, last for convert timedeltas to days use dt.days:

import datetime
now = datetime.date.today()
today = pd.Timestamp(now)

data['Created_Date'] = pd.to_datetime(data['Created_Date'])
data['Aging'] = today
data['Aging'] = data['Aging'].sub(data['Created_Date'], axis=0).dt.days

Solution should be simplify:

data['Created_Date'] = pd.to_datetime(data['Created_Date'])
data['Aging'] = data['Created_Date'].rsub(today, axis=0).dt.days
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • thank you for help but this is the error returned from your code:TypeError: ufunc subtract cannot use operands with types dtype('O') and dtype(' – 0004 Aug 02 '18 at 05:35
  • thank you so much for your help ( i need to learn about fromordinal) if you can have a moment how would I input the following logic , inspired from an excel formula: =IF('Aging'<16,"0-15",IF(AND('Aging'>15,'Aging'<31),"16-30",IF(AND('Aging">30,"Aging"<61),"31-60",IF("Aging">60,"60+")))) I am currently writing over VBA Macro code and having some trouble as you can see on formulas - this logic(formula) would go into the column next to it 'Aging_Bucket' – 0004 Aug 02 '18 at 05:45
  • @pes04 - I think need `pd.cut`, you can check [this solution](https://stackoverflow.com/q/45273731/2901002) – jezrael Aug 02 '18 at 05:49
  • @pes04 - special for you - `df['Bucket'] = pd.cut(df['Aging'], bins=[0,15,30, 60, np.inf], include_lowest=True, labels=['0-15','16-30','31-60','60+'])` – jezrael Aug 02 '18 at 05:59
0

Maybe, try this as the full code:

data['Created_Date'] = pd.to_datetime(data['Created_Date'])
data['Aging'] = pd.to_datetime('now')
data['Aging'] = data['Aging'].sub(data['Created_Date'], axis=0)

What you want, maybe:

data['Created_Date'] = pd.to_datetime(data['Created_Date'])
data['Aging'] = pd.to_datetime('now').strftime('%m/%d/%Y')
data['Aging'] = data['Aging'].sub(data['Created_Date'], axis=0)
U13-Forward
  • 69,221
  • 14
  • 89
  • 114