Delete empty columns

Question
1

Hi

Is there an easy way to delete empty columns. After a filter transform I sometimes have redundant columns and would like to delete the empty ones.
There is still a header on these columns.

2

Currently you can delete certain pre-determined columns using Remove Cols. But it won’t dynamically remove columns that are empty or match a certain column name. Although that is a possibility for the future.

You can write your data to a file and then read it back with ignore empty columns selected. But that is rather kludgy.

3

Hi,

If you can shows us the sample and which column you like to remove.

4

Hi,

I hope the following example fits your need

image
image443×569 40.8 KB

  1. Sample data with 4 columns
  2. Filter data on col1 which makes col2 empty after the filter
  3. Create only column headings in notepad or any text editor and paste it in as clipboard data
    col1,col3,col4
  4. Add Stack transform and link it from step 3 header only that you like to keep and from step 2 Filter transform and you will get the result with the columns you need

And if you need to remove other columns after the filter change simply update your header only by copying new header and updating the step 3 from clipboard or create a new clipboard from new header and remove the previous link from the stack and add from the new clipboard.

So you can either keep multiple clipboard with the headers that you need or keep updating the single clipboard, it’s up to you.

Transform File
RemoveColumns.transform (2.5 KB)

5

I think you still have to manually check which column is empty, so I am not sure it is an improvement on Remove Cols.

6

Hi,

It’s just alternate of choosing the columns each time in Remove Column transform.

Using Stack I could have all the desired column headers in clipboard and depending on the filter I could rearrange the clipboard to get the results.

1 Like
7

Here is an example file:
DK-Price-DKK.csv (32.3 KB)

In this case there are empty columns in the links section, but other times it could be other columns.
So in my case I can not supply a list of headers I want. It needs to be a bit more dynamic than that.
A feature request could be a transform to remove empty columns.

8

You can do it with a couple of Transpose transforms.

image
image1161×124 53 KB

remove-empty-cols.transform (3.2 KB)

But this may break if you add extra columns later.

1 Like
9

Or just remove them on input:

image
image724×524 78.2 KB

10

Hi,

Try this modified version of Andy 's Transform.

image
image585×582 82.7 KB

Things to remember are:

  1. If number of columns change in the source then you have to change the Concat Col transform and Concat all but the first column
  2. If columns number change then note down the last column number, which is used in the Filter Transform with the combination of first column

Hope this helps. All these transforms are done to get the Header to be used with Stack Transform to remove the empty columns.

Transform file
remove-empty-cols.transform (4.7 KB)

Data file
DK-Price-DKK.csv (32.3 KB)

1 Like
11

Thanks. This seems to work.

12

It would be useful if Remove Cols, Concat Cols and possibly others had an option on whether additional columns were checked or unchecked (currently they are unchecked). This would then potentially having to go back an check new columns in Remove Cols, Concat Cols etc. It could just be an additional option on these transforms.

13

Hi,

If possible, why not give option in Remove Cols to remove empty columns, just like you have added Rank option in the Sort Transform.

Or give option in Filter Transform to filter out blank columns.

14

It would mean the behaviour goes from static (remove columns with index 1 and 5) to dynamic based on the content (remove all empty columns). That has various ramifications. For one thing, in auto run mode, doing something to temporarily make a column blank will remove it immediately and all references to it downstream. That might be bad.

15

Hmm, in that case why not make a specific transform to remove blank columns, which could only be used at the end only and after it only To File can be used.

16

Hopefully we can come up with something a bit more elegant!

1 Like
17

Why not add an option in the Header Transform which will output only column header for those columns that are not blank and then one can use that out put and use it with Stack Transform.

18

That would mean the Header transform was doing 2 completely different things. Also it wouldn’t avoid the issues related to dynamically changing columns.

19

Well, purpose of Header Transform is to produce a Header, so either it gobbles up the rows to make header or produce header only.

As for dynamically changing columns, well that is why output of Header Transform is used with Stack Transform so that only columns that match the Header Transform output is shown. Just like the solution above in which output of Header is used with Stack to produce result without empty columns.

So if the Header Transform was able to output only Headers with columns which are not Empty, then those whole set of Transforms could be avoided.

20

How about this as being elegant, give option in To File to avoid writing columns that are empty, just like in From File during load there is option to ignore empty colums