2

I've got an interesting problem I'm not quite sure how best to tackle, with iterating through a list. With the following format -

element, date, unixTime, value
CZ, 12/27/2007 15:55, 1198788900, 42346
CZ, 12/27/2007 17:30, 1198794600, -10543
I, 12/27/2007 19:05, 1198800300, 4475

I want to iterate through, for each unique element, and each date, and get the sign of the "value" column. For instance, I would want 2 lines for 12/27/2007, one for both CZ and I. CZ would be negative because the line that happened at the last part of the day had a negative value, and I would be positive. Then do that again for 12/28, 12/29, etc, for many different elements. I could iterate through this with a nested for loop, but it's a giant file and would be much better to find a way to iterate through the file, sorted by date, only once. I'm a little stuck on how to do this most efficiently. I'm not sure whether bash is suitable for this, or some other language like perl or python.

3
  • 2
    Hi, welcome to StackExchange. Suggest you add an example of what output you'd like to see, in much the same way as you've shown the input.
    – steve
    Commented Mar 1, 2017 at 21:26
  • Please state or verify whether the dates and times for each element are sorted, (i.e. if there are n "CZ" elements, the intent is to always print the nth "CZ" value).
    – agc
    Commented Mar 1, 2017 at 21:32
  • This sort of thing is crying out for a quick awk script, but I'm a little unclear on what you're looking for in terms of the output. As per @steve's comment, if you could edit that into your answer, that would be helpful.
    – DopeGhoti
    Commented Mar 1, 2017 at 21:38

3 Answers 3

1

Let's call the input data foo:

echo 'element, date, unixTime, value
CZ, 12/27/2007 15:55, 1198788900, 42346
CZ, 12/27/2007 17:30, 1198794600, -10543
I, 12/27/2007 19:05, 1198800300, 4475' > foo

Run GNU datamash on foo:

datamash -t, --header-in -g 1 last 4 < foo

Output:

CZ, -10543
I, 4475

The more formal switch names for datamash may make the above clearer, plus I've added header names, (if counting columns is inconvenient):

datamash --field-separator=',' --header-in --group=element last " value" < foo
1

Assuming @agc has correctly interpreted the output you require, plain old awk can be used.

awk -F, 'NR>1{a[$1]=$4}END{for(x in a){print x","a[x]}}' foo
CZ, -10543
I, 4475
0

The fact that your file is pre-sorted makes it tractable using sed where at any given point in time the sed pattern space holds no more than 2 lines.

sed -e '
   $!{
      N
      /^\([^,]*\),[[:space:]]\{1,\}\([^[:space:]]\{1,\}\)[[:space:]].*[[:space:]]\([^[:space:]]\{1,\}\)\n\1,[[:space:]]\{1,\}\2[[:space:]]/D
   }
   s/^\([^,]*\),[[:space:]]\{1,\}\([^[:space:]]\{1,\}\)[[:space:]].*[[:space:]]\([^[:space:]]\{1,\}\)\(\n\)/\1 \3\4/
   /\n/!s/^\([^,]*\),[[:space:]]\{1,\}\([^[:space:]]\{1,\}\)[[:space:]].*[[:space:]]\([^[:space:]]\{1,\}\)/\1 \3/
   P;D
' yourfile

Brief

We always keep 2 lines in the pattern space and note the time when there
is a change in the 1st field. So long as we keep encountering the same
first two fields, we keep chopping off the previous line and reading in
the next. And on a transition we print the 1st and last fields of the 
previous line, print it, remove upto the newline, and go back for more
with whats left in the pattern space.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .