0

I have multiple columns with datetime format "14-09-2021 12:00:00 AM". I have converted them to date with the below code.

df['Column1'] = pd.to_datetime(df['Column1']).dt.date
df['Column2'] = pd.to_datetime(df['Column2']).dt.date

Intention is to take the difference between the two columns to identify the difference in days. When I take the difference df['diff']=df['Column1']-df['Column2'] I get the result as say "- 39 days" and not jus "- 39" (Image added below for clarity)

enter image description here

Intention is to get the difference in integer or number format (i.e just 39 and not 39 days), I'm not sure on how to go about this or when I have erred in the above code.

Help would be much appreciated.

Sid
  • 163
  • 7
  • 1
    Like the [accepted answer](https://stackoverflow.com/a/42247228/15497888) recommends you can grab just the days through the dt accessor -> `df['diff'] = (df['Column1'] - df['Column2']).dt.days` – Henry Ecker Dec 16 '21 at 03:50
  • You could add [Series.abs](https://pandas.pydata.org/docs/reference/api/pandas.Series.abs.html) to get the absolute value if needing all positive numbers`df['diff'] = (df['Column1'] - df['Column2']).dt.days.abs()` – Henry Ecker Dec 16 '21 at 03:51
  • @HenryEcker I tried the above code but got an error ```TypeError: bad operand type for abs(): 'datetime.date'``` – Sid Dec 16 '21 at 03:56
  • It works fine pandas 1.3.5. (I used this DataFrame `df = pd.DataFrame({'Column1': ['2021-01-09', '2021-01-09'], 'Column2': ['2022-08-31', '2022-01-09']})` to start with and ran your conversion code to change the dtype of each column) What version are you using? – Henry Ecker Dec 16 '21 at 04:00
  • @HenryEcker I'm using pandas '1.2.4' – Sid Dec 16 '21 at 04:02
  • Since I can't see your code I don't know where your mistake is, but the only way I can reproduce the error is calling `df['Column1'].abs()` ( _i.e._ calling the `abs` function directly on a datetime column). You need to take the difference of dates to get a timedelta _then_ you can take the `abs`. The complete code in my comment above -> `df['diff'] = (df['Column1'] - df['Column2']).dt.days.abs()` works (even in 1.2.4) on my end. – Henry Ecker Dec 16 '21 at 04:14
  • 1
    @HenryEcker ```df['diff']=df['diff'].dt.days``` did the trick, thank you for providing the above references. – Sid Dec 16 '21 at 04:47

1 Answers1

0

Try this df['diff'] = df['diff'].astype(int)

anarchy
  • 3,709
  • 2
  • 16
  • 48