How to delete columns with numbers in an excel file? | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

How to delete columns with numbers in an excel file?

UNIX for Dummies Questions & Answers


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 12-11-2012
AAWT AAWT is offline
Registered User
 
Join Date: Feb 2012
Last Activity: 6 June 2013, 5:38 AM EDT
Posts: 37
Thanks: 9
Thanked 0 Times in 0 Posts
How to delete columns with numbers in an excel file?

Dear all,
I have one file (see below) with more then 100 columns and 2500 rows, and need only column which has GType in label with Alphabets, please help me to remove these columns with numbers.
input file is


Code:
         n.201.GType    n-201.Theta    n-201.R   n_1.GType    n_1.Theta     n_1.R          n_7.GType      n_7.Theta        n_7.R           
BB                             0.823115       1.23659                   BB       0.791365      1.274946                  AB          0.441334         1.666898           
AA            0.02399     1.526078                               AA        0.021785     1.578031                 AA          0.013456        1.527924

output should be


Code:
         n_201.GType   n_1.GType   n_7.GType        
BB               BB         AB 
          AA               AA         AA

thanks

Last edited by Scott; 12-11-2012 at 11:55 AM.. Reason: Code tags
Sponsored Links
    #2  
Old 12-11-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 31 October 2014, 9:36 PM EDT
Location: Saskatchewan
Posts: 19,745
Thanks: 832
Thanked 3,375 Times in 3,162 Posts
Is this an excel file or a flat file?

If a flat file, what separates the columns? Tabs?

Please try posting your data again, your lack of code tags and attempts at spacing without them have thrown your table well out of whack.
Sponsored Links
    #3  
Old 12-11-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 23 September 2014, 12:09 PM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts
try:

Code:
awk '{for (i=1; i<=NF; i++) if ($i ~ /GType/)c[i]=i;} {for (i=1; i<=NF; i++) $i=(c[i])?$i:"";}1' infile

    #4  
Old 12-11-2012
rdcwayx rdcwayx is offline Forum Advisor  
Use nawk in Solaris
 
Join Date: Jun 2006
Last Activity: 22 March 2014, 12:27 PM EDT
Posts: 2,759
Thanks: 44
Thanked 418 Times in 406 Posts

Code:
awk '{for (i=1;i<=NF;i++) if ($i~/GType/||$i~/^[a-zA-Z]*$/) printf $i "\t";printf RS}' infile

n.201.GType     n_1.GType       n_7.GType
BB      BB      AB
AA      AA      AA

Sponsored Links
    #5  
Old 12-12-2012
AAWT AAWT is offline
Registered User
 
Join Date: Feb 2012
Last Activity: 6 June 2013, 5:38 AM EDT
Posts: 37
Thanks: 9
Thanked 0 Times in 0 Posts
Dear all,

i am sorry, but the both commands are not working for my file,
should I use them in Linux, as I tried in cygwin,
and Corona, yes I have this file in excel as well as in a tab delimited text file
waiting for your help
my file name is data table.txt or data table.xlsx
Regards

---------- Post updated at 10:19 AM ---------- Previous update was at 09:55 AM ----------

Dear Corona, here I upload my text delimited file for your kind consideration, (with in the code) only seven columns, first row is label and first column is names of samples


Code:
Name    n_410.GType    n_410.Theta    n_410.R    n_14.GType    n_014.Theta    n_14.R
B-80011558140-269    BB    0.8585083    1.182597    NC    0.04133479    0.3816845    AB    0.5569273    1.312853    BB    0.8443609    1.156271
B-80011565932-735    AA    0.02112507    1.547318    AA    0.02052359    1.452008    AA    0.02342339    1.444604    AA    0.01875894    1.540271
B-80011565932-883    BB    0.9910369    1.219364    BB    0.9829392    1.228494    BB    0.9917964    1.207549    BB    0.9896955    1.228145
B-80011568019-475    BB    0.9941319    0.8715777    BB    0.9845733    0.8617675    BB    0.9956517    0.8734978    NC    1.2038809    1.890736

Best Rgards

AAWT

---------- Post updated at 05:58 PM ---------- Previous update was at 10:19 AM ----------

hello
I used this command

Code:
awk '{for (i=1;i<=NF;i++) if ($i~/GType/||$i~/^[a-zA-Z]*$/) printf $i "\t";printf RS}' Data_Table.txt>Gtype.txt

and got only one row with the labels of column with Gtype, but not the whole column with AA,,,etc
please also consider that first column is name for rows

Best REgards

AAWT
Sponsored Links
    #6  
Old 12-12-2012
rdcwayx rdcwayx is offline Forum Advisor  
Use nawk in Solaris
 
Join Date: Jun 2006
Last Activity: 22 March 2014, 12:27 PM EDT
Posts: 2,759
Thanks: 44
Thanked 418 Times in 406 Posts
let me guess.


Code:
awk 'NR==1{for (i=1;i<=NF;i++) if ($i~/GType/) a[i]}
     {printf $1 FS; for (i=2;i<=NF;i++) if (i in a) printf $i FS;printf RS}' Data_Table.txt

Name n_410.GType n_14.GType
B-80011558140-269 BB NC
B-80011565932-735 AA AA
B-80011565932-883 BB BB
B-80011568019-475 BB BB

Sponsored Links
    #7  
Old 12-13-2012
AAWT AAWT is offline
Registered User
 
Join Date: Feb 2012
Last Activity: 6 June 2013, 5:38 AM EDT
Posts: 37
Thanks: 9
Thanked 0 Times in 0 Posts
I used following command


Code:
awk 'NR==1{for (i=1;i<=NF;i++) if ($i~/GType/) a[i]}{printf $1 FS; for (i=2;i<=NF;i++) if (i in a) printf $i FS;printf RS}' Data_Table.txt >Gtypes.txt

out put came with only first row with names.Gtype, and no other data column or row

Regards

AAWT
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Urgent_need to delete columns with numbers AAWT UNIX for Dummies Questions & Answers 1 12-11-2012 11:15 AM
To compare first two columns in an excel file arunmanas UNIX for Dummies Questions & Answers 2 06-28-2011 02:10 AM
How to sort columns in excel(csv) file Man83Nagesh Shell Programming and Scripting 2 08-17-2010 07:16 AM
how to convert fields from a text file to excel columns npatwardhan Shell Programming and Scripting 4 12-23-2008 09:19 AM
use awk to read randomly located columns in an excel file mdap Shell Programming and Scripting 9 08-10-2008 11:18 AM



All times are GMT -4. The time now is 02:53 AM.