Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Add 8 columns at the end of .csv file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add 8 columns at the end of .csv file using awk
# 1  
Old 03-23-2015
Add 8 columns at the end of .csv file using awk
Hello all,
I have a .csv file of 16 columns consists of bunch of numbers.
Code:
       6.45E+01   1.17E+01   8.10E+04   8.21E+01   8.50E+00   1.20E+01   1.02E+01   1.88E+01   1.86E+04   3.53E+03   1.09E+07   3.82E+04   2.09E+03   3.57E+03   2.98E+03   3.93E+03
       6.34E+01   3.23E+01   9.24E+04   3.25E+01   3.18E+01   3.35E+01   3.26E+01   4.19E+01   2.96E+04   1.59E+04   2.86E+07   1.61E+04   1.58E+04   1.59E+04   1.59E+04   1.76E+04
       3.82E+01   3.29E+01   1.04E+05   3.25E+01   3.22E+01   3.23E+01   3.23E+01   3.73E+01   1.75E+04   1.63E+04   5.78E+07   1.62E+04   1.62E+04   1.62E+04   1.62E+04   1.71E+04
       4.16E+01   3.21E+01   4.16E+04   3.26E+01   3.25E+01   3.25E+01   3.27E+01   4.24E+01   1.78E+04   1.62E+04   3.85E+07   1.62E+04   1.62E+04   1.62E+04   1.62E+04   1.79E+04
       4.29E+01   3.28E+01   1.76E+04   3.30E+01   3.31E+01   3.29E+01   3.31E+01   4.08E+01   1.84E+04   1.62E+04   2.48E+07   1.62E+04   1.62E+04   1.62E+04   1.62E+04   1.79E+04
       6.02E+03   2.88E+02   1.78E+04   4.71E+02   3.23E+01   9.60E+03   3.22E+01   3.74E+01   4.26E+06   1.34E+06   2.21E+07   5.42E+05   1.62E+04   1.65E+07   1.62E+04   1.71E+04
       4.09E+01   3.21E+01   4.85E+04   7.88E+04   3.25E+01   1.45E+03   3.30E+01   4.20E+01   1.80E+04   1.64E+04   2.93E+07   3.56E+07   1.62E+04   2.66E+06   1.62E+04   1.79E+04

I want to take the last 8 columns in awk and multiply them with 100 and append the results in new column (column 17 to 24).

This is the code I have tried so far:
Code:
awk 'BEGIN{FS=",";RS="\n";OFS=",";ORS="\n";} {for (i=1;i<=8;++i){$(NF+i)=$(i+8)*100;}}1' file.csv > newfile.csv

I am getting the results, but problem is, it is appending extra "," in the csv before every new columns. So after 1 extra "," after the 16th column with new data from column 9, then 1 extra "," columns after 17th with the new data from column 10 and so on..... Basically it is adding extra "," after every loop for the value of i.
Output Observing:
Code:
>awk 'NR==1 {print $0}' newfile.csv 
2.668750000000000000e+01,1.687500000000000000e+00,1.500000000000000000e+00,6.712500000000000000e+01,1.625000000000000000e+00,4.187500000000000000e+00,7.875000000000000000e+00,1.375000000000000000e+00,4.298700000000000000e+04,9.099000000000000000e+03,3.378000000000000000e+03,1.111399900000000000e+07,6.906000000000000000e+03,1.057600000000000000e+04,5.911000000000000000e+03,2.280000000000000000e+03,0.030705,,0.00649929,,,0.00241286,,,,7.93857,,,,,0.00493286,,,,,,0.00755429,,,,,,,0.00422214,,,,,,,,0.00162857

