Find number of occurrences in list and organize from most to least

[BillytheHicks]

Edited my OP to create the new list a bit differently than I originally thought.

Hello KM. This post is an offshoot from a question I had last week here...

Find and cut lines from list to new list based on name

Hello KM ! I'm trying to create new lists from one master list. So for example in this list Christi,19946752,20194048 Brandon,20194048,20369664 Christi,20369664,20520192 Christi,20520192,20631296 Brandon,20745984,20980736 Joe-010,28729383,28733432 Brandon,22341211,22443280 Joe-010,23488449,23499482 Christi,20123984,20124432 I would like to group like so... list1: Christi,19946752,20194048 Christi,20369664,20520192 Christi,20520192,20631296 Christi,20123984,20124432 list2: Brand…

I thought I should start a new thread about it. I want to take a list of data and determine which of Variable A occurs the most but not by how often it appears in list but in total of the sum of the differences of Variable B and Variable C. I think my example below will make more sense than this explanation. As always thank you to the community.

An example data set here....

Variable A, Variable B, Variable C

SK BDC ,178554794,178964282
BDC JON,179082914,179792677
2016030,197931059,201932474
AA01810,213695710,215268068
C104680,215964577,216426614
AA01800,216478686,216925439
AA01790,221589474,222058260
C104680,222115649,222154462
AA01790,222162764,222197257
AA01800,222209339,222605901
AA01790,222647684,222883717
AA01800,222883717,222888307
AA01790,222888307,222934005
AA01800,222934005,222939824
SK BDC ,178554794,178964282
BDC JON,179082914,179792677
2016030,197931059,201932474
AA01810,213695710,215268068
C104680,215964577,216426614
AA01800,216478686,216925439
AA01790,221589474,222058260
C104680,222115649,222154462
AA01790,222162764,222197257
AA01800,222209339,222605901
AA01790,222647684,222883717
AA01800,222883717,222888307
AA01790,222888307,222934005
AA01800,222934005,222939824

So for example in the first line....
SK BDC ,178554794,178964282

The difference between Variable B and C, or the calculation 178964282 - 178554794 = 409488, would be determined to be 409488 (which Ill refer to as Variable D). The macro would then find every time Variable A appeared in the list and add up that variable D to a final number. So a simple example would be if it found SK SDB five times and each had a variable D of ten the final number of variable D would be fifty. This is a simplified example since as you can see in my provided data set below variable D will most like be in the millions at the end.

Then the last step would organize the list from the highest Variable D to the lowest Variable D so it would look something like...

AA01790, 35032456
AA01800, 33053213
SK BDC , 27033044
BDC JON, 191030940

Here is the more complete data set....

Billy Hicks data set.kmmacros (3.6 KB)

[ComplexPoint]

An execute Javascript action solution might be something like:

First fields by descending frequency.kmmacros (22.4 KB)

(() => {
    'use strict';

    const main = () =>
        scores(
            Application('Keyboard Maestro Engine')
            .getvariable('csvList')
        );

    // scores :: String -> String
    const scores = strText => unlines(
        map(
            // String name, tab, and frequency number
            tpl => fst(tpl) + '\t' + snd(tpl),
            sortBy(
                // Descending sort on frequency
                flip(comparing(snd)),
                map(
                    // Name field from first item of the group,
                    // and length of the group.
                    grp => Tuple(fst(fst(grp)), grp.length),
                    groupBy(
                        // Same first field
                        (a, b) => fst(a) === fst(b),
                        sortBy(
                            comparing(fst),
                            map(
                                x => splitOn(',', x),
                                lines(strText)
                            )
                        )
                    )
                )
            )
        )
    );

    // GENERIC FUNCTIONS --------------------------------------

    // Tuple (,) :: a -> b -> (a, b)
    const Tuple = (a, b) => ({
        type: 'Tuple',
        '0': a,
        '1': b,
        length: 2
    });

    // comparing :: (a -> b) -> (a -> a -> Ordering)
    const comparing = f =>
        (x, y) => {
            const
                a = f(x),
                b = f(y);
            return a < b ? -1 : (a > b ? 1 : 0);
        };

    // flip :: (a -> b -> c) -> b -> a -> c
    const flip = f => (a, b) => f.apply(null, [b, a]);

    // fst :: (a, b) -> a
    const fst = tpl => tpl[0];

    // Typical usage: groupBy(on(eq, f), xs)
    // groupBy :: (a -> a -> Bool) -> [a] -> [[a]]
    const groupBy = (f, xs) => {
        const dct = xs.slice(1)
            .reduce((a, x) => {
                const h = a.active.length > 0 ? a.active[0] : undefined;
                return h !== undefined && f(h, x) ? {
                    active: a.active.concat([x]),
                    sofar: a.sofar
                } : {
                    active: [x],
                    sofar: a.sofar.concat([a.active])
                };
            }, {
                active: xs.length > 0 ? [xs[0]] : [],
                sofar: []
            });
        return dct.sofar.concat(dct.active.length > 0 ? [
            dct.active
        ] : []);
    };

    // lines :: String -> [String]
    const lines = s => s.split(/[\r\n]/);

    // map :: (a -> b) -> [a] -> [b]
    const map = (f, xs) => xs.map(f);

    // snd :: (a, b) -> b
    const snd = tpl => tpl[1];

    // sortBy :: (a -> a -> Ordering) -> [a] -> [a]
    const sortBy = (f, xs) =>
        xs.slice()
        .sort(f);

    // splitOn :: String -> String -> [String]
    // splitOn :: a -> [a] -> [[a]]
    const splitOn = (needle, haystack) =>
        haystack.split(needle)

    // unlines :: [String] -> String
    const unlines = xs => xs.join('\n');

    // MAIN ---------------------------------------------------
    return main();
})();

