0

I’m reading a Redis dump file using shell.

There are 3 main columns in the dump file as below.

Text:tags:name    682651    520
Text:tags:age     78262     450
Value:cache       77272     672
Value:cache:name  76258     872
New:specific      77628     762
New:test          76628     8622

Expected output:

Key     Count     Sum
Text:*  2         970
Value:* 2         1544
New:*   2         9384

Looking to get the expected above as columns can be checked based on substrings may be staring/middle/ending with strings (keys).

4
  • 2
    Welcome to U&L, what have you tried so far Dilip ?
    – steve
    Jun 23 at 11:09
  • Also, please explain the input format in more detail (most contributors will not now immediately what a "Redis dump" is): Are the input columns space- or tab-separated? Do you want the output space- or tab-separated? Can there be empty lines/comment lines in the input? And, as @steve already mentioned, what did you try, and where did you face problems you need help with?
    – AdminBee
    Jun 23 at 12:39
  • So no need to process the column starting 682651 ... . ? Are you able to handle that yourself, and possibly post a cleaner 'Sample Input' ? Jun 27 at 0:36
  • If you found any of the answers useful, please consider accepting so that others facing a similar issue may find it more easily.
    – AdminBee
    13 hours ago

4 Answers 4

1

The following awk program will perform the task:

awk '{split($1,f,/:/);count[f[1]]++;sum[f[1]]+=$3}
     END{printf "Key\tCount\tSum\n"; for (k in count) {printf "%s:*\t%d\t%d\n",k,count[k],sum[k]}}' dump.txt
  • This will first split the keys at column 1 at the : into components which are then stored in an array f. The first entry (f[1]) is taken as the relevant key for all further processing.
  • The occurence count will be stored in an associative array count, taking the key f[1] as array index. It will simply be increased by 1 every time the key is found.
  • The sum of the values in column 3 is stored similarly in an associative array sum.
  • In the end, the program prints the header line and then iterates over all array indices found in the array count to print the array index (= the key), the occurence counts and the summed values.

Note that the order in which the keys are printed is defined by the internal logic in which awk stores arrays. If you have GNU Awk, you can set the PROCINFO["sorted_in"] property in a BEGIN section to define the traversing order. For example

BEGIN{PROCINFO["sorted_in"]="@ind_str_asc"}

would make awk print the entries in ascending lexicographical order of the "keys".

0

First modify the first field so that everything after the first : is replaced by a *:

awk -v OFS='\t' '{ sub(":.*", ":*", $1) }; 1' file

This awk command modifies the first whitespace-delimited field by replacing the first match of the extended regular expression :.* with the literal string :*. The output of this, given your data, will be the following tab-delimited data:

Text:*  682651  520
Text:*  78262   450
Value:* 77272   672
Value:* 76258   872
New:*   77628   762
New:*   76628   8622

We may then group this by the first tab-delimited field while counting how many lines are collapsed into each group and summing the third field. One way to do this is by using GNU datamash:

awk -v OFS='\t' '{ sub(":.*", ":*", $1) }; 1' file |
datamash groupby 1 count 1 sum 3

If the original input is unsorted, then either pass the data from awk through sort or use datamash with its -s option.

This would output the following (which is tab-delimited):

Text:*  2       970
Value:* 2       1544
New:*   2       9384

To output the headers, simply first invoke

printf '%s\t%s\t%s\n' 'Key' 'Count' 'Sum'

The complete thing, reading from file and writing to some new file output:

{
    printf '%s\t%s\t%s\n' 'Key' 'Count' 'Sum'
    awk -v OFS='\t' '{ sub(":.*", ":*", $1) }; 1' |
    datamash groupby 1 count 1 sum 3
} <file >output
0
$ awk -F'[:[:space:]]+' '
    { cnt[$1]++; sum[$1]+=$NF }
    END {
        print "Key", "Count", "Sum"
        for (key in cnt) {
            print key":*", cnt[key], sum[key]
        }
    }
' file | column -t
Key      Count  Sum
Value:*  2      1544
Text:*   2      970
New:*    2      9384
0

Using Raku (formerly known as Perl_6)

~$ raku -e 'my (@k, @v); given lines.map(*.words).list {  \
            @k = .map(*.[0].split(":").[0]); @v = .map(*.[2]) };  \
            my %count1 = Bag(@k); my %agg1.=append: [Z=>] @k, @v;  \
            my %sum1; for %agg1 { %sum1.append: $_.key => [+] $_.value };  \
            for ([Z] %count1.sort, %sum1.sort) {  \
            say .[0].key ~qb[\t]~ .[0].value ~qb[\t]~ .[1].value};' file

Sample Input:

Text:tags:name    682651    520
Text:tags:age     78262     450
Value:cache       77272     672
Value:cache:name  76258     872
New:specific      77628     762
New:test          76628     8622

Sample Output:

New     2   9384
Text    2   970
Value   2   1544

This solution utilizes hash functionalities present in all Perl-family languages, here specifically written in Raku.

Briefly, lines are read in, and each line is broken into whitespace-separated words (map is what instructs Raku to work within 'each' line element). The data is saved into @k array containing the first string element after splitting on colons, and @v array which just contains the 3rd column (zero-index = 2). At this point the arrays look like so:

#Add this statement:
.say for @k, @v;

#Returns:
[Text Text Value Value New New]
[520 450 672 872 762 8622]

Raku has a Bag() function built-in, which gives the occurrences (counts) of each of the keys (using the line my %count1 = Bag(@k);).

From here an aggregate %agg1 hash is created, that "zips" down the @k and @v columns, => pairing them together, and appending them into the %agg1 hash. Of course a property of hashes is that every key is unique, thus values associated with the same key are placed as array elements constituting each key's value. At this point the %agg1 hash looks like so:

#Add this statement:
.say for %agg1.sort;

#Returns:
New => [762 8622]
Text => [520 450]
Value => [672 872]

The %agg1 hash is then iterated over again to create the %sum1 hash, which (you guessed it) sums the individual values in a key-wise manner. Finally, data is output, separated by tabs (the ~qb[\t]~ quotes the \t tab reducing the need for double-quotes within the code), and string concatenation is accomplished via ~ tilde.

https://andrewshitov.com/2020/03/16/a-couple-of-syntax-sweets-in-raku/
https://youtu.be/wViVLytlwb8
https://raku.org

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.