Calculating a week beginning date in Python, starting on Monday

Question

I have a dataframe with columns 'Transaction_Date' and 'Day_of_Week'. The former is a datetime64 format, the latter the day of the week spelled out. I'm trying to create a separate column called 'Week_of', which begins on Mondays. Specifically, if a row's 'Transaction_Date' has a 'Day_of_Week' of Monday, then I want 'Week_of' to be the same as 'Transaction_Date'. If it's Tuesday, then 'Week_of' is 'Transaction_Date' - 1 day; minus 2 days if Wednesday, and so on.

I've tried this code:

'''
Determine day of week
'''
df['Day_of_Week']=df['Transaction_Date'].dt.strftime('%A')

'''
Determine Week of Date
'''

# Create a value for Week_of date
if df[df['Day_of_Week']=='Monday']:
    df['Week_of'] = df[df['Transaction_Date']]
elif df[df['Day_of_Week']=='Tuesday']:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=1)
elif df[df['Day_of_Week']=='Wednesday']:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=2)
elif df[df['Day_of_Week']=='Thursday']:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=3)
elif df[df['Day_of_Week']=='Friday']:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=4)
elif df[df['Day_of_Week']=='Saturday']:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=5)
else:
    df['Week_of'] = df[df['Transaction_Date']]-dt.timedelta(days=6)

I get this error:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

How do I fix this?

score 0 · Answer 1 · answered Nov 03 '22 at 20:31

Here's why this is a problem.

# Create a value for Week_of date
if df[df['Day_of_Week']=='Monday']:
    df['Week_of'] = df[df['Transaction_Date']]

There are (probably) many rows where "Day_of_Week" is "Monday". So, what is the if statement supposed to check? How does it come up with one true/false decision? And the assignment statement there affects EVERY row.

You need to use smart indexing. df['Day_of_week']=='Monday' return an array with the rows where that expression is true. You can just use that to select the rows to change:

df[df['Day_of_Week']=='Monday']['Week_of'] = df[df['Day_of_Week']=='Monday']['Transaction_Date']]
df[df['Day_of_Week']=='Tuesday']['Week_of'] = df[df['Day_of_Week']=='Tuesday']['Transaction_Date']] - dt.timedelta(days=1)
df[df['Day_of_Week']=='Wednesday']['Week_of'] = df[df['Day_of_Week']=='Wednesday']['Transaction_Date']] - dt.timedelta(days=2)
df[df['Day_of_Week']=='Thursday']['Week_of'] = df[df['Day_of_Week']=='Thursday']['Transaction_Date']] - dt.timedelta(days=3)
df[df['Day_of_Week']=='Friday']['Week_of'] = df[df['Day_of_Week']=='Friday']['Transaction_Date']] - dt.timedelta(days=4)

So, reading that, "for those rows where Day_of_Week is Tuesday, set the value to the Transaction_Date column minus 1 day".

This is probably not the best way to handle this. apply might even be better.

Calculating a week beginning date in Python, starting on Monday

1 Answers1