Hi I have a dataset where each customer (i.e. Voter) has all historic voting dates on the same row:
In this case Voter ID 1 has multiple dates in 1 column labelled Voting History. What I am trying to do is to create columns out of these dates for each voter as shown below:
For instance, Voter iD 1 can have a column for voting on 11/08/2016, followed by other dates that the voter voted on. Now similarly Voter id 3 can have a flag for same date of 11/08/2016 however Voter ID 2 has not voted for any of those dates.
How can I pivot this data in Knime.
Here is the workflow to do that:
row to header.knwf
In string manipulation I have removed the double quotations for all cells and the commas and question marks for the cells which don’t have any values (I assumed that a user has all missing or none at all just like your sample data).
Then everything else is straightforward and converts the values to collection then ungroups them and finally creates columns based on those values. The GroupBy node aggregates the rows based on IDs (aggregation is applied based on column type in the last tab “Type Based Aggregation”) and the loop converts 0/1 values to No/Yes.