Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk - Pick last value from set of rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk - Pick last value from set of rows
# 1  
Old 02-17-2016
awk - Pick last value from set of rows
Input data

Code:
 
COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13
C,ABC,ABCD,3,ZZ,WLOA,2015-12-01,2016-12-01,975.73,ZZZ,P,111111.00,Y1   **GROUP1**
C,ABC,ABCD,3,ZZ,WLOA,2015-12-01,2016-12-01,975.73,ZZZ,P,222222.00,Y1   **GROUP1**
C,ABC,ABCD,3,ZZ,WLOA,2015-12-01,2016-12-01,975.73,ZZZ,P,333333.00,Y1   **GROUP1**
C,ABC,ABCD,3,ZZ,WLOA,2016-12-01,2017-12-01,898.86,ZZZ,P,444444.00,Y2   **GROUP2** 
C,ABC,ABCD,3,ZZ,WLOA,2016-12-01,2017-12-01,898.86,ZZZ,P,555555.00,Y2   **GROUP2**
C,ABC,ABCD,3,ZZ,WLOA,2016-12-01,2017-12-01,898.86,ZZZ,P,666666.00,Y2   **GROUP2**
C,ABC,ABCD,3,ZZ,WLOA,2017-12-01,2018-12-01,788.81,ZZZ,P,777777.10,Y3   **GROUP3**
C,ABC,ABCD,3,ZZ,WLOA,2019-12-01,2020-12-01,788.81,ZZZ,P,888888.10,Y5   **GROUP4**
C,ABC,ABCD,3,ZZ,WLOA,2020-12-31,2021-10-29,788.81,ZZZ,P,999999.10,Y5+  **GROUP5**
C,ABC,ABCD,4,ZZ,WLOA,2015-12-01,2016-12-01,788.81,ZZZ,P,101010.10,Y1   **GROUP6**
C,ABC,ABCD,4,ZZ,WLOA,2015-12-01,2016-12-01,788.81,ZZZ,P,123123.10,Y1   **GROUP6**

I am using the following command to print out one record where COL_4, COL_7, COL_8 and COL_13 match - It then sums up all the values from COL_9 and prints into the one record

Code and output;

Code:
 
awk 'BEGIN{FS=OFS=","}
NR==1{print;next}
{a[$4";"$7";"$8";"$13]+=$9}
END{for(i in a)print $1, $2, $3, substr(i,0,1), $5, $6, substr(i,3,10), substr(i,14,10), a[i], $10, $11, $12}' BUCKETED.csv > COL_TOTAL.csv
  
 
COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13
C,ABC,ABCD,3,ZZ,WLOA,2019-12-01,2020-12-01,788.81,ZZZ,P,123123.10,Y4     **GROUP4 Record**
C,ABC,ABCD,3,ZZ,WLOA,2017-12-01,2018-12-01,788.81,ZZZ,P,123123.10,Y3     **GROUP3 Record**
C,ABC,ABCD,3,ZZ,WLOA,2016-12-01,2017-12-01,2696.58,ZZZ,P,123123.10,Y2    **GROUP2 Record**
C,ABC,ABCD,3,ZZ,WLOA,2015-12-01,2016-12-01,2927.19,ZZZ,P,123123.10,Y1    **GROUP1 Record**
C,ABC,ABCD,3,ZZ,WLOA,2020-12-31,2021-10-29,788.81,ZZZ,P,123123.10,Y5     **GROUP5 Record**
C,ABC,ABCD,4,ZZ,WLOA,2015-12-01,2016-12-01,1577.62,ZZZ,P,123123.10,Y6    **GROUP6 Record**

The issue I have is with COL_12 and bringing back the same value everytime (123123.10) - The desired output would be to bring back only the final record from each group, so for example we would bring back the following records from the groups

Code:
 
GROUP1=333333.00
GROUP2=666666.00
GROUP3=777777.10
GROUP4=888888.10
GROUP5=999999.10
GROUP6=123123.10

The correct output therefore would show as;

Code:
 
C,ABC,ABCD,3,ZZ,WLOA,2019-12-01,2020-12-01,788.81,ZZZ,P,888888.10,,Y4
C,ABC,ABCD,3,ZZ,WLOA,2017-12-01,2018-12-01,788.81,ZZZ,P,777777.10,Y3
C,ABC,ABCD,3,ZZ,WLOA,2016-12-01,2017-12-01,2696.58,ZZZ,P,666666.00,Y2
C,ABC,ABCD,3,ZZ,WLOA,2015-12-01,2016-12-01,2927.19,ZZZ,P,333333.00,Y1
C,ABC,ABCD,3,ZZ,WLOA,2020-12-31,2021-10-29,788.81,ZZZ,P,999999.10,Y5
C,ABC,ABCD,4,ZZ,WLOA,2015-12-01,2016-12-01,1577.62,ZZZ,P,123123.10,Y6

Is there any way of bringing back just this record to be printed in the awk command?

Thanks for your help.
# 2  
Old 02-17-2016
How about
Code:
    I    /tmp/bash-fc-763387004                                                                                                          Row 1    Col 1    3:16  Ctrl-K H for help
