Neo4j: Average value going wrong in Cypher query

Question

I have a dataframe of below format:

    ORIGIN  UNIQUE_CARRIER  DEST    avg_arr_delay
     JFK          AA         LAX    14.56040268
     JFK          B6         LAX    7.085201794
     JFK          DL         LAX    6.475770925
     JFK          VX         LAX    3.939759036
     LAX          AA         MCI    2.3
     LOG          DL         SEA    3.4

I have two nodes created in graph database for origin airport and destination airport. It is related by a property named 'delayed_by' which points to average arrival delay. The relationship between the nodes are provided below:

   MERGE (origin:origin_airport {name: row.ORIGIN})
   MERGE (destination:dest_airport {name: row.DEST})
   MERGE (carrier:Carrier {name: row.UNIQUE_CARRIER})
   MERGE (origin)-[r:delayed_by]->(destination)
   SET  r.arr_delay=row.avg_arr_delay

I am trying to find average delay for specific origin and destination airport. Here, trying to find between JFK and LAX. JFK to LAX has 4 arr_delay value and I need to find the average of these values. I am using the below cypher query for finding average:

MATCH (oa:origin_airport {name:'JFK'})-[r:delayed_by]->(da:dest_airport     
{name:'LAX'})
RETURN oa.name  AS Origin, 
AVG(toFloat(r.arr_delay)) As Arrdelay,
da.name AS Destination

Instead of average, it is taking the last value 3.939759036 and returning that value. But I am expecting below value.

ORIGIN    DEST    Average
 JFK       LAX      8.0152

Also, what would be the best way to create a relationship between unique_carrier, origin and destination, linked by delayed_by relationship. By doing this I need to select a particular airlines, say AA between JFK and LAX and find the average delay. Something like below works?

   MERGE (origin:origin_airport {name: row.ORIGIN})
   MERGE (destination:dest_airport {name: row.DEST})
   **MERGE (carrier:Carrier {name: row.UNIQUE_CARRIER})**
   CREATE (origin)-[r:delayed_by]->(destination)
   **CREATE (origin)-[:from]->(carrier)-[r1:delayed_by]->(destination)**
   SET  r.arr_delay=row.avg_arr_delay
   **SET  r1.arr_delay=row.avg_arr_delay**

Could someone help me in achieving this output?

William Lyon William Lyon · Accepted Answer · 2016-06-29T17:06:25

The problem is that because you are using MERGE when creating the delayed_by relationship you only end up with one relationship per airport node pairs. Instead use CREATE for creating the relationship:

LOAD CSV WITH HEADERS FROM "https://dl.dropboxusercontent.com/u/67572426/so_flights.csv" AS row  
MERGE (origin:origin_airport {name: row.ORIGIN})     
MERGE (destination:dest_airport {name: row.DEST})     
CREATE (origin)-[r:delayed_by]->(destination)     
SET  r.arr_delay=toFloat(row.avg_arr_delay)

Note also the cast of avg_arr_delay to a float using the toFloat function.

Now you should get the results you expect:

MATCH (oa:origin_airport {name:'JFK'})-[r:delayed_by]->(da:dest_airport {name:'LAX'}) 
RETURN oa.name  AS Origin,  
AVG(toFloat(r.arr_delay)) As Arrdelay, 
da.name AS Destination

Returns:

Origin  Arrdelay    Destination
JFK   8.01528360875 LAX

Here is a Neo4j Console instance demonstrating these queries.

Neo4j: Average value going wrong in Cypher query

1 Answers