Confused on how to fix this...:-(

Also, how to add heading to the new columns that I just created?

Thanks in advance
Last edited by Zam_1234; 03-23-2015 at 03:44 AM.. Reason: Add newlines, change ICODE tags and table creation text to CODE tags.
# 2  
Old 03-23-2015
Linux
Hello Zam_1234,
How about this?
Code:
awk 'BEGIN{FS=",";RS="\n";OFS=",";ORS="\n";} {for (i=1;i<=8;++i){$(16+i)=$(i+8)*100;}}1' file.csv > newfile.csv

This User Gave Thanks to MasWag For This Post:
Zam_1234
# 3  
Old 03-23-2015
Quote:
Originally Posted by MasWag
Hello Zam_1234,
How about this?
Code:
awk 'BEGIN{FS=",";RS="\n";OFS=",";ORS="\n";} {for (i=1;i<=8;++i){$(16+i)=$(i+8)*100;}}1' file.csv > newfile.csv

This worked. But why didn't the one I had worked? Seems like only change is I was using "NF" and you use the hardcoded number "16".

Also, if the number of columns change in the future, what is way to do this robustly.

:-)
# 4  
Old 03-23-2015
I am confused.

The sample input data you provided was a single line with 112 fields; not the 16 field lines you were talking about. (I tried guessing at the intended format, but I have no idea whether I guessed correctly.)

Your sample awk code specifies that the input and output field separators are commas, but your sample input data contains no commas.

You say you want to add headings to the new fields you create, but you don't specify what the headings should be and there aren't any headings on the other fields (to use as examples).

And, the sample output you showed us can't possibly have been produced from the sample input you showed us.

Please clearly specify what format your input takes, and show us a sample of the output you're trying to produce. (And PLEASE use CODE tags (not ICODE tags) for sample input and output as well as for code samples.)
This User Gave Thanks to Don Cragun For This Post:
drl
# 5  
Old 03-23-2015
Quote:
Originally Posted by Don Cragun
I am confused.

The sample input data you provided was a single line with 112 fields; not the 16 field lines you were talking about. (I tried guessing at the intended format, but I have no idea whether I guessed correctly.)

Your sample awk code specifies that the input and output field separators are commas, but your sample input data contains no commas.

You say you want to add headings to the new fields you create, but you don't specify what the headings should be and there aren't any headings on the other fields (to use as examples).

And, the sample output you showed us can't possibly have been produced from the sample input you showed us.

Please clearly specify what format your input takes, and show us a sample of the output you're trying to produce. (And PLEASE use CODE tags (not ICODE tags) for sample input and output as well as for code samples.)
I understand your confusion. Here is hopefully more clarified input file format that I have:
Code:
awk 'NR==1{print $0}' input.csv 
6.450000000000000000e+01,1.168750000000000000e+01,8.102425000000000000e+04,8.206250000000000000e+01,8.500000000000000000e+00,1.200000000000000000e+01,1.018750000000000000e+01,1.875000000000000000e+01,1.864400000000000000e+04,3.527000000000000000e+03,1.093483900000000000e+07,3.818700000000000000e+04,2.089000000000000000e+03,3.574000000000000000e+03,2.979000000000000000e+03,3.933000000000000000e+03

(I copy/pasted from MS Excel in my original post, thus the confusion of not seeing any commas in the .csv)

For the heading, I want to name each column as following:
x1,x2 ...x8, test_x1, testx2, ...test_x8, mul_x1,mulx2,...mulx8
(total 24 names)

Thanks in advance.
# 6  
Old 03-23-2015
With your new input format, and assuming you want the added fields in the same numeric format, and adding the headers you requested; try:
Code:
awk '
BEGIN {	for(i = 1; i <= 8; i++) {
		$i = sprintf("x%d", i)
		$(8 + i) = sprintf("test_x%d", i)
		$(16 + i) = sprintf("mul_x%d", i)
	}
	FS = OFS = ","
	print
}
{	for(i = 9; i <= 16; i++)
		$(i + 8) = sprintf("%.18e", $i * 100)
}
1' input.csv > newfile.csv

which, with your latest single line sample input produces the output:
Code:
x1,x2,x3,x4,x5,x6,x7,x8,test_x1,test_x2,test_x3,test_x4,test_x5,test_x6,test_x7,test_x8,mul_x1,mul_x2,mul_x3,mul_x4,mul_x5,mul_x6,mul_x7,mul_x8
6.450000000000000000e+01,1.168750000000000000e+01,8.102425000000000000e+04,8.206250000000000000e+01,8.500000000000000000e+00,1.200000000000000000e+01,1.018750000000000000e+01,1.875000000000000000e+01,1.864400000000000000e+04,3.527000000000000000e+03,1.093483900000000000e+07,3.818700000000000000e+04,2.089000000000000000e+03,3.574000000000000000e+03,2.979000000000000000e+03,3.933000000000000000e+03,1.864400000000000000e+06,3.527000000000000000e+05,1.093483900000000000e+09,3.818700000000000000e+06,2.089000000000000000e+05,3.574000000000000000e+05,2.979000000000000000e+05,3.933000000000000000e+05

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk.

