GroupBy/Count on many fields, seperately, to create fill rate table

Hello,

I have 100+ columns of data where I need to create a fill rate table that gives me a breakdown of how many times each element within that field is present. For example, if I have a field called "Vehicle Make" I will want to know how many times "Honda", "Mazda", "Ford" and etc appears. If I just had to do this on one field it would be super simple and I would just need the one summarize tool. However, I need to do this on all 100+ fields and do not want to have 100+ different streams of data grouping the different fields. I was thinking a macro might be able to accomplish this where I tell the macro to group the Nth field each each iteration (Iteration 1 group/count the 1st field, iteration 2 group/count the 2nd field and so on).

Macros

Common Use Cases

Best Practices

Transformation

Batch Macro

Accepted answers

All comments

Qiu

@AlteryxTrev
It will better for us to understand the isue if you can provide simple data input and output 😁

AlteryxTrev

Sure, absolutely, please see below:

In this example I have three columns of data. I want to group column number one and count how many of each element exists. I want to do the same for column 2 and 3 as well. Finally, I want to stack the results.

However, I do not want to use three seperate summarize tools (this would be very easy) because unlike this dummy dataset my real dataset has 100+ columns. So, I need to find a way to iterate through all of my columns get the appropriate grouping for each.

SoccerTil

not sure of it's efficiency, but the Frequency widget seems made for this.

SoccerTil

I don't have a very large data set, but I had a file with 8,000 records and 15 columns that ran the workflow in 3 seconds.

Ben_H

Hi @AlteryxTrev,

As @atcodedog05 suggested the other way to do this is with a batch macro that runs on a column at a time.

I've knocked up an example but it could likely be made simpler. I've tested it on a file with 35 columns and 300,000 rows which took 30 seconds. vs. 9 seconds on the non-batched method. Obviously that file is a fraction the size of your input though.

Regards,

Ben

Column Summary.yxzp

AlteryxTrev

Hi @Ben_H ,

What you created is what I was thinking would solve my problem. Do you mind walking me through the logic of how this macro is working? I have some of your logic figured out but not all of it.

In the main workflow:

1. This is our parameter control where you are submitting just the Column Names

2. This is our original data.

Since we are submitting these two streams into the macro does that mean that on the first iteration only the first parameter (or column) will be processed in the macro?

In the macro:

1. This is where our parameter comes in with the Column Name

2. This is where our data comes in.

3. How does this portion of the workflow work?

4. How does this portion of the workflow work?

As an aside, I was surprised that the macro version takes longer than just using transpose/summary on all of the data.

AlteryxTrev

@atcodedog05 @Ben_H @Qiu ,

Thinking about this some more, I know there are ways to split data files horizontally, but is there a way to split data files vertically? Namely, what if I split up my 1 data file consisting of 294 columns into 294 data files consisting of 1 column. Then I create a macro that reads the files one at a time, groups it and outputs the summary results. This way all 294 columns do not need to be in the machines memory all at the same time and it just needs to think about one column (or one data file) at a time. Thoughts? Thank you for all the brain power you have lent me thus far.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703