Skip to content

Instantly share code, notes, and snippets.

@jvilledieu

jvilledieu/sunshine_import.cql Secret

Last active Aug 29, 2015
Embed
What would you like to do?
Charger les données Open Data sur les cadeaux et contrats versés par les laboratoires pharmaceutiques aux praticiens de santé entre janvier 2012 et juin 2014 dans Neo4j. Source : http://www.regardscitoyens.org/sunshine/
//-----------------------
//Importer les docteurs
//-----------------------
CREATE CONSTRAINT ON (a:DOCTOR) ASSERT a.ID IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/sunshine.anonymes.csv" AS line
FIELDTERMINATOR ','
MERGE (a:DOCTOR {ID: line.BENEF_PS_ID})
ON CREATE SET a.ORIGIN= line.ORIGIN, a.BENEF_PS_QUALIFICATION= line.BENEF_PS_QUALIFICATION, a.BENEF_PS_DEPARTEMENT= line.BENEF_PS_DEPARTEMENT, a.totalDECL = toInt(line.DECL_AVANT_MONTANT)
ON MATCH SET a.totalDECL = toInt(line.DECL_AVANT_MONTANT) + a.totalDECL;
//-----------------------
//Importer les laboratoires
//-----------------------
CREATE CONSTRAINT ON (a:PHARMA) ASSERT a.NAME IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/sunshine.anonymes.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.LABO IS NOT NULL
MERGE (a:PHARMA {NAME: line.LABO})
ON CREATE SET a.totalDECL = toInt(line.DECL_AVANT_MONTANT)
ON MATCH SET a.totalDECL = toInt(line.DECL_AVANT_MONTANT) + a.totalDECL;
//-----------------------
//Importer les dons individuels des laboratoires aux médecins
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/sunshine.anonymes.csv" AS line
FIELDTERMINATOR ','
MATCH (b:DOCTOR {ID: line.BENEF_PS_ID})
MATCH (a:PHARMA {NAME: line.LABO})
CREATE (a)-[:HAS_DONATED {
DECL_AVANT_DATE: line.AMNDT_IND,
DECL_AVANT_NATURE: line.DECL_AVANT_NATURE,
DECL_AVANT_MONTANT: toInt(line.DECL_AVANT_MONTANT),
DECL_CONV_OBJET: line.DECL_CONV_OBJET,
SOURCE: line.SOURCE
}]->(b);
//-----------------------
//Faire aggrégation des dons des laboratoires aux médecins
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/sunshine.anonymes.csv" AS line
FIELDTERMINATOR ','
MATCH (b:DOCTOR {ID: line.BENEF_PS_ID})
MATCH (a:PHARMA {NAME: line.LABO})
MERGE (a)-[r:IS_LINKED_TO]->(b)
ON CREATE SET r.numberDECL = 1, r.totalDECL = toINT(line.DECL_AVANT_MONTANT)
ON MATCH SET r.numberDECL = r.numberDECL +1, r.totalDECL = toINT(line.DECL_AVANT_MONTANT) + r.totalDECL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment