"""
# Wrangling of Pak Election DATA
"""
"""
## Importing liabraries required
"""
# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
## Loading data using pandas.read_csv
"""
NA_2002 = pd.read_csv("../input/predict-pakistan-elections-2018/National Assembly 2002 - Updated.csv",encoding = "ISO-8859-1")
NA_2008 = pd.read_csv("../input/predict-pakistan-elections-2018/National Assembly 2008.csv",encoding = "ISO-8859-1")
NA_2013 = pd.read_csv("../input/predict-pakistan-elections-2018/National Assembly 2013 - Updated.csv",encoding = "ISO-8859-1")
"""
## Getting information about dataset
"""
print('Data Dimentions of NA_2002:',NA_2002.shape)
print('Data Dimentions of NA_2008:',NA_2008.shape)
print('Data Dimentions of NA_2013:',NA_2013.shape)
print("NA_ 2002.csv")
NA_2002.info()
print("NA_2008.csv")
NA_2008.info()
print("NA_2013.csv")
NA_2013.info()
"""
### Issues in data:
Unnamed Columns in NA_2008 and NA_2013.

Total number of columns in NA_2013 because there is and extra Unnamed column with no data.

Data type of Turnout Column is not approriate. It must be float64.
"""
"""
### Cleaning data
"""
"""
Changing Data type of Turnout to flot.
"""
NA_2008['Turnout'] = NA_2008['Turnout'].str.rstrip('%').str.rstrip(' ')
NA_2013['Turnout'] = NA_2013['Turnout'].str.rstrip('%').str.rstrip(' ')
NA_2008['Turnout'] = pd.to_numeric(NA_2008['Turnout'], errors='coerce')
NA_2013['Turnout'] = pd.to_numeric(NA_2013['Turnout'], errors='coerce')
"""
Renaming Unnamed column to district
"""
NA_2008.rename(columns={'Unnamed: 0':'District'}, inplace=True)
NA_2013.rename(columns={'Unnamed: 0':'District'}, inplace=True)
"""
Droping extra column "Unnamed" with no data
"""
NA_2013 = NA_2013.drop('Unnamed: 11', axis=1)
"""
### Rechecking data
"""
print("NA_ 2002.csv")
NA_2002.info()
print("NA_2008.csv")
NA_2008.info()
print("NA_2013.csv")
NA_2013.info()
print('First 5 Rows of NA_2002')
NA_2002.head()
"""
Column names are different in NA_2002.

We will change them to make all columns nemes similar.
"""
NA_2002.rename(columns={'Constituency_title':'ConstituencyTitle', 'Candidate_Name':'CandidateName', 'Total_Valid_Votes':'TotalValidVotes', 'Total_Rejected_Votes':'TotalRejectedVotes', 'Total_Votes':'TotalVotes', 'Total_Registered_Voters':'TotalRegisteredVoters', }, inplace=True)
NA_2002.columns
print('First 5 Rows of NA_2008')
NA_2008.head()
print('First 5 Rows of NA_2013')
NA_2013.head()
"""
Looking of Nan values in Data

