import pandas as pd

"""
Explanation: <p><font size="6"><b>Pandas: Combining datasets Part I - concat</b></font></p>


© 2021, Joris Van den Bossche and Stijn Van Hoey  (&#106;&#111;&#114;&#105;&#115;&#118;&#97;&#110;&#100;&#101;&#110;&#98;&#111;&#115;&#115;&#99;&#104;&#101;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;, &#115;&#116;&#105;&#106;&#110;&#118;&#97;&#110;&#104;&#111;&#101;&#121;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;). Licensed under CC BY 4.0 Creative Commons
End of explanation
"""


# redefining the example objects

# series
population = pd.Series({'Germany': 81.3, 'Belgium': 11.3, 'France': 64.3, 
                        'United Kingdom': 64.9, 'Netherlands': 16.9})

# dataframe
data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}
countries = pd.DataFrame(data)
countries

"""
Explanation: Combining data is essential functionality in a data analysis workflow. 
Data is distributed in multiple files, different information needs to be merged, new data is calculated, .. and needs to be added together. Pandas provides various facilities for easily combining together Series and DataFrame objects
End of explanation
"""


pop_density = countries['population']*1e6 / countries['area']

pop_density

countries['pop_density'] = pop_density

countries

"""
Explanation: Adding columns
As we already have seen before, adding a single column is very easy:
End of explanation
"""


countries["country"].str.split(" ", expand=True)

"""
Explanation: Adding multiple columns at once is also possible. For example, the following method gives us a DataFrame of two columns:
End of explanation
"""


countries[['first', 'last']] = countries["country"].str.split(" ", expand=True)

countries

"""
Explanation: We can add both at once to the dataframe:
End of explanation
"""


data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}
countries = pd.DataFrame(data)
countries

data = {'country': ['Nigeria', 'Rwanda', 'Egypt', 'Morocco', ],
        'population': [182.2, 11.3, 94.3, 34.4],
        'area': [923768, 26338 , 1010408, 710850],
        'capital': ['Abuja', 'Kigali', 'Cairo', 'Rabat']}
countries_africa = pd.DataFrame(data)
countries_africa 

"""
Explanation: Concatenating data
The pd.concat function does all of the heavy lifting of combining data in different ways.
pd.concat takes a list or dict of Series/DataFrame objects and concatenates them in a certain direction (axis) with some configurable handling of “what to do with the other axes”.
Combining rows - pd.concat

Assume we have some similar data as in countries, but for a set of different countries:
End of explanation
"""


pd.concat([countries, countries_africa])

"""
Explanation: We now want to combine the rows of both datasets:
End of explanation
"""


pd.concat([countries, countries_africa], ignore_index=True)

"""
Explanation: If we don't want the index to be preserved:
End of explanation
"""


pd.concat([countries, countries_africa[['country', 'capital']]], ignore_index=True)

"""
Explanation: When the two dataframes don't have the same set of columns, by default missing values get introduced:
End of explanation
"""


pd.concat({'europe': countries, 'africa': countries_africa})

"""
Explanation: We can also pass a dictionary of objects instead of a list of objects. Now the keys of the dictionary are preserved as an additional index level:
End of explanation
"""


df = pd.read_csv("data/titanic.csv")
df = df.loc[:9, ['Survived', 'Pclass', 'Sex', 'Age', 'Fare', 'Embarked']]

df

"""
Explanation: <div class="alert alert-info">

**NOTE**:

A typical use case of `concat` is when you create (or read) multiple DataFrame with a similar structure in a loop, and then want to combine this list of DataFrames into a single DataFrame.

For example, assume you have a folder of similar CSV files (eg the data per day) you want to read and combine, this would look like:

```python
import pathlib

data_files = pathlib.Path("data_directory").glob("*.csv")

dfs = []

for path in data_files:
    temp = pd.read_csv(path)
    dfs.append(temp)

df = pd.concat(dfs)
```
<br>
Important: append to a list (not DataFrame), and concat this list at the end after the loop!

</div>

Joining data with pd.merge
Using pd.concat above, we combined datasets that had the same columns. But, another typical case is where you want to add information of a second dataframe to a first one based on one of the columns they have in common. That can be done with pd.merge.
Let's look again at the titanic passenger data, but taking a small subset of it to make the example easier to grasp:
End of explanation
"""


