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?
CodePudding user response:
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