give pandas date columns ordinal value

user_12285101 0
Viewed 255

I have dataframe with measurments for differnt dates and hours:

>>>date       hour   value
0 29-11-2020   8     0.231
1 29-11-2020   9     0.245
2 29-11-2020   10    0.475
3 30-11-2020   8     0.214
4 30-11-2020   9     0.239
5 30-11-2020   10    0.312
...

The table described measured values for every day every hour. the measurments are taken from the third day of an experiment to day 15. I would like to create new column that will have the ordinal day of the experiment base don the dates, e.g:

>>>date       hour   value   count
0 29-11-2020   8     0.231    3
1 29-11-2020   9     0.245    3
2 29-11-2020   10    0.475    3
3 30-11-2020   8     0.214    4
4 30-11-2020   9     0.239    4
5 30-11-2020   10    0.312    4
5 01-12-2020   8     0.312    5
5 01-12-2020   9     0.312    5
5 01-12-2020   10    0.521    5
...

(29-11 is the third day, 30-11 is the 2nd day...)

I have seen this post but it doesn't solve the same problem (they give repetative "codes" while I want to "count" unique dates from the 3rd day and give each date the ordinal number. I have also tried using "tooordinal" but it jsut changed the date values without count it.

pandas ordinal datetime python
1 Answers

Cast the 'date' column to datetime, subtract the first day, take the day of the resulting timedelta Series and add the offset days:

experiment_day_start = 3

df['date'] = pd.to_datetime(df['date'], dayfirst=True)
df['exp_day'] = (df['date']-df['date'].min()).dt.days + experiment_day_start

df
        date  hour  value  count  exp_day
0 2020-11-29     8  0.231      3        3
1 2020-11-29     9  0.245      3        3
2 2020-11-29    10  0.475      3        3
3 2020-11-30     8  0.214      4        4
4 2020-11-30     9  0.239      4        4
5 2020-11-30    10  0.312      4        4
6 2020-12-01     8  0.312      5        5
7 2020-12-01     9  0.312      5        5
8 2020-12-01    10  0.521      5        5
user_101974180
Related