1

I have an 8-year timeseries with daily values where I would like to resample biweekly. However, I only need biweekly values from week 18 to week 30 of each year (i.e. W18, W20, W22, ..., W30). This method would sometimes give me the 'odd' biweekly values (i.e. W19, W21, W23,..., W29). How might I ensure that I would always get the 'even' biweekly values?

df = df.resample("2W").mean() 
df["Week"] = df.index.map(lambda dt: dt.week)
df = df.loc[df.Week.isin(range(18,31))]

An example of the daily data from 2010-01-01 to 2018-12-31: (short version)

Date       value_1   value_2
...              ...       ...
2010-05-03        10         1
2010-05-04        79        66
2010-05-05        40        16
2010-05-06        13        76
2010-05-07         2        36
2010-05-08        31        98
2010-05-09        96         3
2010-05-10        66        18
2010-05-11        99         9
...              ...       ...

Expected biweekly data between week 18 and week 30:

Date       value_1   value_2   Week
2010-05-03        14         1     18
2010-05-17        33        89     20
2010-05-31        21        31     22
2010-06-14        33        56     24
2010-06-28        12        43     26
2010-07-12        21        72     28
2010-07-26        76        13     30
2011-05-02        60        28     18
2011-05-16        82         2     20
2011-05-30        30        15     22
...              ...       ...    ...
Yogi
  • 45
  • 6

1 Answers1

0

I think that the best way is to create the range separately with list comprehension. The code below will give a range between 18 and 30 with only even values:

weeks_to_include = [i for i in range(18, 31) if i % 2 == 0]

With this range you can filter as you have above. I tested the code below and it worked for me:

#create a dummy dataframe
dr = pd.date_range(start='2013-01-01', end='2021-12-31', freq='D')
df = pd.DataFrame(index=dr)
df['col1'] = range(0, len(df))

#create a list of even weeks in a range
weeks_to_include = [i for i in range(18, 31) if i % 2 == 0]

#create a column with the week of the year
df['weekofyear'] = df.index.isocalendar().week

#filter for only weeks_to_include
df = df.loc[df['weekofyear'].isin(weeks_to_include)]
tvanvalkenburg
  • 108
  • 6
  • This filter works to only take the even weeks. However, I am resampling to take the biweekly mean. My intended logic was to resample biweekly mean and then grab the weeks that I need. – Yogi Jan 04 '22 at 21:43
  • Yes, so filtering for only the required weeks and then resampling should give you what you are looking for? – tvanvalkenburg Jan 05 '22 at 08:17
  • After a few digging, I believe resampling biweekly is the main issue as it starts on W1 (which is an odd week), then it alternates on a leap year with 53 weeks. I am trying a different method, to resample weekly and then average two weeks over time but no success so far. – Yogi Jan 05 '22 at 15:11