How to use a boolean to rename and replace values in a column?

user_4805393 0
Viewed 170

I am working with a dataframe, and a few data columns have missing catagories represented by a '?' in a column. I am trying to use a boolean to rename and replace the missing catagories marked '?' with 'Private'in a column labled workclass. The data is read in as:

import numpy as np 
import pandas as pd
import matplotlib.pyplot as plt
from pandas.plotting import scatter_matrix
from sklearn.preprocessing import *
url2="https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data" #Reading in Data from a freely and easily available source on the internet
Adult = pd.read_csv(url2, header=None)
##Assigning column names to the dataframe
Adult.columns = ["age","workclass","fnlwgt","education","educationnum","maritalstatus","occupation",  
                 "relationship","race","sex","capitalgain","capitalloss","hoursperweek","nativecountry",
                 "less50kmoreeq50kn"]

I have tried running the code:

MissingValue = Adult.loc[:, "workclass"] == "?"
Adult.loc[MissingValue, "workclass"] = "Private"

and

Adult.loc[ Adult.loc[:, "workclass"] == "?", "workclass"] = "Private"

I do not get any error's when running the code however when checking the column values with (Adult.loc[:,'workclass'].value_counts()) the '?' is still there. The code: Adult['workclass'] = Adult['workclass'].str.replace('?', 'Private') works for what I want to accomplish however I would like to be able to do it with a boolean. Any suggestions on why this might be happening?

pandas dataframe python
2 Answers

Problem is your value is does not match exactly '?' but is probably something like '? '

You can see this because:

 Adult.loc[Adult['workclass']=='?',:]

returns an empty dataframe whereas

 Adult.loc[Adult['workclass'].str.strip()=='?',:]

returns 1836 rows

strip removes leading and trailing whitespaces so you don't have to test ' ?', '? ', ' ? ' etc

So when you slightly change your code like this

MissingValue = Adult.loc[:, "workclass"].str.strip() == "?"
Adult.loc[MissingValue, "workclass"] = "Private"

You will see that the '?' has disappeared from the value_counts()

user_33011130

There are spaces after delimiter, so add skipinitialspace parameter:

Adult = pd.read_csv(url2, header=None, skipinitialspace=True)

And then working correctly your code:

MissingValue = Adult["workclass"] == "?"
Adult.loc[MissingValue, "workclass"] = "Private"

print ((Adult['workclass'].value_counts().index.tolist()))
['Private', 'Self-emp-not-inc', 'Local-gov', 'State-gov', 
 'Self-emp-inc', 'Federal-gov', 'Without-pay', 'Never-worked']

print ((Adult['workclass'].value_counts()))
Private             24532
Self-emp-not-inc     2541
Local-gov            2093
State-gov            1298
Self-emp-inc         1116
Federal-gov           960
Without-pay            14
Never-worked            7
Name: workclass, dtype: int64

Verify space with your code:

Adult = pd.read_csv(url2, header=None)

#Assigning column names to the dataframe
Adult.columns = ["age","workclass","fnlwgt","education","educationnum","maritalstatus","occupation",  
                 "relationship","race","sex","capitalgain","capitalloss","hoursperweek","nativecountry",
                 "less50kmoreeq50kn"]

print ((Adult['workclass'].value_counts().index.tolist()))
[' Private', ' Self-emp-not-inc', ' Local-gov', ' ?', ' State-gov',
 ' Self-emp-inc', ' Federal-gov', ' Without-pay', ' Never-worked']
user_29010020
Related