Country Opinions.adoc

Country Opinions

In the video game Elite:Dangerous players can buy and sell commodities at stations around the galaxy. This gist shows how to use Neo4j and Cypher queries for trading decisions in this game.

Import data from Google Spreadsheet

All data is already available in a Google Spreadsheet, which we can directly import as CSV files. To get the CSV URL simply do "Export" as CSV in Google Spreadsheet, and copy&paste the download URL that the browser gets, one per sheet. Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1tx-quo1o4Z3WOWgB6kWTzS62f55mONnii3BVFABzlfA/edit?usp=sharing

LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/swainjo/CountryOpinions/master/Data/BBCWSPollData/countries.csv" AS csvLine
MERGE (c:Country { id: toInt(csvLine.id)})
ON CREATE SET c.name = csvLine.name, c.full_name = csvLine.full_name
ON MATCH SET c.name = csvLine.name, c.full_name = csvLine.full_name;

LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/swainjo/CountryOpinions/master/Data/BBCWSPollData/organisations.csv" AS csvLine
MERGE (o:Organisation { id: toInt(csvLine.id)})
ON CREATE SET o.name = csvLine.name, o.organisation_type = csvLine.organisation_type
ON MATCH SET o.name = csvLine.name, o.organisation_type = csvLine.organisation_type ;

LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/swainjo/CountryOpinions/master/Data/BBCWSPollData/polls.csv" AS csvLine
MERGE (p:Poll { id: toInt(csvLine.id)})
ON CREATE SET p.name = csvLine.name, p.year = toInt(csvLine.year), p.positive = toInt(csvLine.positive),
	p.negative = toInt(csvLine.negative)
ON MATCH SET p.name = csvLine.name, p.year = toInt(csvLine.year), p.positive = toInt(csvLine.positive),
	p.negative = toInt(csvLine.negative);

CREATE CONSTRAINT ON (o:Organisation) ASSERT o.id IS UNIQUE;
CREATE CONSTRAINT ON (c:Country) ASSERT c.id IS UNIQUE;
CREATE CONSTRAINT ON (p:Poll) ASSERT p.id IS UNIQUE;

MATCH (p:Poll) ,(o:Organisation { id: 1})
WHERE p.name =~ 'World Service Global Poll.*'
MERGE (o)-[:CARRIED_OUT]->(p);

LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/swainjo/CountryOpinions/master/Data/BBCWSPollData/polls.csv" AS csvLine
MATCH (c_about:Country{name:csvLine.about }),(p:Poll{id:toint(csvLine.id)})
MERGE (p)-[:POLLED_ABOUT]->(c_about);

LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/swainjo/CountryOpinions/master/Data/BBCWSPollData/polls.csv" AS csvLine
MATCH (c_for:Country{name:csvLine.for }),(p:Poll{id:toint(csvLine.id)})
MERGE (p)-[:POLLED_FOR]->(c_for);

MATCH (country1:Country)-[r1:POLLED_ABOUT]-(poll:Poll)-[r2:POLLED_FOR]-(country2:Country)
MERGE (country1)<-[p:POSITIVE]-(country2)
ON CREATE SET p.value = toInt(poll.positive)
ON MATCH SET p.value = toInt(poll.positive);

MATCH (country1:Country)-[r1:POLLED_ABOUT]-(poll:Poll)-[r2:POLLED_FOR]-(country2:Country)
MERGE (country1)<-[p:NEGATIVE]-(country2)
ON CREATE SET p.value = toInt(poll.negative)
ON MATCH SET p.value = toInt(poll.negative);

Edge List

MATCH (country1:Country)<-[r]-(country2:Country)
RETURN country1.name as Source,r.positive as Positive,r.negative as Negative,
r.positive-r.negative as Weight,type(r) as Label ,country2.name as Target

USA

MATCH (n:`Country`)
WITH n LIMIT 10
MATCH (n1:`Country`)
WHERE n1.name IN n.name
RETURN n1 LIMIT 25;

Average buy price of tea

MATCH (commodity:Commodity {name:"Tea"})<-[buy:BUY]-()
RETURN avg(buy.price)

Who sells gold?

MATCH (commodity:Commodity { name:"Gold" })<-[buy:BUY]-(station)
RETURN station.name