Assistance with Transposing Excel Data

[MRWA4687]

Hi All

First time poster but long time reader of the forum and the great wisdom it contains.

I am having some issues developing a fairly simple workflow to deal with some traffic data.

Unfortunately the data is not provided in a consistent format so column headers change and there are blank columns in some of the supplied data.

The workflow needs to simply opens all of the spreadsheets matching the filter and then writes then in a single workbook with a sheet for the AM peak, PM peak and 24 hour volumes.

The data is split into a pattern of 13 columns that provide information on different vehicles. Each pattern represents a “movement” e.g. the first one is the left turn, the second the through movement etc.

Refer to sample below:

And this needs to be changed to:

For future manipulation outside of KNIME I need to transpose the 13 columns for each movement into a single row which means for each intersection there could possibly be up to 12 rows.

I have used loops to read in different part of the spreadsheet to identify each “movement” but not all movements are appearing in the output and I am getting an error with the writing of the data within the loop with the message file in use.

Extracts of workflow
Step 1 opens all of the files and creates a single excel file with a sheet for AM, PM and 24 hours counts.

Step 2 – reconfigure the data
The problem workflow. This reads in different parts of the Excel workbook i.e. in 13 columns chunks and writes it out.

The looping part is not working correctly. If I move the writing of the Excel file to outside of the loop the data is not written in 13 columns it is “staggered” as per the original location of the data.

Any suggestions on how to overcome this issue or a different approach? Is there a setting I am missing in the Excel writer?

I am sure that there is a simple solution to this but I am yet to find it and I have searched the forums.

Thanks for any assistance.

[mlauber71]

@MRWA4687 here is an example where trips are identified based on rules

Label data rows based on column values & calculate measures for each label group

@Er3n you could take a look at this example trying to identify the trips [image] The relevant Column Expression goes like this: if (equals(column("Counter"),1) && // && means AND ( isMissing(column("Status")) || isNaN(column("Status")) // || means OR ) && equals(column("Total cumulated distance"), 0) ) {column("rank")} else {toNull("")}

If you have a way of finding the start or end of you blocks you can mark them by this and handle them in a loop

Move head rows to a column

@willy_oracle typically you would mark all lines with a Counter. Then identify the ‘headers’ with a rule engine [IF Col1 LIKE “Head*” => Counter] and keep that (starting) row in a separate column. Then you fill the missings with the previous value so a block would emerge marking the data table: [image] So now you have the blocks marked an can use a Table Row to Variable Loop Start – KNIME Community Hub or Group Loop Start – KNIME Community Hub to do something with the blocks (export them delet…

To close a loop you can collect the results with a loop end.

[MRWA4687]

Thanks for the response. My issue is not with the finding of the blocks I know where they are but the problem is writing them. I cannot seem to get the data aligned in an output that contains 13 columns and 1 row for each movement as opposed to the original input which contains one row with 13 columns x 12 (one for each movement).

[mlauber71]

@MRWA4687 maybe you can concatenate the data?

Concatenate

Also maybe it is best to explicitly wire the writer to the loop end or if the loop is still collecting the data to have this as the last step?

[MRWA4687]

The loop is still collecting the data. The concatenate does not resolve the issue.

Thanks for the suggestions.

[mlauber71]

@MRWA4687 I think you will have to plan out what you want to do at each step … you could also provide an example with dummy data for us to take a look.

[MRWA4687]

Noted. However, I thought that I had done that in my original post where I provide snapshots of the data (and the workflows) as is as well as showing how it needs to be reformatted. Is this not visible or able to be followed?

[mlauber71]

@MRWA4687 It would be best to provide a real example. Screenshots are nice but only go so far as to work on a problem.

[MRWA4687]

Hi

I have attached 2 files - Sample Data.xlsx this is the data after is extracted from the various spreadsheets. This is the data that needs to be transposed and the 19 columns written in a row for each movement.
Sample Data.xlsx (25.0 KB)

Wanted Output.xlsx - this shows the data reformatted for Intersection 1 for the 12 movements and movement 1 for Intersection 2. The pattern then continues.
Wanted output.xlsx (10.4 KB)

The issue I am struggling with is getting the output written the way shown in the spreadsheet Wanted Output.

Thanks for any assistance.

[mlauber71]

@MRWA4687 so with the sample I think I was able to put together a workflow that can demonstrate how this could be done. It involves several small steps to bring the data in the right shape at several points.

I might add some further explanations down the road. Maybe you take a look.

Transform Movement Data - KNIME Forum (84191) – mlauber71

[MRWA4687]

Thankyou for the suggested workflow.

I am not familar with some of those nodes and I will work my way through the suggested workflow and see if I can implement it. If I have any queries I will come back to you.

Thanks again.