Merging records based on a field

[masgo]

I have data which is like this (whitespaces are just for readability)

key,value1,value2,value3
x,   , 2, 3
x,  4,  , 
x,   , 2, 
y, 10,11,14
y,   ,12,15
y,   ,11,15
y,   ,12,

The records have a key field and many (~60) value fields. I want to merge the lines with identical keys into a single line in order to have unique keys.

The problem are the value fields, which might have identical data for each key or different data for each key. Many values might be empty. The ideal solution would select the most common non-empty value. In case of a tie choosing any value is fine.

For the above example, the ideal solution would be:

key,value1,value2,value3
x,  4, 2, 3
y, 10,11,15

[aborruso]

Hi @masgo , running

mlr --csv label @ then \
reshape -r "[^@]" -o i,v then \
filter -x -S '$v==""' then \
most-frequent -f @,i,v  then \
stats1 -a max -f count -g @,i -s  input.csv | mlr --csv sort -f @,i -nr count_max then \
head -n 1 -g @,i  then \
cut -x -f count,count_max then \
reshape -s i,v then label key

you will have

key,value1,value2,value3
x,4,2,3
y,10,11,15

[aborruso]

Could you insert some real data?

In this way I can help you

[masgo]

It would probably violate my NDA and some privacy laws. But I understand now what you are doing there. The @ has nothing to do with the out-of-stream-variable @ sign. It is just a new name for the first column in order to have a simpler reshape command and overall shorter commands. One of my field-names does actually start with an @ sign, this has cause some wired behavior on my tries.

The CSV has 27 (probably, the pre-processing might change) columns and ~15 of them are "values" which need to be merged. It has ~ 1 M lines and ~ 200k unique keys. There are also some miller steps before and after that step. So running the script is not that fast. I also refrained from sampling the data since there might be wired things in it. (e.g. there where 19 malformed values at line ~ 600k, and things like that.) But that's the beauty of miller, you can throw anything at it. Excel has been degraded to being just a "let's have a quick look at the data" tool.

But the more I think of it, the more I believe that stats1 mean is the solution to this problem. It also seems to be faster. Still thank you for the reply, I am still a beginner with miller and I definitely learned something.

btw: I format my scripts like.

mlr --all the I/O optiones here \
then first-verb ... \ 
then ... \
...
input-file > output-file

[aborruso]

@masgo some step by step comment to my useless script:

mlr --csv label @ then \
reshape -r "[^@]" -o i,v then \
filter -x -S '$v==""' then \
most-frequent -f @,i,v  then \
stats1 -a max -f count -g @,i -s  input.csv | mlr --csv sort -f @,i -nr count_max then \
head -n 1 -g @,i  then \
cut -x -f count,count_max then \
reshape -s i,v then \
label key

mlr --csv label @ to rename the first column to @

+---+--------+--------+--------+
| @ | value1 | value2 | value3 |
+---+--------+--------+--------+
| x | -      | 2      | 3      |
| x | 4      | -      | -      |
| x | -      | 2      | -      |
| y | 10     | 11     | 14     |
| y | -      | 12     | 15     |
| y | -      | 11     | 15     |
| y | -      | 12     | -      |
+---+--------+--------+--------+

reshape -r "[^@]" -o i,v to reshape wide to long all the fields except the one that contains the char @

+---+--------+----+
| @ | i      | v  |
+---+--------+----+
| x | value1 | -  |
| x | value2 | 2  |
| x | value3 | 3  |
| x | value1 | 4  |
| x | value2 | -  |
| x | value3 | -  |
| x | value1 | -  |
| x | value2 | 2  |
| x | value3 | -  |
| y | value1 | 10 |
| y | value2 | 11 |
| y | value3 | 14 |
| y | value1 | -  |
| y | value2 | 12 |
| y | value3 | 15 |
| y | value1 | -  |
| y | value2 | 11 |
| y | value3 | 15 |
| y | value1 | -  |
| y | value2 | 12 |
| y | value3 | -  |
+---+--------+----+

