Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Cut from tables based on column values

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Cut from tables based on column values
# 1  
Old 05-02-2012
Question Cut from tables based on column values
Hello,

I have a tab-delimited table that may contain 11,12 or 13 columns. Depending on the number of columns, I want to cut and get a sub table as shown below. However, the awk commands in the code seem to be an issue. What should I be doing differently?

Code:
#cut columns 1-2,4-5,11 when 12 & 13 are empty
awk '$12=="" && $13==""' myTable | cut -f 1-2,4-5,11 > t1

#cut columns 1-2,4-5,12 when 13 is empty
awk '$13==""' myTable | cut -f 1-2,4-5,12 > t2

#cut columns 1-2,4-5,13 when 13 is not empty
awk '$13!=""' myTable | cut -f 1-2,4-5,13  > t3

Thanks,
Guss
# 2  
Old 05-02-2012
If these files are separated with single spaces or tabs, awk may take the multiple whitespaces to be one field. You can fix this by overriding the FS variable with a single tab.

I'd just do everything in awk actually, to process the file one time instead of six:

Code:
awk 'BEGIN { OFS="\t"; FS="\t"; }
(!$12) && (!$13) { print $1, $2, $4, $5, $11 >"t1" }
(!$13} { print $1, $2, $4, $5, $12 >"t2" }
($13) { print $1, $2, $4, $5, $13  >"t3" }' inputfile

Last edited by Corona688; 05-02-2012 at 03:22 PM.. Reason: Missing $ before 12
This User Gave Thanks to Corona688 For This Post:
Gussifinknottle
# 3  
Old 05-02-2012
I see a pattern here. If you were going to merge t1,t2,t3 afterwards anyway..

Code:
awk -F'\t' '{print $1,$2,$4,$5,$NF}' OFS="\t" input

edit: ok maybe this simplification won't work if the columns exist yet are empty.. :\
This User Gave Thanks to neutronscott For This Post:
Gussifinknottle
# 4  
Old 05-03-2012
Thanks! neutronscott,

Actually your solution will be applicable for my current analysis.

~Guss
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Filtering based on column values

Hi there, I am trying to filter a big file with several columns using values on a column with values like (AC=5;AN=10;SF=341,377,517,643,662;VRT=1). I wont to filter the data based on SF= values that are (bigger than 400) ... (25 Replies)
Discussion started by: daashti
25 Replies

2. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

I have a file (myfile.txt) with contents like this: 1.txt apple is 3.txt apple is 5.txt apple is 2.txt apple is a 7.txt apple is a 8.txt apple is a fruit 4.txt orange not a fruit 6.txt zero isThe above file is already sorted using this command: sort -k2 myfile.txtMy objective is to get... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

3. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

4. UNIX for Dummies Questions & Answers

How to merge two tables based on a matched column?

Hi, Please excuse me , i have searched unix forum, i am unable to find what i expect , my query is , i have 2 files of same structure and having 1 similar field/column , i need to merge 2 tables/files based on the one matched field/column (that is field 1), file 1:... (5 Replies)
Discussion started by: karthikram
5 Replies

5. Shell Programming and Scripting

Adding values of a column based on another column

Hello, I have a data such as this: ENSGALG00000000189 329 G A 4 2 0 ENSGALG00000000189 518 T C 5 1 0 ENSGALG00000000189 1104 G A 5 1 0 ENSGALG00000000187 3687 G T 5 1 0 ENSGALG00000000187 4533 A T 4 2 0 ENSGALG00000000233 5811 T C 4 2 0 ENSGALG00000000233 5998 C A 5 1 0 I want to... (3 Replies)
Discussion started by: Homa
3 Replies

6. Shell Programming and Scripting

Pick the column value based on another column using awk or CUT

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Please someone help me to resolve this issue. Source column1 column2 column3 column4... (2 Replies)
Discussion started by: Ganesh L
2 Replies

7. UNIX for Dummies Questions & Answers

How to cut from a text file based on value of a specific column?

Hi, I have a tab delimited text file from which I want to cut out specific columns. If the second column equals one, I want to cut out columns 1 and 5 and 6. If the second column equals two, I want to cut out columns 1 and 5 and 7. How do I go about doing that? Thanks! (4 Replies)
Discussion started by: evelibertine
4 Replies

8. Emergency UNIX and Linux Support

awk cut column based on string

Using awk I required to cut out column contain word "-Tag" regardles of any order of contents and case INsensitive -Tag:messages -P:/var/log/messages -P:/var/log/maillog -K:Error -K:Warning -K:critical Please Guide ...... --Shirish Shukla ---------- Post updated at 05:58 AM... (15 Replies)
Discussion started by: Shirishlnx
15 Replies

9. Shell Programming and Scripting

How to averaging column based on first column values

Hello I have file that consist of 2 columns of millions of entries timestamp and throughput I want to find the average (throughput ) for each equal timestamp before change it to proper format e.g : i want to average 2 coloumnd fot all 1308154800 values in column 1 and then print... (4 Replies)
Discussion started by: aadel
4 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question