Traverse two lists at once

user_12259229 0
Viewed 27

I have an input CSV that will have the following format 

email,skills,expertiseLevels
john1@xyz.com,"Oracle database;SSIS;SQL Server","5;4;3"
john2@xyz.com,"Python;Hadoop;SQL Server","1;2;4"

where for each row, expertiseLevels[i] signals the person's expertise in skills[i]

I would like to write a Cypher query to obtain a data set like so:

╒════════════════════════════════════╤═════════════════╤════════════════╕
│"email"                             │"skill"          │"expertiseLevel"│
╞════════════════════════════════════╪═════════════════╪════════════════╡
│"john1@xyz.com"                     │"Oracle database"│"5"             │
├────────────────────────────────────┼─────────────────┼────────────────┤
│"john1@xyz.com"                     │"SSIS"           │"4"             │
├────────────────────────────────────┼─────────────────┼────────────────┤
│"john1@xyz.com"                     │"SQL Server"     │"3"             │
├────────────────────────────────────┼─────────────────┼────────────────┤
│"john2@xyz.com"                     │"Python"         │"1"             │
├────────────────────────────────────┼─────────────────┼────────────────┤
│"john2@xyz.com"                     │"Hadoop"         │"2"             │
├────────────────────────────────────┼─────────────────┼────────────────┤
│"john2@xyz.com"                     │"SQL Server"     │"4"             │
└────────────────────────────────────┴─────────────────┴────────────────┘

The query I currently have does work, only I was wondering if there is a more straight forward way to accomplish what I am looking for:

LOAD CSV WITH HEADERS FROM 'file:///test.csv' AS line
WITH line.email AS email, split(line.skills,";") AS skills, split(line.expertiseLevels,";") AS expertiseLevels
WITH email, reduce(x = "", idx in range(0,size(skills)-1) | x + skills[idx] + ":" + expertiseLevels[idx] + ";") AS se
WITH email, split(se,";") AS skillsWithExpertise
UNWIND skillsWithExpertise AS skillWithExpertise
WITH email AS email, split(skillWithExpertise,":") AS tokens
WHERE skillWithExpertise <> ""
WITH email, tokens[0] AS skill, tokens[1] AS expertiseLevel
RETURN email, skill, expertiseLevel;

Thanks

cypher neo4j
1 Answers

This will produce your desired output:

LOAD CSV WITH HEADERS FROM 'file:///test.csv' AS line
WITH
  line.email AS email,
  SPLIT(line.skills, ";") AS skills,
  SPLIT(line.expertiseLevels, ";") AS expertiseLevels
UNWIND RANGE(0, SIZE(skills)-1) AS i
RETURN email, skills[i] AS skill, expertiseLevels[i] AS expertiseLevel
user_9747310
Related