displaying columns based on column name


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting displaying columns based on column name
# 1  
Old 05-04-2012
displaying columns based on column name

Hello,

I have a huge file with many columns . I want to use the names of the columns to print columns to another file.

for example
PHP Code:
file.txt
COLA  COLB  COLC  COLD
1          2          3         5
3          5          6         9 
If I give name for multiple columns to the code: for example COLA , COLC it will give me the those columns.

ryan
# 2  
Old 05-04-2012
You could try something like this:-
Code:
#!/bin/ksh

columns=""
heading_line=`head -1 file.txt`
selection="$*"                     # Read command line argments for the columns wanted.
separator="\t"

i=1
for heading in $heading_line
do
   for item in $selection
   do
      if [ "$heading" = "$item" ]
      then
         columns="$columns,"
      fi
   done
   ((i=$i+1))
done

columns="${columns%,}"             # Trim off trailing comma

cut -f $columns -d "$separator" file.txt


It might need a bit of tweaking/debugging, but this gist of it is to build up a list of column positions you want and pass them into cut
You will need to get the separator right. It appears to be a tab, but a list of spaces could be difficult.


I don't think you can use this method as it is to re-sequence how the columns are displayed. I think they will be as in the input file, even if you specify them in a different or on the command line.


I hope that this helps,


Robin
Liverpool/Blackburn
Uk
# 3  
Old 05-04-2012
Hello,

Thanks, but please tell me where I will put my column names which I want to select.


Quote:
Originally Posted by rbatte1
You could try something like this:-
Code:
#!/bin/ksh

columns=""
heading_line=`head -1 file.txt`
selection="$*"                     # Read command line argments for the columns wanted.
separator="\t"

i=1
for heading in $heading_line
do
   for item in $selection
   do
      if [ "$heading" = "$item" ]
      then
         columns="$columns,"
      fi
   done
   ((i=$i+1))
done

columns="${columns%,}"             # Trim off trailing comma

cut -f $columns -d "$separator" file.txt

It might need a bit of tweaking/debugging, but this gist of it is to build up a list of column positions you want and pass them into cut
You will need to get the separator right. It appears to be a tab, but a list of spaces could be difficult.


I don't think you can use this method as it is to re-sequence how the columns are displayed. I think they will be as in the input file, even if you specify them in a different or on the command line.


I hope that this helps,


Robin
Liverpool/Blackburn
Uk
# 4  
Old 05-04-2012
Here is a shoot:
Code:
#!/bin/sh

file="$1"; shift

awk -v cols="$*" '
BEGIN{
	split(cols,C)
	OFS=FS="\t"
	getline
	split($0,H)
	for(c in C){
		for(h in H){
			if(C[c]==H[h])F[i++]=h
		}
	}
}
{ l="";for(f in F){l=l $F[f] OFS}print l }

' "$file"

Usage: ~/unix.com$ ./thisscript yourfile COLA COLC
This User Gave Thanks to tukuyomi For This Post:
# 5  
Old 05-04-2012
This seems to work:

Code:
#!/usr/bin/perl

  use strict;
  use warnings;

  use Getopt::Std;

  our $opt_c = '';

  getopt('c');

  die "Error: must specify a comma-separated list of columns with -c"
    unless $opt_c;

  my @wantedcols = split(/,/, $opt_c);

  my $collist = <>;
  die "Error: input file must have a first line with column names"
    unless $collist;
  chomp $collist;
  my @cols = split(' ', $collist);
  my @colindices;

  foreach my $col_index (0 .. $#cols) {
    push @colindices, $col_index if grep {/$cols[$col_index]/} @wantedcols;
  }

  while (my $line = <>) {
    chomp $line;
    my @colsin = split(' ', $line);
    print join ' ', @colsin[@colindices];
    print "\n";
  }

