FP7 analysis

fp7_analysis.cql

//-----------------------
//Number of organizations
//-----------------------
MATCH (a:ORGANIZATION)
Return count(a) as total_organizations;

//-----------------------
//Number of projects
//-----------------------
MATCH (a:PROJECT)
Return count(a) as total_projects;

//-----------------------
//Total FP7 budget
//-----------------------
MATCH (a:PROJECT)
Return sum(a.totalCost) as total_budget

//-----------------------
//Project with the most relationships with organizations
//-----------------------

MATCH (a:ORGANIZATION)-[r:IS_RESPONSIBLE_OF]->(b:PROJECT)
RETURN b, count(r) as count, collect(a.organisationName) as names
ORDER BY count DESC
LIMIT 10

//-----------------------
//What programs are French organizations involved in?
//-----------------------

MATCH (a:COUNTRY {country:'FR'})-[IS_COUNTRY_OF]->(b:ORGANIZATION)-[IS_RESPONSIBLE_OF]->(c:PROJECT)-[r:IS_PART_OF_PROGRAM]->(d:PROGRAM)
RETURN d, count(r) as count
ORDER BY count DESC
LIMIT 10

//-----------------------
//What projects are French organizations involved in?
//-----------------------

MATCH (a:COUNTRY {country:'FR'})-[IS_COUNTRY_OF]->(b:ORGANIZATION)-[r:IS_RESPONSIBLE_OF]->(c:PROJECT)
RETURN c, count(r) as count
ORDER BY count DESC
LIMIT 10

//-----------------------
//Which organizations are involved in the most projects?
//-----------------------

MATCH (a:ORGANIZATION)-[r:IS_RESPONSIBLE_OF]->(c:PROJECT)
RETURN a, count(r) as count
ORDER BY count DESC
LIMIT 10

//-----------------------
//Which organizations are involved in the most projects as coordinator?
//-----------------------

MATCH (a:ORGANIZATION)-[r:IS_RESPONSIBLE_OF {organisationRole: 'coordinator'}]->(c:PROJECT)
RETURN a, count(r) as count
ORDER BY count DESC
LIMIT 10

//-----------------------
//Projection to create graph of co-occurence for organizations in projects aka bipartite graph projection
//-----------------------
MATCH (organization:ORGANIZATION)-[:IS_RESPONSIBLE_OF]->(a:PROJECT)<-[:IS_RESPONSIBLE_OF]-(ally:ORGANIZATION)
//MATCH (entity:Entity)-[:LINKS_TO]->(doc:Document)
//CREATE (organization)-[:COOPERATE_WITH]->(ally);
WITH organization, collect(ally) as allies 
FOREACH (e1 in allies | FOREACH (e2 in filter(e in allies WHERE id(e) > id(e1)) | CREATE (e1)-[:RELATED_TO]->(e2)))


//-----------------------
//Which projects cost the most?
//-----------------------
MATCH (a:PROJECT)
RETURN a
ORDER BY a.totalCost DESC
LIMIT 10

//-----------------------
//Which programmes have received the most funding over FP7’s entire lifecycle?
//-----------------------
MATCH (a:PROJECT)-[:IS_PART_OF_PROGRAM]->(b:PROGRAM)
RETURN b, sum(a.ecMaxContribution) as cost
ORDER BY cost DESC
LIMIT 10

//-----------------------
//Which projects share the most participants?
//-----------------------
MATCH (a:PROJECT)<-[:IS_RESPONSIBLE_OF]-(b:ORGANIZATION)-[:IS_RESPONSIBLE_OF]->(c:PROJECT)
RETURN a, c, count(b) as affinity
ORDER BY affinity DESC
LIMIT 10

//-----------------------
//Which organizations share the most projects?
//-----------------------
MATCH (a:ORGANIZATION)-[:IS_RESPONSIBLE_OF]->(b:PROJECT)<-[:IS_RESPONSIBLE_OF]-(c:ORGANIZATION)
RETURN a, c, count(b) as affinity
ORDER BY affinity DESC
LIMIT 10

//-----------------------
//Which projects is connected to the most themes?
//-----------------------
MATCH (a:SUBJECT)-[:IS_SUBJECT_OF]-(b:PROJECT)
RETURN a, count(b) as score
ORDER BY score DESC
LIMIT 10

//-----------------------
//Bipartite graph projection for projects
//-----------------------
MATCH (a:PROJECT)<-[:IS_RESPONSIBLE_OF]-(b:ORGANIZATION)-[:IS_RESPONSIBLE_OF]->(c:PROJECT)
WITH a, collect(c) as links 
FOREACH (e1 in links | FOREACH (e2 in filter(e in links WHERE id(e) > id(e1)) | CREATE (e1)-[:RELATED_TO]->(e2)))

//-----------------------
//Subgraph for the environment program
//-----------------------
export-graphml -t -o graph.graphml MATCH (a:PROGRAM {programmePga: 'FP7-ENVIRONMENT'})<-[IS_PART_OF_PROGRAM]-(b:PROJECT)<-[r:IS_RESPONSIBLE_OF]-(c:ORGANIZATION) return b,c,r
import-graphml -i graph.graphml -b 20000 -t

//-----------------------
//Betweeness centrality for organizations
//-----------------------
MATCH p=allShortestPaths((source:ORGANIZATION)-[*]-(target:ORGANIZATION))
WHERE id(source) < id(target) and length(p) > 1
UNWIND nodes(p)[1..-1] as n
RETURN n, count(*) as betweenness
ORDER BY betweenness DESC
LIMIT 5

//-----------------------
//Which organizations is collaborating with the most organizations
//-----------------------
MATCH (a:ORGANIZATION)-[:IS_RESPONSIBLE_OF]->(b:PROJECT)<-[:IS_RESPONSIBLE_OF]-(c:ORGANIZATION)
RETURN a, count(c) as score
ORDER BY score DESC
LIMIT 10