[ComplexPoint]

Or alternatively:

(() => {
    'use strict';

    const main = () =>
        scores(
            Application('Keyboard Maestro Engine')
            .getvariable('csvList')
        );

    // scores :: String -> String
    const scores = strLines => {
        const dctScores = strLines
            .split('\n')
            .reduce(
                (a, x) => {
                    const k = x.split(',')[0];
                    return Object.assign(a, {
                        [k]: (a[k] || 0) + 1
                    });
                }, {}
            );
        return Object.keys(dctScores)
            .map(k => [k, dctScores[k]])
            .sort(flip(comparing(x => x[1])))
            .map(([k, n]) => k + '\t' + n)
            .join('\n')
    };

    // comparing :: (a -> b) -> (a -> a -> Ordering)
    const comparing = f =>
        (x, y) => {
            const
                a = f(x),
                b = f(y);
            return a < b ? -1 : (a > b ? 1 : 0);
        };

    // flip :: (a -> b -> c) -> b -> a -> c
    const flip = f => (a, b) => f(...[b, a]);

    // MAIN ---
    return main();

})();

[BillytheHicks]

Hi @ComplexPoint thank you for your reply. Im not familiar with Java at all unfortunately so I am not sure exactly how to use this. Was there a final variable in there that would reflect the new list in a display window?

[ComplexPoint]

The value returned by an Execute Script action can be assigned to a variable (set the option at the top of the action) – see the attached example.

(Java vs JavaScript was a deliberate confusion introduced by marketers – two languages sharing 4 letters but little else – see para 3 here for the story of how Mocha/Livescript got branded as 'Javascript')

First fields by descending frequency VER 2.kmmacros (21.5 KB)

[BillytheHicks]

gotcha! I tried the Java Script in your VER 2 but the display window is showing up blank for me.

[ComplexPoint]

The macro in the previous post is working here:

https://forum.keyboardmaestro.com/uploads/default/original/3X/4/e/4e008e166688b485487190b0c7441bca74e8806a.kmmacros

but it may be that using an execute script action is not going to be the best solution for you.

[BillytheHicks]

Thanks @ComplexPoint but this seems to be a bit over my head and I keep getting a blank display window This is where I am at. I can make the list to include Variable D as seen in the macro below. I have tried cobbling together the actions from the macro created by @JMichaelTX in this thread....

Find and cut lines from list to new list based on name

OK, so using an abbreviated set (4 groups, limited rows) of data from your last Source Data, I have a revised Macro that will produce this: Example Output [image] I have changed most KM Variables from Local to Global variables (meaning that they were persist permanently until deleted). All of these Global Variables have a prefix of: DND_ETC__ which means "Do Not Delete" and then "Extract Time Codes". Hopefully this will allow you to create/keep these variables without accidental interac…

but as I would expect when I add another part to each line, that being the new Variable D, and and go from

VariableA, VariableB, VariableC
SK BDC ,178554794,178964282
to
VariableA, VariableB, VariableC, VariableD
SK BDC ,178554794,178964282,409488

it throws off the macro and the groups are not created correctly. I think there is a simple tweak in there I am not getting that would find and organize each group as the original macro by @JMichaelTX intended. That would be the first step. The second step would be adding the Variable D together for each of those groups and I imagine creating a new list with each line consisting of Variable A and the sum of variable Ds for that particular group.

This is a very simplified example but it would take a group like this...

