Convert Dates to number of Days using Pandas

Question

I want to get the number of days corresponding to the date for each country. I have a dataset like so:

Date         Country  
01/03/2020    USA
02/03/2020    USA
03/03/2020    USA
07/04/2020    UK
08/04/2020    UK 
09/04/2020    UK

And I want to get the day numbers based on their first date the country is mentioned. So something like this:

Date         Country    Day_Number
01/03/2020    USA         1
02/03/2020    USA         2
03/03/2020    USA         3
07/04/2020    UK          1
08/04/2020    UK          2
09/04/2020    UK          3

Any help is appreciated. Thanks in advance.

score 0 · Answer 1 · answered Apr 11 '20 at 11:39

Not a complete copy-paste solution but:

You can get the number of days since January 1 1970 this way:

import datetime
days = (datetime.datetime.utcnow() - datetime.datetime(1970,1,1)).days
# Or
days = (datetime.datetime(year, month, day) - datetime.datetime(1970,1,1)).days

So you can convert your dates to numbers (days since Jan 1 1970) and then:

keep track of the minimum per country
subtract the corresponding minimum from each entry

Hope this helps

score 0 · Accepted Answer · answered Apr 11 '20 at 11:40

0

use the following piece of code, it will maintain a cumulative count after groupby operations.

df['Day_Number'] = df.groupby('Country').cumcount()+1

answered Apr 11 '20 at 11:40

oreopot

3,392
2
19
28

That's worked, thanks! – toothsie Apr 11 '20 at 11:45
Sorry, better close like answer, because dupe. – jezrael Apr 11 '20 at 11:53
Only better is 1000times repeated dupes not answering... – jezrael Apr 11 '20 at 11:56
I do it, because I think OP need something else. – jezrael Apr 11 '20 at 11:56

Convert Dates to number of Days using Pandas

2 Answers2