Import Data from City of Oakland Financial Disclosures

income_vs_expenditure.cyp

MATCH (f:Candidate)
MATCH (f)<-[n:CONTRIBUTED_TO]-()
WITH f, sum(n.amount) AS received WHERE received > 0
MATCH (f)-[n:PAYED]->()
WITH f, received, sum(n.amount) AS spent
RETURN f.name AS candidate, spent, received

load_a_contributions.cyp

LOAD CSV WITH HEADERS FROM "file:///Users/cleishm/Downloads/A-Contributions.csv" AS line
WITH line,
CASE line.Entity_Cd
  WHEN "IND" THEN line.Tran_NamF + " " + line.Tran_NamL
  ELSE line.Tran_NamL END AS contributorName
MERGE (f:Candidate {id: line.Filer_ID})
ON CREATE SET
  f.name = line.Filer_NamL,
  f.committeeType = line.Committee_Type
MERGE (c:Contributor {name: contributorName})
ON CREATE SET
  c.zip = substring(line.Tran_Zip4, 0, 5),
  c.occupation = line.Tran_Occ
MERGE (c)-[:CONTRIBUTED_TO {amount: toInt(round(coalesce(toFloat(line.Tran_Amt1), 0.0))), date: line.Tran_Date, desc: line.Tran_Dscr}]->(f)
FOREACH(n IN (CASE line.Tran_Emp WHEN "" THEN [] else [line.Tran_Emp] END) |
  MERGE (e:Employer {name: n})
  MERGE (c)-[:EMPLOYED_BY]->(e)
)
FOREACH(n IN (CASE line.Tran_City WHEN "" THEN [] else [line.Tran_City] END) |
  MERGE (p:City {name: n, state: line.Tran_State})
  MERGE (c)-[:LOCATION]->(p)
);

load_e_expenditure.cyp

LOAD CSV WITH HEADERS FROM "file:///Users/cleishm/Downloads/E-Expenditure.csv" AS line
WITH line,
CASE line.Entity_Cd
  WHEN "IND" THEN line.Payee_NamF + " " + line.Payee_NamL
  ELSE line.Payee_NamL END AS vendorName
MERGE (f:Candidate {id: line.Filer_ID})
ON CREATE SET
  f.name = line.Filer_NamL,
  f.committeeType = line.Committee_Type
MERGE (v:Vendor {name: vendorName})
MERGE (f)-[:PAYED {amount: toInt(round(coalesce(toFloat(line.Amount), 0.0))), date: line.Expn_Date, desc: line.Expn_Dscr}]->(v)
FOREACH(n IN (CASE line.Payee_City WHEN "" THEN [] else [line.Payee_City] END) |
  MERGE (p:City {name: n, state: line.Payee_State})
  MERGE (v)-[:LOCATION]->(p)
);

oakland_contributions.cyp

MATCH (f:Candidate)
MATCH (f)<-[n:CONTRIBUTED_TO]-(c)
WHERE (c)-[:LOCATION]->({name:'Oakland', state:'CA'})
WITH f, sum(n.amount) AS oakContributions
MATCH (f)<-[n:CONTRIBUTED_TO]-(c)
WITH f, oakContributions, sum(n.amount) as total
RETURN f.name AS candidate, round((toFloat(oakContributions) / total) * 100) AS percentage

top_contributors.cyp

MATCH (f:Candidate)<-[n:CONTRIBUTED_TO]-(c)
WITH f, c, sum(n.amount) AS amount ORDER BY amount DESC
RETURN f.name AS candidate, collect({name: c.name, amount: amount})[0..10] AS contributors