Restructuring CSV data with Notepad ++, Regex

Question

I have a CSV file with the following headers and (sample) data:

StopName,RouteName,Travel_Direction,Latitude,Longitude
StreetA @ StreetB,1 NameA,DirectionA,Lat,Long
StreetC @ StreetD,1 NameA,DirectionA,Lat,Long
...
StreetE @ StreetF,1 NameA,DirectionB,Lat,Long
StreetG @ StreetH,1 NameA,DirectionB,Lat,Long
...
StreetI @ StreetJ,2 NameB,DirectionC,Lat,Long
StreetK @ StreetL,2 NameB,DirectionC,Lat,Long
...
StreetM @ StreetN,2 NameB,DirectionD,Lat,Long
StreetO @ StreetP,2 NameB,DirectionD,Lat,Long
.
.
.

I want to use regex (currently in Notepad ++) to get the following results:

1 NameA - DirectionA=[[StreetA @ StreetB,[Lat,Long]], [StreetC @ StreetD,[Lat,Long]], ...]
1 NameA - DirectionB=[[StreetD @ StreetE,[Lat,Long]], [StreetF @ StreetG,[Lat,Long]], ...]
2 NameB - DirectionC=[[StreetH @ StreetI,[Lat,Long]], [StreetJ @ StreetK,[Lat,Long]], ...]
2 NameB - DirectionD=[[StreetL @ StreetM,[Lat,Long]], [StreetN @ StreetO,[Lat,Long]], ...]
.
.
.

Using Regex and Substitution,

RgX: ^([^,]*),([^,]*),([^,]*),(.*)
Sub: $2 - $3=[$1,[\4]]

Demo: https://regex101.com/r/gS9hD6/1

I got this far:

1 NameA - DirectionA=[StreetA @ StreetB,[Lat,Long]]
1 NameA - DirectionA=[StreetC @ StreetD,[Lat,Long]]
1 NameA - DirectionB=[StreetE @ StreetF,[Lat,Long]]
1 NameA - DirectionB=[StreetG @ StreetH,[Lat,Long]]
2 NameB - DirectionC=[StreetI @ StreetJ,[Lat,Long]]
2 NameB - DirectionC=[StreetK @ StreetL,[Lat,Long]]
2 NameB - DirectionD=[StreetM @ StreetN,[Lat,Long]]
2 NameB - DirectionD=[StreetO @ StreetP,[Lat,Long]]

In the new regular expression, I tried to split the above result into "=", but did not know where to go from there.

I think that one way to get the desired results would be to save the first unique instance of what is before "=", replace the new line with "," and enclose it in [..] to make it an array form.

Edit: There are about 10 thousand stops (total), but only about 100 unique routes.

2: (, )

:

• , "\n" "=" ?

• , RouteName StopName, : 1 NameA - DirectionA=[StreetA @ StreetB, ...]?
• , RouteName Coordinates, : 1 NameA - DirectionA=[[Lat,Long]]?

Answer 1

1. :

• : ^([^,]*),([^,]*),([^,]*),(.*)
• : \2 - \3=[[\1,[\4]]]
• Replace All

2. :

• : ^[\S\s]*?^([^][]*=)\[\[.*\]\]\K\]\R\1\[(.*)\]$
• : , \2]
• Replace All

3. 2, .

• , ( - ) 100 (), Replace All 7 (ceiling(log2(N))).

---

1, , .

2 , .

^[\S\s]*?^([^][]*=)     #Group 1: captures "1 NameA - DirA="
\[\[.*\]\]              #matches the set of Stops - "[[StA @ StB,[Lat,Long]], ..."
\K                      #keeps the text matched so far out of the match
\]\R                    #closing "]" and newline
\1                      #match next line (if the same route)
\[(.*)\]$               #and capture the Stop (Group 2)

regex101 1

regex101 2