<jupyter_start><jupyter_text>Course: Computational Thinking for Governance Analytics Prof. <NAME>, PhD * Visiting Professor of Computational Policy at Evans School of Public Policy and Governance, and eScience Institute Senior Data Science Fellow, University of Washington.* Professor of Government and Political Methodology, Pontificia Universidad Católica del Perú. _____ Data Preprocessing in Python: Data Integration and Reshaping By now, we  know how to collect data from different places, and clean/format them as needed. While the cleaning and formatting is done for each data source, we finally need to integrate all the sources into one to start the real analytical work. Let's get the data on [democracy index](https://en.wikipedia.org/wiki/Democracy_Index) by _The Economist_:<jupyter_code># importing pandas for the notebook
import pandas as pd

#link for the tables
demoLink="https://en.wikipedia.org/wiki/Democracy_Index"

# fetching the tables
demodata=pd.read_html(demoLink,header=0,flavor="bs4",attrs={'class':"wikitable sortable"})<jupyter_output><empty_output><jupyter_text>You should remember by now that **demodata** is a _list_ of all the sortable tables from that URL. Let me recover the one we want:<jupyter_code>demoVars=demodata[4].copy()<jupyter_output><empty_output><jupyter_text>In this last session on Python, we want to prepare data on the world countries and their level of democracy. I will add some variables from other tables to this one later. Take a look at what you have now:<jupyter_code># first rows
demoVars.head()<jupyter_output><empty_output><jupyter_text>Let's start by getting rid of the columns we will not use:<jupyter_code>#column positions to drop
whichToDrop=[0,1,5]

#dropping and updating the data frame
demoVars.drop(labels=demoVars.columns[whichToDrop],axis=1,inplace=True)<jupyter_output><empty_output><jupyter_text>Let's take a look at the columns names:<jupyter_code># these are:
demoVars.columns
#demoVars.drop(labels=demoVars.columns[0],axis=1, inplace=True)
#demoVars.drop(labels=demoVars.columns[0],axis=1, inplace=True)
#demoVars.drop(labels=demoVars.columns[3],axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>The previous result shows nothing relevant, but the pandas data frame shows _unneeded dashes_ in the column names. Let's **check the presence of special characters**:<jupyter_code># this is a column name with dashes:
import re
re.sub("\s","",demoVars.columns[5])<jupyter_output><empty_output><jupyter_text>The result shows you some **hidden characters**. Let's use that info with the pandas replace:<jupyter_code># using pandas own replace function
demoVars.columns=demoVars.columns.str.replace("\s|\xad","",regex=True)

# current colum names
demoVars.columns<jupyter_output><empty_output><jupyter_text>Let's clean the data contents. Notice that in the website some labels that are not needed are present in this case. Let's check the frequency table of "Regime Type" to try to identify the wrong labels that are affecting the data frame to delete those *rows*:<jupyter_code># frequency table using "value_counts"
demoVars['Regimetype'].value_counts()
# these are the wrong ones:
demoVars['Regimetype'].value_counts().index[4:]
#saving the wrong ones:
byeValues=demoVars['Regimetype'].value_counts().index[4:]<jupyter_output><empty_output><jupyter_text>Now that we know which ones are not needed, we can **filter** the data frame **rows** using pandas' **isnin**:<jupyter_code># use "~" to request the "opposite" rows
demoVars = demoVars[~demoVars['Regimetype'].isin(byeValues)]

# data frame after
demoVars<jupyter_output><empty_output><jupyter_text>Notice that the index **0** has dissapeared, and that even though you have 167 countries now, the last one has index **170**. When you filter rows that will happen; so it is better to **reset the indexes of the data frame**:<jupyter_code># new indexes will appear
demoVars.reset_index(drop=True,inplace=True)<jupyter_output><empty_output><jupyter_text>Let's save the file as in CSV format:<jupyter_code>demoVars.to_csv("demoVars.csv",index=False)<jupyter_output><empty_output><jupyter_text>Exercise 1:- Go to this link: [https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes](https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes).- Scrap the table of countries, and keep country names, **ISO codes** and **Internet codes**.- Prepare a CSV file with the clean and formatted data, and upload that file into GitHub.- Read the file from Github and add the ISO codes and internet codes to the table of democracy index.  Integrating Data Frames Integrating data sets needs the following considerations:* Merging is done on two data frames.* You need a column in each data frame with that share the same exact and unique values. The column names or titles need not be the same.* The merged table shows by default the mutual coincidences; but you can also request the values not matched, which will help you detect possible extra cleaning.* Pandas differentiates the **left** from the **right** data frames. Let me bring the new data frame with ISO codes and internet codes:<jupyter_code># link to the data in CSV format
linkDataIso='https://github.com/EvansDataScience/CTforGA_integrating/raw/main/isodata.csv'

