INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sum multiple columns output to single row to in separate file.
2

Sum multiple columns output to single row to in separate file.

Sum multiple columns output to single row to in separate file.

(OP)
I am hoping someone can help me find a way to sum two columns but output the answer into a separate file using the first row of the original file as such:

0000229351,4,5.00,533017068270,31.96,UPAC
0000229351,4,3.00,533017068280,27.08,UPAC
0000229351,4,5.00,533017068291,31.96,UPAC
0000229351,4,5.00,533017068306,31.96,UPAC

Desired Output:
0000229351,4,18.00,533017068270,122.96,UPAC

I am targeting columns 3 and 5 in this example and using the information from row 1 in columns 1,2,4,6. Any help with this issue would be greatly appreciated. Thank you.

RE: Sum multiple columns output to single row to in separate file.

(OP)
Thank you PHV. Sorry it took so long to respond. I forgot to mention I am using gawk in a windows command line environment and am receiving the following error:

CODE -->

C:\Windows\system32>awk -F, "NR==1{split($0,x,",")}NR>1{x[3]+=$3;x[5]+=$5}END{OF
S=",";print x[1],x[2],x[3],x[4],x[5],x[6]}" /path/to/input 
awk: NR==1{split($0,x,,)}NR>1{x[3]+=$3;x[5]+=$5}END{OFS=,;print x[1],x[2],x[3],x
[4],x[5],x[6]}
awk:                  ^ syntax error
awk: NR==1{split($0,x,,)}NR>1{x[3]+=$3;x[5]+=$5}END{OFS=,;print x[1],x[2],x[3],x
[4],x[5],x[6]}
awk:                   ^ syntax error
awk: fatal: 0 is invalid as number of arguments for split 

The only change I made to your original code was changing the single quotes to double quotes because windows doesn't like single. I also changed the command to gawk but still have the same problem. Any help you might have would be greatly appreciated. Thank you.

RE: Sum multiple columns output to single row to in separate file.

(OP)
Thank you PHV this worked. However, I did not stop to consider the possibility that the input file may contain additional lines of information that are not related to each other but still aggregate the same:

0000229351,4,5.00,533017068270,31.96,UPAC
0000229351,4,3.00,533017068280,27.08,UPAC
0000229351,4,5.00,533017068291,31.96,UPAC
0000229351,4,5.00,533017068306,31.96,UPAC
0000229355,3,3.00,533017067708,22.51,UPAC
0000229355,3,6.00,533017067715,25.33,UPAC
0000229466,2,10.00,533017067890,16.25,UPAC

Desired Output:
0000229351,4,18.00,533017068270,122.96,UPAC
0000229355,3,9.00,533017067708,47.84,UPAC
0000229466,2,10.00,533017067890,16.25,UPAC

RE: Sum multiple columns output to single row to in separate file.

(OP)
PHV you rock! This works beautifully. Thank you for your help and patience.

RE: Sum multiple columns output to single row to in separate file.

(OP)
PHV, I found a slight problem on our end when I use your script:

CODE

BEGIN{FS=OFS=","}
function myprint(){
  if(NR>1)print x[1],x[2],x[3],x[4],x[5],x[6]
  split($0,x,",");b=$1
}
b!=$1{myprint();next}
{x[3]+=$3;x[5]+=$5}
END{myprint()} 

Although created for mulitple lines of data we have the actual possibility of there only being a single line of data. Can this be altered to do continue doing what it is doing but include the ability to output a single line of data if it exists? Any help with this alteration would be greatly appreciated. Thank you.

RE: Sum multiple columns output to single row to in separate file.

Rearranging the code slightly should fix that:

CODE --> AWK

BEGIN{FS=OFS=","}
function myprint(){
  print x[1],x[2],x[3],x[4],x[5],x[6]
}
b!=$1{if(NR>1)myprint();split($0,x,",");b=$1;next}
{x[3]+=$3;x[5]+=$5}
END{myprint()} 

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Sum multiple columns output to single row to in separate file.

(OP)
This works fantastic. Annihilannic thank you very much!

RE: Sum multiple columns output to single row to in separate file.

(OP)
Yes, I'm back with another challenge. Recently this script ran against an empty file (I just now became aware of this possibility) and it output the following:

CODE

,,,,, 

Is there a way to make this script output an empty file if the file it is run against is empty of any data? Thank you for any help you might be able to provide.

RE: Sum multiple columns output to single row to in separate file.

Just add a check in the myprint() function to see whether there is any data in x[1] before printing.

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Sum multiple columns output to single row to in separate file.

(OP)
Thank you Annihilannic. I tried to create a check however, my skills are basically nill. I have only managed to create a blank line before the commas.

RE: Sum multiple columns output to single row to in separate file.

(OP)
Thank you PHV. I guess I wasn't too far off. Am I correct in assuming that what this states is if x[1] is not empty then print all 6 fields "else" don't print anything. The "else" is basically implied?

RE: Sum multiple columns output to single row to in separate file.

thumbsup2

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Sum multiple columns output to single row to in separate file.

(OP)
I just wanted to tell you both thank you very much for your help and your patience.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close