<jupyter_start><jupyter_text>Sufficiency  Imports<jupyter_code>import pandas as pd
import numpy as np
import random

import xgboost as xgb

import matplotlib.pyplot as plt
from matplotlib import cm, colors
from matplotlib import patches as mpatches
from mpl_toolkits.basemap import Basemap

import seaborn as sns

import os, sys

import sklearn
from sklearn.utils import shuffle
from sklearn.preprocessing import OneHotEncoder
from sklearn.model_selection import train_test_split, GridSearchCV
from sklearn.metrics import r2_score, mean_squared_error

import pickle

import xlsxwriter<jupyter_output><empty_output><jupyter_text>Set constants<jupyter_code># Value to change to get correct paths
data_path = '../data/'
inputs_path = data_path + 'inputs/'
intermediate_path = data_path + 'intermediate/'
output_path = data_path + 'outputs/with_irrig/'

base_path = inputs_path + 'Base/'
diet_path = inputs_path + 'diet/'
pop_path = inputs_path + 'population/'

data_2050_path = intermediate_path + 'Future_dfs/All_change+irrig/'

data_2050_gdp_cont_path = data_2050_path + 'gdp_cont/original/'

compare_path = output_path + 'compare/'
suff_path = output_path + 'sufficiency/'
files_2050 = os.listdir(data_2050_gdp_cont_path)
files_2050 = [filename for filename in files_2050 if 'ssp' in filename]

climate_models, climate_scenarios = [], []

for file in files_2050:
    if len(file) == 11:
        climate_models.append(file[:2])
        climate_scenarios.append(file[3:7])

climate_models = list(set(climate_models))
climate_models.sort()
climate_scenarios = list(set(climate_scenarios))
climate_scenarios.sort()
climate_models, climate_scenarios<jupyter_output><empty_output><jupyter_text>Country sufficiency  Compute<jupyter_code>caloric_req = 2320*1e3*365.25
# Population 
def get_total_pop(ssp):
    '''ssp: 2000 or ssp'''
    pop_dir = inputs_path + 'population/'
    pop = pd.read_csv(os.path.join(pop_dir, 'pop_' + ssp + '.csv'))
    pop = pop.set_index('pixel_id')
    pop_tot = pop['gpw_population'].sum()
    return pop_tot
def get_total_prod(ssp):
    '''ssp: 2000 or ssp'''
    results_dir = compare_path
    if ssp == '2000':
        rez = pd.read_csv(os.path.join(results_dir, 'ssp1_compare_new.csv')) 
        return rez['calories_2000'].sum()
    else:
        rez = pd.read_csv(os.path.join(results_dir, ssp + '_compare_new.csv'))
        rez = rez.set_index('pixel_id')
        return rez['calories_2050'].sum()
def calc_CalSuff(prod_available, LS, pop,
            caloric_req = (2320*1e3*365.25) #in cal/capita/yr
           ):
    ''' prod_available (for Food not Feed + accounting for losses) - in Calories
        LS : animal calories availables
        pop : in capita
        caloric_req : default values is ADER=2320 kcal/cap/d (without fish)
        
        This is all per year
    '''
    return (prod_available + LS) / (caloric_req * pop)<jupyter_output><empty_output><jupyter_text>2000<jupyter_code># Percetenge live stock in 2000
pctLS_2000 = .19
# Live stock value in 2000
LS_2000 = 1.1483e15*1e3 #calories
# Precentage of production used as food
pct_Prod_Food_2000 = .52

pop_2000 = get_total_pop('2000')
prod_2000 = get_total_prod('2000') # calories
LS_2000
# Quantity of food available in 2000 as food
prod_available_2000 = prod_2000*pct_Prod_Food_2000

# 2000 caloric sufficiency
global_CalSuff_2000 = calc_CalSuff(prod_available_2000,
                       LS_2000,
                       pop_2000)

global_CalSuff_2000<jupyter_output><empty_output><jupyter_text>2050<jupyter_code>feed_conversion_factor = 2.3

