Using Graph Databases for Investigative Journalism workshop with Leila Haddou


Expediting and visualising data analysis Using Neo4J.

Building a node (your data point) and your database:

Note: Syntax not case sensitive but labels are.

CREATE (n:Node{ id:"ID",attribute:attribute})

() creating the bubble aka the node
CREATE command blindly goes through each line of the csv looking for matches.
MERGE command goes through every line and checks if it is the only instance and treats multiple entries as the same thing.
n: – assign the node label
Node – assigned a type (case sensitive)
{} – the attributes of the node, there can be multiple separated with commas

Note: If you have spaces in your header you need to put this character ` around the text string ie `description 1`

Example:
LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/leilahaddou/graph-data/master/pef.csv" AS line
MERGE (d:Donor {name:line.DonorName, status:line.DonorStatus})
MERGE (r:Recipient {name:line.EntityName})
CREATE (d)-[dt:DONATED_TO {amount:line.Value, ref:line.ECRef, date:line.RecivedDate}]->(r)
Connecting nodes
CREATE (l)-[r:knows]->(t)
r: label
knows - relationship type
Bringing in csv data
LOAD CSV WITH HEADERS FROM "http://www.nifer.co.uk/wp-content/uploads/2015/10/CAA500.csv" AS line
Viewing your
Strings VS Integers

“47” + “23” would output4723

amount:toInt(line.Value)

This turns the output into an integer not a string.

Querying
MATCH (x:Donor) RETURN x LIMIT 10
MATCH (d:donor)-[dt:DONATED_TO]->(r:Recipient) RETURN * LIMIT 10

This example matches the donor where the donor

MATCH d WHERE d.status= "Company"
RETURN d
LIMIT 20

You only need to bracket () the node name (in this case ‘d’) when creating a node, to query it you just refer to it.

Fuzzy Matching – string query which includes “a bit of”. Change the NAME to an individual in the

MATCH r WHERE r.name=~".*NAME.*"
Return r

Who has given to both the conservatives and labour party:

MATCH (d)-[dt1:DONATED_TO]-(l), (d)-[dt2: DONATED_TO]->(c)
WHERE l.name=~".*Labour.*"
AND c.name=~".*Conservative.*"
RETURN d

Find a donor who has donated to recipient 1 and recipient 2 where recipient 1 is not recipient 2 and they are a company:

Match (d:Donor)-[dt1:DONATED_TO]->(r1), (d)-[dt2:DONATED_TO]->(r2)
WHERE r1 <> r2
AND d.status= "Company"
RETURN*
LIMIT 20

Bristol Cable and Centre for Investigative Journalism (Goldsmiths)

  • CAA plane data: CAA500
    Register of private planes registered by the UK Civil Aviation Authority (first 500 only) Source: CAA
  • To delete all nodes and relationships:
    MATCH (n)
    OPTIONAL MATCH (n)-[r]-()
    DELETE n,r;
  • Political Donations: CAA500
    Cash donations made to polticians and political party between January 2010 and June 2015. Source: PEF online

Part two

  • Cypher refcard
    Creating Nodes and Relationships
    Building nodes and relationships always begin with the CREATE command.
    To create a node, you need to use the following syntax: CREATE (x:Label {property:Property})
    To create a relationship: CREATE (x)-[r:CONNECTED_TO]->(y)
    Delete entire databases in the terminal:
    neo4j stop && rm -rf /usr/local/Cellar/neo4j/*/libexec/data/* && neo4j start

MISC:

Example: Open Corporate api allows you to retrieve company’s house data (retrieved using a scraper) so you can match data based on company number for example. Cross matching ?

Cleaning data – when finding pattens things like blanks in data or several entries with the same name spelled different ways will misrepresent your results.  So your methadology of data visualising might need to be explicit that you are grouping Shell plc & Shell Ltd into one

Gephi – alternative graph database software.

Open Refine – data cleaning automation

periodic commit (save every 100 lines)

NoSQL databases therefore structured query language cannot

linkurio.us – visualising graph data – great inspiration blog including explanations of how they do what they do

JournoCoders – london based group run by Leila Haddou learning tools together

Global Investigative journalism network 100 best data basesTip Sheet

Web scraper tools : Kimono  import.io

David Donald weeklong bootcamp

Paul Mayer’s digital investigation tools and tips

New Economy Organisers Network

Neon‘s Campaign Fundraising workshop notes.

Fundraising through philanthropic foundations application process cycle based on a funder and fundraisers presentations:

  • What you want to do? How far can you get without finding? Plan with a team, people are assets not just beneficiaries. Hope for the best, plan for the worst.
  • Find some one who wants to do it. Collect a database of potential funders, look at successful bids. Funders want to hope for a better world, they have an agenda and targets for giving.
  • Be able to give a 3 minute pitch then phone ahead of applying to test the water so not to waste everyone’s time on inappropriate applications. Bate the funder to ask questions. Don’t just talk about your self, ask questions about the funder, are you a right fit?
  • Request feedback and tweak plans.
  • Write the bid, if possible share it with the funder for feedback before submitting. Tips:
    1. Clarity – application needs 1 sentence explanation. Funders wont spend more than 5 minutes reading an application. Be aware explaining the need is easy but explaining the strategy is more important. I.E. the living wage spent a while shaming employers into committing to the it, this had limited effect. By commissioning research to show that it benefited the business as well as they employees they responded to the positive reputation benefit more than the shame.
    2. Why will your strategy work? What is your theory of change?
    3. Every campaign is a series of mile stones i.e. make an alliance of interested groups, work out who your audience is… Where are you in the journey? Why now? What opportunities are arising?
    4. Why hasn’t your campaign worked yet? Or Why has it? e.g. shifting attitudes, what audience/message. Commission research and evaluate plans. What are your indicators of success?
    5. Understand your adaptability.
    6. Why now?
    7. Niche is sometime easier because there is little competition, if you are working in a crowded field, how are you working with other campaigns? What is your team? Who are you and what legitimacy do you have around this issue?
    8. Targets – audience can’t be too broad. What particular group? i.e. 5 MP’s and 2 particular special advisor?
    9. How much will it cost? Value for money is important. Do not ask for more than 13% – 19% core costs, ask for campaign costs.
    10. What happens afterwards? What follow through? For example how will you get more resources for policy to become practise i.e. will police/councils enforce policy?
  • Make sure you have a bank account.
  • SAY THANK YOU. Treat them like a friend, listen, be honest & upfront. Be able to say no to funding and make sure you don’t change your plan to fit funders, do what you WANT.
  • Evaluate your campaign, get evidence.
  • Send feedback from evaluation.
  • Back to the start with what you want to do NEXT.
 We were also introduced to the Edge Fund which is a community led funding model which promotes rights not charity. They have a great guide which covers different types of grant funding including their own. Edge Fund DRAFT Fundraising For Grassroots Social Justice Groups Guide

 

Designing Authentic Fictions with Ken Eklund

A Fantastic day long workshop with Ken Eklund (organised by the amazing Jess Linington for the Digital Cultures Research Centre hosted at the Pervasive Media Centre… I’m always wowed by how much INTERESTING one city can hold) where Ken with puppet master skills gamed us into designing and pitching an authentic fiction targeting an ecological issue which we were to budget for & pitch to a dummy panel of generous industry pros who gave up their time to hear and give feedback.

Ken’s process perfectly demonstrated his ideas giving us a self-persuasive learning experience. What a clever chappy.

The essence of it was –

Authentic: Multi-sourced & multi-authored. Open to new information. Emergent.

Fiction: Immersive. Playful. What if Scenario.

These things together bring about play, a self persuasive learning experience.

It was really challenging, when presented with Ken’s amazing projects it was hard to focus on the learning objectives of the workshop rather than with coming up with a worthy idea & pitch. The nice folks I was grouped with had very different objectives & motivations which was great because it pushed the edges of my ideas but was also challenging because we didn’t agree on anything. At all. Almost… Lucky for us they were totally great characters and our discourse left us stimulated & provoked in the best possible way.

The idea we finished with was to take data on the agricultural output of the UK and create a days food stamps using only an evenly distributed & accurate representation of what would be available to a person if we only had British Isle grown food. The bleak prospect of drastically reduced intake & variety would come with an invitation to contribute to a digital archive with a creative but real world response to what you would do in this situation and having submitted those responses to then document & submit a recipe (with photo) of the meal using only the ingredients fictitiously available to you. The event would culminate in a pop up restaurant serving the most up voted responses & recipe outcomes.

The hope is that in considering the problem of large scale mono-crop agriculture & reliance on imported goods the food stamp fiction would challenge people to seek out community agricultural projects, allotments, locally produced green grocers, organizations like the the Land Workers Alliance or Farm Hack as well as being an open enough format for new information to emerge and contribute to a hopeful narrative about what we will eat, culminating in actually eating it.

Having spent hundreds of hours researching food issues while involved in Rising Up, food security & the disconnect between local government strategies from our food needs is really high on my personal agenda as an activist. I’m really grateful to the pair I was teamed with for bringing their specialisms and experience to the issue I wanted to tackle.

Ken EklandPhoto credit: Jess Linington