Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

printing two lines in awk as two columns in excel

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting printing two lines in awk as two columns in excel
# 1  
Old 12-20-2008
printing two lines in awk as two columns in excel
hi guys, i would like to print two lines from a file as two adjacent columns using excel using awk.. i have this so far:

Code:
awk '{for(i=1; i<=NF; i++) {printf("%s\n",$i)}}' "$count".ttt  > "$count".csv      #this to print the first line from the .ttt file as rows of the first column in the .csv file

awk '{ for(i=1; i<=NF; i++) {array[i]+=$i} row++}
       END {for(i=1; i<=NF; i++) {print array[i]/row}}' "$count".spd >> "$count".csv    #this is to print the data

with this i am getting:
Code:
first column in excel:
name1
name2
name3
name4

data 1
data 2
data 3
data 4

i want:
Code:
first column in excel       second column in excel
 name1                             data1
  name2                            data2
  name3                             data3
  name4                             data4

appreciate any help.
# 2  
Old 12-20-2008
Can you post an example of the contents of the ttt and spd files?

Regards
# 3  
Old 12-20-2008
the files are huge but here is an excerpt:
the .ttt file has about 700 fields(columns) in one row delimited by a space as follows:
Code:
name1 name2 name3 name4... name700

the .spd file has the same 700 columns with data:
Code:
name1 name2 ... name700
1          3           7
2          4           8
3          5           9
4          6           10

i am trying to do this in an excel file:

Code:
Column1       Column2
name1          average of name1
name2          average of name2
.
.
.
name700       average of name700

i would prefer to keep my code if possible with some mods..
# 4  
Old 12-20-2008
Try this, I've combine (not tested) the 2 commands:

Code:
awk 'NR==FNR{ for(i=1; i<=NF; i++) {array[i]+=$i} row++;next}
{for(i=1; i<=NF; i++) {printf("%s %.2f\n",$i,array[i]/row)}}' "$count".spd "$count".ttt  > "$count".csv

Regards
# 5  
Old 12-20-2008
can you please explain how u did it?

i had to make a minor change to make sure the values were in two separate cells.. i made them comma separated.. i put a comma after the %s.. but it worked!
here is the code

Code:
awk 'NR==FNR{ for(i=1; i<=NF; i++) {array[i]+=$i} row++;next}
{for(i=1; i<=NF; i++) {printf("%s , %.2f\n",$i,array[i]/row)}}' "$count".spd "$count".ttt  > "$count".csv

thanks
# 6  
Old 12-20-2008
Quote:
Originally Posted by npatwardhan
can you please explain how u did it?

i had to make a minor change to make sure the values were in two separate cells.. i made them comma separated.. i put a comma after the %s.. but it worked!
here is the code

Code:
awk 'NR==FNR{ for(i=1; i<=NF; i++) {array[i]+=$i} row++;next}
{for(i=1; i<=NF; i++) {printf("%s , %.2f\n",$i,array[i]/row)}}' "$count".spd "$count".ttt  > "$count".csv

thanks
The 1st row of the command reads the spd file in the array (NR==FNR). The 2nd row of the command reads the ttt file and prints the values of the array and fields of the ttt file.

Regards
# 7  
Old 12-20-2008
thanks for your help!!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk - printing new lines based of 2 dates

I have some test data that is seperated out into annual records, each record has a start date (COL7), an end date (COL8) and a maturity date (COL18) - What I need to do is ensure that there is one record to cover each year right up until Maturity date (COL18). In the first group of the below... (10 Replies)
Discussion started by: Ads89
10 Replies

2. Shell Programming and Scripting

UNIX awk pattern matching and printing lines

I have the below plain text file where i have some result, in order to mail that result in html table format I have written the below script and its working well. cat result.txt Page 2015-01-01 2000 Colors 2015-02-01 3000 Landing 2015-03-02 4000 #!/bin/sh LOG=/tmp/maillog.txt... (1 Reply)
Discussion started by: close2jay
1 Replies

3. Shell Programming and Scripting

Merging multiple lines to columns with awk, while inserting commas for missing lines

Hello all, I have a large csv file where there are four types of rows I need to merge into one row per person, where there is a column for each possible code / type of row, even if that code/row isn't there for that person. In the csv, a person may be listed from one to four times... (9 Replies)
Discussion started by: RalphNY
9 Replies

4. Shell Programming and Scripting

awk pattern match not printing desired columns

Hi all, I'm trying to match the following two files with the code below: awk -F, 'NR==FNR {a=$0; next} ($12,$4) in a {print $12,$1,a}' OFS="," file4.csv file3.csv but the code does not print the entire row from file4 in addition to column 12 and 1 of file3. file4: o,c,q,co,ov,b... (1 Reply)
Discussion started by: bkane3
1 Replies

5. UNIX for Dummies Questions & Answers

Printing lines with specific strings at specific columns

Hi I have a file which is tab-delimited. Now, I'd like to print the lines which have "chr6" string in both first and second columns. Could anybody help? (3 Replies)
Discussion started by: a_bahreini
3 Replies

6. Shell Programming and Scripting

printing only columns containing a determined character with awk

Hello I'm trying to extract two columns from a database using awk, the thing is i have a variable number of columns in each row, and I just need to get out the two first ones ending in "," This is an example: ABE, ABE V149 MAZIE ARD CYN ACY, ACY, , , ,TEC, , 5000, , , 1, ZNY,ZDC ABE, ABE... (1 Reply)
Discussion started by: vir
1 Replies

7. Shell Programming and Scripting

Help With AWK Matching and Re-printing Lines

Hi All, I'm looking to use AWK to pattern match lines in XML file - Example patten for below sample would be /^<apple>/ The sample I wrote out is very basic compared to what I am actually working with but it will get me started I would like to keep the matched line(s) unchanged but have them... (4 Replies)
Discussion started by: rhoderidge
4 Replies

8. Shell Programming and Scripting

use awk to read randomly located columns in an excel file

Hi, I have an excel file that have a random count of columns/fields and what im trying to do is to only retrieve all the rows under 2 specific field headers. I can use the usually command for awk which is awk 'print{ $1 $2}' > output.txt, but the location of the 2 specific field headers is... (9 Replies)
Discussion started by: mdap
9 Replies

9. UNIX for Dummies Questions & Answers

awk printing all columns after (but including) $n

I am piping an "ls -l" to awk so that all it returns is the file size, date, and file name. The problem is that some files may have spaces in the name so awk is only printing the first word in the file name. I won't know how many space-delimited words are in the filename, so what I want to do is... (2 Replies)
Discussion started by: cassj
2 Replies

10. Shell Programming and Scripting

Printing lines with specific awk NF

I have this files: ./frm/lf_mt1_cd.Ic_cell_template.attr ./die/addgen_tb_pumd.Ic_cell_template.attr ./min_m1_n.Ic_cell_template.attr When I use: awk -F\/ '{print NF}' Would result to: 3 3 2 I would like to list the files with 3 fields on it. Any Suggestions? (1 Reply)
Discussion started by: jehrome_rando
1 Replies
Login or Register to Ask a Question