Media, Politics and Graphs

My dear friend and neo4j community member Ron recently pointed me to an amazing piece of work. Thomas Boeschoten, of the Utrecht Data School among many other things, published some amazing work of analysing the Dutch Talk Shows from different perspectives, using Gephi as one of his tools.  Some of his results are nothing short of fascinating, and very cool to look at.

Now I think the world needs more people like Thomas: media have long abandoned political neutrality, and as citizens, we owe it to ourselves to understand the politics of the things that we see, read and hear in the media. Without it, democracy will be short lived and (to paraphrase de Maistre) we "will get the government we deserve". We need to understand this interplay between media and politics - and graphs can help there.

I will not try to help you understand the depths of Thomas' research (just visit his site - lots of cool stuff there, but mostly in Dutch), I would just like to take this dataset - which he kindly shared - for a spin using neo4j. 

Importing the dataset

As Thomas visibly already is a graphista through and through, he shared his datasets with me as a Gephi file. So that made it really easy to get the initial stuff into neo4j: all I needed to do was use the recently updated neo4j-Gephi plugin and generate the data store files. Copy those over to my neo4j server's data directory, call it graph.db and boom - we're done!

However, when I fired up the server, I soon found out that I would have to do some work :) ... the graph that Thomas created did not really have a "database-like" model (it did not do any normalisation of the model, for instance) - and the neo4j browser looked a bit boring:

I needed to add some structure to this all, in order to be able to query it meaningfully.

Adding a model

After browsing around through the data, I decided that the model that I would be playing with would look something like this:

You can see that it is not a very big graph:

but it is quite densely connected - it has a lot of relationships between the nodes:

So now I can do some more interesting queries on the data, and see if - like in Thomas' research - I kind find out some interesting stuff about this dataset.

Take it for a spin: CYPHER queries!

Let's start with some simple queries. Let's figure out how many people have visited the different shows:

match (g:GUEST)-[v:VISITED]->(sh:SHOW)

return sh.id as Show, count(v) as NrOfVisits

order by NrOfVisits desc;

And we immediately get a feel for the dominant talkshows:

But then let's see how many of these talkshow guests are politicians (or have political affiliations at least). Let's expand the query a bit:

match (g:GUEST)-[v:VISITED]->(sh:SHOW),

g-[:AFFILIATED_WITH]->(p:PARTY)

return sh.id as Show, count(v) as NrOfVisits

order by NrOfVisits desc;

And see if there is any difference in the way the shows are ranked:

Interesting. There are indeed some differences, as you can see.

Now let's look at another perspective in our dataset: Gender. Let's look at the distribution of male/female guests to all of these shows:

match (g:GUEST)-[:HAS_GENDER]->(gen:GENDER),

(g)-[v:VISITED]->(sh:SHOW)

return gen.name, count(v)

order by gen.name ASC;

we can clearly still see the dominance of men in these shows:

If we then add the political dimension again, and look at gender distribution for the political visitors to the shows:

match (g:GUEST)-[:HAS_GENDER]->(gen:GENDER),

(g)-[v:VISITED]->(sh:SHOW),

(g)-[:AFFILIATED_WITH]->(p:PARTY)

return gen.name, count(v)

order by gen.name ASC;

then we can see that the distribution is broadly the same:

I am sure there are plenty of other queries to think of, but let me do one more in this post: let's see what the overlap is - in terms of guests visiting them - between the different shows. To do that, all we need to do is calculate some paths between two shows: DWDD and P&W.

match p = AllShortestPaths((s1:SHOW {id:"DWDD"})-[*..2]-(s2:SHOW {id:"P&W"}))

return p

limit 100;

The result is exactly what you would expect: a HUGE amount of overlap - at least between these two (see above: largest) shows. Hence the "limit 100" in the query - so that my poor neo4j browser would survive:

Wrap-up

That's about all I have at this point. You can download the database from over here. And the queries that I used above are all on github.

From my perspective, I think these kinds of datasets are extremely interesting and powerful. I would love to see more work like Thomas', from my own country or abroad, and look at this from an even broader perspective. In any case, I would like to thank and compliment Thomas on his work - and look forward to your feedback.

Hope this was useful.

