I have a pandas dataframe result, looks like this:
Weekday Day Store1 Store2 Store3 Store4 Store5
0 Mon 6 0.0 0.0 0.0 0.0 0.0
1 Tue 7 42.0 33.0 23.0 42.0 21.0
2 Wed 8 43.0 29.0 13.0 33.0 22.0
3 Thu 9 45.0 24.0 20.0 29.0 18.0
4 Fri 10 48.0 21.0 22.0 37.0 22.0
5 Sat 11 34.0 22.0 23.0 34.0 18.0
0 Mon 13 39.0 21.0 21.0 25.0 21.0
1 Tue 14 39.0 20.0 18.0 0.0 19.0
2 Wed 15 46.0 26.0 18.0 31.0 24.0
3 Thu 16 38.0 21.0 15.0 45.0 29.0
4 Fri 17 42.0 21.0 21.0 41.0 20.0
5 Sat 18 40.0 25.0 15.0 36.0 19.0
0 Mon 20 39.0 22.0 23.0 36.0 19.0
1 Tue 21 31.0 18.0 16.0 35.0 23.0
2 Wed 22 33.0 25.0 17.0 39.0 22.0
3 Thu 23 34.0 24.0 19.0 18.0 27.0
4 Fri 24 33.0 18.0 24.0 43.0 24.0
5 Sat 25 38.0 22.0 20.0 40.0 12.0
0 Mon 27 41.0 21.0 18.0 31.0 23.0
1 Tue 28 32.0 21.0 14.0 23.0 14.0
2 Wed 29 33.0 18.0 15.0 19.0 23.0
3 Thu 30 36.0 21.0 21.0 23.0 18.0
4 Fri 1 40.0 30.0 24.0 38.0 23.0
5 Sat 2 40.0 19.0 22.0 38.0 21.0
Notice how Day goes from 6 to 30, then back to 1, and 2. In this example, it's referring to
September 6, 2021 - October 2nd, 2021.
I currently have a variable PrimaryMonth = September and SecondaryMonth = October
I know that I can do result['Month'] = 'September' but it will list all the Month values as September, I'd like to find a way, if possible, to iterate through the rows so that when it reaches the bottom 1 and 2 it will show October in the new Month column.
Is it possible to do a For loop or some other iteration to accomplish this? I was initially brainstorming some pseudocode
#for row in result:
# while Day <= 31
#concat PrimaryMonth
#else concat SecondaryMonth
You can kind of get an idea of where I want to go with this.
CodePudding user response：
Many things are easier if you use proper date formats...
date_str = 'Monday, September 6, 2021 - Saturday, October 2, 2021'
new_index = pd.date_range(*map(pd.to_datetime, date_str.split(' - ')))
dates = pd.DataFrame(index=new_index)
dates['day'] = dates.index.day
dates.columns = ['Day']
df = pd.merge(dates, df, 'outer')
df.index = dates.index
df['month'] = df.index.month_name()
print(df.dropna())
Output:
Day Weekday Store1 Store2 Store3 Store4 Store5 month
2021-09-06 6 Mon 0.0 0.0 0.0 0.0 0.0 September
2021-09-07 7 Tue 42.0 33.0 23.0 42.0 21.0 September
2021-09-08 8 Wed 43.0 29.0 13.0 33.0 22.0 September
2021-09-09 9 Thu 45.0 24.0 20.0 29.0 18.0 September
2021-09-10 10 Fri 48.0 21.0 22.0 37.0 22.0 September
2021-09-11 11 Sat 34.0 22.0 23.0 34.0 18.0 September
2021-09-13 13 Mon 39.0 21.0 21.0 25.0 21.0 September
2021-09-14 14 Tue 39.0 20.0 18.0 0.0 19.0 September
2021-09-15 15 Wed 46.0 26.0 18.0 31.0 24.0 September
2021-09-16 16 Thu 38.0 21.0 15.0 45.0 29.0 September
2021-09-17 17 Fri 42.0 21.0 21.0 41.0 20.0 September
2021-09-18 18 Sat 40.0 25.0 15.0 36.0 19.0 September
2021-09-20 20 Mon 39.0 22.0 23.0 36.0 19.0 September
2021-09-21 21 Tue 31.0 18.0 16.0 35.0 23.0 September
2021-09-22 22 Wed 33.0 25.0 17.0 39.0 22.0 September
2021-09-23 23 Thu 34.0 24.0 19.0 18.0 27.0 September
2021-09-24 24 Fri 33.0 18.0 24.0 43.0 24.0 September
2021-09-25 25 Sat 38.0 22.0 20.0 40.0 12.0 September
2021-09-27 27 Mon 41.0 21.0 18.0 31.0 23.0 September
2021-09-28 28 Tue 32.0 21.0 14.0 23.0 14.0 September
2021-09-29 29 Wed 33.0 18.0 15.0 19.0 23.0 September
2021-09-30 30 Thu 36.0 21.0 21.0 23.0 18.0 September
2021-10-01 1 Fri 40.0 30.0 24.0 38.0 23.0 October
2021-10-02 2 Sat 40.0 19.0 22.0 38.0 21.0 October
And no, no matter what you do, a for-loop is probably the wrong answer when it comes to pandas.