Criminalità Italia


In [1]:
# Import librerie per analisi dati (Pandas) e dati Istat
import os
import pandas as pd
import numpy as np
from IPython.core.display import HTML
import istat
import jsonstat

In [2]:
# cache dir per velocizzare analisi in locale
cache_dir = os.path.abspath(os.path.join("..", "tmp", "istat_cached"))
istat.cache_dir(cache_dir)
istat.lang(0)  # lingua italiano
print("cache_dir is '{}'".format(istat.cache_dir()))


cache_dir is '/Users/cesar/Documents/opendata/tmp/istat_cached'

In [3]:
# Lista delle aree di analisi disponibili
# istat.areas()

In [4]:
# AREA Giustizia e Sicurezza
istat_area_lab = istat.area('JUS')

# Lista dei datasets dell'area
# istat_area_lab.datasets()

In [5]:
# DATASET delitti http://dati.istat.it/Index.aspx?DataSetCode=dccv_delittips
istat_dataset_taxdisoccu = istat_area_lab.dataset('DCCV_DELITTIPS')
istat_dataset_taxdisoccu


Out[5]:
DCCV_DELITTIPS(6):Delitti denunciati dalle forze di polizia all'autorità giudiziaria
nrnamenr. valuesvalues (first 3 values)
0Territorio1481:'Italia', 3:'Nord', 4:'Nord-ovest' ...
1Tipo dato31:'numero di delitti denunciati dalle forze di polizia all'autorità giudiziaria', 2:'delitti di cui si è scoperto l'autore nel corso dell'anno di riferimento (valori percentuali sul totale dei delitti dell'anno di riferimento)', 3:'delitti denunciati dalle forze di polizia all'autorità giudiziaria (valori per 100.000 abitanti)' ...
2Tipo di delitto561:'strage', 2:'omicidi volontari consumati', 3:'omicidi volontari consumati a scopo di furto o rapina' ...
3Identità autore nota22:'si', 3:'totale'
4Periodo del commesso delitto31:'prima dell'anno di riferimento', 2:'durante l'anno di riferimento', 3:'prima o durante l'anno di riferimento' ...
5Anno102015:'2006', 2034:'2007', 2053:'2008' ...

In [7]:
# Lista di tutte le Dimensioni
#istat_dataset_taxdisoccu.dimensions()

In [8]:
# es. spec: Numero crimini registrati dalla polizia(Data Type) per Anno 2014 (Year) su tutto il Territorio Italia (Territory), Omicidi Intenzionali (Type of crime)
spec = {
    "Tipo dato":1,                      # 1 -> number of crimes reported by the police forces to the judicial authority (per 100.000 abitanti)
    "Anno":2167,                        # 2167 -> 2014, 2186 -> 2015
    #"Territory":,                      # 0 -> ALL
    #"Type of crime":,                  # 0 -> ALL   
    "Identità autore nota":3,           # 3 Total
    "Periodo del commesso delitto":2    # 2 -> during the reference year
}

collection = istat_dataset_taxdisoccu.getvalues(spec)

In [9]:
# Lista dei dataset presenti nella collection
# collection

In [10]:
# Leggo il primo e unico dataset
ds = collection.dataset(0)
# ds

In [11]:
# Trasformo il dataset in DataFrame
df = ds.to_data_frame('Territorio')

In [12]:
# Tengo solo Omicidi Volontari, perchè sono gli stessi comunicati in EURO
df_fil = df[
            #(df['Tipo di delitto']=='strage') | 
            #(df['Tipo di delitto']=='infanticidi') |
            # (df['Tipo di delitto']=='omicidi preterintenzionali') |
            (df['Tipo di delitto'].str.contains('omicidi volontari'))
            # (df['Tipo di delitto'].str.contains('omicidi colposi'))
           ]
df_fil = df_fil.drop('Tipo di delitto', 1)

In [13]:
# Reset index per group-by
df_fil.reset_index(level=0, inplace=True)

In [14]:
# Raggruppo i valori per Territorio 
df_fil_agg = df_fil.groupby('Territorio',as_index=False)
df_fil_agg = df_fil_agg.agg({'Value' : np.max})

In [79]:
# writer = pd.ExcelWriter('TerritorioItalia.xlsx')
# df_fil_agg.to_excel(writer,'territorio')
# writer.save()

1. Dataset - Numero Omicidi Italia 2014


In [15]:
# Directory dove salvare i file, da utilizzare in data_visualization
dir_df = os.path.join(os.path.abspath(''),'stg')

In [16]:
df_ita=df_fil_agg[(df_fil_agg['Territorio']=='Italia')]
# df_ita

In [17]:
df_ita_filename = r'df_ita.pkl'
df_ita_fullpath = os.path.join(dir_df, df_ita_filename)
df_ita.to_pickle(df_ita_fullpath)

2. Dataset - Numero Omicidi Regioni 2014


In [18]:
df_reg=df_fil_agg[(df_fil_agg['Territorio']=='Abruzzo') |
                  (df_fil_agg['Territorio']=='Basilicata') |
                  (df_fil_agg['Territorio']=='Calabria') |
                  (df_fil_agg['Territorio']=='Campania') |
                  (df_fil_agg['Territorio']=='Emilia-Romagna') |
                  (df_fil_agg['Territorio']=='Friuli-Venezia Giulia') |
                  (df_fil_agg['Territorio']=='Lazio') |
                  (df_fil_agg['Territorio']=='Liguria') |
                  (df_fil_agg['Territorio']=='Lombardia') |
                  (df_fil_agg['Territorio']=='Marche') |
                  (df_fil_agg['Territorio']=='Molise') |
                  (df_fil_agg['Territorio']=='Piemonte') |
                  (df_fil_agg['Territorio']=='Puglia') |
                  (df_fil_agg['Territorio']=='Sardegna') |
                  (df_fil_agg['Territorio']=='Sicilia') |
                  (df_fil_agg['Territorio']=='Toscana') |
                  (df_fil_agg['Territorio']=='Umbria') |
                  (df_fil_agg['Territorio']=='Veneto') |
                  (df_fil_agg['Territorio'].str.contains('Trentino Alto Adige')) |
                  (df_fil_agg['Territorio'].str.contains('''Valle d'Aosta'''))
                 ]

In [19]:
df_reg_filename = r'df_reg.pkl'
df_reg_fullpath = os.path.join(dir_df, df_reg_filename)
df_reg.to_pickle(df_reg_fullpath)

In [20]:
# Directory dove salvare gli output per il Sito
dir_out = os.path.join(os.path.abspath(''),'output')
df_reg.to_csv(os.path.join(dir_out,r'regioni.csv'))

3. Dataset - Numero Omicidi Città 2014


In [21]:
df_cit=df_fil_agg[(df_fil_agg['Territorio']!='Abruzzo') &
                  (df_fil_agg['Territorio']!='Basilicata') &
                  (df_fil_agg['Territorio']!='Calabria') &
                  (df_fil_agg['Territorio']!='Campania') &
                  (df_fil_agg['Territorio']!='Emilia-Romagna') &
                  (df_fil_agg['Territorio']!='Friuli-Venezia Giulia') &
                  (df_fil_agg['Territorio']!='Lazio') &
                  (df_fil_agg['Territorio']!='Liguria') &
                  (df_fil_agg['Territorio']!='Lombardia') &
                  (df_fil_agg['Territorio']!='Marche') &
                  (df_fil_agg['Territorio']!='Molise') &
                  (df_fil_agg['Territorio']!='Piemonte') &
                  (df_fil_agg['Territorio']!='Puglia') &
                  (df_fil_agg['Territorio']!='Sardegna') &
                  (df_fil_agg['Territorio']!='Sicilia') &
                  (df_fil_agg['Territorio']!='Toscana') &
                  (df_fil_agg['Territorio']!='Veneto') &
                  (df_fil_agg['Territorio']!='Umbria') &
                  (df_fil_agg['Territorio']!='Italia') &
                  (df_fil_agg['Territorio']!='Nord-ovest') &
                  (df_fil_agg['Territorio']!='Sud') &
                  (df_fil_agg['Territorio']!='Centro') &
                  (df_fil_agg['Territorio']!='Nord-est') &
                  (df_fil_agg['Territorio']!='Isole')
                 ]

In [22]:
# Top 10 Città pericolose
df_cit_top=df_cit.sort_values(by='Value',ascending=False).head(10)
df_cit_top_filename = r'df_cit_top.pkl'
df_cit_top_fullpath = os.path.join(dir_df, df_cit_top_filename)
df_cit_top.to_pickle(df_cit_top_fullpath)

3. Dataset - Numero Omicidi Paesi Euro


In [23]:
# url costruito via Query Builder di Euro Stat
url_1 = 'http://ec.europa.eu/eurostat/wdds/rest/data/v1.1/json/en/crim_off_cat?precision=1&iccs=ICCS0101&unit=NR'
file_name_1 = r'eurostat-omicidi.json'
file_path_1 = os.path.join(dir_df, file_name_1)
jsonstat.download(url_1, file_path_1)


Out[23]:
'{"crim_off_cat":{"wsVersion":"V1_1","code":"crim_off_cat","language":"EN","title":"Recorded offences by offence category - police data","subTitle":null,"description":null,"lastUpdateDate":1465941600000,"status":{"value":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",null,null,null,null,":",":",null,null,null,null,null,null,":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"category":{"label":{":":"not available"}}},"value":[88,82,118,124,125,107,98,58,51,58,78,87,61,40,66,67,54,49,60,46,49,203,189,189,214,202,202,202,172,150,148,128,141,109,116,54,51,53,46,47,57,41,9,19,7,8,19,11,10,113,105,105,83,95,91,84,656,721,699,689,619,623,645,54,47,42,44,40,42,58,84,70,70,65,63,52,41,139,143,176,184,165,141,105,407,412,401,385,364,302,323,133,120,117,109,88,88,89,1021,819,796,856,784,777,792,71,49,62,49,51,46,36,147,139,133,142,113,138,129,89,88,89,66,79,83,80,0,1,2,3,1,1,null,615,590,529,552,530,504,475,1,0,1,0,0,0,1,286,240,199,189,181,172,155,7,5,8,4,3,1,4,99,108,70,69,97,69,77,22,10,13,19,15,9,19,35,35,39,27,28,20,25,6,4,4,3,10,6,6,150,154,144,143,145,125,null,34,29,29,111,27,46,29,460,493,436,449,377,298,283,124,130,124,114,122,144,92,470,397,404,335,378,336,298,128,136,114,114,104,131,113,82,93,91,81,68,87,87,11,13,11,17,14,12,17,94,84,89,96,75,78,58,null,2090,1766,1688,1806,null,null,638,595,633,528,547,526,null,95,84,95,91,73,63,60,24,29,23,23,21,20,17,113,73,106,62,90,41,39],"dimension":{"id":["iccs","unit","geo","time"],"size":[1,1,41,7],"iccs":{"label":"iccs","category":{"index":{"ICCS0101":0},"label":{"ICCS0101":"Intentional homicide"}}},"unit":{"label":"unit","category":{"index":{"NR":0},"label":{"NR":"Number"}}},"geo":{"label":"geo","category":{"index":{"AL":0,"AT":1,"BA":2,"BE":3,"BG":4,"CH":5,"CY":6,"CZ":7,"DE":8,"DK":9,"EE":10,"EL":11,"ES":12,"FI":13,"FR":14,"HR":15,"HU":16,"IE":17,"IS":18,"IT":19,"LI":20,"LT":21,"LU":22,"LV":23,"ME":24,"MK":25,"MT":26,"NL":27,"NO":28,"PL":29,"PT":30,"RO":31,"RS":32,"SE":33,"SI":34,"SK":35,"TR":36,"UKC-L":37,"UKM":38,"UKN":39,"XK":40},"label":{"AL":"Albania","AT":"Austria","BA":"Bosnia and Herzegovina","BE":"Belgium","BG":"Bulgaria","CH":"Switzerland","CY":"Cyprus","CZ":"Czech Republic","DE":"Germany (until 1990 former territory of the FRG)","DK":"Denmark","EE":"Estonia","EL":"Greece","ES":"Spain","FI":"Finland","FR":"France","HR":"Croatia","HU":"Hungary","IE":"Ireland","IS":"Iceland","IT":"Italy","LI":"Liechtenstein","LT":"Lithuania","LU":"Luxembourg","LV":"Latvia","ME":"Montenegro","MK":"Former Yugoslav Republic of Macedonia, the","MT":"Malta","NL":"Netherlands","NO":"Norway","PL":"Poland","PT":"Portugal","RO":"Romania","RS":"Serbia","SE":"Sweden","SI":"Slovenia","SK":"Slovakia","TR":"Turkey","UKC-L":"England and Wales","UKM":"Scotland","UKN":"Northern Ireland (UK)","XK":"Kosovo (under United Nations Security Council Resolution 1244/99)"}}},"time":{"label":"time","category":{"index":{"2008":0,"2009":1,"2010":2,"2011":3,"2012":4,"2013":5,"2014":6},"label":{"2008":"2008","2009":"2009","2010":"2010","2011":"2011","2012":"2012","2013":"2013","2014":"2014"}}}}}}'

In [24]:
collection_1 = jsonstat.from_file(file_path_1)
collection_1


Out[24]:
JsonstatCollection contains the following JsonStatDataSet:
posdataset
0'crim_off_cat'

In [25]:
crim_off_cat = collection_1.dataset('crim_off_cat')
crim_off_cat


Out[25]:
name: 'crim_off_cat'title: 'Recorded offences by offence category - police data'size: 287
posidlabelsizerole
0iccsiccs1
1unitunit1
2geogeo41
3timetime7

In [26]:
# Dimensioni Geo e Time
df_eur = crim_off_cat.to_table(content='id',rtype=pd.DataFrame)

In [28]:
# Filtro solo 2014
df_eur=df_eur[(df_eur['time']=='2015')]
# df_eur.head(10)

In [29]:
#writer = pd.ExcelWriter('ZonaEuro.xlsx')
#df_eur.to_excel(writer,'Euro')
#writer.save()

In [30]:
df_eur = df_eur.drop('iccs', 1)
df_eur = df_eur.drop('unit', 1)
df_eur = df_eur.drop('time', 1)

In [31]:
df_eur_filename = r'df_eur.pkl'
df_eur_fullpath = os.path.join(dir_df, df_eur_filename)
df_eur.to_pickle(df_eur_fullpath)

4. Dataset Omicidi volontari principali città Europee


In [32]:
# url costruito via Query Builder di Euro Stat
url_2 = 'http://ec.europa.eu/eurostat/wdds/rest/data/v1.1/json/en/crim_hom_ocit?unit=NR&precision=1'
file_name_2 = r'eurostat-omicidi_citta.json'
file_path_2 = os.path.join(dir_df, file_name_2)
jsonstat.download(url_2, file_path_2)


Out[32]:
'{"crim_hom_ocit":{"wsVersion":"V1_1","code":"crim_hom_ocit","language":"EN","title":"Intentional homicide offences in largest cities","subTitle":null,"description":null,"lastUpdateDate":1464127200000,"status":{"value":[null,null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,":",null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,":",":",":",":",null,null,":",":",":",":",":",null,null,null,null,null,null,null,null,":",":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,":",null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,":",":",":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,":",null,null,null,null,":",":",null,null,null,null,null,null,":",":",null,null,null,null,null,":",null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,":",":",null,null,null,null,null,null,null,null,null,null,null,null,null],"category":{"label":{":":"not available"}}},"value":[22,21,25,26,25,31,23,17,20,18,25,25,24,null,null,20,18,25,25,24,11,10,null,6,9,13,8,8,44,33,31,25,32,34,null,null,33,31,27,35,35,39,27,26,20,20,24,18,null,null,26,20,20,24,18,25,null,4,10,1,2,0,2,3,3,4,2,5,4,null,null,3,4,2,5,4,2,18,23,16,13,16,13,null,null,23,16,13,16,13,10,42,61,37,35,43,43,null,null,67,41,43,50,53,47,null,null,null,null,20,20,null,null,null,null,null,20,20,16,69,70,82,86,66,null,null,null,70,82,86,66,54,40,39,32,28,21,28,22,null,null,32,28,21,28,22,18,16,7,12,14,12,7,null,null,7,12,14,12,6,10,47,36,44,42,46,42,null,null,36,44,42,46,42,43,15,5,10,11,10,6,null,null,5,10,11,10,6,5,26,36,32,32,28,31,null,null,36,32,32,25,28,28,0,1,1,3,1,0,null,null,1,1,3,1,0,null,28,29,9,27,24,29,null,null,29,9,27,24,29,35,0,0,0,0,0,0,1,58,30,26,22,19,25,null,null,30,26,22,19,25,41,3,5,0,3,null,null,null,null,5,0,3,0,0,1,30,37,23,18,26,26,null,null,37,23,18,26,26,30,7,8,7,7,7,1,5,13,12,12,11,8,8,8,0,0,1,0,0,0,null,null,0,1,0,0,0,0,12,7,5,19,8,8,null,null,7,5,19,8,8,7,25,33,22,29,25,18,null,null,33,22,29,25,18,20,10,5,6,6,11,11,null,null,5,6,6,11,11,7,17,20,22,17,21,13,null,null,20,22,17,21,13,21,35,23,20,30,19,36,43,0,1,3,4,2,0,null,null,1,3,4,2,0,4,10,9,8,14,12,9,null,null,9,8,14,12,9,11,null,348,242,215,262,null,null,153,118,133,101,107,108,null,null,118,133,101,107,108,null,23,20,30,12,21,17,null,null,20,30,12,21,19,17,4,7,9,3,4,6,null,null,7,9,3,4,6,6,7,8,6,6,1,3,6],"dimension":{"id":["unit","cities","time"],"size":[1,65,7],"unit":{"label":"unit","category":{"index":{"NR":0},"label":{"NR":"Number"}}},"cities":{"label":"cities","category":{"index":{"AL001C1":0,"AT001C":1,"AT001C1":2,"BA001C1":3,"BE001C":4,"BE001C1":5,"BG001C":6,"BG001C1":7,"CH001C1":8,"CY001C":9,"CY001C1":10,"CZ001C":11,"CZ001C1":12,"DE001C":13,"DE001C1":14,"EE001C":15,"EE001C1":16,"EL001C":17,"EL001C1":18,"ES001C":19,"ES001C1":20,"FI001C":21,"FI001C2":22,"FR001C":23,"FR001C1":24,"HR001C":25,"HR001C1":26,"HU001C":27,"HU001C1":28,"IS001C":29,"IS001C1":30,"IT001C":31,"IT001C1":32,"LI002C1":33,"LT001C":34,"LT001C1":35,"LU001C":36,"LU001C1":37,"LV001C":38,"LV001C1":39,"ME001C":40,"MK001C":41,"MT001C":42,"MT001C1":43,"NO001C":44,"NO001C1":45,"PL001C":46,"PL001C1":47,"PT001C":48,"PT001C1":49,"RO001C":50,"RO001C1":51,"RS001C":52,"SI001C":53,"SI001C1":54,"SK001C":55,"SK001C1":56,"TR012C1":57,"UK001C":58,"UK001K2":59,"UK004C":60,"UK004C1":61,"UK012C":62,"UK012C1":63,"XK001C":64},"label":{"AL001C1":"Tiranë","AT001C":"Wien","AT001C1":"Wien","BA001C1":"Sarajevo","BE001C":"Bruxelles / Brussel","BE001C1":"Bruxelles / Brussel","BG001C":"Sofia","BG001C1":"Sofia","CH001C1":"Zürich","CY001C":"Lefkosia","CY001C1":"Lefkosia","CZ001C":"Praha","CZ001C1":"Praha","DE001C":"Berlin","DE001C1":"Berlin","EE001C":"Tallinn","EE001C1":"Tallinn","EL001C":"Athina","EL001C1":"Athina","ES001C":"Madrid","ES001C1":"Madrid","FI001C":"Helsinki","FI001C2":"Helsinki / Helsingfors","FR001C":"Paris","FR001C1":"Paris","HR001C":"Zagreb","HR001C1":"Zagreb","HU001C":"Budapest","HU001C1":"Budapest","IS001C":"Reykjavik","IS001C1":"Reykjavík","IT001C":"Roma","IT001C1":"Roma","LI002C1":"Schaan","LT001C":"Vilnius","LT001C1":"Vilnius","LU001C":"Luxembourg (city)","LU001C1":"Luxembourg","LV001C":"Riga","LV001C1":"Riga","ME001C":"Podgorica","MK001C":"Skopje","MT001C":"Valletta","MT001C1":"Valletta","NO001C":"Oslo","NO001C1":"Oslo","PL001C":"Warszawa","PL001C1":"Warszawa","PT001C":"Lisboa","PT001C1":"Lisboa","RO001C":"Bucuresti","RO001C1":"Bucuresti","RS001C":"Beograd","SI001C":"Ljubljana","SI001C1":"Ljubljana","SK001C":"Bratislava","SK001C1":"Bratislava","TR012C1":"Istanbul","UK001C":"London","UK001K2":"London (greater city)","UK004C":"Glasgow","UK004C1":"Glasgow","UK012C":"Belfast","UK012C1":"Belfast","XK001C":"Pristinë"}}},"time":{"label":"time","category":{"index":{"2008":0,"2009":1,"2010":2,"2011":3,"2012":4,"2013":5,"2014":6},"label":{"2008":"2008","2009":"2009","2010":"2010","2011":"2011","2012":"2012","2013":"2013","2014":"2014"}}}}}}'

In [33]:
collection_2 = jsonstat.from_file(file_path_2)
collection_2


Out[33]:
JsonstatCollection contains the following JsonStatDataSet:
posdataset
0'crim_hom_ocit'

In [34]:
crim_hom_ocit = collection_2.dataset('crim_hom_ocit')
crim_hom_ocit


Out[34]:
name: 'crim_hom_ocit'title: 'Intentional homicide offences in largest cities'size: 455
posidlabelsizerole
0unitunit1
1citiescities65
2timetime7

In [35]:
# Dimensioni Geo e Time
df_eur_cit = crim_hom_ocit.to_table(content='id',rtype=pd.DataFrame)

In [36]:
# Filtro solo 2014
df_eur_cit=df_eur_cit[(df_eur_cit['time']=='2015')]

In [37]:
df_eur_cit = df_eur_cit.drop('time', 1)
df_eur_cit = df_eur_cit.drop('unit', 1)

In [38]:
# Top 10 Città pericolose
df_eur_cit_top10per=df_eur_cit.sort_values(by='Value',ascending=False).head(10)
# df_eur_cit_top10per

In [39]:
df_name = pd.read_excel(os.path.join(dir_df, 'NUTS3.xls'), sheetname='Local information')

In [104]:
# df_name.head(5)

In [40]:
df_name = df_name.drop('NUTS0', 1)
df_name = df_name.drop('NUTS 3 ID (2010)', 1)
df_name = df_name.drop('NUTS 3 2010 code and name', 1)
df_name = df_name.drop('UA city in NUTS 3', 1)
df_name = df_name.drop('Functional Urban Zone code', 1)
df_name = df_name.drop('Port in NUTS 3', 1)
df_name = df_name.drop('Port ID', 1)
df_name = df_name.drop('Name of the port', 1)
df_name = df_name.drop('Remark', 1)

In [106]:
# df_name.head(5)

In [41]:
df = pd.merge(df_eur_cit_top10per, df_name, how='left',left_on='cities', right_on='City code')
df = df[pd.notnull(df['City name'])]
#df

In [42]:
df_eur_cit_filename = r'df_eur_cit.pkl'
df_eur_cit_fullpath = os.path.join(dir_df, df_eur_cit_filename)
df.to_pickle(df_eur_cit_fullpath)