# helper functions
import getEPH
import categorize
import createVariables
import schoolYears
import make_dummy
import functionsForModels
# libraries
import pandas as pd
import numpy as np
from scipy import stats
import statsmodels.api as sm
import matplotlib.pyplot as plt
import seaborn as sns
from statsmodels.sandbox.regression.predstd import wls_prediction_std
from statsmodels.iolib.table import (SimpleTable, default_txt_fmt)
np.random.seed(1024)
%matplotlib inline

getEPH.getEPHdbf('t310')

"""
Explanation: New York University
Applied Data Science 2016 Final Project

Measuring household income under Redatam in CensusData
2. Merge Individual to Household Data

Project Description: Lorem ipsum
Members:
- Felipe Gonzales
- Ilan Reinstein
- Fernando Melchor
- Nicolas Metallo
Sources:
- http://dlab-geo.github.io/geocoding-geopy/slides/index.html#2
- https://gist.github.com/rgdonohue/c4beedd3ca47d29aef01
- http://darribas.org/gds_scipy16/ipynb_md/07_spatial_clustering.html
- https://glenbambrick.com/2016/01/09/csv-to-shapefile-with-pyshp/
- http://statsmodels.sourceforge.net/devel/examples/generated/example_wls.html
LIBRARIES
End of explanation
"""


data1 = pd.read_csv('data/cleanDatat310.csv')

data2 = categorize.categorize(data1)
data3 = schoolYears.schoolYears(data2)
data4 = createVariables.createVariables(data3)

"""
Explanation: DATA HANDLING
End of explanation
"""


jobsAndIncome = (data4.activity == 1) & (data4.P21 > 1) # we only consider people who are working and have income
headAndSpouse = (data4.familyRelation == 1)|(data4.familyRelation == 2) #
dataParaModelo = data4.copy().loc[jobsAndIncome,:]

variablesOfInterest = ['age',
                              'age2',
                              'female',
                              'education',
                              'education2']

model = functionsForModels.runModel(dataset = dataParaModelo, income = 'lnIncome', variables = variablesOfInterest)

X = sm.add_constant(dataParaModelo.copy().loc[:,variablesOfInterest].values)

dataParaModelo['predictedLnIncome'] = model.predict(X)

"""
Explanation: REGRESSION MODEL
Predicted Income:
Using the model generated in the previous notebook (Model # 2 - Alternative) we create a new variable called predicted income with its output. The ultimate goal is to merge this data with household data.
End of explanation
"""


paraMerge = dataParaModelo.loc[:,['CODUSU', 'NRO_HOGAR', 'COMPONENTE','predictedLnIncome']]
paraMerge.head()

data = pd.merge(left = data4 , right = paraMerge, on = ['CODUSU', 'NRO_HOGAR', 'COMPONENTE'], how = 'left')

data.predictedLnIncome[data.predictedLnIncome.isnull()] = 0 

"""
Explanation: Merge subset of individuals (with job and income) with total individuals
We create a merged dataset combining a subset of individuals (with job and income) with the total individuals. We then account for people with no income (kids or unemployed) and assign them a "0" income. The resulting dataset will succesfully match every individual with their household.
End of explanation
"""


# Variables related to Occupation
data['job'] = (data.activity==1).astype(int)
data['noJob'] = (data.activity!=1).astype(int)
data['schoolAndJob'] = data.job * data.education

cantidadActivos = data.job.groupby(by=data['id']).sum() # Number of people working in the household
cantidadInactivos = data.noJob.groupby(by=data['id']).sum() # Number of people not working in the household
schoolAndJob = data.schoolAndJob.groupby(by=data['id']).sum() # Sum of total schooling years in the household

dfJobsAndEduc = pd.merge(left = schoolAndJob.to_frame() ,
                         right = cantidadInactivos.to_frame(),
                         left_index = True,
                         right_index = True)
dfJobsAndEduc = pd.merge(left = dfJobsAndEduc ,
                         right = cantidadActivos.to_frame(),
                         left_index = True,
                         right_index = True)

"""
Explanation: New variables for individuals
We create a set of new variables (re-arrangement of previous ones) set in a way we create will add value to our prediction
End of explanation
"""


cleanData = data.copy().loc[(headAndSpouse),
                              ['id',
                               'AGLOMERADO',
                            'familyRelation',
                              'age',
                              'age2',
                              'female',
                              'education',
                              'education2',
                              'primary',
                              'secondary',
                              'university',
                              'P21',
                              'P47T',
                              'lnIncome',
                               u'lnIncomeT',
                              'predictedLnIncome',
                              'job',
                              'DECCFR',
                               'DECIFR',
                               'maritalStatus',
                               'reading',
                               'placeOfBirth',
                              ]]

cleanData.head()

pivot = cleanData.pivot(index='id', columns='familyRelation')
pivot.head()

"""
Explanation: Create Pivot Dataset
We create a pivot dataset in order to merge it later with the household dataset. With this we will have all individual information relative to a particular household.
End of explanation
"""


pivot.to_csv('data/pivotInd.csv')

dataN = pd.read_csv('data/pivotInd.csv', names = ['id','AGLO1','AGLO2','headAge','spouseAge','headAge2','spouseAge2',
                                                  'headFemale','spouseFemale','headEduc','spouseEduc',
                                                  'headEduc2','spouseEduc2','headPrimary','spousePrimary',
                                                  'headSecondary','spouseSecondary','headUniversity','spouseUniversity',
                                                  'headP21','spouseP21','headP47T','spouseP47T',
                                                  'headLnIncome','spouseLnIncome','headLnIncomeT','spouseLnIncomeT',
                                                  'headPredictedLnIncome','spousePredictedLnIncome','headJob','spouseJob',
                                                  'headDECCFR','spouseDECCFR','headDECIFR','spouseDECIFR',
                                                  'headMaritalStatus','spouseMaritalStatus',
                                                  'headReading','spouseReading','headPlaceOfBirth','spouseplaceOfBirth',
                                                 ],skiprows = 3)