SK BDC ,5,15,10
SK BDC ,20,30,10
SK BDC ,40,50,10
SK BDC ,60,70,10
SK BDC ,80,90,10

and the macro would then make that group into one line that was the sum of VariableD to look like...

SK BDC ,50

and of course make that single line with each group name (Variable A) with the sums of Variable D for each particular group set.

With the final step being a numerical ordering from highest Variable D to lowest Variable D

numerical order list by variable D.kmmacros (5.1 KB)

[ComplexPoint]

Well, you are assigning the name DList to an empty string,

then not using the JavaScript action at all as far as I can see.

Not quite sure what the connection is between this and the macro which I posted ...

[BillytheHicks]

oh just trying to show the workflow I have seen work since the JavaScript was returning a blank display window. Obviously this is a problem on my end because I dont understand JS but that unfortunately means that I have no way of knowing why it isnt working on my computer but seems to be functioning fine on yours.

If the workflow has KM actions and regex that I can put into an online tester (like the link I provided) it is much easier for me to grasp what is actually happening and why. Then if something goes wrong I have a greater chance of solving it myself than posting on the forums.

[ComplexPoint]

Are you using a pre-Sierra OS X ?

[BillytheHicks]

oh wow yes I am. Its a really old laptop Im using to write these macros. Yosemite 10.10.3

[ComplexPoint]

@BillytheHicks

Its a really old laptop Im using to write these macros. Yosemite 10.10.3

Perhaps then even more reason to avoid a script-based solution then, but you may find that this ES5 Javascript version (generated by the on-line Babel REPL) has a better chance of working on a pre-Sierra system.

First fields by descending frequency (fossil OS version).kmmacros (22.6 KB)

[JMichaelTX]

@BillytheHicks

The difference between Variable B and C, or the calculation 178964282 - 178554794 = 409488, would be determined to be 409488 (which Ill refer to as Variable D). The macro would then find every time Variable A appeared in the list and add up that variable D to a final number. So a simple example would be if it found SK SDB five times and each had a variable D of ten the final number of variable D would be fifty. This is a simplified example since as you can see in my provided data set below variable D will most like be in the millions at the end.

Then the last step would organize the list from the highest Variable D to the lowest Variable D so it would look something like...

AA01790, 35032456
AA01800, 33053213
SK BDC , 27033044
BDC JON, 191030940

Given the data ("csvList") in your Macro numerical order list by variable D.kmmacros, I think this should work for you.

Please test and let us know if it works for you.

---

Example Results

---

MACRO:   Compile List of Groups by Max Difference [Example]

#### DOWNLOAD:
<a class="attachment" href="/uploads/default/original/3X/8/2/824d72751654ab53d0bc3542d3fb27ae0c4c126d.kmmacros">Compile List of Groups by Max Difference [Example].kmmacros</a> (14 KB)
**Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.**

---

### ReleaseNotes

Author.@JMichaelTX

**PURPOSE:**

* **Compile List of Groups by Max Diff in Data**

**REQUIRES:**

1. **KM 8.0.2+**
  * But it can be written in KM 7.3.1+
  * It is KM8 specific just because some of the Actions have changed to make things simpler, but equivalent Actions are available in KM 7.3.1.
.
2. **macOS 10.11.6 (El Capitan)**
  * KM 8 Requires Yosemite or later, so this macro will probably run on Yosemite, but I make no guarantees.  :wink: 

**NOTICE: This macro/script is just an _Example_**

* It has had very limited testing.
* You need to test further before using in a production environment.
* It does not have extensive error checking/handling.
* It may not be complete.  It is provided as an example to show you one approach to solving a problem.

**How To Use**

1. Trigger this macro.

**MACRO SETUP**

* **Carefully review the Release Notes and the Macro Actions**
  * Make sure you understand what the Macro will do.  
  * You are responsible for running the Macro, not me.  ??
.
1. Assign a Trigger to this maro..
2. Move this macro to a Macro Group that is only Active when you need this Macro.
3. ENABLE this Macro.
.
* **REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:**
(all shown in the magenta color)
  * Set Variable "csvList"

TAGS: @RegEx

USER SETTINGS:

* Any Action in _magenta color_ is designed to be changed by end-user

ACTION COLOR CODES

* To facilitate the reading, customizing, and maintenance of this macro,
      key Actions are colored as follows:
* GREEN   -- Key Comments designed to highlight main sections of macro
* MAGENTA -- Actions designed to be customized by user
* YELLOW  -- Primary Actions (usually the main purpose of the macro)
* ORANGE  -- Actions that permanently destroy Variables or Clipboards,
OR IF/THEN and PAUSE Actions


**USE AT YOUR OWN RISK**