filter -x -S '$v==""' to filter all records in which v is not NULL

+---+--------+----+
| @ | i      | v  |
+---+--------+----+
| x | value2 | 2  |
| x | value3 | 3  |
| x | value1 | 4  |
| x | value2 | 2  |
| y | value1 | 10 |
| y | value2 | 11 |
| y | value3 | 14 |
| y | value2 | 12 |
| y | value3 | 15 |
| y | value2 | 11 |
| y | value3 | 15 |
| y | value2 | 12 |
+---+--------+----+

most-frequent -f @,i,v to count the most frequent i,v couple

+---+--------+----+-------+
| @ | i      | v  | count |
+---+--------+----+-------+
| x | value2 | 2  | 2     |
| y | value2 | 11 | 2     |
| y | value2 | 12 | 2     |
| y | value3 | 15 | 2     |
| x | value3 | 3  | 1     |
| x | value1 | 4  | 1     |
| y | value1 | 10 | 1     |
| y | value3 | 14 | 1     |
+---+--------+----+-------+

stats1 -a max -f count -g @,i -s to have the max value of previous count

+---+--------+----+-------+-----------+
| @ | i      | v  | count | count_max |
+---+--------+----+-------+-----------+
| x | value2 | 2  | 2     | 2         |
| y | value2 | 11 | 2     | 2         |
| y | value2 | 12 | 2     | 2         |
| y | value3 | 15 | 2     | 2         |
| x | value3 | 3  | 1     | 1         |
| x | value1 | 4  | 1     | 1         |
| y | value1 | 10 | 1     | 1         |
| y | value3 | 14 | 1     | 2         |
+---+--------+----+-------+-----------+

mlr --csv sort -f @,i -nr count_max to sort by @,i and by count_max descending

+---+--------+----+-------+-----------+
| @ | i      | v  | count | count_max |
+---+--------+----+-------+-----------+
| x | value1 | 4  | 1     | 1         |
| x | value2 | 2  | 2     | 2         |
| x | value3 | 3  | 1     | 1         |
| y | value1 | 10 | 1     | 1         |
| y | value2 | 11 | 2     | 2         |
| y | value2 | 12 | 2     | 2         |
| y | value3 | 15 | 2     | 2         |
| y | value3 | 14 | 1     | 2         |
+---+--------+----+-------+-----------+

head -n 1 -g @,i to get only the first row of @,i couple

+---+--------+----+-------+-----------+
| @ | i      | v  | count | count_max |
+---+--------+----+-------+-----------+
| x | value1 | 4  | 1     | 1         |
| x | value2 | 2  | 2     | 2         |
| x | value3 | 3  | 1     | 1         |
| y | value1 | 10 | 1     | 1         |
| y | value2 | 11 | 2     | 2         |
| y | value3 | 15 | 2     | 2         |
+---+--------+----+-------+-----------+

cut -x -f count,count_max to remove count,count_max fields

+---+--------+----+
| @ | i      | v  |
+---+--------+----+
| x | value1 | 4  |
| x | value2 | 2  |
| x | value3 | 3  |
| y | value1 | 10 |
| y | value2 | 11 |
| y | value3 | 15 |
+---+--------+----+

reshape -s i,v to reshape long to wide by i,v

+---+--------+--------+--------+
| @ | value1 | value2 | value3 |
+---+--------+--------+--------+
| x | 4      | 2      | 3      |
| y | 10     | 11     | 15     |
+---+--------+--------+--------+

label key to rename the first field

+-----+--------+--------+--------+
| key | value1 | value2 | value3 |
+-----+--------+--------+--------+
| x   | 4      | 2      | 3      |
| y   | 10     | 11     | 15     |
+-----+--------+--------+--------+

[aborruso]

Could not make it work on my real data, yet. I think I do not really understand the role of @ here. I only ever encountered it as @some_variable for global variables.

You are right and also for this reason I have written a long text of notes

[masgo]

Thank you!!