import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from IPython.display import Markdown


dfg = pd.read_csv('dfg.csv')
df = pd.read_csv('df.csv')

cols = ['country','population','population_2015','totalproviders','totalproviders2015','totalproviders_cap','totalproviders2015_cap','totalproviders_cap_diff','totalprovidersdiff','certified_specialist','provider_number','totalpap','physicians2015','totalpap_cap','physicians2015_cap', 'totalnpap','nurses2015','totalnpap_cap','nurses2015_cap','popdiff']
printinfocols = ['country','name','email','role','certified_specialist','provider_number','nurse_num','nurse_num2','nurse_num3','nurse_num4','other_1']

def countryinvestigation(country):
    df = dfg[dfg['country']==country][cols]
    sns.set_theme(style="whitegrid")
    fig, ax = plt.subplots(3,3,figsize=(10,10))
    
    # Paps
    pap = df[['physicians2015', 'certified_specialist','provider_number','totalpap']].melt()
    sns.barplot(x='variable',y='value',data=pap,ax=ax[0,0])
    ax[0,0].bar_label(ax[0,0].containers[0])
    #set xtick rotation
    ax[0,0].tick_params(axis='x', rotation=30)
    
    # Npaps
    npap = df[['nurses2015', 'totalnpap']].melt()
    sns.barplot(x='variable',y='value',data=npap,ax=ax[0,1])
    ax[0,1].bar_label(ax[0,1].containers[0])
    
    # Total Providers
    providers = df[['totalproviders2015', 'totalproviders']].melt()
    sns.barplot(x='variable',y='value',data=providers,ax=ax[0,2])
    ax[0,2].bar_label(ax[0,2].containers[0])
    
    # Pap Cap
    papcap = df[['physicians2015_cap', 'totalpap_cap']].melt()
    sns.barplot(x='variable',y='value',data=papcap,ax=ax[1,0])
    ax[1,0].bar_label(ax[1,0].containers[0])
    
    # Npap Cap
    npapcap = df[['nurses2015_cap', 'totalnpap_cap']].melt()
    sns.barplot(x='variable',y='value',data=npapcap,ax=ax[1,1])
    ax[1,1].bar_label(ax[1,1].containers[0])
    
    # Total Providers Cap
    providerscap = df[['totalproviders2015_cap', 'totalproviders_cap']].melt()
    sns.barplot(x='variable',y='value',data=providerscap,ax=ax[1,2])
    ax[1,2].bar_label(ax[1,2].containers[0])
    
    # Population
    population = df[['population_2015', 'population']].melt()
    sns.barplot(x='variable',y='value',data=population,ax=ax[2,0])
    ax[2,0].bar_label(ax[2,0].containers[0])
    
    #set xlabel and ylabel to ""
    for ax in fig.axes:
        ax.set_xlabel('')
        ax.set_ylabel('')

    # Adjust subplot layout
    plt.tight_layout()
    plt.show()
    plt.close()

def print_country_info(country):
    t1 = df[df['country']==country][printinfocols].set_index('name').T
    return t1

df=df.sort_values(by=['country','Region'])
dff = df[df['Region']=='Region of the Americas']
display(Markdown('# Region of the Americas'))
for i in dff['country'].unique():
    display(Markdown(f'## {i}'))
    display(print_country_info(i))
    countryinvestigation(i)

Region of the Americas

Argentina

name	FACUNDO JOSE BALDERRAMA	FACUNDO JOSE BALDERRAMA
country	Argentina	Argentina
email	faaaar@anestesia.org.ar	faaaar@anestesia.org.ar
role	Member and part of leadership	miembro y parte del liderazgo
certified_specialist	4900.0	4900.0
provider_number	NaN	NaN
nurse_num	NaN	NaN
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Belize

name	RUBEN PINEDA
country	Belize
email	sevorane67@hotmail.com
role	Member and part of leadership
certified_specialist	20.0
provider_number	NaN
nurse_num	19.0
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Bolivia

name	Elizabeth Justiniano Zarate
country	Bolivia
email	elijustiniano@hotmail.com
role	miembro y parte del liderazgo
certified_specialist	450.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Brazil

name	Luiz Fernando dos Reis Falcão
country	Brazil
email	international@sbahq.org
role	Member and part of leadership
certified_specialist	25484.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Canada

name	Amanda Cormier
country	Canada
email	acormier@cas.ca
role	Member and part of leadership
certified_specialist	3678.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Chile

name	Veronica Varas Vega	Veronica Varas
country	Chile	Chile
email	vvaras@sachile.cl	vvaras@sachile.cl
role	miembro y parte del liderazgo	miembro y parte del liderazgo
certified_specialist	2131.0	2131.0
provider_number	NaN	NaN
nurse_num	NaN	NaN
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Colombia

