In [36]:
#Script to reformat methane data Top-Down file for two-level Sankey
#In BU approach, Sources = methane sources, Targets = regions
#Output: json file formatted for Sankey diagram
#Created: 31.05.2016
#Last modified: 02.06.2016 New data files from Marielle Saunois; remove GLO category from final file
# 03.06.2016 Add "Not affected" category; Exclude 0 and -99 values
# 06.06.2016 Output json files (Sankey format) with min and max values, respectively
# 07.06.2016 Min/max values output only when mean != -99 or != 0
# 09.06.2016 three levels
# 10.06.2016 New data files from Marielle
In [19]:
import numpy as np
import pandas as pd
import collections
import os
import xlrd
In [20]:
#Subcategories
#Pour Wetlands, il n'y a pas de sous catégories.
#Pour Other Natural, tu pourrais essayer avec :
# Fresh water (122)
# Geological source (52)
# Wild animals (10)
# Termites( 9)
# Permafrost and hydrates (3)
# Wild fires (1)
122+52+10+9+3+3
Out[20]:
In [90]:
df_BU = pd.read_csv("../data/Sankey_BU_2003-2012_10june2016.txt", header=1, delim_whitespace=True)
df_BU
Out[90]:
In [91]:
df_BU.rename(columns = {'proc':'stats'}, inplace = True)
df_BU.index.name = 'proc'
df_BU
Out[91]:
In [92]:
#Add "Not affected" column and set values to 0
df_BU['Not affected'] = 0
df_BU
Out[92]:
In [93]:
#Copy GLO values for OtherNat into "Not affected" col
df_BU['Not affected']['OtherNat'] = df_BU['GLO']['OtherNat']
In [94]:
df_BU
Out[94]:
In [95]:
#sub_df = pd.read_csv("../data/Sankey_BU_subcategories.csv", header=1, sep="\;")
sub_df = pd.read_csv("../data/Sankey_BU_subcategories_withFake.csv", header=1, sep="\;")
sub_df
Out[95]:
In [96]:
sub_df.index = sub_df['proc']
sub_df = sub_df.drop(['proc'], axis=1)
sub_df.head(10)
Out[96]:
In [97]:
#Pivot table so that "stats" Mean, Min, Max become three columns under each region column
#and "proc" becomes index
df_BU_piv = df_BU.pivot(columns='stats', index=df_BU.index)
df_BU_piv
Out[97]:
In [98]:
#Pivot table so that "stats" Mean, Min, Max become three columns under each region column
#and "proc" becomes index
sub_df_piv = sub_df.pivot(columns='stats', index=sub_df.index)
sub_df_piv
Out[98]:
In [99]:
df_BU_piv.columns[0][0] #Bor_NAme
df_BU_piv.columns[3][0] #contUSA
df_BU_piv.columns[6][0] #CentName
df_BU_piv.columns[9][0] #Trop_SAme
Out[99]:
In [100]:
df_BU_piv['Bor_NAme']['mean']
Out[100]:
In [101]:
df_BU_piv['Bor_NAme'].loc['Agriwast']
Out[101]:
In [102]:
df_BU_piv['Bor_NAme'].loc['BioBurBiof']['mean']
Out[102]:
In [103]:
#Create regions, categories and subcategories array
#regions
#Store region names in list
numRegions = df_BU_piv.shape[1] / 3
idx = 0
regions = []
for num in range(0,numRegions):
if df_BU_piv.columns[idx][0] != "GLO" : #exclude GLO column
regions.append(df_BU_piv.columns[idx][0])
idx = idx + 3
#categories
categories = df_BU_piv.index.tolist()
#subcategories
subcategories = sub_df.index.tolist()
subcategories = list(set(subcategories)) #finds unique values
regions
categories
subcategories
Out[103]:
In [104]:
nodes = categories + regions + subcategories
nodes
Out[104]:
In [105]:
#Replace -99 values with 0
#df_BU_piv = df_BU_piv.replace(-99, 1)
#df_BU_piv
#Replace -99 and 0 values with -1
#df_BU_piv = df_BU_piv.replace(-99, -1)
#df_BU_piv = df_BU_piv.replace(0, -1)
df_BU_piv
Out[105]:
In [106]:
sub_df_piv
Out[106]:
In [107]:
#df_BU_piv['Bor_NAme'].loc['Agriwast']['mean']
sub_df_piv['OtherNat'].loc['Freshwater'][2] #[0] = max, [1] = min, [2] = mean
sub_df_piv['Agriwast'].loc['Termites'][2] #[0] = max, [1] = min, [2] = mean
Out[107]:
In [108]:
#Write json file for 3-level Sankey, MEAN VALUES ONLY
#GLO omitted
file = open('../data/Sankey_BU_2003-2012_10june2016_mean_3levels.json', 'w')
file.write('{\n')
file.write('"nodes": [\n')
for node in nodes:
file.write('{"name": "%s"},\n' %(node))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n],\n')
file.write('"links": [\n')
#source-target pairs for categories -> regions
for category in categories:
#print category
for region in regions:
#print region
value = df_BU_piv[region].loc[category]['mean']
if value != -99:
if value != 0:
#print value
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(category, region, float(value)))
#source-target pairs for subcategories -> categories
for subcategory in subcategories:
for category in categories:
value2 = sub_df_piv[category].loc[subcategory][2] #[0] = max, [1] = min, [2] = mean
if value2 != 0:
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(subcategory, category, float(value2)))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n]\n')
file.write('}\n')
file.close()
In [109]:
#Write json file for 3-level Sankey, MIN VALUES ONLY
#GLO omitted
file = open('../data/Sankey_BU_2003-2012_10june2016_min_3levels.json', 'w')
file.write('{\n')
file.write('"nodes": [\n')
for node in nodes:
file.write('{"name": "%s"},\n' %(node))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n],\n')
file.write('"links": [\n')
#source-target pairs for categories -> regions
for category in categories:
for region in regions:
meanValue = df_BU_piv[region].loc[category]['mean']
value = df_BU_piv[region].loc[category]['min']
if meanValue != -99:
if meanValue != 0:
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(category, region, float(value)))
#source-target pairs for subcategories -> categories
for subcategory in subcategories:
for category in categories:
value2 = sub_df_piv[category].loc[subcategory][1] #[0] = max, [1] = min, [2] = mean
if value2 != 0:
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(subcategory, category, float(value2)))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n]\n')
file.write('}\n')
file.close()
In [110]:
#Write json file for Sankey, MAX VALUES ONLY
#GLO omitted
file = open('../data/Sankey_BU_2003-2012_10june2016_max_3levels.json', 'w')
file.write('{\n')
file.write('"nodes": [\n')
for node in nodes:
file.write('{"name": "%s"},\n' %(node))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n],\n')
file.write('"links": [\n')
#source-target pairs for categories -> regions
for category in categories:
for region in regions:
meanValue = df_BU_piv[region].loc[category]['mean']
value = df_BU_piv[region].loc[category]['max']
if meanValue != -99:
if meanValue != 0:
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(category, region, float(value)))
#source-target pairs for subcategories -> categories
for subcategory in subcategories:
for category in categories:
value2 = sub_df_piv[category].loc[subcategory][0] #[0] = max, [1] = min, [2] = mean
if value2 != 0:
file.write('{"source": "%s", "target": "%s", "value": "%.2f"},\n' %(subcategory, category, float(value2)))
# remove last comma
file.seek(-2, os.SEEK_END)
file.truncate()
file.write('\n]\n')
file.write('}\n')
file.close()
In [ ]:
In [ ]:
In [40]:
In [ ]: