Group by specific row

[CKlinske]

Hello,

I’m using some oddly formatted data and was hoping for a little help. Essentially the data is set up as csv in this format:

GameID, 1234
event, 111
event, 222
event, 333
GameID, 1235
event, 444
event, 555

such that event 111-333 belongs to GameID 1234 and 444 & 555 belong to GameID 1235. What I’d like to be able to do is assign those GameIDs to a column that’s on each event. My first idea was to somehow use the JSON nodes to turn this into a hierarchy, but I can’t quite figure it out.

Thank You

[takbb]

Hi @CKlinske , the way I’d approach this as follows…

First off use the rule engine to create a new column and into it place the value of column2, if column1=“GameID”, and leave it as missing otherwise.

This would give you the following
GameID, 1234, 1234
event, 111, ?
event, 222, ?
event, 333, ?
GameID, 1235, 1235
event, 444, ?
event, 555 ?

After that you can use a Missing Value node and have it set missing values on column3 to previous value, thus copying down the GameID to each event. I think that should work.

GameID, 1234, 1234
event, 111, 1234
event, 222, 1234
event, 333, 1234
GameID, 1235, 1235
event, 444, 1235
event, 555, 1235

This then tells you which events belong to each GameID. After that you can do Group By or whatever else you want to do with it

[CKlinske]

That worked perfectly, thank you so much. I had forgotten about that feature of Missing Value.