INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Brilliant! Your site is great...and saving me hours of time at work and making my boss think I am brilliant too! I also picked up on a thread that will potentially save us a lot of money in the future..."
Geography
Where in the world do Tek-Tips members come from?
|
Sum multiple columns output to single row to in separate file. (2)
|
|
|
chino10 (TechnicalUser) |
2 Jul 12 15:56 |
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. |
|
CODE --> TypedUntestedawk -F, '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]}' /path/to/input Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer? |
|
|
chino10 (TechnicalUser) |
5 Jul 12 8:07 |
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. |
|
Use a file to store the awk program, eg chino10.awk:
CODE --> chino10.awkNR==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]}
And then:
gawk -F, -f chino10.awk \path\to\input Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer? |
|
|
chino10 (TechnicalUser) |
5 Jul 12 9:46 |
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
|
|
A starting point:
CODE --> chino10.awkBEGIN{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()}
And then:
gawk -f chino10.awk input Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer? |
|
|
chino10 (TechnicalUser) |
5 Jul 12 13:17 |
PHV you rock! This works beautifully. Thank you for your help and patience. |
|
|
chino10 (TechnicalUser) |
19 Sep 12 9:40 |
PHV, I found a slight problem on our end when I use your script:
CODEBEGIN{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. |
|
Rearranging the code slightly should fix that:
CODE --> AWKBEGIN{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 |
|
|
chino10 (TechnicalUser) |
20 Sep 12 7:58 |
This works fantastic. Annihilannic thank you very much! |
|
|
chino10 (TechnicalUser) |
25 Sep 12 0:42 |
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:
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. |
|
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 |
|
|
chino10 (TechnicalUser) |
25 Sep 12 9:29 |
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. |
|
|
PHV (MIS) |
25 Sep 12 10:33 |
|
|
chino10 (TechnicalUser) |
25 Sep 12 10:50 |
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? |
|
 Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts |
|
|
chino10 (TechnicalUser) |
26 Sep 12 7:42 |
I just wanted to tell you both thank you very much for your help and your patience. |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One