# using 'read_csv' with a link
DataIso=pd.read_csv(linkDataIso)
#Check the current names:
DataIso.columns<jupyter_output><empty_output><jupyter_text>* **Option one**(default): merge only the coincidences:<jupyter_code># notice the key column are country names, but the column names are different:
allData=demoVars.merge(DataIso,left_on="Country",right_on="Countryname")<jupyter_output><empty_output><jupyter_text>Once the merged has taken place, check the differences:<jupyter_code># number of rows (countries) before merge
demoVars.shape
# number of rows after merge
allData.shape<jupyter_output><empty_output><jupyter_text>The previous merge has **lost several rows** (countries). * **Option two**: you want to keep all the rows, matched and unmatched, from **both** data frames:<jupyter_code># merge 'outer' keeps all matched and unmatched
# merge 'indicator' will tell you if the key was found in 'both' or only in one of them
allData=demoVars.merge(DataIso,left_on="Country",right_on="Countryname",how='outer',indicator='True')<jupyter_output><empty_output><jupyter_text>The _allData_ dataframe has now a  column named 'True', the **indicator**:<jupyter_code>allData.columns
# explore matched and unmatched counts:
allData['True'].value_counts()<jupyter_output><empty_output><jupyter_text>Our main task is to add columns to the Democracy Index data. So these are the countries from The Economist that did not find a match:<jupyter_code>allData[allData['True']=='left_only'].Country<jupyter_output><empty_output><jupyter_text>Those names should be here, but written differently:<jupyter_code>allData[allData['True']=='right_only'].Countryname<jupyter_output><empty_output><jupyter_text>The next steps might seem a little laborious, combining computational thinking adn finally some brute force. The first tool we need is another library allows fuzzy merging: [TheFuzz](https://github.com/seatgeek/thefuzz).The library has two functions of interest:_extractOne_ and _extract_. Let me show you how they work:<jupyter_code># The countries unmatched
UnmatchedLeft=allData[allData['True']=='left_only'].Country.to_list()
UnmatchedRight=allData[allData['True']=='right_only'].Countryname.to_list()<jupyter_output><empty_output><jupyter_text>This is a country name fro the economist that did not get a match in the ISO data:<jupyter_code>UnmatchedLeft[0]<jupyter_output><empty_output><jupyter_text>Let's find the best match from the ISO data:<jupyter_code>from thefuzz import process
process.extractOne(UnmatchedLeft[0], UnmatchedRight)
!pip install thefuzz<jupyter_output>Collecting thefuzz
  Downloading thefuzz-0.19.0-py2.py3-none-any.whl (17 kB)
Installing collected packages: thefuzz
Successfully installed thefuzz-0.19.0<jupyter_text>Let's find the three best matches from the ISO data:<jupyter_code>import thefuzz
process.extract(UnmatchedLeft[0], UnmatchedRight,limit=3)<jupyter_output><empty_output><jupyter_text>These operations will not be done blindly. We will need to see what matches are done:<jupyter_code>UnmatchedLeft=allData[allData['True']=='left_only'].Country.to_list()
UnmatchedLeft
[(left, process.extractOne(left, UnmatchedRight)) for left in sorted(UnmatchedLeft)]<jupyter_output><empty_output><jupyter_text>These are the totally wrong matches:<jupyter_code># this is a list of tuples:
TotallyWrong=[('Czech Republic', ('Central African Republic (the)', 86)),
              ('Laos', ('Barbados', 68)),
              ('Republic of the Congo', ('Bahamas (the)', 86)),
              ('South Korea', ("Korea (the Democratic People's Republic of)", 86)),
              ('Sudan', ('South Sudan', 90)),
              ('United States', ('United States Minor Outlying Islands (the)', 90))]<jupyter_output><empty_output><jupyter_text>Let me make a list with all the names in the democracy index countries that must be omitted currently:<jupyter_code>omitLeft=[leftName for (leftName,rightFuzzy) in TotallyWrong] #parenthesis not needed
omitLeft<jupyter_output><empty_output><jupyter_text>Create a dictionary with the changes that will be done:<jupyter_code># process.extractOne(left, UnmatchedRight)[0] IS JUST THE COUNTRY NAME TO THE RIGHT
# [0] just took first element in tuple.

# left for left in UnmatchedLeft IS JUST EVERY COUNTRY NAME TO THE LEFT

# if left not in omitLeft IS A CONDITION

{process.extractOne(left, UnmatchedRight)[0]:left for left in UnmatchedLeft if left not in omitLeft}<jupyter_output><empty_output><jupyter_text>Let's save the dict above and use it to replace:<jupyter_code>changesRight={process.extractOne(left, UnmatchedRight)[0]:left for left in UnmatchedLeft if left not in omitLeft}
DataIso.Countryname.replace(changesRight,inplace=True)<jupyter_output><empty_output><jupyter_text>DataIso has new country names. Let's prepare manually the remaining changes:<jupyter_code>DataIso.Countryname.to_list()
# dict of manual changes
bruteForceChanges={'Korea (the Republic of)':'South Korea', 
                'United States of America (the)':'United States',
                'Czechia':'Czech Republic', 
                'Congo (the)':'Republic of the Congo',
                'Sudan (the)':'Sudan',
                "Lao People's Democratic Republic (the)":'Laos'}

# replacing
DataIso.Countryname.replace(bruteForceChanges,inplace=True)<jupyter_output><empty_output><jupyter_text>Let's redo the merging with full coincidences (option 1):<jupyter_code># redoing merge
allData=demoVars.merge(DataIso,left_on="Country",right_on="Countryname")

# current dimension
allData.shape<jupyter_output><empty_output><jupyter_text>Here, we could drop the columns that are not needed:<jupyter_code>allData.drop(["Countryname"],axis=1,inplace=True)
allData<jupyter_output><empty_output><jupyter_text>Let's save this merged data frame in CSV format:<jupyter_code>allData.to_csv('allData.csv',index=False)<jupyter_output><empty_output><jupyter_text>Exercise 2:- Go to this link: [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population](https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population).- Scrap the table of countries, and keep country names, and region.- Make sure the country names match exactly the ones from the table of democracy index.- Merge this table with the allData data frame (just add the region)- Prepare a CSV file with the clean, formatted and merged data, and upload that file into GitHub.<jupyter_code>#link for the tables
ex2Link="https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population"

# fetching the tables
ex2data=pd.read_html(ex2Link,header=0,flavor="bs4",attrs={'class':"wikitable sortable"})
ex2vars = ex2data[0].copy()
ex2vars.head()
#column positions to drop
ex2WhichToDrop = [0,3,4,5,6,7]

#Dropping unnecessary columns and updating df
ex2vars.drop(labels=ex2vars.columns[ex2WhichToDrop], axis=1, inplace=True)
ex2vars.columns
#Cleaning Column Names
import re
#re.sub("\s", "", ex2vars.columns[0])

ex2vars.columns=ex2vars.columns.str.replace("\s|/","",regex=True)
ex2vars.columns
#cleaning data contents
ex2vars['Region'].value_counts()

#should we drop world?
removeVals= ex2vars['Region'].value_counts().index[5]
removeVals

removeValsList=[removeVals]
removeValsList
# use "~" to request the "opposite" rows
ex2vars = ex2vars[~ex2vars['Region'].isin(removeValsList)]

# data frame after
ex2vars
ex2vars.reset_index(drop=True,inplace=True)

ex2vars.shape
ex2vars.to_csv("ex2vars.csv", index=False)<jupyter_output><empty_output><jupyter_text>Now to Merge with AllData Frame  RESHAPING Wide and Long formatLet's open **allDataFull**, whose structure has the **WIDE** format. In this format, the variables are in every column, the most traditional one for spreadsheet users.<jupyter_code>allDataFull=pd.read_csv('https://github.com/EvansDataScience/CTforGA_integrating/raw/main/allDataFull.csv')
allDataFull<jupyter_output><empty_output><jupyter_text>This is the final dataframe, let's check the data types, in case they need formatting:<jupyter_code>allDataFull.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 167 entries, 0 to 166
Data columns (total 12 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Country                       167 non-null    object 
 1   Regimetype                    167 non-null    object 
 2   Overallscore                  167 non-null    float64
 3   Electoralprocessandpluralism  167 non-null    float64
 4   Functioningofgovernment       167 non-null    float64
 5   Politicalparticipation        167 non-null    float64
 6   Politicalculture              167 non-null    float64
 7   Civilliberties                167 non-null    float64
 8   InternetccTLD                 167 non-null    object 
 9   iso2                          166 non-null    object 
 10  iso3                          167 non-null    object 
 11  Region                        167 non-null    object 
dtypes: float64(6), object(6)
memory usage: 15.8+ KB<jupyter_text>We should turn the texts of the regime type column into ordinal levels:<jupyter_code>from pandas.api.types import CategoricalDtype

# create data type - notice ascending order of levels
levelsDemo = CategoricalDtype(categories=['Authoritarian', 'Hybrid regime', 'Flawed democracy', 'Full democracy'], 
                              ordered=True)

# make the change:
allDataFull.Regimetype=allDataFull.Regimetype.astype(levelsDemo)

# see result:
allDataFull.Regimetype<jupyter_output><empty_output><jupyter_text>Once you have the right data types, you can request stats:<jupyter_code>allDataFull.describe(include='all')
# try? 
# allDataFull.describe()<jupyter_output><empty_output><jupyter_text>Let's see turn it into a **LONG** format:<jupyter_code>allDataFull_LONG=allDataFull.melt(id_vars=['Country','Region',"Regimetype",'iso3','iso2','InternetccTLD'])
allDataFull_LONG<jupyter_output><empty_output><jupyter_text>The amount of of rows multiplies, but **some** codes are easier to implement with long format (or can only be implemented in that format), like plotting:<jupyter_code>import plotnine as p9


base=p9.ggplot(data=allDataFull_LONG)
base + p9.geom_boxplot(p9.aes(x='variable',y='value')) + p9.coord_flip()<jupyter_output><empty_output><jupyter_text>Using the original format (wide), you could only ploy one bar using the previous *grammar*:<jupyter_code>base=p9.ggplot(data=allDataFull)
base + p9.geom_boxplot(p9.aes(x=1,y='Overallscore')) + p9.coord_flip()<jupyter_output><empty_output><jupyter_text>Nonetheless, there are packages in Python that use the wide format for plotting (i.e. _seaborn_):<jupyter_code>import seaborn as sns
  
whatVars= ['Overallscore', 'Electoralprocessandpluralism',
           'Functioningofgovernment', 'Politicalparticipation', 'Politicalculture',
           'Civilliberties']
sns.boxplot(data=allDataFull[whatVars], 
                 orient="h",color='white')<jupyter_output><empty_output><jupyter_text>AggregatingSometimes you need to aggregate your original data frame, that is, aggregate using a particular function some columns by a particular group . For example, if we wanted the average of the overall score of democracy by region, and get the result as a data frame:<jupyter_code>allDataFull.groupby("Region")[['Overallscore']].agg('mean').reset_index()<jupyter_output><empty_output><jupyter_text>What if we wanted the average of the overall score of democracy by region and by regime type, and get the result as a data frame:<jupyter_code>allDataFull.groupby(["Region","Regimetype"])[['Overallscore']].agg('mean').reset_index()<jupyter_output><empty_output><jupyter_text>Notice the if you do not use _reset_index()_, you will not get the groups as columns, but as row indexes:<jupyter_code>allDataFull.groupby(["Region","Regimetype"])[['Overallscore']].agg('mean')<jupyter_output><empty_output><jupyter_text>You can also request more aggregative functions:<jupyter_code>allDataFull.groupby(["Region","Regimetype"])[['Overallscore']].agg(['min','max']).reset_index()<jupyter_output><empty_output><jupyter_text>And of course, more variables to aggregate:<jupyter_code>severalVars=['Electoralprocessandpluralism','Functioningofgovernment', 'Politicalparticipation']
allDataFull.groupby(["Region","Regimetype"])[severalVars].agg(['min','max']).reset_index()<jupyter_output><empty_output><jupyter_text>The previous column names look more complex:<jupyter_code>multi=allDataFull.groupby(["Region","Regimetype"])[severalVars].agg(['min','max']).reset_index()
multi.columns<jupyter_output><empty_output><jupyter_text>The aggregation process can produce **multiIndex**. Most of the time you want to flatten the multiIndex. We could use some basic string operations for that:<jupyter_code># concatenating:
[(col[0]+'_'+col[1]) for col in multi.columns ]
# concatenating if secind element is not '':
[(col[0]+'_'+col[1]) if col[1]!='' else col[0] for col in multi.columns ]
# same as before, but using 'join' function: 
['_'.join(col).strip() if col[1]!='' else col[0] for col in multi.columns ]<jupyter_output><empty_output><jupyter_text>I will save the new column names:<jupyter_code>multi.columns= ['_'.join(col).strip() if col[1]!='' else col[0] for col in multi.columns ]<jupyter_output><empty_output><jupyter_text>This is the current look:<jupyter_code>multi<jupyter_output><empty_output><jupyter_text>The current result can also be used when melting to a long format:<jupyter_code>multi.melt(id_vars=['Region','Regimetype'])<jupyter_output><empty_output><jupyter_text>____ SAVING FILEThe current _allDataFull_ data frame is clean and formatted. We have used CSV files so far:<jupyter_code>allDataFull.to_csv("allDataFull_OK.csv",index=False)<jupyter_output><empty_output><jupyter_text>When we read back the file you may notice something wrong:<jupyter_code>allDataFull_OK=pd.read_csv("allDataFull_OK.csv")
allDataFull_OK.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 167 entries, 0 to 166
Data columns (total 12 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Country                       167 non-null    object 
 1   Regimetype                    167 non-null    object 
 2   Overallscore                  167 non-null    float64
 3   Electoralprocessandpluralism  167 non-null    float64
 4   Functioningofgovernment       167 non-null    float64
 5   Politicalparticipation        167 non-null    float64
 6   Politicalculture              167 non-null    float64
 7   Civilliberties                167 non-null    float64
 8   InternetccTLD                 167 non-null    object 
 9   iso2                          166 non-null    object 
 10  iso3                          167 non-null    object 
 11  Region                        167 non-null    object 
dtypes: float64(6), object(6)
memory usage: 15.8+ KB<jupyter_text>We need a file that avoids we have to reformat the columns again and again. Let's see the choices:  For future use in Python:<jupyter_code>allDataFull.to_pickle("allDataFull_OK.pkl")
# you will need: DF=pd.read_pickle("allDataFull.pkl")
# or:
# from urllib.request import urlopen
# DF=pd.read_pickle(urlopen("https://..../allDataFull.pkl"),compression=None)
allDataFull_OK=pd.read_pickle("allDataFull_OK.pkl")
allDataFull_OK.info()
allDataFull_OK.Regimetype<jupyter_output><empty_output><jupyter_text>For future  use in R:<jupyter_code>from rpy2.robjects import pandas2ri
pandas2ri.activate()

from rpy2.robjects.packages import importr

base = importr('base')
base.saveRDS(allDataFull,file="allDataFull_OK.RDS")

#In R, you call it with: DF = readRDS("allDataFull.RDS")
#or, if iyou read from cloud: DF = readRDS(url("https://..../allDataFull.RDS")<jupyter_output>/Users/JoseManuel/anaconda3/envs/Evans_GovAnalytics/lib/python3.7/site-packages/rpy2/robjects/vectors.py:980: UserWarning: R object inheriting from "POSIXct" but without attribute "tzone".
StarcoderdataJupyterStructuredCleanDedup