"""
print("NA_2002", NA_2002.isnull().any(), "\nNA_2008: ", NA_2008.isnull().any(), "\nNA_2013:", NA_2013.isnull().any())
"""
### Concat all 3 datasets into 1:
"""
df = pd.concat([NA_2002, NA_2008, NA_2013])
df.shape
df.head()
df.isnull().any()
# get all the unique values in the 'District' column
#df['District'] = df['District'].astype(str)
dist = df['District'].unique()
#dist.sort()
dist
# convert to lower case
df['District'] = df['District'].str.lower()
# remove trailing white spaces
df['District'] = df['District'].str.strip()
dist = df['District'].unique()
#dist.sort()
dist
import fuzzywuzzy
from fuzzywuzzy import process
import chardet
# get the top 10 closest matches to "charsadda"
matches = fuzzywuzzy.process.extract("charsadda", dist, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio)

# take a look at them
matches
# function to replace rows in the provided column of the provided dataframe
# that match the provided string above the provided ratio with the provided string
def replace_matches_in_column(df, column, string_to_match, min_ratio = 90):
    # get a list of unique strings
    strings = df[column].unique()
    
    # get the top 10 closest matches to our input string
    matches = fuzzywuzzy.process.extract(string_to_match, strings, 
                                         limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio)

    # only get matches with a ratio > 90
    close_matches = [matches[0] for matches in matches if matches[1] >= min_ratio]

    # get the rows of all the close matches in our dataframe
    rows_with_matches = df[column].isin(close_matches)

    # replace all rows with close matches with the input matches 
    df.loc[rows_with_matches, column] = string_to_match
# use the function we just wrote to replace close matches to "charsadda" 
replace_matches_in_column(df=df, column='District', string_to_match="charsadda")
dist = df['District'].unique()
#dist.sort()
dist
replace_matches_in_column(df=df, column='District', string_to_match="nowshera")
replace_matches_in_column(df=df, column='District', string_to_match="rawalpindi")
replace_matches_in_column(df=df, column='District', string_to_match="sheikhupura")
replace_matches_in_column(df=df, column='District', string_to_match="shikarpur")
replace_matches_in_column(df=df, column='District', string_to_match="nankana sahib")
del dist

pty = df['Party'].unique()
pty.sort()
pty
df['Party'] = df['Party'].replace(['MUTTHIDA\xa0MAJLIS-E-AMAL\xa0PAKISTAN'], 'Muttahidda Majlis-e-Amal Pakistan')
df['Party'] = df['Party'].replace(['Pakistan Muslim League'], 'Pakistan Muslim League (QA)')
#converting text to lower case & removing white spaces
df['Party'] = df['Party'].str.lower()
df['Party'] = df['Party'].str.strip()
replace_matches_in_column(df=df, column='Party', string_to_match="Balochistan National Movement")
replace_matches_in_column(df=df, column='Party', string_to_match="Independent")
replace_matches_in_column(df=df, column='Party', string_to_match="Istiqlal Party")
replace_matches_in_column(df=df, column='Party', string_to_match="Jamote Qaumi Movement")
replace_matches_in_column(df=df, column='Party', string_to_match="Labour Party Pakistan")
replace_matches_in_column(df=df, column='Party', string_to_match="Mohib-e-Wattan Nowjawan Inqilabion Ki Anjuman (MNAKA)")
replace_matches_in_column(df=df, column='Party', string_to_match="Muttahida Qaumi Movement") # Muttahida Qaumi Movement Pakistan
replace_matches_in_column(df=df, column='Party', string_to_match="Muttahidda Majlis-e-Amal") # Muttahidda Majlis-e-Amal Pakistan
replace_matches_in_column(df=df, column='Party', string_to_match="National Peoples Party")
replace_matches_in_column(df=df, column='Party', string_to_match="Nizam-e-Mustafa Party")
replace_matches_in_column(df=df, column='Party', string_to_match="Pak Muslim Alliance")
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Awami Party")
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Democratic Party")
# After analyzing each of the below strings.
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Muslim League (QA)", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Muslim League (N)", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Muslim League (J)", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Muslim League (F)", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Peoples Party Parliamentarians", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Peoples Party(Shaheed Bhutto)", min_ratio =95)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Peoples Party(Sherpao)", min_ratio =97)
replace_matches_in_column(df=df, column='Party', string_to_match="Pakistan Tehreek-e-Insaf", min_ratio =95)
replace_matches_in_column(df=df, column='Party', string_to_match="Saraiki Sooba Movement Pakistan", min_ratio =95)
df['Party'] = df['Party'].str.lower()
# few fixes taken from https://www.kaggle.com/usman786/exploratory-data-analysis-for-interesting-insights/notebook
df['Party'].replace(['muttahida qaumi movement pakistan'], 'muttahida qaumi movement', inplace = True)
df['Party'].replace(['indeindependentdente','independent (retired)','indepndent'], 'independent',inplace = True)
df['Party'].replace(['indeindependentdente','independent (retired)','indepndent'], 'independent',inplace = True)
df['Party'].replace(['muttahidda majlis-e-amal pakistan','mutthida\xa0majlis-e-amal\xa0pakistan'
                     ,'mutthidaï¿½majlis-e-amalï¿½pakistan'] 
                     ,'muttahidda majlis-e-amal' ,inplace = True)
df['Party'].replace(['nazim-e-mistafa'], 'nizam-e-mustafa party' ,inplace = True)
df['Party'].replace(['pakistan muslim league (qa)'], 'pakistan muslim league (q)' ,inplace = True)
df['Party'].replace(['pakistan muslim league council'], 'pakistan muslim league (c)' ,inplace = True)
df['Party'].replace(['pakistan muslim league \x93h\x94 haqiqi'], 'pakistan muslim league haqiqi' ,inplace = True)
df['Party'].replace(['pakistan muslim league(z)'], 'pakistan muslim league (z)' ,inplace = True)
df['Party'].replace(['pakistan peoples party(shaheed bhutto)'], 'pakistan peoples party (shaheed bhutto)' ,inplace = True)
df['Party'].replace(['pakistan peoples party parliamentarians'], 'pakistan peoples party parliamentarians' ,inplace = True)
df['Party'].replace(['pakistan sariaki party'], 'Pakistan Siraiki Party (T)' ,inplace = True)
df['Party'].replace(['pasban'], 'pasban pakistan' ,inplace = True)
df['Party'].replace(['qaumi watan party (sherpao)'], 'qaumi watan party' ,inplace = True)
df['Party'].replace(['tehreek-e-suba hazara'], 'tehreek-e-suba hazara pakistan' ,inplace = True)
#...
df['Party'].replace(['pashtoonkhwa milli awami party'], 'pakhtoonkhwa milli Awami party' ,inplace = True)
df['Party'].replace(['pakistan amn party'], 'pakistan aman party' ,inplace = True)
df['Party'].replace(['pakistan awami inqelabi'], 'Pakistan Awami Inqelabi League' ,inplace = True)
df['Party'].replace(['pakistan freedom party'], 'pakistan freedom movement' ,inplace = True)
df['Party'].replace(['pakistan insani haqook party (pakistan human rights party)'], 'pakistan human rights party' ,inplace = True)
df['Party'].replace(['awami justice party'], 'awami justice party pakistan' ,inplace = True)
df['Party'].replace(['indeindependentdent'], 'independent' ,inplace = True)
df['Party'].replace(['jamiat ulama-e-pakistan  (noorani)'], 'jamiat ulama-e-pakistan (noorani)' ,inplace = True)
df['Party'].replace(['jumiat ulma-e-islam(nazryati)'], 'jamiat ulma-e-islam nazryati pakistan' ,inplace = True)
df['Party'].replace(['majlis-e-wahdat-e-muslimeen pakistan'], 'Majlis Wahdat-e-Muslimeen Pakistan' ,inplace = True)
df['Party'].replace(['markazi jamat-al-hadais'], 'Markazi Jamiat Ahl-e-Hadith' ,inplace = True)
df['Party'].replace(['mohib-e-wattan nowjawan inqilabion ki anjuman (mnaka)'], 'Muhib-e-Watan Noujawan Anqlabion Ki Anjuman (MNAKA)' ,inplace = True)

pty = df['Party'].unique()
pty.sort()
pty
#convert textual content to lower case & remove trailing white spaces
df['CandidateName'] = df['CandidateName'].str.lower()
df['CandidateName'] = df['CandidateName'].str.strip()
# remove mr at the beginning of names.
df['CandidateName'] = df.loc[:, 'CandidateName'].replace(regex=True, to_replace="mr ", value="")
df['CandidateName'] = df.loc[:, 'CandidateName'].replace(regex=True, to_replace="mrs ", value="")
df['CandidateName'] = df.loc[:, 'CandidateName'].replace(regex=True, to_replace="miss ", value="")
#df['CandidateName'] = df.loc[:, 'CandidateName'].replace(regex=True, to_replace="mis ", value="")
df['CandidateName'].head(10)
cn = df['CandidateName'].unique()
cn.sort()
print("cn size: ", cn.shape, "\nValues: ", cn) 
# Lets observe few to set the threshold for fuzzywuzzy
fuzzywuzzy.process.extract("zumurad khan", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >90
fuzzywuzzy.process.extract("zobaida jalal", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >79
#fuzzywuzzy.process.extract("barkat ali", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >90
#fuzzywuzzy.process.extract("sher muhammad baloch", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >90
#fuzzywuzzy.process.extract("gulab baloch", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >90
#fuzzywuzzy.process.extract("babu gulab", cn, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio) # acceptance value >90
replace_matches_in_column(df=df, column='CandidateName', string_to_match="zumurad khan", min_ratio=92)
replace_matches_in_column(df=df, column='CandidateName', string_to_match="zobaida jalal", min_ratio=80)
replace_matches_in_column(df=df, column='CandidateName', string_to_match="barkat ali", min_ratio=90)
replace_matches_in_column(df=df, column='CandidateName', string_to_match="muhammad yasin baloch", min_ratio=90)

for candi in df['CandidateName'].unique(): # 7000
    replace_matches_in_column(df=df, column='CandidateName', string_to_match=candi, min_ratio=90)

# let us know the loop is completed
print("All done!")
"""
Saving this concatinated file as 'NA2002-18.csv' 
"""
df.to_csv('NA_2002-18.csv', index=None) 
"""
# Next Steps in progress..
"""
"""
 ### Please Upvote this notebook and comment about my work.**
"""