FEC Import

README

Graph.db dump available here: https://dl.dropboxusercontent.com/u/67572426/FEC_2015_graph.db.zip

FEC-2015.cql

// Import Federal Election campaign contribution data into Neo4j

// /path/to/neo4j/bin/neo4j-shell < FEC-2015.cql

//first let us create indexes!
CREATE INDEX ON :Candidate(fullName);
CREATE INDEX ON :Candidate(lastName);
CREATE INDEX ON :Candidate(candidateID);
CREATE INDEX ON :Contributor(fullName);
CREATE INDEX ON :Contributor(occupation);
CREATE INDEX ON :Employer(name);
CREATE INDEX ON :Zipcode(zip);
CREATE INDEX ON :Day(day);
CREATE INDEX ON :Month(month);
CREATE INDEX ON :Year(year);
CREATE INDEX ON :Contribution(transactionID);
CREATE INDEX ON :State(name);
//now candidates
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line
MERGE (c:Candidate {candidateID:line.cand_id})
ON CREATE SET  c.firstName = line.cand_first, c.lastName = line.cand_last, c.candidateID = line.cand_id, c.fullName = line.cand_full;
//
//now, contributors
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line
MERGE (c:Contributor {fullName:line.contbr_full})
ON CREATE SET c.firstName = line.contbr_first, c.lastName = line.contbr_last, c.occupation = line.contbr_occupation;
//
//employers
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line
MERGE (e:Employer {name:line.contbr_employer});
//
//who works for whom?
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line
MATCH (c:Contributor {fullName:line.contbr_full}), (e:Employer {name:line.contbr_employer})
MERGE (c)-[:EMPLOYED_BY]->(e);
//
//States, Zips, Full Zips
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line
MERGE (s:State {name:line.contbr_st});
//
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line, toINT(line.contbr_zip_5) as zipC
MERGE (z5:Zipcode {zip:zipC});
//
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line, toINT(line.contbr_zip_5) as zipC
MATCH (s:State {name:line.contbr_st}), (z5:Zipcode {zip:zipC})
MERGE (s)-[:HAS_ZIPCODE]->(z5);
//
//who lives where?
USING PERIODIC COMMIT 50000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line, toINT(line.contbr_zip_5) as zip
MATCH (c:Contributor {fullName:line.contbr_full}), (z:Zipcode {zip:zip})
MERGE (c)-[:LIVES_IN]->(z);
//
//time tree of election cycle!
WITH range(2012, 2016) AS years, range(1,12) as months
FOREACH(year IN years |
  MERGE (y:Year {year: year})
  FOREACH(month IN months |
    CREATE (m:Month {month: month})
    MERGE (y)-[:HAS_MONTH]->(m)
    FOREACH(day IN (CASE
                      WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31)
                      WHEN month = 2 THEN
                        CASE
                          WHEN year % 4 <> 0 THEN range(1,28)
                          WHEN year % 100 <> 0 THEN range(1,29)
                          WHEN year % 400 <> 0 THEN range(1,29)
                          ELSE range(1,28)
                        END
                      ELSE range(1,30)
                    END) |
      CREATE (d:Day {day: day})
      MERGE (m)-[:HAS_DAY]->(d))));
//
//creating contributions
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line, toINT(line.file_num) as fileNum, toINT(line.contb_receipt_amt) as amount
CREATE (c:Contribution {form:line.form_tp, fileNumber:fileNum, transactionID:line.tran_id, amount:amount});
//
//when contributions occured
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
WITH line, toINT(line.contb_day) as day, toINT(line.contb_mo) as month, toINT(line.contb_yr) as year
MATCH (c:Contribution {transactionID:line.tran_id}), (d:Day {day:day})<-[:HAS_DAY]-(:Month {month:month})<-[:HAS_MONTH]-(:Year {year:year})
WITH c, d
MERGE (c)-[:CONTRIBUTED_ON]->(d);
//
//now, who is contributing what?!
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
with line
MATCH (c:Contribution {transactionID:line.tran_id}), (contrib:Contributor {fullName:line.contbr_full})
MERGE (contrib)-[:MADE_CONTRIBUTION]->(c);
//
//who did that money go to?
USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'https://dl.dropboxusercontent.com/u/313565755/FEC-2015.csv' AS line
with line
MATCH (c:Contribution {transactionID:line.tran_id}), (pres:Candidate {candidateID:line.cand_id})
MERGE (pres)<-[:RECIEVED_CONTRIBUTION]-(c);