name	Nidia Zabala Cordoba	LUZ MARIA GOMEZ BUITRAGO	Nidia Zabala Cordoba
country	Colombia	Colombia	Colombia
email	asesoriagremial@scare.org.co	asesoriagremial@scare.org.co	asesoriagremial@scare.org.co
role	Other	Member and part of leadership	otro
certified_specialist	4148.0	4148.0	4148.0
provider_number	NaN	NaN	NaN
nurse_num	NaN	NaN	NaN
nurse_num2	NaN	NaN	NaN
nurse_num3	NaN	NaN	NaN
nurse_num4	NaN	NaN	NaN
other_1	NaN	NaN	NaN

Costa Rica

name	Ana Eugenia Herrera Mora
country	Costa Rica
email	presidencia@anestesiologos.cr
role	miembro y parte del liderazgo
certified_specialist	353.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Cuba

name	Dr. Antonio de Jesús Cabrera Prats	Dr. Antonio de Jesús Cabrera Prats
country	Cuba	Cuba
email	cabrerap@infomed.sld.cu	cabrerap@infomed.sld.cu
role	Member and part of leadership	miembro y parte del liderazgo
certified_specialist	1452.0	1452.0
provider_number	NaN	NaN
nurse_num	NaN	NaN
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Dominican Republic

name	Josue jimenez
country	Dominican Republic
email	Drjcaminero@hotmail.com
role	miembro y parte del liderazgo
certified_specialist	1167.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Ecuador

name	Hamilton Lama	Hamilton Lama
country	Ecuador	Ecuador
email	jotyin77@hotmail.com	jotyin77@hotmail.com
role	Member and part of leadership	miembro y parte del liderazgo
certified_specialist	500.0	500.0
provider_number	NaN	NaN
nurse_num	NaN	NaN
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

El Salvador

name	Susana Abrego
country	El Salvador
email	susanabrego2001@yahoo.com.mx
role	miembro y parte del liderazgo
certified_specialist	143.0
provider_number	NaN
nurse_num	750.0
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Guatemala

name	Maria Mercedes Garcia Robles
country	Guatemala
email	mechita_0511@hotmail.com
role	miembro y parte del liderazgo
certified_specialist	554.0
provider_number	0.0
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Guyana

name	Dr Onica Higgins Gill
country	Guyana
email	Onicahiggins@gmail.com
role	Member and part of leadership
certified_specialist	16.0
provider_number	NaN
nurse_num	25.0
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Honduras

name	Cindy Regina Montoya Sierra
country	Honduras
email	shardjdn@gmail.com
role	miembro y parte del liderazgo
certified_specialist	200.0
provider_number	NaN
nurse_num	90.0
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Mexico

name	Cecilia Sandoval Larios
country	Mexico
email	secretario@fmcaac.mx
role	miembro y parte del liderazgo
certified_specialist	9665.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Nicaragua

name	Roger Pasquier Hernández
country	Nicaragua
email	raph1962@gmail.com
role	miembro y parte del liderazgo
certified_specialist	250.0
provider_number	NaN
nurse_num	300.0
nurse_num2	150.0
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Panama

name	Leandro Gonzalez
country	Panama
email	leandroadal05@gmail.com
role	miembro y parte del liderazgo
certified_specialist	400.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Paraguay

name	Dario Zarate	DARÍO ZARATE
country	Paraguay	Paraguay
email	dj_zarate1982@hotmail.com	dj_zarate1982@hotmail.com
role	miembro y parte del liderazgo	miembro y parte del liderazgo
certified_specialist	310.0	310.0
provider_number	NaN	NaN
nurse_num	NaN	110.0
nurse_num2	NaN	50.0
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Peru

name	Freddy Espinoza	Freddy Espinoza
country	Peru	Peru
email	fespinozam@hotmail.com	fespinozam@hotmail.com
role	miembro y parte del liderazgo	miembro y parte del liderazgo
certified_specialist	2850.0	2850.0
provider_number	NaN	0.0
nurse_num	NaN	NaN
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Trinidad and Tobago

name	Dr Deryk Chen
country	Trinidad and Tobago
email	uwichen@yahoo.com
role	Member and part of leadership
certified_specialist	85.0
provider_number	92.0
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

United States of America

name	Thomas Miller	Jackie Rowles
country	United States of America	United States of America
email	t.miller@asahq.org	jackierowles1@gmail.com
role	Member and part of leadership	Member and part of leadership
certified_specialist	53250.0	NaN
provider_number	NaN	NaN
nurse_num	NaN	56172.0
nurse_num2	NaN	NaN
nurse_num3	NaN	NaN
nurse_num4	NaN	NaN
other_1	NaN	NaN

Uruguay

name	Ana Schwartzmann
country	Uruguay
email	anaschbruno@gmail.com
role	miembro y parte del liderazgo
certified_specialist	422.0
provider_number	NaN
nurse_num	0.0
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN

Venezuela

name	Maria Teresa Prado
country	Venezuela
email	informacion.sva@gmail.com
role	miembro y parte del liderazgo
certified_specialist	800.0
provider_number	NaN
nurse_num	NaN
nurse_num2	NaN
nurse_num3	NaN
nurse_num4	NaN
other_1	NaN