# Data source: Bodirsky : share of calories in diet from animal products
pctLS_2050 = .19 #Assuming cst
def global_CalSuff_2050(ssp,
                        pctLS_2050 = .19):
    
    prod_2050 = get_total_prod(ssp)
    
    LS_2050 = pctLS_2050 * (prod_2050*pct_Prod_Food_2000 + feed_conversion_factor*LS_2000) / (1 + pctLS_2050*(feed_conversion_factor-1)) 

    feed_now_food = feed_conversion_factor * (LS_2000-LS_2050)
    
    prod_available = prod_2050*pct_Prod_Food_2000 + feed_now_food
    
    pop = get_total_pop(ssp)

    calsuff = calc_CalSuff(prod_available,
                           LS_2050,
                           pop)
    
    return calsuff
global_CalSuff_2000
for ssp in climate_scenarios:
    print(ssp, global_CalSuff_2050(ssp))
CalSuff_df = pd.DataFrame(columns=['ssp','global_CalSuff','pctLS'])

for ssp in climate_scenarios:
    for pctLS_2050 in [0,.05,.1,.11,.12,.13,.14,.15,.16,.17,.18,.19,.20,.21,.22,.23,.25,.30]:
        
        CalSuff = global_CalSuff_2050(ssp,
                                     pctLS_2050)

        CalSuff_df = CalSuff_df.append({'ssp':ssp,'global_CalSuff':CalSuff,'pctLS':pctLS_2050},ignore_index=True)

fig, ax = plt.subplots()

for ssp, grp in CalSuff_df.groupby(['ssp']):
    ax = grp.plot(ax=ax, kind='line', x='pctLS', y='global_CalSuff', label=ssp)

plt.axvline(x=.19,linestyle=':',color='grey')
plt.axhline(y=1,linestyle='--',color='grey')

# Baseline
plt.plot(.19, global_CalSuff_2000, 'yo') 

#Prettify
ax.spines['right'].set_visible(False)
ax.spines['top'].set_visible(False)

plt.ylabel('global Caloric Sufficiency');
plt.xlabel('Share of animal products in diet');
plt.legend(loc='best');<jupyter_output><empty_output><jupyter_text>By country 2000<jupyter_code># Country of each pixel
countries = pd.read_csv(base_path + 'country_ids.csv')
countries_names = pd.read_csv(base_path + 'country_names.csv',encoding='latin-1')
countries = countries[['country_id','pixel_id']].merge(countries_names[['base_id','ISO3','name', 'sovereignt']],right_on='base_id',left_on='country_id',how='right')
countries = countries.set_index('pixel_id')
countries.head()
# 2000 diet data FAOSTAT
diet_2000 = pd.read_csv(diet_path + 'Consumption_2000_FAOSTAT.csv')
# LS 2000 Data with countries
LS_2000_df = diet_2000[diet_2000['Item']=='Animal Products'][['Country','Value']].merge(countries[['name','ISO3']].drop_duplicates(),
                                                                                        right_on='name',left_on='Country',how='left')
# kcal/capita/day to cal/capita
LS_2000_df['LS2000_percapita'] = LS_2000_df['Value'].apply(lambda x:x*1e3*365.25)



LS_2000_df = LS_2000_df[['name','ISO3','LS2000_percapita']].set_index('ISO3')
# Population by country
pop_dir = pop_path
pop = pd.read_csv(os.path.join(pop_dir,'pop_2000.csv'))
pop = pop.merge(countries, right_on='pixel_id', left_on='pixel_id')
pop_bycountry_2000 = pop.groupby('ISO3')['gpw_population'].sum()
total_pop_2000 = pop['gpw_population'].sum()

