Pandas: Dynamically Find Date of Current Week's Sunday and Place in Empty Cell in Dataframe

Question

I have a pandas data frame that does counts by week. The beginning of week is always a Monday and the end is the corresponding Sunday.

Below is my sample data frame:

Week_Start_Date (Mon)   Week_End_Date (Sun)  Count 
2018-08-20                                   35
2018-08-13              2018-08-19           40

I want to fill the blank cell (date associated with current Sunday) with the Sunday associated with the current week. I want this to be dynamic because the weeks will be changing.

Two questions:

Q1) How do I find the date of the Sunday associated with current week?

Q2) How do I place that date in the missing cell? Positionally, the missing cell will always be 2nd column, 1st row.

I have scoured Google and stackoverflow for some direction but couldn't find anything.

Answer 1

First convert to datetime. Then use fillna with your start date incremented by 6 days:

cols = ['Week_Start_Date', 'Week_End_Date']
df[cols] = df[cols].apply(pd.to_datetime, errors='coerce')

df['Week_End_Date'] = df['Week_End_Date'].fillna(df['Week_Start_Date'] + pd.DateOffset(days=6))

print(df)

  Week_Start_Date Week_End_Date  Count
0      2018-08-20    2018-08-26     35
1      2018-08-13    2018-08-19     40

If the 6-day increment is always true, you don't even need fillna:

df['Week_End_Date'] = df['Week_Start_Date'] + pd.DateOffset(days=6)