locations = pd.DataFrame({'Embarked': ['S', 'C', 'N'],
                          'City': ['Southampton', 'Cherbourg', 'New York City'],
                          'Country': ['United Kindom', 'France', 'United States']})

locations

"""
Explanation: Assume we have another dataframe with more information about the 'Embarked' locations:
End of explanation
"""


pd.merge(df, locations, on='Embarked', how='left')

"""
Explanation: We now want to add those columns to the titanic dataframe, for which we can use pd.merge, specifying the column on which we want to merge the two datasets:
End of explanation
"""


import zipfile

with zipfile.ZipFile("data/TF_VAT_NACE_SQ_2019.zip", "r") as zip_ref:
    zip_ref.extractall()

"""
Explanation: In this case we use how='left (a "left join") because we wanted to keep the original rows of df and only add matching values from locations to it. Other options are 'inner', 'outer' and 'right' (see the docs for more on this, or this visualization: https://joins.spathon.com/).
Exercise with VAT numbers
For this exercise, we start from an open dataset on "Enterprises subject to VAT" (VAT = Value Added Tax), from https://statbel.fgov.be/en/open-data/enterprises-subject-vat-according-legal-form-11. For different regions and different enterprise types, it contains the number of enterprises subset to VAT ("MS_NUM_VAT"), and the number of such enterprises that started ("MS_NUM_VAT_START") or stopped ("MS_NUM_VAT_STOP") in 2019.
This file is provided as a zipped archive of a SQLite database file. Let's first unzip it:
End of explanation
"""


import sqlite3

# connect with the database file
con = sqlite3.connect("TF_VAT_NACE_2019.sqlite")
# list the tables that are present in the database
con.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()

"""
Explanation: SQLite (https://www.sqlite.org/index.html) is a light-weight database engine, and a database can be stored as a single file. With the sqlite3 module of the Python standard library, we can open such a database and inspect it:
End of explanation
"""


df = pd.read_sql("SELECT * FROM TF_VAT_NACE_2019", con)
df

"""
Explanation: Pandas provides functionality to query data from a database. Let's fetch the main dataset contained in this file:
End of explanation
"""


df_legal_forms = pd.read_sql("SELECT * FROM TD_LGL_PSN_VAT", con)
df_legal_forms

"""
Explanation: More information about the identifyer variables (the first three columns) can be found in the other tables. For example, the "CD_LGL_PSN_VAT" column contains information about the legal form of the enterprise. What the values in this column mean, can be found in a different table:
End of explanation
"""


joined = pd.merge(df, df_legal_forms, on="CD_LGL_PSN_VAT", how="left")
joined

"""
Explanation: This type of data organization is called a "star schema" (https://en.wikipedia.org/wiki/Star_schema), and if we want to get the a "denormalized" version of the main dataset (all the data combined), we need to join the different tables.
<div class="alert alert-success">

**EXERCISE**:

Add the full name of the legal form (in the DataFrame `df_legal_forms`) to the main dataset (`df`). For this, join both datasets based on the "CD_LGL_PSN_VAT" column.

<details><summary>Hints</summary>

- `pd.merge` requires a left and a right DataFrame, the specification `on` to define the common index and the merge type `how`. 
- Decide which type of merge is most appropriate: left, right, inner,...

</details>      

</div>
End of explanation
"""


joined.groupby("TX_LGL_PSN_VAT_EN_LVL1")["MS_NUM_VAT"].sum().sort_values(ascending=False)

"""
Explanation: <div class="alert alert-success">

**EXERCISE**:

How many registered enterprises are there for each legal form? Sort the result from most to least occurring form.

<details><summary>Hints</summary>

- To count the number of registered enterprises, take the `sum` _for each_ (`groupby`) legal form.
- Check the `ascending` parameter of the `sort_values` function.

</details>    

</div>
End of explanation
"""


df_muni = pd.read_sql("SELECT * FROM TD_MUNTY_REFNIS", con)
df_muni

