mapping day of week to number python

user_15803074 0
Viewed 45

I need to iterate through a dataframe and generate a column that transposes a weekday into its related number.

I've came up with the following:

 inputData['DOW'] = ""
    for i in range(0,len(inputData)):
        if 'Sunday' in inputData.iloc[i]['day_of_the_week']:
            inputData.iloc[i]['DOW']=1
        elif 'Monday' in inputData.iloc[i]['day_of_the_week']:
           inputData.iloc[i]['DOW']=2
        elif 'Tuesday' in inputData.iloc[i]['day_of_the_week']:
           inputData.iloc[i]['DOW']=3
        elif 'Wednesday' in inputData.iloc[i]['day_of_the_week']:
           inputData.iloc[i]['DOW']=4
        elif 'Thursday' in inputData.iloc[i]['day_of_the_week']:
            inputData.iloc[i]['DOW']=5
        elif 'Friday' in inputData.iloc[i]['day_of_the_week']:
            inputData.iloc[i]['DOW']=6
        elif 'Saturday' in inputData.iloc[i]['day_of_the_week']:
            inputData.iloc[i]['DOW']=7

But this gives me back the column "DOW" empty for all the rows... What's going wrong here?

pandas conditional-statements dataframe date python
1 Answers

I am not going to break down why this is wrong - but it is on many levels. You can simply remap by using replace

import pandas as pd
mapping = {
    'Sunday': 0,
    'Monday': 1,
    'Tuesday': 2,
    'Wednesday': 3,
}
df = pd.DataFrame(data=['Monday', 'Tuesday', 'Wednesday', 'Monday'], columns=['day_of_week'])
df['DOW'] = df['day_of_week'].replace(mapping)
df

output

    day_of_week DOW
0   Monday       1
1   Tuesday      2
2   Wednesday    3
3   Monday       1

To answer why your code did not work, I am still trying to figure this out myself.

But if you change to either of the following it seems to work

df.iloc[i, <index location of 'DOW' column>] = value
df.loc[i]['DOW'] = value

work because .iloc is integer based, and with loc you are using labels which are now consistent as outlined.

However, but it seems to work for all if statements if I only change one of your if statements to

inputData['DOW'] = ""

for i in range(0,len(inputData)):
#     print(inputData.iloc[i]['day_of_the_week'] == 'Monday')

    if 'Sunday' == inputData.iloc[i]['day_of_the_week']:
        inputData.iloc[i].DOW =1
        
    elif 'Monday' in inputData.iloc[i]['day_of_the_week']:
        inputData.loc[i]['DOW'] = 2
    elif 'Tuesday' in inputData.iloc[i]['day_of_the_week']:
       inputData.iloc[i].DOW=3
    elif 'Wednesday' in inputData.iloc[i]['day_of_the_week']:
       inputData.iloc[i].DOW=4
    elif 'Thursday' in inputData.iloc[i]['day_of_the_week']:
        inputData.iloc[i].DOW=5
    elif 'Friday' in inputData.iloc[i]['day_of_the_week']:
        inputData.iloc[i].DOW=6
    elif 'Saturday' in inputData.iloc[i]['day_of_the_week']:
        inputData.iloc[i].DOW =7
user_37650340
Related