Cheers

Rik

Food networks, Countries, Diets, Health - and LOAD CSV

Last weekend I took my kids to the awesome Antwerp Zoo. We have season's tickets, and go there regularly - but for some reason it had been a while since all of us had gone together. While visiting the penguins, my daughter points to this picture

and shouts: "LOOK DADDY, A NEO4J DATABASE!". I am not kidding you - true story. And she was, of course, right: it was the "web of the sea", a predator-prey network of how sealife interacts with eachother.
So that got me browsing the web for a while, looking for other examples of such networks. And before long I found a dataset that really triggered my interest: on "Follow the data" I found this article that mentioned a google spreadsheet with some really interesting stuff. It basically has a lot of information about Countries, their dietary habits, and their health statistics. Excellent. I can make a graph out of that. 

Neo4j 2.1.MO1 – native loading of CSV files in cypher

At the same time one of my colleagues pinged me about a new milestone beta release of neo4j: version 2.1.MO1. This is the first milestone release after the ground-breaking 2.0 release that came out end of last year – and it is looking like a very interesting one. One of the key new features in 2.1 is going to be a set of features that will allow us to Import Data more easily. A pet pieve of mine, as you know.
I read through the manual pages, and thought it would be easy enough to use. So I spend some time getting the spreadsheet mentioned above into the right format for import, and took it for a spin.
In the zip-file over here, you can download a couple of files that allow you to do it all yourself. But for now, let me take you through it.

Importing data with LOAD CSV

The process of importing data was really, really easy now. All you need to do is       tell Neo4j what to do (load the csv),       assign a variable to the set (csvimport in this case) and then use the column names of the set as parameters for your cypher statement. The result was there instantaneously:
One thing that I did want to do then, was to use labels to provide structure to the graph, and use it for indexing:

With that Import I have my Countries and my Food Categories imported, so now I would want to add some relationships. I chose a model like the one outlined below: a country uses different food categories, at a different rate (kcalories used per day).

So first we import the relationships between the countries and the food categories used in that country:
As you can see, the relationships hold the values of the kilo-calories that that country uses of this specific food category.




That was quick!


So now we can do some querying. Let’s see what are the food categories that Belgium and the Netherlands have in common, and that have a significant part of the diet:




When we limit the query to only the food categories that are used for more than 500 kcal per day, we get:
These are the categories that apply:

• Animal Products
• Vegetal Products
•  Cereals - Excluding Beer (strange!)
•  Wheat

Then, I decided to use LOAD CSV one last time to add some health data that was also in the original dataset: the life expectancy data of the countries in the dataset. This data contains two interesting data elements that I imported:

• The Life Expectancy At Birth (LEAB)
• The HEalthy Life Expectancy At Birth (HELEAB)

I decided to import both of these, in a specific way. You may have been able to tell from the model picture above, but I created an in-graph Life Expectancy Index. By importing 100 Life Expectancies (1-100 years of age) as separate nodes, and then connecting the countries to these nodes as I used LOAD CSV. I used two different types of relationships for the LEAB and the HELEAB.




The following import was easy using LOAD CSV:
 



So then we could actually revisit the queries above, but include these interesting health stats about life expectancies:



The result shows how Belgium and the Netherlands have identical LEABs, but different HELEABs – interesting.





I am sure there are a bunch of other interesting queries in this dataset, but for now I think I have satisfied my curiosity – and learned about an awesome new Import tool – LOAD CSV. 


Hope this was useful.

Cheers

Rik

Some Neo4j import tweaks - what and where

As you probably know, importing data into Neo4j can be a bit tricky, in spite of some of the wonderful tools that we have these days. I blogged about this last year, and if you are looking for some guidance then please go there. 

Turns out that, in order to get the most out of your import efforts, there's actually a few settings that you should be aware of and tweak - depending on your specific environment. Your machine's memory will be of paramount importance, and your dataset will also determine some of the optimization characteristics that we will discuss below.

Essentially there's three parameters to tweak:

• the Java heap size
• the Memory-mapping of neo4j files
• the neo4j cache configuration.

The following table will try to provide an overview of a number of settings that you can add to your neo4j installation/tooling to optimize your data import performance. Let me start by explaining some of these settings for the Batch Importer:

Settings	Batch Importer
Heap size	Add parameters to the batch importers command line start statement: -Xms<size> :  this sets initial Java heap size -Xmx<size> : this sets maximum Java heap size
Memory mapping settings	Important Note: for the Batch importer, memory mapping settings are PART OF the heap settings above - you use a part of the heap size by using memory mapped files. That’s why you should try to give as much memory as possible as heap to the batch-importer. Leave 1-4GB to the operating system. Try to memory map all of the node store, and as much of the relationship store files as possible. Edit: /path/to/importer/batch.properties use_memory_mapped_buffers=true # 14 bytes per node neostore.nodestore.db.mapped_memory=200M # 33 bytes per relationship neostore.relationshipstore.db.mapped_memory=3G # 38 bytes per property neostore.propertystore.db.mapped_memory=500M # 60 bytes per long-string block neostore.propertystore.db.strings.mapped_memory=500M
Cache settings	For bulk update/import operations, the cache should be disabled as you write only and no node or relationship objects are loaded. Edit: /path/to/importer/batch.properties cache_type=none

Then, let's explore the same setting for the running neo4j server import capabilities, for example using neo4j-shell-tools:

Settings	Neo4j Server / Neo4j-shell-tools
Heap size	Edit: /path/to/neo4j/conf/neo4j-wrapper.conf # Initial Java Heap Size (in MB) wrapper.java.initmemory=4096 # Maximum Java Heap Size (in MB) wrapper.java.maxmemory=4096
Memory mapping settings	Important Note: for the Neo4j server (and neo4j-shell-tools that run against a server), memory mapping settings are SEPARATE of the heap settings. Your heap memory allocation will be additional to the memory mapping allocation. Usually you use between 4 and 8GB as heap. The remainder of your RAM is used for memory mapping. Note that on users that run Neo4j on Windows, there is a significant difference: there, the memory mapping is part of the heap, and the principle explained in the batch-importer section should be followed. Try to memory map all of the node store, and as much of the relationship store files as possible. Edit: /path/to/neo4j/conf/neo4j.properties The settings and settings to be added to this file are identical to the ones mentioned for the Batch Importer: use_memory_mapped_buffers=true # 14 bytes per node neostore.nodestore.db.mapped_memory=200M # 33 bytes per relationship neostore.relationshipstore.db.mapped_memory=3G # 38 bytes per property neostore.propertystore.db.mapped_memory=500M # 60 bytes per long-string block neostore.propertystore.db.strings.mapped_memory=500M
Cache settings	As you create relationships by looking up and updating nodes, the cache should be kept active on a running neo4j server that you are loading data into. Here we have a difference between the Community and Enterprise editions of neo4j: the Enterprise edition has a better cache that is not present in Community - the “High Performance Cache”. Therefore, for bulk update/import operations, you should Edit the neo4j.properties file in the conf directory of your neo4j installation: Edit: /path/to/neo4j/conf/neo4j.properties # Setting for Community Edition: cache_type=weak # Setting for Enterprise Edition: cache_type=hpc

I am hoping that this was a good overview of the different setting that you should keep in mind and tweak - and where you should tweak them - in your specific environment. 

Hope this was useful

Rik

The Open Source Licensing Graph

Selling an open source product like Neo4j, always gets you into the interesting question around Licensing. How do you license your product? And then I get into a very interesting explanation on how the different versions of Neo4j compare in terms of features, license, support capability, and of course pricing.

Cool graph events in the next couple of weeks!

Waw. I just realised that there are a TON of very cool graph events coming up that I am going to be fondly participating in.

• Graph Gist challenge meetup in Stockholm tomorrow, january 15th. Not going to make it there - but it will be cool!
• Graph Café Amsterdam next week on the 22nd. Really looking forward to that.
• First meetup in Oslo - more than 35 people coming, and counting! Hoping to really get something going there, together with our friends at Bekk. And I will let you in on a secret: there will be super-nice bottles of Belgian liquid substances there. Mmmm.
• Graph Café Stockholm: first Graph Café in Stockholm - first of many I hope. We had great meetups like this in Antwerp, Amsterdam and London before - it should be very nice!
• Intro to Graphs session in London - sold out, but always a pleasure!
• Modelling with graphs meetup in London - more than 100 people signed up! 
• FOSDEM Graph Devroom in Brussels - there's going to be a crowd of NeoTech's greatest engineers there. *Extremely* excited about that.