awk '
BEGIN   {FS = OFS = ","
         SUBSEP = ";"
        }
NR==1   {print
         next
        }
        {a[$4, $7, $8, $13] += $9
         b[$4, $7, $8, $13] =  $12
        }
END     {for (i in a) print $1, $2, $3, substr(i,0,1), $5, $6, substr(i,3,10), substr(i,14,10), a[i], $10, $11, b[i]
        }
' file
COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13
C,ABC,ABCD,,ZZ,WLOA,2015-12-01,2016-12-01,1577.62,ZZZ,P,123123.10
C,ABC,ABCD,,ZZ,WLOA,2019-12-01,2020-12-01,788.81,ZZZ,P,888888.10
C,ABC,ABCD,,ZZ,WLOA,2016-12-01,2017-12-01,2696.58,ZZZ,P,666666.00
C,ABC,ABCD,,ZZ,WLOA,2017-12-01,2018-12-01,788.81,ZZZ,P,777777.10
C,ABC,ABCD,,ZZ,WLOA,2020-12-31,2021-10-29,788.81,ZZZ,P,999999.10
C,ABC,ABCD,,ZZ,WLOA,2015-12-01,2016-12-01,2927.19,ZZZ,P,333333.00

Please not that field 4 is empty as substr indices start at 1, not 0.
This User Gave Thanks to RudiC For This Post:
Ads89
# 3  
Old 02-17-2016
Works perfect - thank you for your help!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pick the column value including comma from csv file using awk

Source 1 column1 column2 column 3 column4 1,ganesh,1,000,1 222,ram,2,000,5 222,ram,50,000,5 33,raju,5,000,7 33,raju,5,000,7 33,raju,5,000,8 33,raju,5,000,4 33,raju,5,000,1 In my .csv file, third column is having price value with comma (20,300), it has to be considered 1,000 as... (1 Reply)
Discussion started by: Ganesh L
1 Replies

2. Shell Programming and Scripting

Pick the column value based on another column using awk or CUT

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Please someone help me to resolve this issue. Source column1 column2 column3 column4... (2 Replies)
Discussion started by: Ganesh L
2 Replies

3. Shell Programming and Scripting

awk command to compare a file with set of files in a directory using 'awk'

Hi, I have a situation to compare one file, say file1.txt with a set of files in directory.The directory contains more than 100 files. To be more precise, the requirement is to compare the first field of file1.txt with the first field in all the files in the directory.The files in the... (10 Replies)
Discussion started by: anandek
10 Replies

4. Shell Programming and Scripting

How to pick a group of data using awk/ksh

Hi gurus, I have data coming in as shown below. And in each case, I need to pick the data in the last group as shown below. Data Set 1: DC | 18161621 LA | 15730880 NY | 16143237 DC | 18161621 LA | 17316397 NY | 17915905 DC | 18161621 LA | 17993534 NY | 18161621 DC | 18161621... (11 Replies)
Discussion started by: calredd
11 Replies

5. Shell Programming and Scripting

use awk pick value from lines as condition for grep

Hi Folks! I have a file like this 000000006 dist:0.0 FILE ./MintRoute/MultiHopWMEWMA.nc LINE:305:1 NODE_KIND:131 nVARs:4 NUM_NODE:66 TBID:733 TEID:758 000000000 dist:0.0 FILE ./Route/MultiHopLEPSM.nc LINE:266:1 NODE_KIND:131 nVARs:4 NUM_NODE:66 TBID:601 TEID:626 000000001 ... (2 Replies)
Discussion started by: jackoverflow
2 Replies

6. UNIX Desktop Questions & Answers

awk to pick out more than one line

This really is a dummy question but I'm stuck and out of time... I have a large file and out of it I only want to pick out lines starting with either "Pressure" or "N". I still need these lines to be in their original order. example of text Pressure 3 N 2 N 3 bla bla bla bla Pressure 4... (3 Replies)
Discussion started by: jenjen_mt
3 Replies

7. Shell Programming and Scripting

Use awk to pick out zip code

Hi, Suppose I have a csv file, each line look like this: ABC Company, 1999, March, caucasian owned, 123 BroadWay NY 92939-2222 How do I create two new columns at the end, one for state, one for zip. So that the line is ABC Company, 1999, March, caucasian owned, 123 BroadWay NY... (2 Replies)
Discussion started by: grossgermany
2 Replies

8. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies

9. Shell Programming and Scripting

Manipulating Pick multi dimensional data with awk.

Hi. I am reasonably new to awk, but have done quite a lot of unix scripting in the past. I have resolved the issues below with unix scripting but it runs like a dog. Moved to awk for speed and functionality but running up a big learning curve in a hurry, so hope there is some help here. I... (6 Replies)
Discussion started by: mike.strategis
6 Replies

10. Shell Programming and Scripting

Pick the latest set of files

I have task in which I need to pickup a set of files from a directory depending on the following criteria: Every month 6 files are expected to arrive at /test. The files come with date timestamp and the latest file set for the month needs to be used Suppose this is the set of files that present... (5 Replies)
Discussion started by: w020637
5 Replies
Login or Register to Ask a Question