How to group data on Pandas with multiple conditions?

Question

This is my table

timestamp        date month  day   hour   price
0  2017-01-01 00:00  01/01/2017   Jan  Sun  00:00  60.23
1  2017-01-01 01:00  01/01/2017   Jan  Sun  01:00  60.73
2  2017-01-01 02:00  01/01/2017   Jan  Sun  02:00  75.99
3  2017-01-01 03:00  01/01/2017   Jan  Sun  03:00  60.76
4  2017-01-01 04:00  01/01/2017   Jan  Sun  04:00  49.01

I have data for all 24 hours of the day for everyday and every month for a whole year.

I want to group the data per season for a weekdays and weekend, for example Weekend_Winter = All Saturday and Sunday datas for months Nov,Dec,Jan,Feb

Quite a newbie to this so any help would be useful

@user3256363 What do you mean by group? Split them into different DataFrames? Do a groupby? Assign a new column with the name of said group? — Little Bobby Tables, Jan 25 '18 at 12:20

score 2 · Answer 1 · answered Jan 25 '18 at 13:27

Below solution is slightly different to @jezrael in that seasons and weekdays are explicitly defined.

import pandas as pd

df = pd.DataFrame([['2017-01-01 00:00', '01/01/2017', 'Jan', 'Mon', '00:00', 60.23],
                   ['2017-01-01 01:00', '01/01/2017', 'Jan', 'Sat', '01:00', 60.73],
                   ['2017-01-01 02:00', '01/01/2017', 'May', 'Tue', '02:00', 75.99],
                   ['2017-01-01 03:00', '01/01/2017', 'Jan', 'Sun', '03:00', 60.76],
                   ['2017-01-01 04:00', '01/01/2017', 'Sep', 'Sat', '04:00', 49.01]],
                   columns=['timestamp', 'date', 'month', 'day', 'hour', 'price'])

def InvertKeyListDictionary(input_dict):
    return {w: k for k, v in input_dict.items() for w in v}

season_map = {'Spring': ['Mar', 'Apr', 'May'],
              'Summer': ['Jun', 'Jul', 'Aug'],
              'Autumn': ['Sep', 'Oct', 'Nov'],
              'Winter': ['Dec', 'Jan', 'Feb']}

weekend_map = {'Weekday': ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'],
               'Weekend': ['Sat', 'Sun']}

month_map = InvertKeyListDictionary(season_map)
day_map = InvertKeyListDictionary(weekend_map)

df['season'] = df['month'].map(month_map)
df['daytype'] = df['day'].map(day_map)

df_groups = df.groupby(['season', 'daytype'])

df_groups.get_group(('Winter', 'Weekend'))

# output
# timestamp date month day hour price season daytype
# 2017-01-01 01:00 01/01/2017 Jan Sat 01:00 60.73 Winter Weekend 
# 2017-01-01 03:00 01/01/2017 Jan Sun 03:00 60.76 Winter Weekend

jezrael · Answer 2 · 2018-01-25T12:55:06.450

If want filter data by conditions use boolean indexing with boolean mask created by compare dayofweek with isin for check membership in list L:

#changed timestamp values only for better sample
print (df)
            timestamp        date month  day   hour  price
0 2017-01-01 00:00:00  01/01/2017   Jan  Sun  00:00  60.23
1 2017-01-03 00:00:00  01/01/2017   Jan  Sun  00:00  60.23
2 2017-02-01 01:00:00  01/01/2017   Jan  Sun  01:00  60.73
3 2017-02-05 01:00:00  01/01/2017   Jan  Sun  01:00  60.73
4 2017-03-01 02:00:00  01/01/2017   Jan  Sun  02:00  75.99
5 2017-04-01 03:00:00  01/01/2017   Jan  Sun  03:00  60.76
6 2017-11-01 04:00:00  01/01/2017   Jan  Sun  04:00  49.01

L = ['Nov','Dec','Jan','Feb']
mask = (df['timestamp'].dt.dayofweek > 4) & (df['month'].isin(L))
df1 = df[mask]
print (df1)
            timestamp        date month  day   hour  price
0 2017-01-01 00:00:00  01/01/2017   Jan  Sun  00:00  60.23
3 2017-02-05 01:00:00  01/01/2017   Jan  Sun  01:00  60.73
5 2017-04-01 03:00:00  01/01/2017   Jan  Sun  03:00  60.76

If need new columns for season and for type of day:

df['season'] = (df['timestamp'].dt.month%12 + 3) // 3
df['state'] = np.where(df['timestamp'].dt.dayofweek > 4, 'weekend','weekdays')
print (df)
            timestamp        date month  day   hour  price  season     state
0 2017-01-01 00:00:00  01/01/2017   Jan  Sun  00:00  60.23       1   weekend
1 2017-01-03 00:00:00  01/01/2017   Jan  Sun  00:00  60.23       1  weekdays
2 2017-02-01 01:00:00  01/01/2017   Jan  Sun  01:00  60.73       1  weekdays
3 2017-02-05 01:00:00  01/01/2017   Jan  Sun  01:00  60.73       1   weekend
4 2017-03-01 02:00:00  01/01/2017   Jan  Sun  02:00  75.99       2  weekdays
5 2017-04-01 03:00:00  01/01/2017   Jan  Sun  03:00  60.76       2   weekend
6 2017-11-01 04:00:00  01/01/2017   Jan  Sun  04:00  49.01       4  weekdays

And it is possible used for groupby with aggregate, e.g. by sum:

df2 = df.groupby(['season','state'], as_index=False)['price'].sum()
print (df2)
   season     state   price
0       1  weekdays  120.96
1       1   weekend  120.96
2       2  weekdays   75.99
3       2   weekend   60.76
4       4  weekdays   49.01

How to group data on Pandas with multiple conditions?

2 Answers2

Linked