import os
from os import path

import matplotlib
import pandas as pd
import seaborn as _

DATA_FOLDER = os.getenv('DATA_FOLDER')

salaries = pd.read_csv(path.join(DATA_FOLDER, 'imt/salaries.csv'), dtype={'AREA_CODE': 'str'})
salaries.head()

"""
Explanation: Author: Marie Laure, marielaure@bayesimpact.org
IMT Salaries from API
The IMT dataset provides regional statistics about different jobs. The salary information is collected annually by the French National Institute of Statistics and Economic Studies (INSEE). Job groups are described using the PCS (Classification of Professions and Socioprofessional Categories) classification.  Previously, we retrieved IMT data by scraping the IMT website. As an exploratory step, we are interested in the sanity of the API based data and identifying putative additional information provided only by the API.
A crosswalk between PCS and ROME/FAP can be found in  data/crosswalks/passage_pcs_fap2009_romev3.csv.
The dataset can be obtained with the following command, note that it may take some time to download:
docker-compose run --rm data-analysis-prepare make data/imt/salaries.csv
Data Sanity
Loading and General View First let's load the dataset:
End of explanation
"""


salaries.AGE_GROUP_NAME.unique()

"""
Explanation: Nice! We have the basic useful columns area, job groups but also data on the salaries and the age of the recruitee (a proxy for experience I guess). According to the documentation 90% of the employees earn more than the MINIMUM_SALARY, while only 10% earn more than the MAXIMUM_SALARY.
Concerning the age groups, how many categories do we have and what are there?
End of explanation
"""


salaries.AREA_TYPE_NAME.unique()

"""
Explanation: Only two… Maybe it is enough.
Let's do a basic check on the areas.
First what about the granularity?
End of explanation
"""


salaries[salaries.AREA_TYPE_CODE == 'D'].AREA_CODE.sort_values().unique().size

"""
Explanation: Ok! We have 3 different levels, whole country, regions and departments.
Is everyone there?  Let's start by the departments!
End of explanation
"""


salaries[salaries.AREA_TYPE_CODE == 'R'].AREA_CODE.sort_values().unique().size

"""
Explanation: We could expect 101 (metropolitan departments) or 103 (metropolitan departments + overseas collectivities). Here Mayotte is missing.
What about the regions?
End of explanation
"""


len(salaries[salaries.MINIMUM_SALARY < 0]) / len(salaries) * 100

"""
Explanation: Oh… We expected 18! Again, Mayotte is missing.
The first overview revealed some negative salaries. The documentation states that when they are not enough data, the value is -1 while when the data in unavailable it is marked as -2. 
Let's have a basic description of the salaries.
How many missing or uninformative salary data do we have?  
We start with the minimum salary
End of explanation
"""


invalid_rows = salaries[(salaries.MAXIMUM_SALARY < 0) | (salaries.MINIMUM_SALARY < 0)]
all(invalid_rows.MINIMUM_SALARY == invalid_rows.MAXIMUM_SALARY)

"""
Explanation: Around 13% of the job groups for a given area (market) don't have salary data! That is a bit more than nothing!
When salaries are lesser than 0, are the minimum salary and the maximum salaries always the same?
End of explanation
"""


valid_salaries = salaries[salaries.MAXIMUM_SALARY > 0]
valid_salaries[['MAXIMUM_SALARY', 'MINIMUM_SALARY']].describe()

"""
Explanation: Yeahh!! They are exactly the same… How convenient!
So let's get a basic overview of the salaries.
End of explanation
"""


all(salaries.MAXIMUM_SALARY >= salaries.MINIMUM_SALARY)

"""
Explanation: Because the minimum MINIMUM_SALARY is lower than the french minimum wage (~1400), we think that these data gather both full-time and part-time offers. It can be scary to deliver as such to our users…
Anyway, no weird or missing values here.
It can be an overkill but we'll see if the maximum salary is always greater or equal to the minimum salary.
End of explanation
"""


salaries.PCS_PROFESSION_CODE.unique().size

"""
Explanation: Great!
Last but not least. Do we cover every job groups?
End of explanation
"""


valid_salaries.PCS_PROFESSION_CODE.unique().size

"""
Explanation: According to INSEE documentation, we should expect around 500 job groups. ~85% of them are covered.
But, then how many of these job groups have valid data?
End of explanation
"""


pcs_to_rome = pd.read_csv(path.join(DATA_FOLDER, 'crosswalks/passage_pcs_romev3.csv'))
pcs_to_rome.head()

