Sponsored Content
Top Forums Shell Programming and Scripting Sum of multiple columns based on few conditions Post 302906409 by vinus on Thursday 19th of June 2014 11:09:46 AM
Old 06-19-2014
Sum of multiple columns based on few conditions

Input file:
Code:
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003200,0000111
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00001,0003400,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00007,0003100,0000200
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003000,0000100
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003100,0000100
0013,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100

i need to sum of multiple columns(8th,9th,10th) based on few conditions on the above file.
1) Sort by column 1
2)Sum of columns(8th,9th,10th) based on unique column 1 and column 5.
3)print the original record and print the output in a separate line(second column value should be T
and 6th column should be blank in the output line)

Output:
Code:
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003000,0000100
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003100,0000100
0012,T,2020,K,047225000000,                        ,B,00002,0006100,0000200
0013,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0013,T,2020,K,047225000000,                       ,B,00001,0003300,0000100
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003200,0000111
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00001,0003400,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00007,0003100,0000200
0014,T,2020,K,011115000000,                        ,B,00002,0006500,0000211
0014,T,2020,K,011116000000,                        ,B,00008,0006500,0000300

I tried the below command, the total is coming correct. The formatting is not correct and need the output mentioned above
Code:
awk -F',' '{a[$1"."$5]+=$8;b[$1"."$5]+=$9;c[$1"."$5]+=$10;f[$1"."$5]=$2;
g[$1"."$5]=$1;h[$1"."$5]=$3;
j[$1"."$5]=$4;k[$1"."$5]=$5;
r[$1"."$5]=$7
l[$1"."$5]=$6;m[$1"."$5]=$7}END{for(i in a)print g[i]",",f[i]",",h[i]",",j[i]",",k[i]",",l[i]",",r[i]",",a[i]",",b[i]",",c[i] |"sort -t ',' -n -k1,1"}' file.txt

Can anyone please assist me?

Last edited by bartus11; 06-19-2014 at 12:19 PM.. Reason: Please use [code][/code] tags.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk 3 files to one based on multiple columns

Hi all, I have three files, one is a navigation file, one is a depth file and one is a file containing the measured field of gravity. The formats of the files are; navigation file: 2006 320 17 39 0 0 *nav 21.31542 -157.887 2006 320 17 39 10 0 *nav 21.31542 -157.887 2006 320 17 39 20 0... (2 Replies)
Discussion started by: andrealphus
2 Replies

2. Shell Programming and Scripting

Sorting based on Multiple columns

Hi, I have a requirement whereby I have to sort a flat file based on Multiple Columns (similar to ORDER BY Clause of Oracle). I am getting 10 columns in the flat file and I want the file to be sorted on 1st, 3rd, 4th, 7th and 9th columns in ascending order. The flat file is pipe seperated. Any... (15 Replies)
Discussion started by: dharmesht
15 Replies

3. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

4. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

5. Shell Programming and Scripting

Sum a column value based on multiple keys

Hi, I have below as i/p file: 5ABC 36488989 K 000010000ASB BYTRES 5PQR 45757754 K 000200005KPC HGTRET 5ABC 36488989 K 000045000ASB HGTRET 5GTH 36488989 K 000200200ASB BYTRES 5FTU ... (2 Replies)
Discussion started by: nirnkv
2 Replies

6. Shell Programming and Scripting

sort by based on multiple columns

Hi, Is there any way to sort a file in cshell by sort command, sorting it by multiple fields, like to sort it first by the second column and then by the first column. Thanks forhead (1 Reply)
Discussion started by: Takeeshe
1 Replies

7. Shell Programming and Scripting

Sort based on Multiple Columns in UNIX

Hi, I would like to sort a list in different ways: 1> Unique based on Field 1 with highest Field 4 For Instance Input: 1678923450;11112222333344;11-1x;2_File.xml 1678923450;11112222333344;11-1x;5_File.xml 1234567890;11113333222244;11-1x;3_File.xml Output: ... (7 Replies)
Discussion started by: DevendraG
7 Replies

8. Shell Programming and Scripting

Merge records based on multiple columns

Hi, I have a file with 16 columns and out of these 16 columns 14 are key columns, 15 th is order column and 16th column is having information. I need to concate the 16th column based on value of 1-14th column as key in order of 15th column. Here are the example file Input File (multiple... (3 Replies)
Discussion started by: Ravi Agrawal
3 Replies

9. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

10. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

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... (12 Replies)
Discussion started by: as7951
12 Replies
GRID-CERT-INFO(1)						  Globus Commands						 GRID-CERT-INFO(1)

NAME
grid-cert-info - Display information about a certificate SYNOPSIS
grid-cert-info [-help] [-usage] [-version] [-versions] grid-cert-info [-file CERTIFICATE-FILE] [-rfc2253] [-all] [-subject] | [-s] [-issuer] | [-i] [-issuerhash] | [-ih] [-startdate] | [-sd] [-endate] | [-ed] DESCRIPTION
The grid-cert-info program displays information contained within a certificate file. By default it shows a text representation of the entire certificate. Specific facts about the certificate can be shown instead by using command-line options. If any of those options are used, then the default display is suppressed. This can be added to the output by using the -all command-line option. If multiple display options are included on the command-line, the facts related to those will be displayed on separate lines in the order that they occur. If an option is specified multiple time, that fact will be displayed multiple times. The full set of command-line options to grid-cert-info are: -help, -usage Display the command-line options to grid-cert-info and exit. -version, -versions Display the version number of the grid-cert-info command. The second form includes more details. -file CERTIFICATE-FILE Display information about the first certificate contained in the file named by CERTIFICATE-FILE instead of the default user certificate. -rfc2253 Display X.509 distinguished names using the string representation defined in RFC 2253 instead of the default OpenSSL oneline format. -all Display the text representation of the entire certificate in addition to any other facts requested by command-line options. This is the default if no fact-specific command-line options are used. -subject, -s Display the subject name of the X.509 certificate. -issuer, -i Display the issuer name of the X.509 certificate. -issuerhash, -ih Display the default hash of the issuer name of the X.509 certificate. This can be used to locate which CA certificate in the trusted certificate directory issued the certifcate being inspected. -startdate, -sd Display a string representation of the date and time when the certificate is valid from. This is displayed in the format used by the OpenSSL x509 command. -enddate, -dd Display a string representation of the date and time when the certificate is valid until. This is displayed in the format used by the OpenSSL x509 command. EXAMPLES
Display the validity times for the default certificate % grid-cert-info -sd -ed Aug 31 12:33:47 2009 GMT Aug 31 12:33:47 2010 GMT Display the same information about a different certificate specified on the command-line % grid-cert-info -sd -ed -f /etc/grid-security/hostcert.pem Jan 21 12:24:48 2003 GMT Jul 15 11:30:57 2020 GMT Display the subject of a certificate in both the default and the RFC 2253 forms. % grid-cert-info -subject /DC=org/DC=example/DC=grid/CN=Joe User % grid-cert-info -subject -rfc2253 CN=Joe User,DC=grid,DC=example,DC=org ENVIRONMENT VARIABLES
The following environment variables affect the execution of grid-cert-info: X509_USER_CERT Path to the default certificate file to inspect. AUTHOR
University of Chicago Globus Toolkit 5.0.1 03/22/2010 GRID-CERT-INFO(1)
All times are GMT -4. The time now is 06:31 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy