Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Add the values in second and third columns with group by on first column.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add the values in second and third columns with group by on first column.
# 1  
Old 02-14-2013
Add the values in second and third columns with group by on first column.
Hi All,

I have a pipe seperated file. I need to add the values in second and third columns with group by on first column.

HTML Code:
MYFILE_28012012_1115|47|173.90
MYFILE_28012012_1115|4|0.00
MYFILE_28012012_1115|6|22.20
MYFILE_28012012_1116|47|173.90
MYFILE_28012012_1116|4|0.00
MYFILE_28012012_1116|6|22.20
MYFILE_28012012_1117|47|173.90
MYFILE_28012012_1117|4|0.00
MYFILE_28012012_1117|6|22.20
I expect the result to be as below:

HTML Code:
MYFILE_28012012_1115|57|196.11
MYFILE_28012012_1116|57|173.90
MYFILE_28012012_1117|57|173.90
Any help on this will be highle appreciated

Thanks
Angshuman
# 2  
Old 02-14-2013
Try

Code:
awk -F "|" '{A[$1]+=$2;B[$1]+=$3;}END{for(i in A){print i,A[i],B[i]}}' OFS="|" file

# 3  
Old 02-14-2013
Hi Pamu,

Thank you for your prompt help. I just need to make a small change to it so that the last filed in the out put always displays two decimal places.

May I request your help on this and can I request you to explain the code?

Thanks & Regards
Angshuman
# 4  
Old 02-14-2013
Quote:
Originally Posted by angshuman
I just need to make a small change to it so that the last filed in the out put always displays two decimal places.
try this..

Code:
awk -F "|" '{A[$1]+=$2;B[$1]+=$3;}END{for(i in A){printf "%s|%s|%0.02f\n", i,A[i],B[i]}}' file

Quote:
Originally Posted by angshuman
May I request your help on this and can I request you to explain the code?
Code:
awk -F "|" '{    # Define | as a Field Separator
A[$1]+=$2;        # Add $2 to the array A having index of $1
B[$1]+=$3;}        # Add $3 to the array B having index of $1
END{for(i in A)    # Get all the indexes of array A in i

{printf "%s|%s|%0.02f\n", i,A[i],B[i]}}' file        # Print i(means $1),A[i](means sum of all $2 for the particular $1),B[i](means sum of all $3 for the particular $1). Used 0.02f here for floating purpose.

Hope this helps you.

Regards,

pamu
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

3. Shell Programming and Scripting

Splitting the numeric vs alpha values in a column to distinct columns

How could i take an input file and split the numeric values from the alpha values (123 vs abc) to distinc columns, and if the source is blank to keep it blank (null) in both of the new columns: So if the source file had a column like: Value: |1 | |2.3| | | |No| I would... (7 Replies)
Discussion started by: driftlogic
7 Replies

4. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

5. Shell Programming and Scripting

Average of columns with values of other column with same name

I have a lot of input files that have the following form: Sample Cq Sample Cq Sample Cq Sample Cq Sample Cq 1WBIN 23.45 1WBIN 23.45 1CVSIN 23.96 1CVSIN 23.14 S1 31.37 1WBIN 23.53 1WBIN 23.53 1CVSIN 23.81 1CVSIN 23.24 S1 31.49 1WBIN 24.55 1WBIN 24.55 1CVSIN 23.86 1CVSIN 23.24 S1 31.74 ... (3 Replies)
Discussion started by: isildur1234
3 Replies

6. 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

7. 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

8. 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

9. Shell Programming and Scripting

how to flip values of two columns and add an extra column

Hi guys, Couldn't find the solution of this problem. Please Help! I have a file- Input_File TC200232 92 30 TC215306 2 74 TC210135 42 14 I want an output file in which if column2>column3, the values are swapped and an additional column with value Rev_Com is... (4 Replies)
Discussion started by: smriti_shridhar
4 Replies

10. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies
Login or Register to Ask a Question