how can I align different-day timeseries in pandas?

Question

I have two time series, df1

       day        cnt
2020-03-01  135006282
2020-03-02  145184482
2020-03-03  146361872
2020-03-04  147702306
2020-03-05  148242336

and df2:

       day        cnt
2017-03-01  149104078
2017-03-02  149781629
2017-03-03  151963252
2017-03-04  147384922
2017-03-05  143466746

The problem is that the sensors I'm measuring are sensitive to the day of the week, so on Sunday, for instance, they will produce less cnt. Now I need to compare the time series over 2 different years, 2017 and 2020, but to do that I have to align (March, in this case) to the matching day of the week, and plot them accordingly. How do I "shift" the data to make the series comparable?

Can you include some sample output. – sushanth Jul 03 '20 at 13:15 — sushanth, Jul 03 '20 at 13:15

score 3 · Accepted Answer · answered Jul 03 '20 at 13:27

3

The ISO calendar is a representation of date in a tuple (year, weeknumber, weekday). In pandas they are the dt members year, weekofyear and weekday. So assuming that the day column actually contains Timestamps (convert if first with to_datetime if it does not), you could do:

df1['Y'] = df1.day.dt.year
df1['W'] = df1.day.dt.weekofyear
df1['D'] = df1.day.dt.weekday

Then you could align the dataframes on the W and D columns

answered Jul 03 '20 at 13:27

Serge Ballesta

143,923
11
122
252

Thanks! "Align" how, though? – Dervin Thunk Jul 03 '20 at 13:50
`df1.merge(df2,on=['W','D'], how='inner')` If you combine them in this way, you get a single line. – r-beginners Jul 03 '20 at 13:56
`.weekday` is now `.weekday()` on newer datetime versions. – micycle Feb 07 '23 at 15:13

score 0 · Answer 2 · answered Jul 03 '20 at 13:32

March 2017 started on wednesday March 2020 started on Sunday So, delete the last 3 days of march 2017 So, delete the first sunday, monday and tuesday from 2020 this way you have comparable days

df1['ctn2020'] = df1['cnt']
df2['cnt2017'] = df2['cnt'] 
df1 = df1.iloc[2:, 2]
df2 = df2.iloc[:-3, 2]

Since you don't want to plot the date, but want the months to align, make a new dataframe with both columns and a index column. This way you will have 3 columns: index(0-27), 2017 and 2020. The index will represent.

new_df = pd.concat([df1,df2], axis=1)

If you also want to plot the days of the week on the x axis, check out this link, to know how to get the day of the week from a date, and them change the x ticks label.

Sorry for the "written step-to-stop", if it all sounds confusing, i can type the whole code later for you.

how can I align different-day timeseries in pandas?

2 Answers2