<jupyter_start><jupyter_text>Jupyter notebook to extract COVID data for Nepal from an API and aggregate the data on the basis of date and admins (municipality, district, and province).  _Work done by Nepal Poverty Team, The World Bank_  API1. [Nepal Coronavirus Information](https://nepalcorona.info/) We have used Python 3 and produced the Python 3 Jupyter notebook showing data cleaning and merging.  Setup Running of this notebook requires Jupyter software system. Either Jupyter notebook or Jupyter lab can be installed on the system. In addition, a Python package, _pandas_, is required.  Jupyter Software Installationhttps://jupyter.org/install pandas Package Installationhttps://pandas.pydata.org/pandas-docs/stable/getting_started/install.html After all the dependencies are installed the notebook can be imported to the Jupyter software and run.  Imports<jupyter_code>import requests
import json
import pandas as pd<jupyter_output><empty_output><jupyter_text>API URLs<jupyter_code># API urls to extract districts data, municipalities data and individual covid cases data
districts_url = 'https://data.nepalcorona.info/api/v1/districts'
municipals_url = 'https://data.nepalcorona.info/api/v1/municipals'
covid_cases_url = 'https://data.nepalcorona.info/api/v1/covid'<jupyter_output><empty_output><jupyter_text>Function to extract data from API URL<jupyter_code># function to get list of dictionaries from URL
def get_data_from_url(url):
    """
    Gets list of dictionaries from the API URL.
    
    Params:
    url (str): URL for the API
    """
    response = requests.get(url)
    response_content = response.content.decode()
    return json.loads(response_content)<jupyter_output><empty_output><jupyter_text>Data preprocessing -- extraction and wrangling<jupyter_code># get data for districts, municipalities and individual COVID cases
districts = get_data_from_url(districts_url)
municipals = get_data_from_url(municipals_url)
covid_cases = get_data_from_url(covid_cases_url)

# store the data in dataframe using pd.DataFrame constructor
districts = pd.DataFrame(districts)
municipals = pd.DataFrame(municipals)
covid_cases = pd.DataFrame(covid_cases)

# add a column in municipals -- district_en -- which stores districts names
municipals['district_en'] = municipals['district'].map(dict(zip(districts['id'], districts['title_en'])))

# helper function to strip the whitespaces
def clean(name):
    return name.strip()

# concatenate two columns, municipality name and municipality type to get the full municipality name
municipals['municipal_en'] = municipals['title_en'].apply(clean) + ' ' + municipals['type'].apply(clean)

# add municipality and district names in the covid cases dataframe
covid_cases['municipality_name'] = covid_cases['municipality'].map(dict(zip(municipals['id'], municipals['municipal_en'])))
covid_cases['district_name'] = covid_cases['district'].map(dict(zip(municipals['district'], municipals['district_en'])))

# convert the string date format into proper pandas date format
covid_cases['date'] = pd.to_datetime(covid_cases['createdOn']).dt.date<jupyter_output><empty_output><jupyter_text>Data aggregation<jupyter_code># aggregate the data by date and municipality
date_muni_covid_cases = covid_cases.groupby(['date', 'province', 'district_name', 'municipality_name']).agg('count')['id'].reset_index()
date_muni_covid_cases = date_muni_covid_cases.rename(columns={'id': 'cases_count'})

# aggregate the data by date and district
date_dist_covid_cases = covid_cases.groupby(['date', 'province', 'district_name']).agg('count')['id'].reset_index()
date_dist_covid_cases = date_dist_covid_cases.rename(columns={'id': 'cases_count'})

# aggregate the data by date and province
date_province_covid_cases = covid_cases.groupby(['date', 'province']).agg('count')['id'].reset_index()
date_province_covid_cases = date_province_covid_cases.rename(columns={'id': 'cases_count'})
# aggregate up-to-date data by municipality
municipals['cases_count'] = municipals['id'].map(dict(covid_cases.groupby('municipality').agg('count')['id']))

# aggregate up-to-date data by district
districts['cases_count'] = districts['id'].map(dict(covid_cases.groupby('district').agg('count')['id']))

# aggregate up-to-date data by province
provinces = pd.DataFrame({'province': [1, 2, 3, 4, 5, 6, 7]})
provinces['cases_count'] = provinces['province'].map(dict(covid_cases.groupby('province').agg('count')['id']))<jupyter_output><empty_output><jupyter_text>Validation If some error pops up in the below *assertion* code, make sure to check the cleaning and aggregation section. The error shows that the cleaning and/or aggregation might not have been done correctly.<jupyter_code>assert date_muni_covid_cases['cases_count'].sum() == covid_cases.shape[0]
assert date_dist_covid_cases['cases_count'].sum() == covid_cases.shape[0]
assert date_province_covid_cases['cases_count'].sum() == covid_cases.shape[0]
assert municipals['cases_count'].sum() == covid_cases.shape[0]
assert districts['cases_count'].sum() == covid_cases.shape[0]
assert provinces['cases_count'].sum() == covid_cases.shape[0]<jupyter_output><empty_output><jupyter_text>Export<jupyter_code># export the aggregates to Nepal COVID folder

date_muni_covid_cases.to_csv('Nepal COVID/date_muni_covid_cases.csv', index=False)
date_dist_covid_cases.to_csv('Nepal COVID/date_dist_covid_cases.csv', index=False)
date_province_covid_cases.to_csv('Nepal COVID/date_province_covid_cases.csv', index=False)

covid_cases.to_csv('Nepal COVID/individual_covid_cases.csv', index=False)
municipals.to_csv('Nepal COVID/municipal_covid_cases.csv', index=False)
districts.to_csv('Nepal COVID/district_covid_cases.csv', index=False)
provinces.to_csv('Nepal COVID/province_covid_cases.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11815512178269132, 'fraction_numerical': 0.0032820867161858698, 'mean_word_length': 7.654708520179372, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '132901', 'license': 'MIT', 'n_tokens_mistral': 1916, 'n_tokens_neox': 1864, 'n_words': 425, 'path': '03 Nepal COVID Data Extraction and Aggregation.ipynb', 'repo_name': 'Suraj1127/covid-data', 'size': '5789'}