<jupyter_start><jupyter_text>Table of Contents 1&nbsp;&nbsp;Overview1.1&nbsp;&nbsp;Input1.2&nbsp;&nbsp;Save the df<jupyter_code>%pylab inline
import pandas as pd<jupyter_output>Populating the interactive namespace from numpy and matplotlib<jupyter_text>OverviewIn this notebook we show how to import the ISTAT commuting data to provide information on commuting to the synthetic population generator.We are going to load the tables containing the information about the number of students/workers commuting to:- same municipality of residence;- different municipality in same province of residence;- different province in same region of residence;- different region in same nation.- by now we will ignore the inter-state commuting as it is marginal.- also, we assume that all the people working and/or studying do commute to a workplace/school thus ignoring people working at home (even thoughdata are available).We will then convert these absolute counts to commuting probabilities and project them to all the eurostat codes to create a fake commuting matrix for teh whole Europe. InputHere we import the input files (excel tables) downloaded from the [ISTAT](http://dati-censimentopopolazione.istat.it/index.aspx?queryid=3432) website.<jupyter_code>columnCodes = ["NUTS", "sCty", "sMun", "sMun_CT",
               "dMun_sPrv", "dMun_sPrv_CT",
               "dPrv_sReg", "dPrv_sReg_CT",
               "dReg", "dReg_CT", "sCty_CT",
               "dCty", "TOTAL"
              ]

studentsCommuting_df = pd.read_excel("resources/Italy/population/commuting_studentsCommuters_ISTAT_2011_NUTS3.xlsx",
                                    na_values="..", skiprows=3, names=columnCodes)

workersCommuting_df = pd.read_excel("resources/Italy/population/commuting_workersCommuters_ISTAT_2011_NUTS3.xlsx",
                                    na_values="..", skiprows=3, names=columnCodes)

nonCommuting_df = pd.read_excel("resources/Italy/population/commuting_nonCommuters_ISTAT_2011_NUTS3.xlsx",
                                    na_values="..", skiprows=1,
                                    names=["NUTS", "studyHome", "workHome", "workNoPlace", "NEET", "TOTAL"])

# Replace holes with no commuters...
workersCommuting_df.replace(to_replace=np.NaN, value=0., inplace=True)
studentsCommuting_df.replace(to_replace=np.NaN, value=0., inplace=True)
nonCommuting_df.replace(to_replace=np.NaN, value=0., inplace=True)

# Drop columns about the destination in capitals (we do not care at
# this stage to distinguish between commuting to capital or other
# municipalities)...
for capitalColumn in [c for c in columnCodes if "_CT" in c]:
    del workersCommuting_df[capitalColumn]
    del studentsCommuting_df[capitalColumn]

# Remove spaces in nuts codes and keep only up to NUTS3 (no LAU1).
workersCommuting_df["NUTS"] = workersCommuting_df["NUTS"].apply(lambda v: v.replace(" ", ""))
studentsCommuting_df["NUTS"] = studentsCommuting_df["NUTS"].apply(lambda v: v.replace(" ", ""))

workersCommuting_df = workersCommuting_df[workersCommuting_df["NUTS"].str.len() <= 5]
studentsCommuting_df = studentsCommuting_df[studentsCommuting_df["NUTS"].str.len() <= 5]

# Set the index
workersCommuting_df.set_index("NUTS", inplace=True)
studentsCommuting_df.set_index("NUTS", inplace=True)
nonCommuting_df.set_index("NUTS", inplace=True)
workersCommuting_df.head(2)
studentsCommuting_df.head(2)
nonCommuting_df.head(2)
# Check that I have the right number of commuters (or a number not too much wrong)
aggregationsToCheck = {
                "sCty": ["sMun", "dMun_sPrv", "dPrv_sReg", "dReg"],
                "TOTAL": ["sCty", "dCty"],
                      }

for dfName, df in zip(["work", "stud"], [workersCommuting_df, studentsCommuting_df]):
    print dfName
    for tot, cols in aggregationsToCheck.iteritems():
        print "\t", tot
        problematic = df[(df[cols].sum(axis=1) != df[tot])]
        if problematic.shape[0] > 0:
            print (problematic[cols].sum(axis=1) - problematic[tot])
nonCommuting_df[nonCommuting_df[nonCommuting_df.columns[:-1]].sum(axis=1) != nonCommuting_df["TOTAL"]]
# Check that we have the same indexes
print studentsCommuting_df.index.symmetric_difference(workersCommuting_df.index)
# Create fake tables for all the europe NUTS3 sampling from this dataframe...

