Competition - XP Competition 2022

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import re

# Read the data
broadband = pd.read_csv('data/broadband.csv')
internet = pd.read_csv('data/internet.csv')
people = pd.read_csv('data/people.csv')

#Merging internet and people tables, keeping only country rows
countries=internet[internet['Code'].notnull()].merge(people,on=['Entity','Code','Year'],how='left').reset_index(drop=True)
countries=countries.rename(columns={'Entity':'Country'})

#Sorting values by Internet Usage
topcountries_2019usage=countries.query('Year==2019').sort_values('Internet_Usage',ascending=False)
topcountries_2019usage.iloc[0:5].reset_index(drop=True)

#Indexes of top 5 countries highest Internet Usage by Year
top5year=[i[1] for i in countries.groupby('Year')['Internet_Usage'].nlargest(5).keys()]

#Using previous indexes to subset countries and adding ranks
countries_top5usage=countries.iloc[top5year].reset_index(drop=True)
countries_top5usage['rank']=[1,2,3,4,5]*(int(len(countries_top5usage)/5))

#Parameters for point plot
start=2015
end=2020
countries_top5usage_sub=countries_top5usage.query(f'Year>={start} and Year<{end}').reset_index(drop=True)
countries_top5usage_sub['Country(Code)']=countries_top5usage_sub['Country']+' ('+countries_top5usage_sub['Code']+')'

#Point plot inverting ylabels and adding data labels
g3=sns.catplot(x='Year',y='rank',hue='Country(Code)',data=countries_top5usage_sub,kind='point',legend=True)
for ax in g3.axes.ravel():
    ax.invert_yaxis()
for idx,row in countries_top5usage_sub.iterrows():
    x = row[2]%start
    y = row[5]
    text = row[1]
    plt.text(x+.05,y-.05,text)
    
plt.yticks([1,2,3,4,5])
plt.show()
            

#Parameters for point plot
start=2010
end=2015
countries_top5usage_sub=countries_top5usage.query(f'Year>={start} and Year<{end}').reset_index(drop=True)
countries_top5usage_sub['Country(Code)']=countries_top5usage_sub['Country']+' ('+countries_top5usage_sub['Code']+')'

#Point plot inverting ylabels and adding data labels
g2=sns.catplot(x='Year',
               y='rank',
               hue='Country(Code)', 
               data=countries_top5usage_sub,
               kind='point',
               legend=True
              )
for ax in g2.axes.ravel():
    ax.invert_yaxis()
for idx,row in countries_top5usage_sub.iterrows():
    x = row[2]%start
    y = row[5]
    text = row[1]
    plt.text(x+.05,y-.05,text)
    
plt.yticks([1,2,3,4,5])
plt.show()

#Reading scraped data
CountryRegion=pd.read_csv('data/CountryRegion.csv')
EUcountries=['Austria', 'Belgium', 'Bulgaria', 'Croatia', 'Cyprus', 'Czech Republic', 'Denmark', 'Estonia', 'Finland', 'France', 'Germany', 'Greece', 'Hungary', 'Ireland', 'Italy', 'Latvia', 'Lithuania', 'Luxembourg', 'Malta', 'Netherlands', 'Poland', 'Portugal', 'Romania', 'Slovak Republic', 'Slovenia', 'Spain','Sweden','United Kingdom']

AEScountries=[]
with open('data/AfricaEasternSouthern.txt') as f:
    for line in f:
        country=re.findall('<a.*?>(.+?)</a>',line)
        if len(country)>0:
            AEScountries.append(country[0])
AEScountries=[s.replace('Democratic Republic of Congo','Congo, Dem. Rep.') for s in AEScountries]
AEScountries=[s.replace('Sao Tome &amp; Principe','São Tomé and Principe') for s in AEScountries]

AWCcountries=[]
with open('data/AfricaWesternCentral.txt') as f:
    for line in f:
        country=re.findall('<a.*?>(.+?)</a>',line)
        if len(country)>0:
            AWCcountries.append(country[0])
AWCcountries=[s.replace('Republic of Congo','Congo, Rep.') for s in AWCcountries]
AWCcountries=[s.replace("Cote d'Ivoire","Côte d'Ivoire") for s in AWCcountries]
AWCcountries=[s.replace("Gambia","Gambia, The") for s in AWCcountries]

APGcountries=['Bahrain', 'Kuwait', 'Iraq', 'Oman', 'Qatar', 'Saudi Arabia','United Arab Emirates']

#Assigning Region to countries in CountryRegion
CountryRegion=CountryRegion[['Country Code','Table Name','Region']]
CountryRegion=CountryRegion.rename(columns={'Country Code': "Code"})
CountryRegion.loc[CountryRegion['Table Name'].isin(EUcountries),'Region']='European Union'
CountryRegion.loc[CountryRegion['Table Name'].isin(AWCcountries),'Region']='Africa Western and Central'
CountryRegion.loc[CountryRegion['Table Name'].isin(AEScountries),'Region']='Africa Eastern and Southern'
CountryRegion.loc[CountryRegion['Table Name'].isin(APGcountries),'Region']='Arab Persian Gulf'

selectedRegions=['Africa Eastern and Southern', 'Africa Western and Central', 'Latin America & Caribbean', 'East Asia & Pacific', 'South Asia', 'North America', 'European Union']

#Merging countries and CountryRegion
countries=countries.merge(CountryRegion[['Code','Region']],on='Code',how='left')[['Country','Code','Year','Internet_Usage','Users','Region']]

#Calculating Internet Usage for created Region: Arab Persian Gulf
countriesAPG=countries[countries['Region']=='Arab Persian Gulf']
countriesAPG_grouped=pd.DataFrame(countriesAPG.groupby(['Year','Region'])['Users'].sum()*100/countriesAPG.groupby(['Year','Region']).apply(lambda x: (x['Users']*100/x['Internet_Usage']).sum())).reset_index()

#Joining countries and created region
countriesAPG_grouped.columns=['Year','Entity','Internet_Usage']
selectedRegions_pd=pd.concat([internet.loc[internet['Entity'].isin(selectedRegions)],countriesAPG_grouped]).reset_index(drop=True)

#Plotting Internet Usage by Region by Year
sns.color_palette("tab10")
sns.relplot(x='Year',
            y='Internet_Usage',
            data=selectedRegions_pd,
            kind='line',
            style='Entity',
            hue='Entity',
            markers=True,
            dashes=False
            )
plt.show()