I have a dataframe with a double index (day, time) and would like to create new columns 'Monday', 'Tuesday', 'Wednesday' etc equal to one if the index day is in the correct day.
My original dataframe:
Visitor
Date Time
2017-09-11 4:45 0
5:00 1
5:15 26
....
2017-09-12 4:45 0
5:00 1
5:15 26
....
What I would like to have:
Visitor Monday Tuesday
Date Time
2017-09-11 4:45 0 1 0
5:00 1 1 0
5:15 26 1 0
....
2017-09-12 4:45 0 0 1
5:00 1 0 1
5:15 26 0 1
....
Here is what I tried:
df['Monday'] = (df.index.get_level_values(0).weekday() == 0)
However I get an error saying "'Int64Index' object is not callable".
Thanks in advance!
You need remove () from :
df['Monday'] = (df.index.get_level_values(0).weekday == 0).astype(int)
print (df)
Visitor Monday
Date Time
2017-09-11 4:45 0 1
5:00 1 1
5:15 26 1
2017-09-12 4:45 0 0
5:00 1 0
5:15 26 0
names = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
for i, x in enumerate(names):
df[x] = (df.index.get_level_values(0).weekday == i).astype(int)
print (df)
Visitor Monday Tuesday Wednesday Thursday Friday \
Date Time
2017-09-11 4:45 0 1 0 0 0 0
5:00 1 1 0 0 0 0
5:15 26 1 0 0 0 0
2017-09-12 4:45 0 0 1 0 0 0
5:00 1 0 1 0 0 0
5:15 26 0 1 0 0 0
Saturday Sunday
Date Time
2017-09-11 4:45 0 0
5:00 0 0
5:15 0 0
2017-09-12 4:45 0 0
5:00 0 0
5:15 0 0
Another solution is a improved another original answer - need DatetimeIndex.weekday_name with get_dummies, then set_index by original index and if necessary add reindex for add missig names:
names = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
df1 = df.join(pd.get_dummies(df.index.get_level_values(0).weekday_name)
.set_index(df.index).reindex(columns=names, fill_value=0))
print (df1)
Visitor Monday Tuesday Wednesday Thursday Friday \
Date Time
2017-09-11 4:45 0 1 0 0 0 0
5:00 1 1 0 0 0 0
5:15 26 1 0 0 0 0
2017-09-12 4:45 0 0 1 0 0 0
5:00 1 0 1 0 0 0
5:15 26 0 1 0 0 0
Saturday Sunday
Date Time
2017-09-11 4:45 0 0
5:00 0 0
5:15 0 0
2017-09-12 4:45 0 0
5:00 0 0
5:15 0 0