Relating nodes in csv with column names in Neo4J

[aditya_digala]

I have a csv file in below format,

1. There are n location columns of various locations.
2. There's Token column at the end where each location row is associated with a different token.

TASK

Associate each token to each location column such that the relationship is named after the column name.

Example

My cypher code

load csv with headers from "file:///locations.csv" as row with row where row is not null
merge (l1:locations {name:row.LOC_1})
merge (t:tokens {name:row.Token})
merge (l1) -[:LOC_1]->(t)

As you can see, This code loads each column and relates them to tokens manually which is a tedious task. In reality, there are 67 columns and doing this manually takes a lot of time.

[filantrop]

Hi,
This one works, but could somehow be optimized.

load csv with headers from "file:///locations.csv" as row with row
with row,keys(row) as columns
merge (token:tokens {name:row.Token})
with row,columns,token
unwind range(0,size(columns)) as columnIndex
with row,columns[columnIndex] as columnName,token
where not columnName='Token'
merge (l1:locations {name:row[columnName]})
with token,columnName,l1
call apoc.merge.relationship(token,columnName,null,null,l1,null) 
yield rel
return count(rel)

[aditya_digala]

Hey @filantrop,
Thanks for the code.
There are few things to consider,
1.There are null values in some columns. (Avoid them and load remaining values)
2. The dataset is too huge with 150K records and 67 columns. (How can we load faster)
(Also in original dataset the columns are named, c1,c2....cn)
Can you please modify your code keeping in mind the above points?

[filantrop · Accepted solution]

Test this one. You also could change the batch size of the transactions by uncommenting the //OF 500 rows

otherwise the defaultsize is 1000

:auto load csv with headers from "file:///locations.csv" as row with row
with row
call{
    with row
    with row,keys(row) as columns

    merge (token:tokens {name:row.Token})
    with row,columns,token
    unwind range(0,size(columns)) as columnIndex
        with row,columns[columnIndex] as columnName,token
        where not columnName='Token' and not row[columnName] is null
        merge (l1:locations {name:row[columnName]})
        with token,columnName,l1
        call apoc.merge.relationship(token,columnName,null,null,l1,null)
        yield rel
        return rel
} IN TRANSACTIONS //OF 500 ROWS
return count(rel)

[aditya_digala]

5 minutes since I started running this code. It's still running, will keep you updated here about the progress.
Also, it ran out of memory when I ran the first code in Neo4J Aura. Now I'm running it locally, hope this works faster. Any other tweaks I should do if this also runs out of memory?

[filantrop]

You could change the memory.

Have a look here:

Neo4j Graph Database Platform

Neo4j Performance Tuning - Developer Guides

This guide gives an overview on memory configuration, logical log configuration and Linux open files configuration.

[aditya_digala]

After 45 minutes, it finally completed running and works well.
Also, I am a beginner and still learning about Neo4J. I appreciate it if you suggest any important concepts for me to learn this software quickly.
Thank you so much!

[filantrop]

Great!
I think myself it was a good starting point to use the free online courses

Neo4j Graph Database Platform

Become a Neo4j Certified Professional - GraphAcademy

[inge_vandenherr]

Hey,

I have a very similar problem. This is the csv data I want to load. I want to create relationships HAS_AIM between Aim (who has Id and Name, first two columns) and Exo (Liftsuit 2.0, Paexo Back, ... and all other columns that come after), but only where under the Exo the word starts with HAS_AIM. The entire word (eg HAS_AIM_nevendoel) should come as a property of the relationship. 

aimId	aimName	Liftsuit 2.0	Paexo Back	CarrySuit	Laevo Flex	CareExo Lift
101	repositioning	HAS_AIM_nevendoel			HAS_AIM_nevendoel
102	repositioning lifting	HAS_AIM_hoofddoel			HAS_AIM_hoofddoel
103	repositioning lowering	HAS_AIM_nevendoel			HAS_AIM_nevendoel	HAS_AIM_hoofddoel
104	carrying	HAS_AIM_nevendoel		HAS_AIM_hoofddoel	HAS_AIM_nevendoel
105	holding	HAS_AIM_nevendoel		HAS_AIM_hoofddoel	HAS_AIM_nevendoel
106	pushing and pulling long		HAS_AIM_nevendoel
107	pushing and pulling short		HAS_AIM_hoofddoel
108	push or squeeze eg clips or plugs		HAS_AIM_nevendoel
109	power grip, pliens, contact grip		HAS_AIM_nevendoel

To do this for the first Exo (Listsuit 2.0), the working code looks like this.

LOAD CSV WITH HEADERS FROM 'file:///exo_aim.csv' AS row
MATCH (e:Exo{exoName: row.`Liftsuit 2.0`})
MATCH (a:Aim {aimId: toInteger(row.aimId)})
WHERE 
row.`Liftsuit 2.0` CONTAINS 'HAS_AIM'
MERGE (e)-[ea:HAS_AIM]->(a)
ON CREATE SET ea.aimCategory = row.`Liftsuit 2.0`;

Now to do this for a variable number of exo's that the csv file can have, I created something following your example above. It doesn't give errors, though doesnt create the relationships either.. I don't understand enough of it to find the mistake. 

:auto LOAD CSV WITH HEADERS FROM 'file:///exo_aim.csv' AS row with row
With row
Call{
	With row
	With row, keys(row) as columns
	Unwind range(0,size(columns)) as columnIndex
		With row,columns[columnIndex] as columnName,columnIndex
		Where row[columnIndex] CONTAINS 'HAS_AIM'
        Match (a:Aim {aimId: toInteger(row.`aimId/structureKinematicNameId`)})
		Match (e:Exo {exoName: columnName})
		With row,a,columnIndex,e
		Merge (e)-[rel:HAS_AIM]->(a)
		On create set rel.aimCategory = row[columnIndex] 
		Return rel
}  IN TRANSACTIONS
Return count(rel)

Thanks for the help !

[glilienfield]

What is the row attribute you have on line 9? 

`aimId/structureKinematicNameId`

[inge_vandenherr]

That should have been aimId. I simplified the terminology for purpose of clarity of the question, but forgot this one sorry 🤔. The question stays the same 😊