Sponsored Content
Top Forums Shell Programming and Scripting Column sum group by uniq records Post 302317782 by sandeep13 on Wednesday 20th of May 2009 02:25:31 AM
Old 05-20-2009
Hi Guys,

Once again I have a query regarding grouping the columns, below is my requirement:

Input File:
COL1; COL2; AMT1;AMT2
PORT1;CURR1;100;50
PORT1;CURR1;200;100
PORT2;CURR2;300;150
PORT3;CURR3;400;200
PORT3;CURR3;500;250

Expected Output:
COL1; COL2; AMT1;AMT2
PORT1;CURR1;300;150
PORT2;CURR2;300;150
PORT3;CURR3;900;450

How can I pass to values in below command:
/usr/bin/nawk 'BEGIN{FS=OFS=";"}NR==1{print;next}{a[$1";"$2]+=$3}END{for(i in a)print i, a[i]}' <INPUT FILE>

Please suggest so that I can get both AMT1 and AMT2 gouped by COL1 and COL2.
Thanks in advance.

Regards,
Sandeep
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to calculate a sum of certain records?

Hi, i have a file where the records are like this. vt100 2048 D402 MG0010 0 586 262144 D403 MG0011 1000 486 8192 D404 MG0012 270 386 8192 A423 CC0177 40 586 65536 A424 CC0182 670 486 16384 A423 CC0183 100 486 16384 A425 CC0184 80 65000 4096 B407 EE1027 80 I want firstly, to count how... (10 Replies)
Discussion started by: sickboy
10 Replies

2. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

3. Shell Programming and Scripting

Merge group numbers and add a column containing group names

Hi All I do have a file like this with 6 columns. Groups of data merge together and the group number is indicated above each group. 1 1 12 26 289 3.2e-027 GCGTATGGCGGC 2 12 26 215 6.7e+006 TTCCACCTTTTG 3 9 26 175 ... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

4. Shell Programming and Scripting

Sum of column by group wise

Hello All , I have a problem with summing of column by group Input File - COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11 3010,21,1923D ,6,0,0.26,0,0.26,-0.26,1,200807 3010,21,192BI ,6,24558.97,1943.94,0,1943.94,22615.03,1,200807 3010,21,192BI... (8 Replies)
Discussion started by: jambesh
8 Replies

5. Shell Programming and Scripting

Sum up the column values group by using some field

12-11-2012,PNL,158406 12-11-2012,RISK,4564 12-11-2012,VAR_1D,310101 12-11-2012,VAR_10D,310101 12-11-2012,CB,866 12-11-2012,STR_VAR_1D,298494 12-11-2012,STR_VAR_10D,309623 09-11-2012,PNL,1024106 09-11-2012,RISK,4565 09-11-2012,VAR_1D,317211 09-11-2012,VAR_10D,317211 09-11-2012,CB,985... (7 Replies)
Discussion started by: manas_ranjan
7 Replies

6. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

7. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

8. UNIX for Dummies Questions & Answers

Match sum of values in each column with the corresponding column value present in trailer record

Hi All, I have a requirement where I need to find sum of values from column D through O present in a CSV file and check whether the sum of each Individual column matches with the value present for that corresponding column present in the trailer record. For example, let's assume for column D... (9 Replies)
Discussion started by: tpk
9 Replies

9. Shell Programming and Scripting

Bring values in the second column into single line (comma sep) for uniq value in the first column

I want to bring values in the second column into single line for uniq value in the first column. My input jvm01, Web 2.0 Feature Pack Library jvm01, IBM WebSphere JAX-RS jvm01, Custom01 Shared Library jvm02, Web 2.0 Feature Pack Library jvm02, IBM WebSphere JAX-RS jvm03, Web 2.0 Feature... (10 Replies)
Discussion started by: kchinnam
10 Replies

10. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (1 Reply)
Discussion started by: as7951
1 Replies
Vend::Ship::QueryUPS(3pm)				User Contributed Perl Documentation				 Vend::Ship::QueryUPS(3pm)

NAME
Vend::Ship::QueryUPS -- calculate UPS costs via www SYNOPSIS
(catalog.cfg) Shipping QueryUPS default_geo 45056 (shipping.asc) ground: UPS Ground Commercial origin 45056 service GNDCOM min 0 max 0 cost e Nothing to ship! min 0 max 150 cost s QueryUPS min 150 max 99999999 cost e Too heavy for UPS. DESCRIPTION
Calculates UPS costs via the WWW using Business::UPS. To activate, configure any parameter in catalog.cfg. A good choice is the default origin zip. Options: weight Weight in pounds. Required -- normally passed via CRIT parameter. service Any valid Business::UPS mode (required). Example: 1DA,2DA,GNDCOM. Defaults to the mode name. geo Location of field containing zip code. Default is 'zip'. country_field Location of field containing country code. Default is 'country'. default_geo The ZIP code to use if none supplied -- for defaulting shipping to some value in absence of ZIP. No default -- will return 0 and error if no zip. default_country The country code to use if none supplied -- for defaulting shipping to some value in absence of country. Default US. aggregate If 1, aggregates by a call to weight=150 (or $Variable->{UPS_QUERY_MODULO}). Multiplies that times number necessary, then runs a call for the remainder. In other words: [ups-query weight=400 mode=GNDCOM aggregate=1] is equivalent to: [calc] [ups-query weight=150 mode=GNDCOM] + [ups-query weight=150 mode=GNDCOM] + [ups-query weight=100 mode=GNDCOM]; [/calc] If set to a number above 1, will be the modulo to do repeated calls by. So: [ups-query weight=400 mode=GNDCOM aggregate=100] is equivalent to: [calc] [ups-query weight=100 mode=GNDCOM] + [ups-query weight=100 mode=GNDCOM] + [ups-query weight=100 mode=GNDCOM] + [ups-query weight=100 mode=GNDCOM]; [/calc] To aggregate by 1, use .999999. cache_table Set to the name of a table (default ups_cache) which can cache the calls so repeated calls for the same values will not require repeated calls to UPS. Table needs to be set up with: Database ups_cache ship/ups_cache.txt __SQLDSN__ Database ups_cache AUTO_SEQUENCE ups_cache_seq Database ups_cache DEFAULT_TYPE varchar(12) Database ups_cache INDEX weight origin zip shipmode country And have the fields: code weight origin zip country shipmode cost updated Typical cached data will be like: code weight origin zip country shipmode cost updated 14 11 45056 99501 US 2DA 35.14 1052704130 15 11 45056 99501 US 1DA 57.78 1052704130 16 11 45056 99501 US 2DA 35.14 1052704132 17 11 45056 99501 US 1DA 57.78 1052704133 Cache expires in one day. perl v5.14.2 2010-03-25 Vend::Ship::QueryUPS(3pm)
All times are GMT -4. The time now is 10:04 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy