Remove columns if all rows have a certain value

Question
1

Hi there,

I have just started poking around in this excellent piece of software, but already am amazed of its featureset and versatility. I have a set of conversion jobs coming up where I have datasets from one system that needs to be transformed into another dataset going into another system. It seems like the easiest way to do this in EDT is to filter and transform the export, remove unwanted columns, rename the existing columns, and then write out a new dataset (csv).

I was just wondering if there is an easy way to remove those unwanted columns based on them all containing a certain value. I have lots of columns coming over with typically 0 in their row values, and as long as they are all the same, I don’t have a need for that column coming across. I can of course remove them one by one, or add the ones I need back in from removing all, but was wondering if there are transforms or methods to e.g. remove columns based on all of the rows containing a certain value for that column?

2

Have a look at ‘Summary - Distinct values’. If the answer is one value for a column then it may go, based on your description. This works for any form of data uniformly presented, and covers multiple columns in one Summary. You may want to remove whitespace first.

1 Like
3

Currently the Remove Cols transforms doesn’t have a way to remove certain columns based dynamically on the content of the columns.

There is perhaps a (slightly clunky) way you can do it using Gather → Filter → Spread.

image
image1004×164 28.5 KB

remove-empty-cols.transform (3.3 KB)

And there are almost certainly other ways using Transpose.

You could possibly also

  • read in the data
  • change 0 values to empty
  • write the data
  • read in the data again with ignore empty columns checked

The best approach depends on your data.

We have made some internal changes that make dynamic filtering of columns feasible and we hope to have a more elegant way to do this in future.

4

This will allow you to quickly see which columns are all 0s. But there is no straightforward way to automatically remove those columns.

5

This topic was also discussed a while back here:

6

I was aware that I was only providing a list of columns to remove, but wondering whether there would be some way to call an existing Transform as a function in Javascript?

7

wondering whether there would be some way to call an existing Transform as a function in Javascript?

Probably not. But we certainly want to have a more powerful version of the Javascript transform.

1 Like
8

There is now a feature in v2.3.0 to allow you to quickly check or uncheck columns based on various criteria, including column names and column contents. See:

1 Like
9

Wow, that is great and what a turnaround! :smiley:

1 Like