joined = pd.merge(df, df_muni[["CD_REFNIS", "TX_PROV_DESCR_EN"]], on="CD_REFNIS", how="left")
joined

joined.groupby("TX_PROV_DESCR_EN")["MS_NUM_VAT"].sum()

"""
Explanation: <div class="alert alert-success">

**EXERCISE**:

How many enterprises are registered per province?

* Read in the "TD_MUNTY_REFNIS" table from the database file into a `df_muni` dataframe, which contains more information about the municipality (and the province in which the municipality is located).
* Merge the information about the province into the main `df` dataset.
* Using the joined dataframe, calculate the total number of registered companies per province.

<details><summary>Hints</summary>

- Data loading in Pandas requires `pd.read_...`, in this case `read_sql`. Do not forget the connection object as a second input.
- `df_muni` contains a lot of columns, whereas we are only interested in the province information. Only use the relevant columns "TX_PROV_DESCR_EN" and "CD_REFNIS" (you need this to join the data).
- Calculate the `sum` _for each_ (`groupby`) province.    


</details>    

</div>
End of explanation
"""


import geopandas
import fiona

stat = geopandas.read_file("data/statbel_statistical_sectors_2019.shp.zip")

stat.head()

stat.plot()

"""
Explanation: Joining with spatial data to make a map
The course materials contains a simplified version of the "statistical sectors" dataset (https://statbel.fgov.be/nl/open-data/statistische-sectoren-2019), with the borders of the municipalities. This dataset is provided as a zipped ESRI Shapefile, one of the often used file formats used in GIS for vector data.
The GeoPandas package extends pandas with geospatial functionality.
End of explanation
"""


df_by_muni = df.groupby("CD_REFNIS").sum()

"""
Explanation: The resulting dataframe (a GeoDataFrame) has a "geometry" column (in this case with polygons representing the borders of the municipalities), and a couple of new methods with geospatial functionality (for example, the plot() method by default makes a map). It is still a DataFrame, and everything we have learned about pandas can be used here as well.
Let's visualize the change in number of registered enterprises on a map at the municipality-level. 
We first calculate the total number of (existing/starting/stopping) enterprises per municipality:
End of explanation
"""


df_by_muni["NUM_VAT_CHANGE"] = (df_by_muni["MS_NUM_VAT_START"] - df_by_muni["MS_NUM_VAT_STOP"]) / df_by_muni["MS_NUM_VAT"] * 100

df_by_muni

"""
Explanation: And add a new column with the relative change in the number of registered enterprises:
End of explanation
"""


joined = pd.merge(stat, df_by_muni, left_on="CNIS5_2019", right_on="CD_REFNIS")
joined

"""
Explanation: We can now merge the dataframe with the geospatial information of the municipalities with the dataframe with the enterprise numbers:
End of explanation
"""


joined["NUM_VAT_CHANGE_CAT"] = pd.cut(joined["NUM_VAT_CHANGE"], [-15, -6, -4, -2, 2, 4, 6, 15])

joined.plot(column="NUM_VAT_CHANGE_CAT", figsize=(10, 10), cmap="coolwarm", legend=True)#k=7, scheme="equal_interval")

"""
Explanation: With this joined dataframe, we can make a new map, now visualizing the change in number of registered enterprises ("NUM_VAT_CHANGE"):
End of explanation
"""


data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}
countries = pd.DataFrame(data)
countries

data = {'country': ['Belgium', 'France', 'Netherlands'],
        'GDP': [496477, 2650823, 820726],
        'area': [8.0, 9.9, 5.7]}
country_economics = pd.DataFrame(data).set_index('country')
country_economics

pd.concat([countries, country_economics], axis=1)

"""
Explanation: Combining columns  - pd.concat with axis=1
We can use pd.merge to combine the columns of two DataFrame based on a common column. If our two DataFrames already have equivalent rows, we can also achieve this basic case using pd.concat with specifying axis=1 (or axis="columns").

Assume we have another DataFrame for the same countries, but with some additional statistics:
End of explanation
"""


countries2 = countries.set_index('country')

countries2

pd.concat([countries2, country_economics], axis="columns")

"""
Explanation: pd.concat matches the different objects based on the index:
End of explanation
"""


