Visit Our UNIX and Linux User Community

Des/awk for change format and adding integers in a column of data?

Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Des/awk for change format and adding integers in a column of data?
# 1  
Old 09-23-2013
Des/awk for change format and adding integers in a column of data?


I need a quick way to change the format in a table of data

Here is an example of the input:
10	72	Value=177	VDB=0.0245	Value4=0,0,171,0
10	274	Value=238	VDB=0.0433	Value4=29,0,205,0
10	312	Value=222	VDB=0.0384	Value4=8,0,190,19
10	540	Value=405	VDB=0.0391	Value4=13,30,153,195
10	542	Value=403	VDB=0.0384	Value4=45,35,121,188
10	637	Value=341	VDB=0.0418	Value4=0,1,122,195
10	645	Value=370	VDB=0.0368	Value4=0,1,131,211
10	674	Value=366	VDB=0.0323	Value4=0,5,148,200
10	854	Value=356	VDB=0.0418	Value4=14,11,188,119
10	888	Value=396	VDB=0.0374	Value4=0,0,209,176
10	892	Value=397	VDB=0.0351	Value4=0,1,210,176
10	909	Value=402	VDB=0.0374	Value4=1,6,205,186

What I need is the "Value4" column to be just a number (no text) and that number is the total of all the comma-separated integers.

Desired output:
10	72	Value=177	VDB=0.0245	171
10	274	Value=238	VDB=0.0433	234
10	312	Value=222	VDB=0.0384	217
10	540	Value=405	VDB=0.0391	391
10	542	Value=403	VDB=0.0384	389
10	637	Value=341	VDB=0.0418	318
10	645	Value=370	VDB=0.0368	343
10	674	Value=366	VDB=0.0323	353
10	854	Value=356	VDB=0.0418	332
10	888	Value=396	VDB=0.0374	385
10	892	Value=397	VDB=0.0351	387
10	909	Value=402	VDB=0.0374	398

I feel like this shouldn't be too difficult to do using sed/awk, but can't figure it out.

Any help is greatly appreciated.

# 2  
Old 09-23-2013
See if this helps
awk ' {gsub("[^0-9]|Value4",",",$NF); split($NF,a,","); for (i in a) $NF=a[3]+a[4]+a[5]+a[6] }1 ' inputfile

This User Gave Thanks to Peasant For This Post:
# 3  
Old 09-23-2013
It works! Thanks!
# 4  
Old 09-23-2013
Peasant's proposal works, but I'm not sure why he 6 times sums up the array's values.
awk ' {gsub(/^.*=/, "", $NF); split($NF, a, ","); $NF=0; for (i in a) $NF+=a[i]} 1 ' file

This User Gave Thanks to RudiC For This Post:

Previous Thread | Next Thread
Test Your Knowledge in Computers #399
Difficulty: Medium
The 11i v2 release of HP-UX introduced kernel-based intrusion detection, strong random number generation, stack buffer overflow protection, security partitioning, role-based access management, and various open-source security tools.
True or False?

4 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Adding new column data in csv from UNIX

Adding new column data in csv from UNIX Hi I need to add new column data daily to existing csv file. Please assist 7/11 7/10 7/9 7/8 space 10 GB 20 GB I was able to generate current day's data in csv but unable to add the previous 30 days data to the same csv Please use code tags,... (2 Replies)
Discussion started by: archana25
2 Replies

2. Shell Programming and Scripting

Change data in one column with data from another file's column

Hello, I have this file outputData: # cat /tmp/outputData __Capacity^6^NBSC01_Licences^L3_functionality_for_ESB_switch __Capacity^2100^NBSC01_Licences^Gb_over_IP __Capacity^1837^NBSC01_Licences^EDGE_BSS_Fnc __Capacity^1816^NBSC01_Licences^GPRS_CS3_and_CS4... (1 Reply)
Discussion started by: nypreH
1 Replies

3. Shell Programming and Scripting

awk - script help: column to row format of data allignment?

Experts Good day, I have the following data, file1 BRAAGRP1 A2X B2X C2X D2X BRBGRP12 A3X B3X Z10 D09 BRC1GRP2 LO01 (4 Replies)
Discussion started by: rveri
4 Replies

4. Shell Programming and Scripting

Format - Inventory Row data into Column - Awk - Nawk

Hi All, I have the following file that has computer data for various pcs in my network... Snap of the file is as follows ******************************************************************************* Serial 123456 Computer IP Address lo0:... (1 Reply)
Discussion started by: aavam
1 Replies

Featured Tech Videos