Cleaning the Adult Dataset

import pandas as pd
import numpy as np

adult_data_path = 'raw_adult/adult.data'
adult_test_path = 'raw_adult/adult.test'
cols = ['age','workclass','fnlwgt','education','education-num','marital-status',
        'occupation','relationship','race','sex','capital-gain', 'capital-loss',
        'hours-per-week', 'native-country','compensation']
a = (pd.read_csv(adult_data_path,
                 names=cols,
                 sep=', ',
                 engine='python')
     .append(pd.read_csv(adult_test_path,
                         skiprows=1,
                         names=cols,
                         sep=', ',
                         engine='python')))
print(a.shape)
a.head()

a = a.replace({'<=50K.' : '<=50K',
               '>50K.'  : '>50K'})

a = (a[(a['workclass']!='?')&
       (a['occupation']!='?')&
       (a['workclass']!='Without-pay')]
     .reset_index(drop=True))
a['idx'] = a.index

a.loc[a['occupation']=='Armed-Forces','occupation'] = 'Protective-serv'

a.loc[a['workclass'].isin(['State-gov', 'Federal-gov', 'Local-gov']), 
      'employment-type'] = 'Government'
a.loc[a['workclass'].isin(['Self-emp-not-inc', 'Self-emp-inc']),      
      'employment-type'] = 'Self-Employed'
a.loc[a['workclass'].isin(['Private']),                               
      'employment-type'] = 'Privately-Employed'
a['employment-type'].unique()

a.loc[a['education-num'] <= 8,          'education'] = 'Less than High School'
a.loc[a['education-num'].isin([ 9,10]), 'education'] = 'High School'
a.loc[a['education-num'].isin([11,12]), 'education'] = 'Associates'
a.loc[a['education-num'].isin([13]),    'education'] = 'Bachelors'
a.loc[a['education-num'].isin([14]),    'education'] = 'Masters'
a.loc[a['education-num'].isin([15,16]), 'education'] = 'PhD/Professional'
a['education'].unique()

a = a.rename(columns={'hours-per-week':'weekly-hours'})
a.head()

a['age raw'] = a['age']
a['employment-type raw'] = a['employment-type']
a['education raw'] = a['education']
a['occupation raw'] = a['occupation']
a['race raw'] = a['race']
a['sex raw'] = a['sex']
a['weekly-hours raw'] = a['weekly-hours']
a['compensation raw'] = a['compensation']

a['employment-type'] = a['employment-type'].astype('category')
a['employment-type'] = a['employment-type'].cat.codes
a[['employment-type','employment-type raw','idx']].groupby(['employment-type',
                                                            'employment-type raw']).count()

a['education'] = a['education'].astype('category')
a['education'] = a['education'].cat.codes
a[['education','education raw','idx']].groupby(['education',
                                                'education raw']).count()

a['occupation'] = a['occupation'].astype('category')
a['occupation'] = a['occupation'].cat.codes
a[['occupation','occupation raw','idx']].groupby(['occupation',
                                                  'occupation raw']).count()

a['race'] = a['race'].astype('category')
a['race'] = a['race'].cat.codes
a[['race','race raw','idx']].groupby(['race',
                                      'race raw']).count()

a['sex'] = a['sex'].astype('category')
a['sex'] = a['sex'].cat.codes
a[['sex','sex raw','idx']].groupby(['sex',
                                    'sex raw']).count()

a['compensation'] = a['compensation'].astype('category')
a['compensation'] = a['compensation'].cat.codes
a[['compensation','compensation raw','idx']].groupby(['compensation',
                                                      'compensation raw']).count()

a = a[['idx',
       'age',
       'employment-type',
       'education',
       'occupation',
       'race',
       'sex',
       'weekly-hours',
       'compensation']].copy()
print(a.shape)
a.head()

print('   Mean Age = ' + str(a['age'].mean()))
print('Std Dev Age = ' + str(a['age'].std()))
a['age'] = (a['age'] - a['age'].mean()) / a['age'].std()

print('   Mean Weekly-Hours = ' + str(a['weekly-hours'].mean()))
print('Std Dev Weekly-Hours = ' + str(a['weekly-hours'].std()))
a['weekly-hours'] = (a['weekly-hours'] - a['weekly-hours'].mean()) / a['weekly-hours'].std()
a.head()

print(a.shape)
a = pd.get_dummies(a,
                   columns=['employment-type',
                            'occupation',
                            'race'],
                   drop_first=True)
a = a[['idx', 'age', 'education', 'sex', 'weekly-hours',
       'employment-type_1', 'employment-type_2', 
       'occupation_1', 'occupation_2', 'occupation_3', 'occupation_4', 'occupation_5', 'occupation_6', 
       'occupation_7', 'occupation_8', 'occupation_9', 'occupation_10', 'occupation_11', 'occupation_12', 
       'race_1', 'race_2', 'race_3', 'race_4',
       'compensation']].copy()
print(a.shape)
a.head()

a.to_csv('adult_clean.csv',
         index=False)