Loop needed to GroupBy Column(n), Column(n+1) to create Network Graph from BGP Routing Table

Hello! I’m working with a unique dataset and need some advice on transforming it.

strings broken down to pairs of consecutive elements.knwf (14.4 KB)

Each row in my dataset starts with the letter ‘A’ and is followed by a sequence of other letters, with varying row lengths.

For instance, some rows look like 

A B C
A D E F
A G
A T R


What I aim to do is restructure this data so that each row is broken down into pairs of consecutive elements. To illustrate, from the row 

A B C

I would create 

A B
B C

and from the row; 

A D E F
the new rows would be 
A D
D E
E F

to achieve this, I’ve considered splitting each row into multiple columns, each representing one letter, and then the number of columns would depend on the number of letters in the row. After splitting, my idea is to perform a group-by operation for each first column with its subsequent column. This would essentially extract the path within the pairs until I reach the maximum number of columns for that row. Each group-by operation would then yield the consecutive pairs as a new row in my transformed dataset. However, I’m not entirely sure if this is the most efficient approach or if there’s a better method to handle this kind of data manipulation. Could you provide some insights or suggest alternative strategies that might be more effective?

I will use this method to break AS Number Paths into pairs to create a network graph.

Hi @hakandurgut ,

I think the attached will do what you need

strings broken down to pairs of consecutive elements - takbb.knwf (34.7 KB)

image

1 Like

Hi @hakandurgut,

here is my solution without looping.


Output
Example.knwf (15.9 KB)

BR

2 Likes

It works, great solution ! I am running it on Full BGP Route table now, after that I will try to create network

1 Like

can you attach the workflow ?

Hi @hakandurgut , thanks. Glad it works.

I think you might find @morpheus 's excellent solution faster (without the loops)

@morpheus, I think you forgot to upload the flow. (Unless you want me to have to explain it :wink: )

Hi @takbb,
thanks for reminder. I’ve attached the workflow to my previous post.

BR

2 Likes

indeed @morpheus 's solution is much faster, I would never be able to think about it, never used rank before.

thanks @morpheus and @takbb

I was able to create AS Number Pairs to create a network.

image

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.