#!/usr/bin/env python
# coding: utf-8

# In[19]:


import pandas as pd, json


# Beta version, use my own country list

# In[4]:


c=pd.read_excel('../../universal/countries/cnc.xlsx').columns


# In[16]:


d=[]
for i in c:
    d.append({'country':i,'c':i[0],"countries":1,"type":"countries"})
d.append({'country':'Total','c':'Total',"countries":0,"type":"countries"})


# In[18]:


file('c.json','w').write(json.dumps(d))


# Final version, use Wikipedia country list

# In[20]:


df=pd.read_html('https://en.wikipedia.org/wiki/List_of_sovereign_states')


# In[42]:


h=[]
for i in df[0].index[4:225]:
    k=df[0].loc[i][0]
    if 'ZZZ' not in k:
        if u'→' not in k:
            if 'Gambia' not in k:
                if u'–' in k:k=k[:k.find(u'–')]
                if '[' in k:k=k[:k.find('[')]
                h.append({'country':k,'c':k[0],"countries":1,"type":"countries"})
            else: 
                h.append({'country':'Gambia','c':'G',"countries":1,"type":"countries"})
h.append({'country':'Total','c':'Total',"countries":0,"type":"countries"})


# In[43]:


file('h.json','w').write(json.dumps(h))