dfJobsAndEduc['id'] = dfJobsAndEduc.index
dfJobsAndEduc['id'] = dfJobsAndEduc['id'].astype(int)
dataFinalCSV = pd.merge(left = dfJobsAndEduc ,
                         right = dataN,
                         left_on = 'id',
                         right_on = 'id')

dataFinalCSV.to_csv('data/pivotInd.csv',index=False)
dataFinalCSV.head()

"""
Explanation: Create Intermediate Dataset
We save our dataset into a csv file in order to eliminate the double index, and then after some data handling we export our final dataset.
End of explanation
"""


getEPH.getEPHdbf('t310')
hog = pd.read_csv('data/cleanDataHouseholdt310.csv')
print hog.shape
hog.head()

"""
Explanation: GET HOUSEHOLD DATA
 Variables description: 
- HomeType
- FloorMaterial
- RoofMaterial
- RoofCoat
- Water
- WaterType
- Toilet
- ToletLocation
- ToiletType
End of explanation
"""


def remove9(df,variables):
    for var in variables:
        df[var].replace(to_replace=[9], value=[np.nan] , inplace=True, axis=None)

def remove0(df,variables):
    for var in variables:
        df[var].replace(to_replace=[0], value=[np.nan] , inplace=True, axis=None)

def remove99(df,variables):
    for var in variables:
        df[var].replace(to_replace=[99], value=[np.nan] , inplace=True, axis=None)

hog2 = hog.copy()

remove9(df = hog2, variables = ['FloorMaterial','RoofMaterial','RoofCoat','Water','WaterType','Toilet','ToiletLocation',
                               'ToiletType','Sewer','DumpSites','Flooding','EmergencyLoc','CookingCombustible',
                               'BathroomUse'])
remove0(df = hog2, variables = ['FloorMaterial','RoofMaterial','RoofCoat','Water','WaterType','Toilet','ToiletLocation',
                               'ToiletType','Sewer','DumpSites','Flooding','EmergencyLoc','Ownership','CookingCombustible',
                               'BathroomUse'])
remove99(df = hog2, variables = ['Ownership'])

"""
Explanation: DATA CLEANING
End of explanation
"""


variables = ['CookingCombustible']
for var in variables:
    print hog2[var].value_counts()
    plt.scatter(hog2[var], hog2.TotalHouseHoldIncome)
    plt.show()

hog2['CookingRec'] = np.nan
hog2['CookingRec'][hog2.CookingCombustible == 4] = 1
hog2['CookingRec'][hog2.CookingCombustible == 3] = 1
hog2['CookingRec'][hog2.CookingCombustible == 2] = 2
hog2['CookingRec'][hog2.CookingCombustible == 1] = 3

variables = ['CookingRec']
for var in variables:
    print hog2[var].value_counts()
    plt.scatter(hog2[var], hog2.TotalHouseHoldIncome)
    plt.show()

hog2['WaterRec'] = (hog2.Water == 1).astype(int)

hog2['OwnershipRec'] = ((hog2.Ownership == 1) | (hog2.Ownership == 3)).astype(int)
hog2['Hacinamiento'] = hog2.HouseMembers * 1.0 / hog2.SleepingRooms
hog2['id'] =  (hog2.CODUSU.astype(str) + hog2.NRO_HOGAR.astype(str))

hog2['TotalHouseHoldIncome'].replace(to_replace=[0], value=[1] , inplace=True, axis=None)
hog2['lnHouseIncome'] = np.log(hog2['TotalHouseHoldIncome'])
sinCuartosParaDormir = (hog2.SleepingRooms == 0)

hogReducida = hog2.copy().drop(['CODUSU','NRO_HOGAR','REGION','HomeTypeesp','FloorMaterialesp',
                              'WaterTypeesp','Ownershipesp','CookingCombustibleesp','DomesticService1', 
                               'DomesticService2', 'DomesticService3','DomesticService4', 'DomesticService5',
                               'DomesticService6'],axis = 1)

"""
Explanation: Ordinal Order Transformation
In order to have positive coefficients we changed the order in which ordinal variables were displayed. We only do this for 'CookingCombustible' (which shows what combustible is used in the household) and for the others we use dummy variables.
End of explanation
"""


# Load data previously exported

ind = pd.read_csv('data/pivotInd.csv')
ind['id'] = ind['id'].astype(str)

ind.drop(['AGLO2'],axis =1,inplace=True)
ind['sumPredicted'] = ind.headPredictedLnIncome + ind.spousePredictedLnIncome

"""
Explanation: LOAD INDIVIDUAL DATA
End of explanation
"""


hogReducida = hogReducida.copy().loc[~sinCuartosParaDormir,:]

# check before merge

print 'filas hog:',hogReducida.shape[0]
print 'filas ind:',ind.shape[0]
print 'cantidad de ind en hog:', sum(ind['id'].sort_values().isin(hogReducida['id'].sort_values()))

dataUnida = pd.merge(left=hogReducida, right=ind, on='id',how='left')

dataUnida.to_csv('data/dataFinalParaModelo.csv',index=False)

"""
Explanation: MERGE INDIVIDUAL WITH HOUSEHOLD
End of explanation
"""


