Split cell values; one to be a new column header, the other to be column value.

Hi all,

I am facing a recurring issue with my data, any help would be much appreciated!

So I have participant data, say for number items bought at different times of the day. On the transaction level it will look like this:

ID | date | time | no. bought
11 | 12/12/2012 | 0800 | 5

I usually have to consolidate on a daily level, so I’ll use a Groupby node, and get something like this:

ID | date | (unique_concat) no. bought
11 | 12/12/2012 | 0800(5), 1200(2), 2000(1)

What I really need, is for the time to be the header, and the no bought to be the column value, like so:

ID | date | 0800 | 1200 | 2000
11 | 12/12/2012 | 5 | 2 | 1

The problem is that, for other participants the time won’t be aligned. So I can’t just split, but need to check for the time to add to the correct value.

Anyone faced something like that who could help? Thanks in advance!

I constructed a loop to demonstrate how this could be done. The collection of the results is not so elegant, I have to use an intermediate Table that has to be initiated at the beginning in order to collect all possibilities.

kn_example_column_time_transpose.knwf (57.5 KB)

1 Like

Hi Both,

I have the feeling I miss something in this challenge :).

Why don’t you use the pivoting node?

kn_example_column_time_transpose.knwf (10.9 KB)

Cheers, Iris

2 Likes

Dear mlauber71,

Thanks so much for your quick reply! Really appreciate it, and I’m trying it out now.

Dear Iris,

Thanks also for the quick reply! I did try the Pivoting node, but the issue is that the values in the <…concat…> column might not be in sequence, or not all values might show. An example:

ID | date | (unique_concat) no. bought
11 | 12/12/2012 | 0800(5), 1200(2), 2000(1)
11 | 13/12/2012 | 0900(5), 1300(2)

upon Pivoting would give something like:

ID | date | 0800, 1200, 2000 | 0900, 1300
11 | 12/12/2012 | 1 | 0
11 | 13/12/2012 | 0 | 1
i.e. the unique sequence of time would be a variable, and pivoting would count that as 1 instance.

I might have misunderstood something from the Pivoting node. Appreciate any enlightenment!

Dear transpose

the pivoting node would be applied on your raw data and not after the groupby.
The pivoting basically can do the grouping for you as well and do the pivoting in addition.

Best, Iris

2 Likes

Dear Iris,

Thanks! I got it now. Did what you proposed and Joiner it later down the workflow.

1 Like

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