Hoping to see many of you there!

The Making of - my genealogy graph database

Quite a few people asked me "how did you create the genealogy graph?" - well: here's your answer in a quick 5min video.

Hope this is useful.

Cheers

Rik

Leftovers from the holidays: Genealogy Graphs!

Of all the graphy posts that I have written over the past 18 months, this is probably one of the ones that is closest to my heart. Beer-posts not included of course - those are in a category of their own :) ... This is a post about my family, and a tale of how a tiny little experiment led to a couple of very late graphy nights.

Mid-life crisis schmisis!

So maybe a bit of context. The fact is that I turned 40 a couple of months ago, and with that, in spite of what you may think, there has been a surprising lack of crisis. I feel good about my age, have no problem getting older, don't feel any need whatsoever to go off and race around on a loud Harley Davidson or something. But. Maybe this is completely unrelated, but I have found myself to do something else. I have gotten a lot more interested in the most basic of things: family history. I have been playing around with old photographs, scanning them and trying to make something out of them - and more stuff like that.

So a couple of weeks before Christmas, I discover a new website that I had never heard of before: geni.com. I spent some time playing around it, and found the concept of "social genealogy" really interesting. Geni allows you to share your tree with people in the tree, and then lets them work together with you on completing the information. Next thing I know I share it with my dad, he shares it with an uncle, and next thing you know old dusted boxes of family history are being opened. One of these boxes contained a lot of research done by my great-grand-uncle (the brother of my grandfather's father), Alphonse Van Bruggen. He's the priest in the picture below:

The fact that he was a priest is relevant: he had access to all of the baptism registries in Belgium/The Netherlands, and was extremely well placed to do some family tree research. And he did. At a family party this last Christmas break, I got my hands on his hand-written notes full of details, names, birthdates, burial dates, places, etc etc. It was an amazing piece of information - I still can barely believe that I found this.

Next thing I know I end up inputting all of this data into Geni. It was a lot of work - two late nights went into this. It's now an amazing tree that many of my family members are working together on - and which dates back to Jan van Brugel - my oldest ancestor - at the end of the 17th century. 

I can make a graph out of that!

Then of course my mind starts thinking: wouldn't it be great if I could do more with this data? After all - this is not just a "family tree"! It's a family graph - all the different trees (my own family, my inlaws, my mother's family, my dad's family, etc.) interact here, and it is clearly something that could really benefit from something like a graph database. So I started to look around, noticed that I could export a GedCom file from geni, and that there are tools like Gramps around that allow you to read these files offline. Gramps actually allowed me to export my family tree into a CSV file - and then... you know I love spreadsheets ... it was just one more step away to create a google spreadsheet doc that would allow me to prepare my import. I used the good old spreadsheet import method here - the dataset is just a couple of hundred nodes and relationships.

The model I chose to work with is as follows (courtesy to the wonderful graphjson.io tool for the colours): people have labels (male/female), are in relationships, and have kids as part of that relationship. 

I must admit that I had a couple of data quality issues at first, but I spent some time troubleshooting the dataset and was able to get a neo4j database with my entire family network in a matter of minutes rather than hours. Result!

Some interesting family queries!

Let's take a look at some interesting queries that I was able to write with this dataset.

Find my kids!

match (rik:male {FirstName:"Rik"}),

(kids)-[:CHILD_OF]-(relationship:relationship)-[:IS_MAN_OF]-rik,

(spouse:female)-[:IS_WOMAN_OF]-relationship

return kids.FirstName, kids.LastName;

And there they are:

Easy! So let's try something more difficult. 

Find my grandfathers!

This is the query I wrote to find my own grandfathers - a bit more difficult as it involves using a UNION statement to combine the two grandfathers from both my father's and my mother's side of the family into one resultset.

match (rik:male {FirstName:"Rik"})-[:CHILD_OF]->()<-[:IS_MAN_OF]-(father:male)-[:CHILD_OF]->()<-[:IS_MAN_OF]-(grandfather1:male)

return grandfather1.FirstName as FirstName, grandfather1.LastName as LastName

UNION

match

(rik:male {FirstName:"Rik"})-[:CHILD_OF]->()<-[:IS_WOMAN_OF]-(mother:female)-[:CHILD_OF]->()<-[:IS_MAN_OF]-(grandfather2:male)

return grandfather2.FirstName as FirstName, grandfather2.LastName as LastName;

and there they are:

But to be honest: I thought this was terribly complicated. So decided to try and make things easier.

Inferring fatherhood/motherhood

Part of the complexity of the query above is simply because we have to traverse through a relationship everytime we want to find a child of a parent. Seems like an unnatural and complicated things to do. Wouldn't it be nicer if we had a model that looked more like this:

At first I was uneasy about this, as I felt that I was duplicating information in my model, but after reading Mark's article on the topic I decided to just go with it - it would make life a lot easier. So to implement  this, I would need to infer the FATHER_OF and MOTHER_OF relationships, by executing a cypher query that would update the graph. The query goes like this:

match (child)-[:CHILD_OF]->(rel:relationship)-[:IS_MAN_OF]-(father:male),

(rel)<-[:IS_WOMAN_OF]-(mother:female)

create mother-[:MOTHER_OF]->child<-[:FATHER_OF]-father;

Simple enough! So let's see what this would do to the grandfather query:

match (rik:male {FirstName:"Rik"})<-[:FATHER_OF*2..2]-grandfather1

return grandfather1.FirstName as FirstName, grandfather1.LastName as LastName

UNION

match (rik:male {FirstName:"Rik"})<-[:MOTHER_OF]-()<-[:FATHER_OF]-grandfather2

return grandfather2.FirstName as FirstName, grandfather2.LastName as LastName;

That's a lot more readable in my book. And one of the nice things is that these new relationships would actually allow me to easily follow my lineage all the way back to the 17th century roots of my family tree:

match (rik:male {FirstName:"Rik"})<-[:FATHER_OF*]-(lineage:male)

return lineage;

gives me this:

All the way left are me and my sister. All the way right is Jan Van Brugel.

Learning more about life in the past few centuries

Obviously, now with this dataset in neo4j, I could ask lots of other questions:

Age of my forefathers

match (n:male) 
where NOT n.Birthdatenumber = "Unknown" AND NOT n.Deathdatenumber = "Unknown" 
return n.FirstName as FirstName, n.LastName as LastName, (n.Deathdatenumber-n.Birthdatenumber)/365 as Age
order by Age DESC;

Not a "bad" result - but not a very exciting one either. Chances of getting over eighty seem slim, based on this historical performance :) ...