# Load the reference df
referenceDF = pd.read_pickle("resources/Europe/population/structure/dataframes/1990-2016_broadAgeBySexNUTS3.pkl")

missingNUTS3codes = referenceDF.index.difference(studentsCommuting_df.index)
NmissingNUTS3codes = missingNUTS3codes.shape[0]
missingStudentCommuting = studentsCommuting_df.sample(NmissingNUTS3codes, replace=True)
missingStudentCommuting.index = missingNUTS3codes

europeStudentsCommuting_df = studentsCommuting_df.append(missingStudentCommuting,
                                                   ignore_index=False, verify_integrity=True)
europeStudentsCommuting_df.head()
missingWorkerCommuting = workersCommuting_df.sample(NmissingNUTS3codes, replace=True)
missingWorkerCommuting.index = missingNUTS3codes

europeWorkersCommuting_df = workersCommuting_df.append(missingWorkerCommuting,
                                                   ignore_index=False, verify_integrity=True)
europeWorkersCommuting_df.head()
# Now delete columns on foreign commuting and compute the probabilities...
# We will use the counter to the same country as the total value
workersCommutingRAW_df = europeWorkersCommuting_df.copy(deep=True)
studentsCommutingRAW_df = europeStudentsCommuting_df.copy(deep=True)

del workersCommutingRAW_df["dCty"], workersCommutingRAW_df["TOTAL"]
del studentsCommutingRAW_df["dCty"], studentsCommutingRAW_df["TOTAL"]

# Compute probability and delete total...
workersCommutingPDF_df = workersCommutingRAW_df.div(workersCommutingRAW_df["sCty"], axis=0)
studentsCommutingPDF_df = studentsCommutingRAW_df.div(studentsCommutingRAW_df["sCty"], axis=0)

del workersCommutingRAW_df["sCty"], studentsCommutingRAW_df["sCty"]
del workersCommutingPDF_df["sCty"], studentsCommutingPDF_df["sCty"]

# Compute the actual PDF now that we have the total (total may be different from sum of columns)
workersCommutingPDF_df = workersCommutingPDF_df.div(workersCommutingPDF_df.sum(axis=1), axis=0)
studentsCommutingPDF_df = studentsCommutingPDF_df.div(studentsCommutingPDF_df.sum(axis=1), axis=0)

workersCommutingCDF_df = workersCommutingPDF_df.cumsum(axis=1)
studentsCommutingCDF_df = studentsCommutingPDF_df.cumsum(axis=1)
workersCommutingRAW_df.head(2)
workersCommutingPDF_df.head(2)
workersCommutingCDF_df.head(2)
# concat the three ones!
studentCommutingDistribution = pd.concat({
                                    "RAW": studentsCommutingRAW_df,
                                    "PDF": studentsCommutingPDF_df,
                                    "CDF": studentsCommutingCDF_df,
                                        }).unstack(0)
studentCommutingDistribution.columns = studentCommutingDistribution.columns.swaplevel(1, 0)
studentCommutingDistribution.sort_index(axis=1, level=-2, inplace=True)
studentCommutingDistribution.head(2)
# concat the three ones!
workerCommutingDistribution = pd.concat({
                                    "RAW": workersCommutingRAW_df,
                                    "PDF": workersCommutingPDF_df,
                                    "CDF": workersCommutingCDF_df,
                                        }).unstack(0)
workerCommutingDistribution.columns = workerCommutingDistribution.columns.swaplevel(0, 1)
workerCommutingDistribution.sort_index(axis=1, level=-2, inplace=True)
workerCommutingDistribution.head(2)<jupyter_output><empty_output><jupyter_text>Save the df<jupyter_code>workerCommutingDistribution.to_pickle("resources/Europe/population/structure/dataframes/2011_workCommuting_ISTAT_NUTS3.pkl")
studentCommutingDistribution.to_pickle("resources/Europe/population/structure/dataframes/2011_studyCommuting_ISTAT_NUTS3.pkl")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09514807813484562, 'fraction_numerical': 0.011846250787649653, 'mean_word_length': 4.402314499659632, 'pattern_counts': {'":': 8, '<': 14, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '371919', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2599, 'n_tokens_neox': 2370, 'n_words': 509, 'path': 'synpop/synPop_04_commutingDataISTAT.ipynb', 'repo_name': 'CoeGSS-Project/HealthHabits', 'size': '7935'}