* While I have given this limited testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
* If you have any doubts or questions:
  * **Ask first**
  * Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.

---

![image|483x2000](upload://vEK8F6eLVPVzfjSnfW7vzkDiZmm.jpg)

[BillytheHicks]

still no dice unfortunately. Thank you so much for trying @ComplexPoint I really appreciate it. I have to make this work on this computer though since I want to run macros on this dinosaur while working on a newer computer at work.

[BillytheHicks]

Hi Michael thank you again for the help.

I tried to run it and got a different result. My display window only showed....

[JMichaelTX]

@BillytheHicks

I tried to run it and got a different result. My display window only showed....

What version of KM are you running?
Did you run the macro exactly as I uploaded it, or did you change the data (csvList)?

[JMichaelTX]

@BillytheHicks

I tried to run it and got a different result. My display window only showed....

You might try this in the first For Each Action:
(?sm)^([^,]+?),.+?(?:([\n\r](?!\1))|\Z)

The RegEx \R syntax is a recent addition, and maybe the KM or macOS version you are running doesn't have it.

[BillytheHicks]

I imported your exact macro and hit run, didnt change anything. its KM 8.2.1. Im not sure if you saw my reply above where I mentioned to Complex that Im also on OSX 10.10.3

The new regex got it closer though you will notice some of the lines get repeated with AA01800 being repeated with a different variable D

2016030,4001415
2016030,4001415
AA01810,3144716
AA01800,1701629
AA01790,1570020
BDC JON,1419526
C104680,1001700
SK BDC ,818976
AA01800,5819

[JMichaelTX]

@BillytheHicks

I imported your exact macro and hit run, didnt change anything. its KM 8.2.1. Im not sure if you saw my reply above where I mentioned to Complex that Im also on OSX 10.10.3

The new regex got it closer though you will notice some of the lines get repeated with AA01800 being repeated with a different variable D

OK, thanks for testing.
After some more testing on my end, it looks like to me that your data has a mixture of CRs and LFs, which is causing confusion. The \R should have fixed that, but maybe your old macOS has not been updated.

So, I tried another approach, in which I first replace all CRs with LFs.
Now we have a clean set of data.
This works on my end. Please give it a try:

---

MACRO:   Compile List of Groups by Max Difference [Example]

#### DOWNLOAD:
<a class="attachment" href="/uploads/default/original/3X/2/6/2681d777043e4dbf3a18ee4e717792a0720edb4e.kmmacros">Compile List of Groups by Max Difference [Example].kmmacros</a> (17 KB)
**Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.**

---

### ReleaseNotes

Author.@JMichaelTX

**PURPOSE:**

* **Compile List of Groups by Max Diff in Data**

**REQUIRES:**

1. **KM 8.0.2+**
  * But it can be written in KM 7.3.1+
  * It is KM8 specific just because some of the Actions have changed to make things simpler, but equivalent Actions are available in KM 7.3.1.
.
2. **macOS 10.11.6 (El Capitan)**
  * KM 8 Requires Yosemite or later, so this macro will probably run on Yosemite, but I make no guarantees.  :wink: 

**NOTICE: This macro/script is just an _Example_**

* It has had very limited testing.
* You need to test further before using in a production environment.
* It does not have extensive error checking/handling.
* It may not be complete.  It is provided as an example to show you one approach to solving a problem.

**How To Use**

1. Trigger this macro.

**MACRO SETUP**

* **Carefully review the Release Notes and the Macro Actions**
  * Make sure you understand what the Macro will do.  
  * You are responsible for running the Macro, not me.  ??
.
1. Assign a Trigger to this maro..
2. Move this macro to a Macro Group that is only Active when you need this Macro.
3. ENABLE this Macro.
.
* **REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:**
(all shown in the magenta color)
  * Set Variable "csvList"

TAGS: @RegEx

USER SETTINGS:

* Any Action in _magenta color_ is designed to be changed by end-user

ACTION COLOR CODES

* To facilitate the reading, customizing, and maintenance of this macro,
      key Actions are colored as follows:
* GREEN   -- Key Comments designed to highlight main sections of macro
* MAGENTA -- Actions designed to be customized by user
* YELLOW  -- Primary Actions (usually the main purpose of the macro)
* ORANGE  -- Actions that permanently destroy Variables or Clipboards,
OR IF/THEN and PAUSE Actions


**USE AT YOUR OWN RISK**

* While I have given this limited testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
* If you have any doubts or questions:
  * **Ask first**
  * Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.

---

![image|451x1999](upload://2OUOAhNZ4g8KXXJ6Bb9XU4610kg.jpg)