Childhood deaths

This one's actually more encouraging: 

match (n)

where n.Deathdatenumber <> 'Unknown' AND n.Birthdatenumber <> 'Unknown'

with n, (n.Deathdatenumber-n.Birthdatenumber) as Age

return n.FirstName as FirstName, n.LastName as LastName, n.Birthdate, Age

order by Age ASC

limit 5;

Seems like in 3 centuries worth of (incomplete) data, we only had three babies die within the first 100 days. Tragic as that is - it still seemed like an ok number, especially if you know the huge advances in medical care that we have made in Western Europe over the past century.

Multiple relationships? Check!

And then of course the question to end all questions: how many of my ancestors had more than one wife? Here's the cypher query:

match (r1:relationship)-[:IS_MAN_OF|IS_WOMAN_OF]-(n)-[:IS_MAN_OF|IS_WOMAN_OF]-(r2:relationship)

return distinct n.FirstName as FirstName, n.LastName as LastName, n.Birthdate as Birthdate;

And again, it yielded an encouraging result:

Only 4! I think my missus will feel reassured :) ...

So that's about it. I must say that this was one blogpost that I more than enjoyed writing - it was fantastic to explore my family history, and use the wonderful world of graphs and neo4j while doing so. I will not be publishing the database - in the interest of my family's privacy - but am happy to discuss if you are interested in knowing more.

Hope this was interesting and/or useful.

All the best.

Rik