Pandas Dataframe, Getting Average Value For Each Monday 1 Am

my DataFrame looks like this: index value 2016-03-21 00:00:00 0.613014 2016-03-21 01:00:00 0.596383 2016-03-21 02:00:00 0.623570 2016-03-21 03:00:00

Solution 1:

I'm not sure how to interpret your question, so here are two answers (to different questions)

to get the mean of all values that occur on a monday at 1:00 am (output will be a single scalar):

# Make sure the index is a pd.datetime object
df.index = pd.to_datetime(df.index)

# find all rows which occur on a monday and at 01:00:00, and take the mean
monday_means = (df.loc[(df.index.weekday_name == 'Monday') &
                       (df.index.time == pd.to_datetime('01:00:00').time())]
                .mean()
                .to_frame('Monday 1 Am'))

to get the mean of the previous week, with the week starting on monday at 1:00 am (output will be a series):

# Make sure the index is a pd.datetime object
df.index = pd.to_datetime(df.index)

# Create a column for week number, which counts consecutively every monday at 1:00:00df['week_number'] = ((df.index.weekday_name == 'Monday') &
                     (df.index.time == pd.to_datetime('01:00:00').time())
                     .cumsum())


# Groupby week number and get the mean
df.groupby('week_number').mean()

Or, more simply (but less flexible, it will start the week on monday at midnight, rather than 1 am):

df.groupby(pd.Grouper(freq='W')).mean()