<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <h2>Preparation phase</h2>
Objective for training dataframe
Step1:  take 80% as train dataframe, and leave 20% as a validator/to check the accuracy of our prediction before applying the model to the real test data.
Step2:  <h3>#prepare the training dataset for export</h3>
Step3:  <h3>#prepare the validation dataset</h3>
The validation dataset must have same table structure with the training dataset so that we can calculate the accuracy.
Step4:  <h3>#preparation of test dataset</h3>
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
df = pd.read_csv('preparation.csv',delimiter=',')
df.shape
!ls
Explanation: <h2>Preparation phase</h2>
Objective for training dataframe:
- Cleaning the data (missing data imputation/replacement, change the string to numeric, and so on)
- Split the data frame into subsets (if the size of the training data is very large) and perform feature extraction/feature engineering and then merge the result
- Split training dataframe into two part; 80% of the data for the training itself, and other 20% is for validation (repeat the process using different subset and it become cross-validation)
Objective for test dataframe:
- Cleaning the data (missing data imputation/replacement, change the string to numeric, and so on)
- Split the data frame into subsets (if the size of the training data is very large) and perform feature extraction/feature engineering and then merge the result. Create the same features for the test set
End of explanation
59400*0.8
dftrain = pd.read_csv('preparation.csv',delimiter=',',nrows=47520)
dfpayment = pd.read_csv('trainingset.csv',delimiter=',',nrows=47520)
dftrain.tail()
dftrain['terrain'] = 'dataran tinggi'
dftrain['terrain'][dftrain['gps_height']<=500] = 'dataran rendah'
dftrain['terrain'][(dftrain['gps_height']>500) & (dftrain['gps_height']<=1000)] = 'dataran sedang'
dftrain['terrain'].replace('dataran rendah',0,inplace=True)
dftrain['terrain'].replace('dataran sedang',1,inplace=True)
dftrain['terrain'].replace('dataran tinggi',2,inplace=True)
dftrain.head()
print dftrain.columns
dftrain['water_availability'] = dftrain.apply(lambda row: 0 if row['amount_tsh']==0.0 else 1,axis=1)
dftrain['payment'] = dfpayment['payment']
dftrain['payment'].unique()
dftrain['payment'].replace('pay annually',1,inplace=True)
dftrain['payment'].replace('never pay',2,inplace=True)
dftrain['payment'].replace('pay per bucket',3,inplace=True)
dftrain['payment'].replace('unknown',0,inplace=True)
dftrain['payment'].replace('pay when scheme fails',4,inplace=True)
dftrain['payment'].replace('other',5,inplace=True)
dftrain['payment'].replace('pay monthly',6,inplace=True)
Explanation: take 80% as train dataframe, and leave 20% as a validator/to check the accuracy of our prediction before applying the model to the real test data.
End of explanation
dftrain2 = dftrain[['id','water_availability','terrain','num_private','region_code','district_code','population','water_quality','quality_group','quantity_group','source_type','source_class','waterpoint_type','wp_age','daysdiff','payment','status_group']].copy()
dftrain2.tail()
dftrain2.to_csv('dftrain.csv',sep=',',header=True,index=False)
Explanation: <h3>#prepare the training dataset for export</h3>
End of explanation
dfvalidation = pd.read_csv('preparation.csv',delimiter=',',skiprows=range(1,47520),nrows=11880)
dfv_payment = pd.read_csv('trainingset.csv',delimiter=',',skiprows=range(1,47520),nrows=11880)
print dfvalidation.shape
dfvalidation.tail()
dfvalidation['terrain'] = 'dataran tinggi'
dfvalidation['terrain'][dftrain['gps_height']<=500] = 'dataran rendah'
dfvalidation['terrain'][(dfvalidation['gps_height']>500) & (dfvalidation['gps_height']<=1000)] = 'dataran sedang'
dfvalidation['terrain'].replace('dataran rendah',0,inplace=True)
dfvalidation['terrain'].replace('dataran sedang',1,inplace=True)
dfvalidation['terrain'].replace('dataran tinggi',2,inplace=True)
dfvalidation.head()
dfvalidation['water_availability'] = dfvalidation.apply(lambda row: 0 if row['amount_tsh']==0.0 else 1,axis=1)
dfvalidation['payment'] = dfv_payment['payment']
dfvalidation['payment'].replace('pay annually',1,inplace=True)
dfvalidation['payment'].replace('never pay',2,inplace=True)
dfvalidation['payment'].replace('pay per bucket',3,inplace=True)
dfvalidation['payment'].replace('unknown',0,inplace=True)
dfvalidation['payment'].replace('pay when scheme fails',4,inplace=True)
dfvalidation['payment'].replace('other',5,inplace=True)
dfvalidation['payment'].replace('pay monthly',6,inplace=True)
dfvalidation2 = dfvalidation[['id','water_availability','terrain','num_private','region_code','district_code','population','water_quality','quality_group','quantity_group','source_type','source_class','waterpoint_type','wp_age','daysdiff','payment','status_group']].copy()
dfvalidation2.head()
dfvalidation2.to_csv('validation.csv',sep=',',header=True,index=False)
!ls -l
print "done"
Explanation: <h3>#prepare the validation dataset</h3>
The validation dataset must have same table structure with the training dataset so that we can calculate the accuracy.
End of explanation
dftest = pd.read_csv('testset.csv',delimiter=',')
dftest.head()
dftest.isnull().sum().sum()
print list(dftest.columns)
dftest['source_class'].isnull().sum().sum()
dftest['public_meeting'].fillna('unknown',inplace=True)
dftest['payment'].unique()
dftest['wp_age'] = dftest.apply(lambda row: (2017-row['construction_year']),axis=1)
dftest['water_quality'].replace('soft',1,inplace=True)
dftest['water_quality'].replace('salty',2,inplace=True)
dftest['water_quality'].replace('milky',3,inplace=True)
dftest['water_quality'].replace('fluoride',4,inplace=True)
dftest['water_quality'].replace('coloured',5,inplace=True)
dftest['water_quality'].replace('salty abandoned',6,inplace=True)
dftest['water_quality'].replace('fluoride abandoned',7,inplace=True)
dftest['water_quality'].replace('unknown',0,inplace=True)
dftest['quantity_group'].replace('enough',1,inplace=True)
dftest['quantity_group'].replace('insufficient',2,inplace=True)
dftest['quantity_group'].replace('dry',3,inplace=True)
dftest['quantity_group'].replace('seasonal',4,inplace=True)
dftest['quantity_group'].replace('unknown',0,inplace=True)
dftest['waterpoint_type'].replace('communal standpipe',1,inplace=True)
dftest['waterpoint_type'].replace('communal standpipe multiple',2,inplace=True)
dftest['waterpoint_type'].replace('hand pump',3,inplace=True)
dftest['waterpoint_type'].replace('other',0,inplace=True)
dftest['waterpoint_type'].replace('improved spring',4,inplace=True)
dftest['waterpoint_type'].replace('cattle trough',5,inplace=True)
dftest['waterpoint_type'].replace('dam',6,inplace=True)
dftest['source_type'].replace('spring',1,inplace=True)
dftest['source_type'].replace('rainwater harvesting',2,inplace=True)
dftest['source_type'].replace('dam',3,inplace=True)
dftest['source_type'].replace('borehole',4,inplace=True)
dftest['source_type'].replace('other',0,inplace=True)
dftest['source_type'].replace('shallow well',5,inplace=True)
dftest['source_type'].replace('river/lake',6,inplace=True)
dftest['quality_group'].replace('good',1,inplace=True)
dftest['quality_group'].replace('salty',2,inplace=True)
dftest['quality_group'].replace('milky',3,inplace=True)
dftest['quality_group'].replace('unknown',0,inplace=True)
dftest['quality_group'].replace('fluoride',4,inplace=True)
dftest['quality_group'].replace('colored',5,inplace=True)
dftest['source_class'].replace('groundwater',1,inplace=True)
dftest['source_class'].replace('surface',2,inplace=True)
dftest['source_class'].replace('unknown',0,inplace=True)
import datetime as dt
dftest['today'] = dftest.apply(lambda row: dt.datetime.today().strftime('%Y-%m-%d'),axis=1)
dftest['date_recorded'] = [time.date() for time in dftest['date_recorded'].astype('datetime64[ns]')]
dftest['today'] = [time.date() for time in dftest['today'].astype('datetime64[ns]')]
dftest['lastcheckdays'] = abs(dftest['date_recorded'].sub(dftest['today'], axis=0))
dftest['daysdiff'] = dftest['lastcheckdays'] / np.timedelta64(1, 'D')
dftest['terrain'] = 'dataran tinggi'
dftest['terrain'][dftest['gps_height']<=500] = 'dataran rendah'
dftest['terrain'][(dftest['gps_height']>500) & (dftest['gps_height']<=1000)] = 'dataran sedang'
dftest['terrain'].replace('dataran rendah',0,inplace=True)
dftest['terrain'].replace('dataran sedang',1,inplace=True)
dftest['terrain'].replace('dataran tinggi',2,inplace=True)
dftest['water_availability'] = dftest.apply(lambda row: 0 if row['amount_tsh']==0.0 else 1,axis=1)
dftest2 = dftest._get_numeric_data()
dftest2.head()
dftest3 = dftest2[['id','water_availability','terrain','num_private','region_code','district_code','population','water_quality','quality_group','quantity_group','source_type','source_class','waterpoint_type','wp_age','daysdiff']].copy()
dftest3.head()
dftest3.to_csv('testsetfix.csv',sep=',',header=True,index=False)
dftest3 = pd.read_csv('testsetfix.csv',delimiter=',')
dftest3['payment'] = dftest['payment']
dftest3['payment'].unique()
dftest3['payment'].replace('pay annually',1,inplace=True)
dftest3['payment'].replace('never pay',2,inplace=True)
dftest3['payment'].replace('pay per bucket',3,inplace=True)
dftest3['payment'].replace('unknown',0,inplace=True)
dftest3['payment'].replace('pay when scheme fails',4,inplace=True)
dftest3['payment'].replace('other',5,inplace=True)
dftest3['payment'].replace('pay monthly',6,inplace=True)
dftest3 = dftest3[['id','water_availability','terrain','num_private','region_code','district_code','population','water_quality','quality_group','quantity_group','source_type','source_class','waterpoint_type','wp_age','daysdiff','payment']].copy()
dftest3.head()
dftest3.to_csv('testsetfix.csv',sep=',',header=True,index=False)
Explanation: <h3>#preparation of test dataset</h3>
End of explanation
<END_TASK>