I am trying to pull the week number given a date and then add that week number to the corresponding row in a pandas/python dataframe.
When I run a for loop it is only storing the last calculated value instead of recording each value.
I've tried .append but haven't been able to get anything to work.
import datetime
from datetime import date
for i in df.index:
week_number = date(df.year[i],df.month[i],df.day[i]).isocalendar()
df['week'] = (week_number[1])
Expected values:
day month year week
8 12 2021 49
19 12 2021 50
26 12 2021 51
Values I'm getting:
day month year week
8 12 2021 51
19 12 2021 51
26 12 2021 51
You need to assign it back at the correct corresponding, i, position. Using loc, should help:
for i in df.index:
week_number = date(df.year[i],df.month[i],df.day[i]).isocalendar()
df.loc[i,'week'] = (week_number[1])
prints back:
print(df)
day month year week
0 8 12 2021 49
1 19 12 2021 50
2 26 12 2021 51
You can simply use Pandas .apply method to make it a one-liner:
df["week"] = df.apply(lambda x: date(x.year, x.month, x.day).isocalendar()[1], axis=1)
You can use this, without using apply which is slow:
df['week'] = pd.to_datetime(df['year'].astype(str) + '-' + df['month'].astype(str) + '-' + df['day'].astype(str)).dt.isocalendar().week