0

using this input:

08/22/2019 12:00:58
Name Cans Bucks Puns
Clyde 12 2 79
Sheila 32 16 42
Elmo 44 18 21

08/23/2019 19:00:22
Name Cans Bucks Puns
Clyde 18 21 46
Sheila 37 2 11
Elmo 41 3 10

I am able to get this output:

name=Clyde cans=12 bucks=2 puns=79 ts=1566475258
name=Sheila cans=32 bucks=16 puns=42 ts=1566475258
name=Elmo cans=44 bucks=18 puns=21 ts=1566475258
name=Clyde cans=18 bucks=21 puns=46 ts=1566586822
name=Sheila cans=37 bucks=2 puns=11 ts=1566586822
name=Elmo cans=41 bucks=3 puns=10 ts=1566586822

Using this code:

 awk -F'[/: ]' '{
  if (NF==6){
    ts=mktime($3" "$1" "$2" "$4" "$5" "$6)
    skipheader=1
  }
  else if (NF==0 || skipheader){
    skipheader=0
  }
  else {
    print "name="$1,"cans="$2,"bucks="$3,"puns="$4,"ts="ts
  }
}' file

The issue I am having, however, is what do I do if the input has multiple entries for the same time stamp and I just need one output with sum of those instances?

For example:

08/23/2019 19:00:22
Name Cans Bucks Puns
Sheila 37 2 11
...
Sheila 5 1 0

How can we get this to output sum? So instead of getting output like this:

Name=Sheila Cans=37 Bucks=2 Puns=11 ts=1566567001
Name=Sheila Cans=5 Bucks=1 Puns=0 ts=1566567001

I can get single output like this:

Name=Sheila Cans=42 Bucks=3 Puns=11 ts=1566567001

I am familiar with method described here: https://stackoverflow.com/questions/2311228/how-can-i-sum-values-in-column-based-on-the-value-in-another-column

I just have no idea how to use the timestamp as a condition for the output.

  • Related Appending first row time stamp to list below. It looks like you have multiple accounts. You should merge them – cas Oct 30 '19 at 1:21
  • Use an Array of Hashes (AoH) or Hash of Hashes (HoH). i.e. an array/hash with the timestamp as the index, containing a hash with Name, Cans, Bucks, Puns as the keys. This is possible in awk but is a PITA to do. It is much easier in perl - handling multidimensional arrays and nested data structures is one of the points where I give up on awk and switch to perl, it's just not worth the effort of writing 5 or more times as much code to crappily emulate something that is trivially easy in perl. – cas Oct 30 '19 at 1:28
  • another alternative is to do it in two stages. the first to produce the output in your first sample (you already have this - your awk script above). the second stage is to sort that output by timestamp (e.g. with sort -k5,5) and pipe it into a second awk script to accumulate a total for keys until the timestamp changes. When it changes, print the current totals, reset them to zero, and start accumulating them again from the current input line. – cas Oct 30 '19 at 1:32
0

Here we're using three arrays to save/accumulate the values instead of printing them. When the next block is processed (NF=0), print the accumulated values of the previous block and delete the array elements for the given name.

Note that the order of the printed lines within a block is arbitrary. The END block is needed to print the values of the last processed block before exiting – this is duplicated code and not elegant.

awk -F'[/: ]' '{
  if (NF==6){
    ts=mktime($3" "$1" "$2" "$4" "$5" "$6)
    skipheader=1
  }
  else if (NF==0 || skipheader){
    skipheader=0
    if (NF==0) {
      for (i in cans){
        print "name="i,"cans="cans[i],"bucks="bucks[i],"puns="puns[i],"ts="ts
        delete cans[i]; delete bucks[i]; delete puns[i]
      }
    }
  }
  else {
    cans[$1]+=$2; bucks[$1]+=$3; puns[$1]+=$4
  }
}
END{ 
  for (i in cans) print "name="i,"cans="cans[i],"bucks="bucks[i],"puns="puns[i],"ts="ts
}' file

Sample input:

08/22/2019 12:00:58
Name Cans Bucks Puns
Clyde 12 2 79
Sheila 32 16 42
Elmo 44 18 21
Sheila 8 4 8

08/23/2019 19:00:22
Name Cans Bucks Puns
Clyde 18 21 46
Sheila 37 2 11
Elmo 41 3 10
Elmo 10 4 2

Output:

name=Sheila cans=40 bucks=20 puns=50 ts=1566468058
name=Elmo cans=44 bucks=18 puns=21 ts=1566468058
name=Clyde cans=12 bucks=2 puns=79 ts=1566468058
name=Sheila cans=37 bucks=2 puns=11 ts=1566579622
name=Elmo cans=51 bucks=7 puns=12 ts=1566579622
name=Clyde cans=18 bucks=21 puns=46 ts=1566579622
| improve this answer | |

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.