# Production by country
prod = pd.read_csv(os.path.join(compare_path, 'ssp1'+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
prod_bycountry = prod.groupby('ISO3')['calories_2000'].sum()
# List of all countries
countries_list = pop_bycountry_2000[pop_bycountry_2000>0].index
len(countries_list)
LS_2000_df_country = pd.DataFrame(index=countries_list, columns=['LS_2000'])
for country in countries_list:
    try:
        LS_2000_df_country.loc[country, 'LS_2000'] = LS_2000_df.at[country,'LS2000_percapita'] * pop_bycountry_2000[country]
        
    except: 
        try: 
            # Get value from sovereignt country
            sovereignt_country = countries[countries['ISO3'] == country].drop_duplicates()['sovereignt']
            sovereignt_iso = countries[countries['sovereignt'] == sovereignt_country].drop_duplicates()['ISO3']
            print(country, sovereignt_iso)
            LS_2000_df_country.loc[country, 'LS_2000'] = LS_2000_df.at[sovereignt_c,'LS2000_percapita'] * pop_bycountry_2000[sovereignt_c]
            
            
        except: 
            LS_2000_df_country.loc[country, 'LS_2000'] = LS_2000 / total_pop_2000 * pop_bycountry_2000[country]
            
LS_2000_df_country.head()
# Compute sufficiencies of each country

sufficiencies = pd.DataFrame()

# sufficiency 2000
df = pd.concat([prod_bycountry, pop_bycountry_2000], axis=1)
df = df[df['calories_2000'] > 0]
suff_country = df.apply(lambda r: calc_CalSuff(r['calories_2000']*pct_Prod_Food_2000, LS_2000_df_country.at[r.name, 'LS_2000'], r['gpw_population']), axis=1).replace([np.inf, -np.inf], np.nan)

sufficiencies['2000'] = suff_country
sufficiencies.loc[df[df['calories_2000'] <= 0].index, '2000'] = np.nan

sufficiencies.head()
# The countries where we could retrieve the sufficiency
len(sufficiencies)<jupyter_output><empty_output><jupyter_text>By country 2050<jupyter_code>def calc_CalSuff_numerator_2050(LS_2000_c,
                      pct_LS_2050,
                      prod_2050):
    
    LS_2050 = pct_LS_2050 * (prod_2050 * pct_Prod_Food_2000 + feed_conversion_factor * LS_2000_c) / (1 + pct_LS_2050*(feed_conversion_factor-1)) 

    feed_now_food = feed_conversion_factor * (LS_2000_c - LS_2050)
    
    prod_available = prod_2050 * pct_Prod_Food_2000 + feed_now_food
    
    return (prod_available+LS_2050)
# Load diets data
diets = pd.read_csv(diet_path + 'pctLS_SSPs_Bodirsky.csv',sep=';')

for ssp in climate_scenarios:

    # Population by country
    pop_dir = pop_path
    pop = pd.read_csv(os.path.join(pop_dir, 'pop_' + ssp + '.csv'))
    pop = pop.merge(countries, right_on='pixel_id', left_on='pixel_id')
    pop_bycountry = pop.groupby('ISO3')['gpw_population'].sum()
    
    total_pop_2050 = pop['gpw_population'].sum()
        
    # Production by country
    prod = pd.read_csv(os.path.join(compare_path, ssp+'_compare_new.csv'), index_col='pixel_id')
    #prod = prod.merge(countries, right_on='pixel_id', left_on='pixel_id')
    prod_bycountry = prod.groupby('ISO3')['calories_2050'].sum()
    
    calsuff_numerator = 0
    suff_c = pd.Series()
    for country in countries_list:
        pr = 0
        suff_nan = False
        # Diet
        try:
            # Get from the data
            pct_LS_2050 = diets[(diets['Scenario'] == ssp) & (diets['Region'] == country)]['2050'].values[0]
        except IndexError:
            #print(countries[countries['ISO3'] == country].name.values[0])
            # Assume percentage constant as defined before if value not in data
            pct_LS_2050 = 0.19
        
        # Production
        prod_country = prod_bycountry[country]
        
        # Caloric sufficiency
        num = calc_CalSuff_numerator_2050(LS_2000_df_country.at[country, 'LS_2000'],
                                          pct_LS_2050,
                                          prod_2050=prod_country)
        
        calsuff_numerator += num
                
        # sufficiency of the country
        suff_c[country] = num/(caloric_req*pop_bycountry[country])
    
    sufficiencies[ssp] = suff_c

sufficiencies = sufficiencies.rename({'Unnamed: 0': 'ISO3'}, axis=1)
# Drop ESB, CNM
sufficiencies = sufficiencies[~sufficiencies.index.isin(['ESB', 'CNM'])]
#sufficiencies.replace(0, np.nan).to_csv(suff_path + 'country_sufficiencies_new.csv')
sufficiencies = pd.read_csv(suff_path + 'country_sufficiencies_new.csv')
sufficiencies.head()
def visualize_sufficiency(suff, scenario, fig, ax, title, shape=(2160, 4320), savefig=False):
    bins = [0, 50, 100, 150, 200, np.inf]
    indexes = suff.index
    # Create the bins
    suff = pd.cut(suff, bins=bins, labels=range(1, len(bins)))
    suff_array = np.zeros((shape[0]*shape[1],))
    suff_array[indexes] = suff.values
    suff_df = pd.DataFrame(suff_array, columns=['label'])
        
    c_list = ['white', 'red', 'orange', '#ADFF2F', '#32CD32', '#556B2F']
    cmap = colors.ListedColormap(c_list, name='my_colormap_name')
    
    legend = []
    legend.append(mpatches.Patch(color='red', label='Less than 50%'))
    legend.append(mpatches.Patch(color='orange', label='50 to 100%'))
    legend.append(mpatches.Patch(color='#ADFF2F', label='100 to 150%'))
    legend.append(mpatches.Patch(color='#32CD32', label='150 to 200%'))
    legend.append(mpatches.Patch(color='#556B2F', label='More than 200%'))
    
    #Plot data
    
    data = suff_df.values
    bm = Basemap()
    
    im = bm.imshow(np.flipud(data.reshape(shape)), cmap=cmap, ax=ax)
    
    bm.drawcoastlines(linewidth=0.15, color='0.1', ax=ax)
    bm.drawcountries(linewidth=0.1, color='0.1', ax=ax)
        
    ax.set_title(title, fontsize=20)
    plt.legend(handles=legend, loc=3)
# Plot 6 sufficiency maps on the same figure
fig = plt.figure(figsize=(20, 13))
fig.suptitle('Sufficiency 2000 vs 2050', fontsize=25)

for i, ssp in enumerate(climate_scenarios): 
    print(ssp)
    prod = pd.read_csv(os.path.join(compare_path, ssp+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
    suff_all = prod.join(sufficiencies.set_index('ISO3'), on='ISO3')
    
    if ssp == 'ssp1':
        title_2000 = '2000'
        ax = plt.subplot(321)
        suff_2000 = suff_all['2000']
        visualize_sufficiency(suff_2000*100, ssp, fig, ax, title_2000, savefig=False)

    ax = plt.subplot(321 + i + 1)
    title = ssp
    suff = suff_all[ssp]
    visualize_sufficiency(suff*100, ssp, fig, ax, title, savefig=False)
    
fig.tight_layout(rect=[0, 0, 1, 0.95])<jupyter_output>ssp1
ssp2
ssp3
ssp4
ssp5<jupyter_text>Aggregate sufficiencies<jupyter_code>sufficiencies = pd.read_csv(suff_path + 'country_sufficiencies_new.csv')
def visualize_sufficiency_all(suff, fig, ax, title, shape=(2160, 4320), savefig=False):
    suff_array = np.zeros((shape[0]*shape[1],)) - 1
    suff_array[suff.index] = suff['all_ssp'].values
    suff_df = pd.DataFrame(suff_array)
        
    c_list = ['red', 'orange', 'pink', '#0D98BA', 'green']
    cmap = colors.ListedColormap(c_list, name='my_colormap_name')
    cmap.set_under('w')
    
    legend = []
    legend.append(mpatches.Patch(color='red', label='Down in all'))
    legend.append(mpatches.Patch(color='orange', label='Down in most'))
    legend.append(mpatches.Patch(color='pink', label='Variable'))
    legend.append(mpatches.Patch(color='#0D98BA', label='Up in most'))
    legend.append(mpatches.Patch(color='green', label='Up in all'))
    
    #Plot data
    
    data = suff_df.values
    bm = Basemap()
    
    im = bm.imshow(np.flipud(data.reshape(shape)), vmin=0, cmap=cmap, ax=ax)
    
    bm.drawcoastlines(linewidth=0.15, color='0.1', ax=ax)
    bm.drawcountries(linewidth=0.1, color='0.1', ax=ax)
        
    ax.set_title(title, fontsize=20)
    plt.legend(handles=legend, loc=3)
# Cases: 
#-1: no info

#0: down in all scenario (Red)
#1: down in most scenario (Orange)

# in 2/3 scenarios
#2: variable (Pink)

# in 4 scenarios
#3: up in most scenario (Blue-Green)
#4: up in all scenario (Green)

fig, ax = plt.subplots(figsize=(12, 8))

sufficiencies['all_ssp'] = -1
for i, country in sufficiencies.iterrows():
    state = -1
    val_2000 = country['2000']   
    down = 0
    up = 0
    num_nan = 0
    for ssp in climate_scenarios:
        if (country[ssp] == np.nan) or (val_2000 == np.nan):
            num_nan += 1
        # not significant if not change of 0.1 unit of sufficiency
        # 2050 pred less than 2000 sufficiency
        elif country[ssp] - val_2000 < -0.1:
            down += 1
        # 2050 pred more than 2000 sufficinecy
        elif country[ssp] - val_2000 >= 0.1:
            up += 1
    
    # Compare diff values
    # Case 
    if up == 5 - num_nan: state = 4     #4: up all scenarios
    elif down == 5 - num_nan: state = 0    #0: down all scenarios
    elif up == 4 - num_nan: state = 3     #3: up most
    elif down == 4 - num_nan: state = 1    #1: down most
    else: state = 2     #2: variable (Pink)
    
    sufficiencies.at[i, 'all_ssp'] = state
            

prod = pd.read_csv(os.path.join(compare_path, 'ssp1'+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
suff_all = prod.join(sufficiencies.set_index('ISO3'), on='ISO3')

title = 'Sufficiencies in 2000 vs 2050 over all scenario'
visualize_sufficiency_all(suff_all, fig, ax, title, savefig=True)
def visualize_sufficiency_below(suff, fig, ax, title, shape=(2160, 4320), savefig=False):
    suff_array = np.zeros((shape[0]*shape[1],)) - 1
    suff_array[suff.index] = suff['get_below'].values
    suff_df = pd.DataFrame(suff_array)
        
    c_list = ['red', 'orange', 'purple', 'violet', 'beige']
    cmap = colors.ListedColormap(c_list, name='my_colormap_name')
    cmap.set_under('w')
    
    legend = []
    legend.append(mpatches.Patch(color='red', label='Gets below 1 in all'))
    legend.append(mpatches.Patch(color='orange', label='Gets below 1 in most'))
    legend.append(mpatches.Patch(color='purple', label='Was and is still below 1 in all'))
    legend.append(mpatches.Patch(color='violet', label='Was and is still below 1 in most'))
    
    
    #Plot data
    
    data = suff_df.values
    bm = Basemap()
    
    im = bm.imshow(np.flipud(data.reshape(shape)), vmin=0, cmap=cmap, ax=ax)
    
    bm.drawcoastlines(linewidth=0.15, color='0.1', ax=ax)
    bm.drawcountries(linewidth=0.1, color='0.1', ax=ax)
        
    ax.set_title(title, fontsize=20)
    plt.legend(handles=legend, loc=3)
# Cases: 
#0: all get bellow 1 
#1: most get bellow 1

#2: was belown 1 and still in all
#3: was belown 1 and still in most

#4: other

fig, ax = plt.subplots(figsize=(12, 8))

sufficiencies['get_below'] = 4
for i, country in sufficiencies.iterrows():
    state = 6
    # If country is sufficient in 2000
    if country['2000'] >= 1: 
        start = 1
    else: 
        start = 0        
    diff = 0
    for ssp in climate_scenarios:
        # sufficient in 2000 but not in 2050
        if start == 1 and country[ssp] < 1:
            diff += 1
        # not sufficient in 2000 but sufficient in 2050
        elif start == 0 and country[ssp] > 1:
            diff += 1
    
    # Compare diff values
    #0: all get bellow 1
    if start == 1 and diff == 5: 
        state = 0
    #1: most get bellow 1    
    elif start == 1 and diff == 4:
        state = 1
    #2: was belown 1 and still in all    
    elif start == 0 and diff == 0:
        state = 2
    #3: was belown 1 and still in most
    elif start == 0 and diff == 1:
        state = 3
              
    
    sufficiencies.at[i, 'get_below'] = state
            

prod = pd.read_csv(os.path.join(compare_path, 'ssp1'+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
suff_all = prod.join(sufficiencies.set_index('ISO3'), on='ISO3')

title = 'Countries where the sufficiency gets below 1'
visualize_sufficiency_below(suff_all, fig, ax, title, savefig=True)
# Useful to do the QGIS plot
sufficiencies['plot'] = -1
for i, row in sufficiencies.iterrows():
    sufficiencies.loc[i, 'plot'] = str(row['all_ssp']) + str(row['get_below'])
sufficiencies.head(20)
sufficiencies.to_csv(suff_path + 'suff_map_new.csv')<jupyter_output><empty_output><jupyter_text>Infos countries<jupyter_code>sufficiencies = pd.read_csv(suff_path + 'country_sufficiencies_new.csv')
sufficiencies.head()
print(2000, len(sufficiencies[sufficiencies['2000']<1]))
for ssp in climate_scenarios:
    print(ssp, len(sufficiencies[sufficiencies[ssp]<1]))
countries_names = pd.read_csv(base_path + 'country_names.csv',encoding='latin-1')
len(countries_names)
countries = pd.read_csv(base_path + 'country_ids.csv')
countries_names = pd.read_csv(base_path + 'country_names.csv',encoding='latin-1')
countries = countries[['country_id','pixel_id']].merge(countries_names[['base_id','ISO3','name', 'sovereignt']],right_on='base_id',left_on='country_id',how='right')

countries.head()
countries = pd.read_csv(base_path + 'country_ids.csv')
countries_names = pd.read_csv(base_path + 'country_names.csv',encoding='latin-1')
countries = countries[['country_id','pixel_id']].merge(countries_names[['base_id','ISO3','name', 'sovereignt']],right_on='base_id',left_on='country_id',how='right')

pop_dir = pop_path
pop = pd.read_csv(os.path.join(pop_dir,'pop_2000.csv'))
pop = pop.merge(countries, right_on='pixel_id', left_on='pixel_id')
pop_bycountry_2000 = pop.groupby('ISO3')['gpw_population'].sum()

prod = pd.read_csv(os.path.join(compare_path, 'ssp1'+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
prod_bycountry_2000 = prod.groupby('ISO3')['calories_2000'].sum()

diets = pd.read_csv(diet_path + 'pctLS_SSPs_Bodirsky.csv',sep=';')
def get_changes(country):
    changes = pd.DataFrame(columns=['2000', 'Change SSP1', 'Change SSP2', 'Change SSP3', 'Change SSP4', 'Change SSP5'], 
                           index=['Caloric Sufficiency', 'Production', 'Population', 'Cropland', 'Livestock'])
    
    changes = changes.rename_axis(countries[countries['ISO3'] == country].name.iloc[0], axis=1)
    
    # sufficiency 2000
    changes.at['Caloric Sufficiency', '2000'] = sufficiencies[sufficiencies['ISO3'] == country]['2000'].values[0]
    # population 2000
    changes.at['Population', '2000'] = pop_bycountry_2000[country]
    # production 2000
    changes.at['Production', '2000'] = prod_bycountry_2000[country]
    # livestock 2000
    try:
        changes.at['Livestock', '2000'] = diets[(diets['Scenario'] == 'ssp1') & (diets['Region'] == country)]['2000'].values[0]
    except:
        changes.at['Livestock', '2000'] = np.nan
        pass
    
    df = pd.read_csv(os.path.join(compare_path, 'ssp1'+'_compare_new.csv'), index_col='pixel_id')
    # cropland 2000
    df['cropland_2000'] = df.dropna(subset=['calories_2000'])['ha_per_pixel'] * df.dropna(subset=['calories_2000'])['%cropland_2000']
    changes.at['Cropland', '2000'] = df.groupby('ISO3')['cropland_2000'].sum()[country]
    
    
    for ssp in climate_scenarios:
        name = 'Change ' + ssp.upper()
        df = pd.read_csv(os.path.join(compare_path, ssp+'_compare_new.csv'), index_col='pixel_id', na_values=[-9999])
        # change in caloric sufficicency
        changes.at['Caloric Sufficiency', name] = sufficiencies[sufficiencies['ISO3'] == country][ssp].values[0]
        
        # change in population
        pop = pd.read_csv(os.path.join(pop_dir,'pop_' + ssp + '.csv'))
        pop = pop.merge(countries, right_on='pixel_id', left_on='pixel_id')
        pop_bycountry = pop.groupby('ISO3')['gpw_population'].sum()
        
        change_pop = (pop_bycountry[country] - changes.at['Population', '2000']) / changes.at['Population', '2000']
        changes.at['Population', name] = '{:+,.2f}%'.format(100*change_pop)
        
        # change in production
        prod_bycountry = df.groupby('ISO3')['calories_2050'].sum() 
        change_prod = (prod_bycountry[country] - changes.at['Production', '2000']) / changes.at['Production', '2000']
        changes.at['Production', name] = '{:+,.2f}%'.format(100*change_prod)
        
        # change in cropland
        df['cropland_2050'] = df['ha_per_pixel'] * df['%cropland_2050']
        change_cropland = (df.groupby('ISO3')['cropland_2050'].sum()[country] - changes.at['Cropland', '2000']) / changes.at['Cropland', '2000']
        changes.at['Cropland', name] = '{:+,.2f}%'.format(100*change_cropland)
        
        # livestock
        try:
            changes.at['Livestock', name] = diets[(diets['Scenario'] == ssp) & (diets['Region'] == country)]['2050'].values[0]
        except:
            changes.at['Livestock', name] = np.nan
            pass
    # invert axis (transpose df)
    return changes.T<jupyter_output><empty_output><jupyter_text>Information about changes in every country<jupyter_code># Creating Excel Writer Object from Pandas  
writer = pd.ExcelWriter(suff_path + 'country_info_new.xlsx', engine='xlsxwriter')   
workbook = writer.book
worksheet = workbook.add_worksheet('Country_results')
writer.sheets['Country_results'] = worksheet

country_list = sufficiencies['ISO3'].values
start_row = 0
done = 0
for country in country_list:
    done += 1
    # compute information about the country
    changes = get_changes(country)
    # write to excel file
    changes.to_excel(writer, sheet_name='Country_results', startrow=start_row, startcol=0, index=True)
    start_row += len(changes) + 2
    print(f'{done}/{len(country_list)}')

# save excel file
writer.save()<jupyter_output>1/166
2/166
3/166
4/166
5/166
6/166
7/166
8/166
9/166
10/166
11/166
12/166
13/166
14/166
15/166
16/166
17/166
18/166
19/166
20/166
21/166
22/166
23/166
24/166
25/166
26/166
27/166
28/166
29/166
30/166
31/166
32/166
33/166
34/166
35/166
36/166
37/166
38/166
39/166
40/166
41/166
42/166
43/166
44/166
45/166
46/166
47/166
48/166
49/166
50/166
51/166
52/166
53/166
54/166
55/166
56/166
57/166
58/166
59/166
60/166
61/166
62/166
63/166
64/166
65/166
66/166
67/166
68/166
69/166
70/166
71/166
72/166
73/166
74/166
75/166
76/166
77/166
78/166
79/166
80/166
81/166
82/166
83/166
84/166
85/166
86/166
87/166
88/166
89/166
90/166
91/166
92/166
93/166
94/166
95/166
96/166
97/166
98/166
99/166
100/166
101/166
102/166
103/166
104/166
105/166
106/166
107/166
108/166
109/166
110/166
111/166
112/166
113/166
114/166
115/166
116/166
117/166
118/166
119/166
120/166
121/166
122/166
123/166
124/166
125/166
126/166
127/166
128/166
129/166
130/166
131/166
132/166
133/166
134/166
135/166
136/166
137/166
138/166
139/[...]
{'fraction_non_alphanumeric': 0.14000964475164765, 'fraction_numerical': 0.07888603118469699, 'mean_word_length': 3.745423340961098, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 44, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '163600', 'license': 'MIT', 'n_tokens_mistral': 10522, 'n_tokens_neox': 9036, 'n_words': 1747, 'path': 'sufficiencies/sufficiency.ipynb', 'repo_name': 'charlottegiseleweil/future-food-sufficiency', 'size': '24884'}