"""
Explanation: Yeahh!! All of them!!
Currently, in Bob, we are mostly using ROME classification. Then, we are interested in the number of ROME job groups covered by this dataset.
First, we need to download the mapping between PCS and ROME classifications.
End of explanation
"""


pcs_to_rome[pcs_to_rome['PCS'].isin(salaries.PCS_PROFESSION_CODE.unique())]\
    .ROME.unique().size

"""
Explanation: Quite concise, isn't it!
End of explanation
"""


region_professions = salaries[salaries.AREA_TYPE_CODE == 'R']\
    .PCS_PROFESSION_CODE.unique()
pcs_to_rome[pcs_to_rome['PCS']\
    .isin(region_professions)]\
    .ROME.unique().size

"""
Explanation: Impressive! We have a ~97% coverage for ROME job groups.
What about the granularity of this coverage?
Coverage at the regions level.
End of explanation
"""


department_professions = salaries[salaries.AREA_TYPE_CODE == 'D']\
    .PCS_PROFESSION_CODE.unique()
pcs_to_rome[pcs_to_rome['PCS']\
    .isin(department_professions)]\
    .ROME.unique().size

"""
Explanation: Exactly the same…
Let's have a look at the ROME job groups coverage at the department level.
End of explanation
"""


salaries['mean_senior_salary'] = salaries[['MINIMUM_SALARY', 'MAXIMUM_SALARY']].sum(axis=1).div(2)
valid_salaries = salaries[salaries.MAXIMUM_SALARY > 0]
stats_within_departments = valid_salaries[valid_salaries.AREA_TYPE_CODE == 'D']\
    .groupby('AREA_NAME')\
    .mean_senior_salary.agg({'mean', 'std'})\
    .sort_values('mean', ascending=False)
    
stats_within_departments.plot(kind='box');

"""
Explanation: Again, no difference.
Everything is going well so far!
Global Overview and Comparison with Scraped Data
Actually, we have multiple source of data for salaries: the IMT and the FHS (more or less Pôle Emploi statistics history). The FHS dataset provides jobseekers salary expectancies. A notebook has been written before to investigate the distribution of these expected salaries.
An analysis of the IMT salary data has been done before.
The main conclusions of this notebook were:
- for a given job group, the salary was quite consistent on the french territory (you'll earn the almost the same if you are a deliverer in Lyon or in Paris).
- there is a high variation in salaries within a single department.
Does it still stands?
How variable are the salaries within departments?
End of explanation
"""


stats_within_jobgroups = valid_salaries[valid_salaries.AREA_TYPE_CODE == 'D'].groupby('PCS_PROFESSION_CODE')\
    .mean_senior_salary.agg({'mean', 'std'})\
    .sort_values('std', ascending=False)

stats_within_jobgroups.plot(kind='box');

"""
Explanation: Within a department, ~30% of the jobs propose a salary greater than 4200€ or lesser than 1800€.
How variable are the salaries within job groups?
End of explanation
"""


valid_salaries[
    (valid_salaries.AREA_TYPE_CODE == 'D') &
    (valid_salaries.PCS_PROFESSION_CODE.isin(stats_within_jobgroups.index))]\
    .drop_duplicates().PCS_PROFESSION_NAME.to_frame().head(5)\
    .style.set_properties( **{'width': '500px'})

"""
Explanation: As expected, within a job group, the dispersion is lesser than within a department (standard deviation most of the time lesser than 1000€).
Still, why not looking at some examples of highly variable job goups?
End of explanation
"""


nurse_pcs = ['431a', '431b', '431c', '431d', '431f', '431g']
valid_salaries[(valid_salaries.AREA_NAME == "ISERE") \
    & (valid_salaries.PCS_PROFESSION_CODE.isin(nurse_pcs) \
    & (valid_salaries.AGE_GROUP_NAME == 'Moins de 35 ans'))] \
    [['MAXIMUM_SALARY', 'MINIMUM_SALARY', 'PCS_PROFESSION_CODE', 'PCS_PROFESSION_NAME']]

"""
Explanation: Sales persons are the ones with the most highly variable salaries. That seems sensible!
What about the conformity of API data with scraped data?
According to the website (on the 2nd of October 2017), a nurse in the Isère department, younger than 35 years old, could expect a salary between 1850€ and 4050€.
Note that ROME code for nurse is "J1502" which corresponds to 6 PCS classifications (431a, 431b, 431c, 431d, 431f and 431g).
End of explanation
"""