As for why your code was adding empty fields:
Code:
awk '...{for (i=1;i<=8;++i){$(NF+i)=$(i+8)*100;}}...'

Assuming that you start with an input record containing 16 fields, NF will initially be 16.
After the 1st time through the loop, NF will be 17 (16 + 1) with $17 set to $9 * 100.
After the 2nd time through the loop, NF will be 19 (17 + 2) with $19 set to $10 * 100.
After the 3rd time through the loop, NF will be 22 (19 + 3) with $22 set to $11 * 100.
...
After the 8th time through the loop, NF will be 52 (44 + 8) with $52 set to $16 * 100.
And all of the unset fields (18, 20, 21, 23, 24, 25, 27, ... 51) will default to the empty string.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To add multiple columns in csv file

Hi Guys, Can you help to add static values at the end of the csv file with headers input_file id,name 1,neo 2,pull Expected id,name,status,entry,g_id 1,neo,done,2019-11-01T07:14:23,pass 2,pull,done,2019-11-01T07:14:23,pass My try but not able replacing properly and unable... (1 Reply)
Discussion started by: Master_Mind
1 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Linux

Add empty columns at the end of a CSV file

I have a .CSV file (lets say named as file.csv) with numeric and string values. The string might contain commas hence they are enclosed in double quotes as in the below format. column1,column2,column3,column4,column5,column6,column7 12,455,"string, with, quotes, and with, commas, in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

4. Shell Programming and Scripting

awk filter by columns of file csv

Hi, I would like extract some lines from file csv using awk , below the example: I have the file test.csv with in content below. FLUSSO;COD;DATA_LAV;ESITO ULL;78;17/09/2013;OL ULL;45;05/09/2013;Apertura NP;45;13/09/2013;Riallineamento ULLNP;78;17/09/2013;OL NPG;14;12/09/2013;AperturaTK... (6 Replies)
Discussion started by: giankan
6 Replies

5. Shell Programming and Scripting

Need help with awk statement to break nth column in csv file into 3 separate columns

Hello Members, I have a csv file in the format below. Need help with awk statement to break nth column into 3 separate columns and export the changes to new file. input file --> file.csv cat file.csv|less "product/fruit/mango","location/asia/india","type/alphonso" need output in... (2 Replies)
Discussion started by: awk-admirer
2 Replies

6. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

AWK : Add columns in the end of csv file

Hi everybody, I need some help please I have a csv file named masterFile1.csv header1,header2,header3 value1,value2,value3 value4,value5,value6 I am trying to add new columns in the end of the csv to have a new csv file named masterFile2.csv like this :... (3 Replies)
Discussion started by: villebonnais
3 Replies

8. Shell Programming and Scripting

Add columns in csv file

Hi everybody, I am new here and I need a reel help please A have a csv file and I want to add new in the end of the file Devisce Model,VMGuest Name,Host OS Name, Memory Size Value1,Value2,Value3,Value4 Value5,Value6,Value7,Value8 Value9,Value10,Value11,Value12 And I want to add to new... (3 Replies)
Discussion started by: villebonnais
3 Replies

9. Shell Programming and Scripting

Add Empty columns at the end of csv file

Hi, Can you please tell me how to add empty columns at the end csv file? Currently there are 6 columns in the csv file. I want to add 35 empty columns at the end of this csv file. Thanks, Tushar (17 Replies)
Discussion started by: Tushar Bendale
17 Replies

10. Filesystems, Disks and Memory

manipulate csv file to add columns

Hi, I have a csv file with a key composed by 3 columns and some other numeric fields and I need to obtain the partial amounts by some part of the key. This may be some difficult to understand, so better see an example, where my input file is: name,surname,department,y2004,y2005,y2006... (6 Replies)
Discussion started by: oscarmon
6 Replies
Login or Register to Ask a Question