Main Filters

~technical - ~ENSAE - ~recreative

XD blog

blog page

excel, fusion, merge, pandas, python, tableau


2013-11-21 Fusionner deux tableaux

L'exercice est le suivant : on récupère deux tableaux depuis le site http://www.data.gouv.fr/. On récupère les deux fichiers suivants :

Ils contiennent des effectifs de la fonction publique ventilés par ministères :

Ministère    Libellé    Catégorie d'emploi    Emploi    ETPPLF
1    Affaires étrangères    1101    Titulaires et CDI en administration centrale    3 059
1    Affaires étrangères    1102    Titulaires et CDI dans le réseau    2 895
1    Affaires étrangères    1103    CDD et volontaires internationaux    2 877
1    Affaires étrangères    1104    Militaires    712
1    Affaires étrangères    1105    Agents de droit local    4 962
...

On cherche à mesurer l'évolution des effectifs entre ces deux années même si la liste des ministères et des catégories évolue.

Les deux fichiers ont été téléchargés sous les noms 2012.csv et 2014.csv. On utilise d'abord le langage Python seul pour construire ces différences. Une des colonnes contient un identifiant unique pour chaque ligne. Il conserve la même signification deux ans plus tard. L'astuce consiste à créer un dictionnaire { id : [ ligne ] } . Si les deux fichiers ont des chiffres comparables, alors les deux dictionnaires construits auront des clés en commun.

# on charge le premier fichier
f = open("2014.csv","r")
lines = f.readlines()
f.close()

lines2014 = [ l.strip("\n").split(";") for l in lines ]

# même code copié/collé pour le second fichier
f = open("2012.csv","r")
lines = f.readlines()
f.close()

lines2012 = [ l.strip("\n").split(";") for l in lines ]

d2012 = { l[2]:l for l in lines2012 if len(l[2]) > 0}
d2014 = { l[2]:l for l in lines2014 if len(l[2]) > 0}

# les catégoriés supprimées en 2014
for code in d2012 :
    if code not in d2014 :
        print ("-",code, d2012[code])
        
# les catégories ajoutées en 2014
for code in d2014 :
    if code not in d2012 :
        print ("+",code, d2014[code])
    
# on calcule la différence    
for code in d2012 :
    if code in d2014 :
        if len(code) > 5 : continue  # pas assez de colonne = ligne d'en-tête ou autre
        # il faut supprimer les espaces et convertir en réels 
        d = float(d2014[code][4].replace(" ","")) - float(d2012[code][5].replace(" ",""))
        sum = d2012[code] + d2014[code] + [str(d) ]
        # on affiche les deux lignes
        print ("\t".join(sum))
        # il suffira de copier/coller le résultat dans Excel

La seconde option est d'utiliser le module pandas qui facilite l'écriture des opérations qu'on vient juste de faire.

# coding:latin-1
import pandas
t2012 = pandas.read_csv("2012.csv", sep=";", encoding="latin-1", thousands = " ")
t2012 = t2012[["Catégorie d'emploi", "ETPPLF"]]    

t2014 = pandas.read_csv("2014.csv", sep=";", encoding="latin-1", thousands = " ")
t2014 = t2014[["Catégorie d'emploi", "ETPPLF"]]    

# l'opération suivante correspond à un JOIN (en SQL)
m = t2012.merge(t2014, left_on="Catégorie d'emploi",
        right_on ="Catégorie d'emploi",
        how='outer')
        
m ["delta"] = m["ETPPLF_y"] - m["ETPPLF_x"]  # les colonnes ont des suffixes _x et _y pour les distinguer
m.to_csv("result.txt", sep="\t")

Les premières lignes des deux fichiers ont été supprimées car elles empêchaient pandas de détecter correctement les noms des colonnes. Le fait d'écrire how='outer' permet de conserver les catégories qui n'auront pas été appariées. Le programme contient deux lignes qui réduisent le nombre de colonnes (t2014 = t2014[["Catégorie d'emploi", "ETPPLF"]]).

#coding:latin-1
import pandas
t2012 = pandas.read_csv("a2012.csv", sep=";", encoding="latin-1", thousands = " ")
t2014 = pandas.read_csv("a2014.csv", sep=";", encoding="latin-1", thousands = " ")

m = t2012.merge(t2014, left_on="Catégorie d'emploi",
		right_on ="Catégorie d'emploi",
		how='outer')
        
m ["delta"] = m["ETPPLF_y"] - m["ETPPLF_x"]
m.to_csv("result.txt", sep="\t")

Connaître SQL est utile pour utiliser pandas car les fonctions du module suivent la même logique. Quelques liens :

On retrouve également la même logique sous R : merge (exemple).

En langage SQL, cela donne la requête suivante~:

SELECT at2012.*, at2014.*
FROM at2012 FULL OUTER JOIN at2014
ON at2012.col2 == at2014.col2

Si vous testez cela avec SQLite (avec SQLiteSpy.exe par exemple). Il est seulement possible de faire un LEFT OUTER JOIN. Pour arriver à ses fins, il faut procéder comme suit :

SELECT at2012.*, at2014.*
FROM at2012 LEFT OUTER JOIN at2014
ON at2012.col2 == at2014.col2

UNION ALL

SELECT at2012.*, at2014.*
FROM at2014 LEFT OUTER JOIN at2012
ON at2012.col2 == at2014.col2
WHERE at2012.col2 is null

Cela revient à faire deux LEFT OUTER JOIN en permutant les deux tables. Lors du second JOIN, on ne garde que les lignes non appariés. Les autres ayant déjà été traitées lors du premier JOIN.

Pour ceux qui souhaitent essayer, le programme suivant permet de nettoyer les données de sorte qu'elles puissent facilement être transférées dans une base de données SQLite.

#coding:latin-1
import pandas
t2012 = pandas.read_csv("a2012.csv", sep=";", encoding="utf8", thousands = " ")
t2014 = pandas.read_csv("a2014.csv", sep=";", encoding="utf8", thousands = " ")
t2012.columns = ["col%d" % i for i in range(len(t2012.columns)) ]
t2014.columns = ["col%d" % i for i in range(len(t2014.columns)) ]
t2012 = t2012 [ [ "col%d" % i for i in range(6) ] ]
t2014 = t2014 [ [ "col%d" % i for i in range(5) ] ]
t2014 = t2014.ix [ t2014["col0"].map( lambda row : not row.startswith("Total")), : ]

t2012.to_csv("at2012.csv", sep="\t", encoding="utf8", index=False)
t2014.to_csv("at2014.csv", sep="\t", encoding="utf8", index=False)

from pyensae import import_flatfile_into_database
dbf = "a2012_2014.db3"
import_flatfile_into_database(dbf, "at2014.csv")
import_flatfile_into_database(dbf, "at2012.csv")

<-- -->

Xavier Dupré