Save it as a file (for example, something like "bycols", and make it executable. To invoke it:

bycols -cCOLA,COLB,COLD input.file

Maybe not the most compact perl in the world, but it does work
This User Gave Thanks to ab2zu For This Post:
# 6  
Old 05-06-2012
(I reply here to share with the community)
Received from PM
Quote:
Originally Posted by ryan9011
Hello,
Thanks for the code. But I have one more question to ask. I don't get the header in my output file where should I put it in code?

Regards,
R
Code:
#!/bin/sh

file="$1"; shift

awk -v cols="$*" '
BEGIN{
	split(cols,C)
	OFS=FS="\t"
	getline
	split($0,H)
	for(c in C){
		for(h in H){
			if(C[c]==H[h])F[i++]=h
		}
		l=l C[c] OFS
	}
	print l
}
{ l="";for(f in F){l=l $F[f] OFS}print l }

' "$file"

---------- Post updated at 08:49 PM ---------- Previous update was at 09:03 AM ----------

Here is a more condensed version of the same script, it displays the header with no extra code:
Code:
#!/bin/sh

file="$1"; shift

awk -v c="$*" '
BEGIN{split(c,C);OFS=FS="\t"}
NR==1{
  for(c in C){
    f=0;while(f++<NF){
      if(C[c]==$f)F[i++]=f
}}}
{l="";for(f in F)l=l $F[f] OFS}
$0=l' "$file"

exit 0

*feels like I miss something to make this code simpler, but I can't figure out :p*
This User Gave Thanks to tukuyomi For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

2. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

3. UNIX for Dummies Questions & Answers

Split 1 column into numerous columns based on patterns

Hi, I have a text file 'Item_List.txt' containing only 1 column. This column lists different products, each separated by the same generic string header "NEW PRODUCT, VERSION 1.1". After this the name of the product is given, then a delimiter string "PRODUCT FIELD", and then the name of the... (11 Replies)
Discussion started by: mmab
11 Replies

4. UNIX for Dummies Questions & Answers

Extract columns based on the first line of each column

Sorry to bother you guys again. I have a file1 with multiple columns like this:gga_miR_100 gga_miR_300 gga_miR_3500 gga_miR_4600 gga_miR_5600 gga_miR_30 gga_miR_500 kj rwg ghhh jy jyu we vf 5g 5hg h6 56h i8 45t 44r4 4bg 4r546 9lgtr (fer) 4fr f433 3feev f4 bf4 35g vfr ge 2rr ... (5 Replies)
Discussion started by: yuejian
5 Replies

5. Shell Programming and Scripting

Insert Columns before the last Column based on the Count of Delimiters

Hi, I have a requirement where in I need to insert delimiters before the last column of the total delimiters is less than a specified number. Say if the delimiters is less than 139, I need to insert 2 columns ( with blanks) before the last field awk -F 'Ç' '{ if (NF-1 < 139)} END { "Insert 2... (5 Replies)
Discussion started by: arunkesi
5 Replies

6. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

7. Shell Programming and Scripting

Please Help!!!! Awk for summing columns based on selected column value

a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff,gg,hh,ii a thru ii are digits and strings.... The awk needed....if coloumn 9 == i (coloumn 9 is string ), output the sum of x's(coloumn 22 ) in all records and sum of y's (coloumn 23 ) in all records in a file (records.txt).... (6 Replies)
Discussion started by: BrownBob
6 Replies

8. Shell Programming and Scripting

Merging columns based on one or more column in two files

I have two files. FileA.txt 30910 rs7468327 36587 rs10814410 91857 rs9408752 105797 rs1133715 146659 rs2262038 152695 rs2810979 181843 rs3008128 182129 rs3008131 192118 rs3008170 FileB.txt 30910 1.9415219673 0 36431 1.3351312477 0.0107191428 36587 1.3169171182... (2 Replies)
Discussion started by: genehunter
2 Replies

9. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

10. UNIX for Advanced & Expert Users

Grep all the columns based on a particular column

This is the data file that I have 21879, 0, 22, 58, 388 0, -1, 300, 1219172589765, 1708, 0, 200, 21891, 0, 0, 33, 309 0, -1, 300, 1219172591478, 1768, 0, 200, 22505, 0, 0, 33, 339 0, -1, 300, 1219172593251, 1738, 0, 200, 21888, 0, 1, 33, 308 0, -1, 300, 1219172594995, 633, 0, 200, 24878,... (2 Replies)
Discussion started by: pmallur
2 Replies
Login or Register to Ask a Question