Author Topic: First step in visualizing and possibly analyzing CiviCRM Data (code provided) (Read 1348 times)

davideps · October 28, 2011, 08:10:43 am

Hello,

I am using CiviCRM to track my dissertation data collection on non-profit neighborhood development organizations. I find if very useful in planning and organizing my phone conversations with staff members. Many of the people I speak with work for multiple organizations and I need to track all those relationships. I can imagine CiviCRM being very useful to much larger research projects that have teams of people conducting interviews.

I've written code in R (www.r-project.org) to extract contact and relationship information from the CiviCRM MySQL database and write a graphml file. One can then load that file into any graph visualization/analysis software. I currently use Yed (www.yworks.com/products/yed/) to arrange, color, and print the graph.

A better programmer could probably find a way to incorporate these visualizations as image maps within CiviCRM to aid navigation. They could probably even be generated automatically for each organization to show campaigns and employees.

The code is minimal but functional. Please feel free to improve it!

Code: [Select]

library(RJDBC)
library(igraph)
drv <- JDBC("com.mysql.jdbc.Driver","/home/davideps/Software/extlibs/mysql-connector-java-5.0.7-bin.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/civicrm", "USERNAME", "PASSWORD")
org_table=dbGetQuery(conn,"SELECT civicrm_contact.id,display_name,city,contact_type,contact_sub_type FROM civicrm_contact LEFT JOIN civicrm_address ON civicrm_contact.id=civicrm_address.contact_id WHERE city='Cleveland' OR city='Minneapolis' AND is_deleted=0")
query_string=paste("SELECT contact_id_a,contact_id_b,relationship_type_id FROM civicrm_relationship WHERE contact_id_a IN (",paste(org_table$id,collapse=","),") AND contact_id_b IN (",paste(org_table$id,collapse=","),")",sep="")
edge_table=dbGetQuery(conn,query_string)
g=graph.empty()
g=add.vertices(g,nrow(org_table),id=org_table$id,label=org_table$display_name,contact_type=org_table$contact_type,subtype=org_table$contact_sub_type,city=org_table$city)
e=as.vector(rbind(edge_table$contact_id_a,edge_table$contact_id_b))
e=match(e,org_table$id) 
g=add.edges(g,e-1)
V(g)$label=gsub(" ","\n",V(g)$label)
V(g)$color="black"
V(g)[city=="Minneapolis"]$color="blue"
V(g)[city=="Cleveland"]$color="red"
write.graph(g,"/home/davideps/Desktop/civcrm_full.graphml","graphml")

xavier · October 28, 2011, 08:41:27 am

Hi,

As a fellow R user, sounds awesome. I have on my "whenever I have the time TODO list" to write a R client to access directly civicrm API using the rest interface.

Sounds cool. Could you share an example of what yed generates (never used it)

X+

petednz · October 28, 2011, 01:43:24 pm

For us non-adepts - would you care to put up a screenshot to illustrate what the potential outcome might look like - make it fuzzy if you need to hide real details. Or is this not the sort of visual representation of data/relationships that i am thinking it might be.

davideps · October 28, 2011, 03:04:04 pm

I didn't know if anyone would be interested enough to want an example.I've blurred the names since I like to get written permission from contacts before putting anything about them into writing. Please find two attached images made with Yed.

1. hierarchical layout
2. organic layout

It takes about 5 minutes to extract the data from MySQL using the R script above, load the file into Yed, apply a previously-saved properties mapping, and select a layout. I also manually create two groups, one for each city. The Yed properties mapper allows one to map node and edge (contact and relationship) attributes to visual characteristics such as color, width, size, etc. Here is the mapping I use:

node color = different cities (I also put them in different groups since the layout functions separate groups by default)

node border width = subtype (The "Program/Committee" subtype is rendered with thinner borders than its parent "Organization")

contact_type = Organizations (and program/committees) are rendered as blocks with labels. Individuals are rendered as just labels

Tooltip = Contact ID in the CiviCRM MySQL database

I'm not pleased with the default Yed layouts so far on my data, but I'm still experimenting. I'm also not sure these grand overviews would be useful to most CiviCRM users. However, a distinct organizational map for each organization may be useful, especially if clicking in a name or department would bring you to the appropriate page. Also, instead of rendering all individuals with black labels, I'd like to render in different colors according to last contact date.

Finally, Yed is closed-source but free. Cytoscape and Graphviz are open source alternatives.

-david

CiviCRM Community Forums (archive)

News:

Author Topic: First step in visualizing and possibly analyzing CiviCRM Data (code provided) (Read 1348 times)

davideps

First step in visualizing and possibly analyzing CiviCRM Data (code provided)

xavier

Re: First step in visualizing and possibly analyzing CiviCRM Data (code provided)

petednz

Re: First step in visualizing and possibly analyzing CiviCRM Data (code provided)

davideps

Re: First step in visualizing and possibly analyzing CiviCRM Data (code provided)