# Extract columns where header matches a given string

**URL:** <https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791>\
**Category:** Shell Programming and Scripting\
**Created:** [March 17, 2011, 4:05pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791 "2011-03-17T16:05:52Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 17, 2011, 4:05pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/1 "2011-03-17T16:05:52Z")

</div>

Hi,

I'm having trouble pulling out columns where the headers match a file of key ID's I'm interested in and was looking for some help.

file1.txt

```nohighlight
I
Name
34
56
84
350
790
1215
1919
7606
9420

```

file2.txt

```nohighlight
I Name 1 1 2 2 3 3 ... 34 34... 56 56... 84 84... 350 350... 
M 1 A A A A B B A A A B B A A A
M 2 A A B A B B B A A B B A A A
M 3 A A A A B B A A A B A A B A
M 4 A B A A B B A B A A B A A A

```

I would like to create a new file where the columns from file2 match ids from file 1, so:

```nohighlight
I Name 34 34 56 56 84 84 350 350...9420 9420
M 1 A A A B B A A A ... B A
M 2 B A A B B A A A ... B A
M 3 A A A B A A B A ... B B

```

I initially tried the following:

```nohighlight
% awk '{print $1}' file1.txt | while read i; do awk 'NR=1;{for (j=0;j<=NF;j++) {if ($j == $i) break; }} {printf "%s ",$i}' file2.txt;done > file3.txt 

```

But I'm obviously doing something wrong as I get the entire matrix "X" printed first then on subsequent lines I get the whole matrix printed twice

```nohighlight
% vi file3.txt

X
X X
X X
X X
...
X

```

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![vgersh99](https://community.unix.com/user_avatar/community.unix.com/vgersh99/32/14851_2.png) [@vgersh99](https://community.unix.com/u/vgersh99)\
**Post date:** [March 17, 2011, 4:45pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/2 "2011-03-17T16:45:34Z")

</div>

`nawk -f flot.awk file1.txt file2.txt`

flot.awk:

```nohighlight
FNR==NR {f1[$1];next}
FNR==1{
       for(i=1;i<=NF;i++) {
         f2=$i
         if ($i in f1)
            printf("%c%s", c++?OFS:"", $i)
       }
       print ""
       next
}
{
  b=0
  for(i=1;i<=NF;i++)
    if (f2 in f1)
       printf("%c%s", b++?OFS:"",$i)
  print ""
}

```

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 17, 2011, 5:26pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/3 "2011-03-17T17:26:38Z")

</div>

it may be done with this trick too, you could try:

save following statement into a file, let's say, myawk.sh

```nohighlight
awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }'

```

then execute this statement: assume file1 & file2 are your two files

```nohighlight
sh myawk.sh<file2 |awk 'NR==FNR{a[$1]=1;} NR>FNR{if ($1 in a )print $0}' file1 - |sh myawk.sh

```

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 17, 2011, 5:38pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/4 "2011-03-17T17:38:28Z")

</div>

Thanks for the quick reply unfortunately your example doesn't work.

I tried to run flot.awk and got the following

A single entry for the column header ~100 columns and then the entire matrix ~10000 columns \* n rows below the header. What I was hoping to get was a matrix of 200 columns \* n rows.

Any idea why your script may be only matching the header once instead of the two times I expect and why it's printing the entire matrix following that? The input file2 is separated by whitespace, does it need -F ' ' somewhere?

Thanks again

---

<div class="post-metadata">

**Author:** ![vgersh99](https://community.unix.com/user_avatar/community.unix.com/vgersh99/32/14851_2.png) [@vgersh99](https://community.unix.com/u/vgersh99)\
**Post date:** [March 17, 2011, 5:41pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/5 "2011-03-17T17:41:39Z")

</div>

Hmm..... strange - worked with my sample files.  
Could you provide the sample files you were using?  
Tailor it down to be small enough and be representative as well - say 5 records in file1 and 20 columns in file2.

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 17, 2011, 6:34pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/6 "2011-03-17T18:34:29Z")

</div>

Thanks for the replies

@sk1418  
I tried your example, but never received anything back from stdout after running the scripts. I've tried to run the first awk command inputing file2.txt and received some output but from that point on the computer just hangs.

@vgersh99  
Here's an example of the files. And I appologise, I'm not sure if a file should be code tagged?

file1.txt:

```nohighlight
I
Name
104069
109706
113889
115561
115979

```

file2.txt

```nohighlight
I Name 021071 021071 021215 021215 104069 104069 124236 124236 212439 212439 109706 109706 215398 215398 113889 113889 272728 272728 360976 360976 
M 10080_CO B B B B B B B B B B B B B B B B A A B B
M 10068_CO A A A A A A B B A A A A A A A A A A A A
M 12187_ND B B B B B B B B B B B B B B B B B B B B
M GGA_0061 A A A A A B B B A B B B A B A A A B A B
M GGA_0013 A B A B A B A B A B A B A B B B A A A B
M GGA_0024 A A A B A A A A A A A B A A A A A B A A
M GGA_0025 B B B B B B B B B B B B B B B B B B B B

```

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 17, 2011, 6:41pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/7 "2011-03-17T18:41:57Z")

</div>

hi, i tried your examples:

```nohighlight
ArchT60 23:39:39 /tmp
kent$ sh tawk.sh<f2 |awk 'NR==FNR{a[$1]=1;} NR>FNR{if ($1 in a )print $0}' f1 - |sh tawk.sh 
I Name 104069 104069 109706 109706 113889 113889 
M 10080_CO B B B B B B 
M 10068_CO A A A A A A 
M 12187_ND B B B B B B 
M GGA_0061 A B B B A A 
M GGA_0013 A B A B B B 
M GGA_0024 A A A B A A 
M GGA_0025 B B B B B B 

```

if the myawk.sh doesn't work there, try this:

```nohighlight
awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' f2 |awk 'NR==FNR{a[$1]=1;} NR>FNR{if ($1 in a )print $0}' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 

```

actually the myawk.sh just for save some words. as you can see above statement, same part of codes duplicated at the beginning and end.

---

<div class="post-metadata">

**Author:** ![vgersh99](https://community.unix.com/user_avatar/community.unix.com/vgersh99/32/14851_2.png) [@vgersh99](https://community.unix.com/u/vgersh99)\
**Post date:** [March 17, 2011, 6:46pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/8 "2011-03-17T18:46:11Z")

</div>

here's the output I get - looks fine to me - unless I'm missing something in your explanation:

```nohighlight
I Name 104069 104069 109706 109706 113889 113889
M 10080_CO B B B B B B
M 10068_CO A A A A A A
M 12187_ND B B B B B B
M GGA_0061 A B B B A A
M GGA_0013 A B A B B B
M GGA_0024 A A A B A A
M GGA_0025 B B B B B B

```

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 4:30pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/9 "2011-03-18T16:30:07Z")

</div>

Interesting. that it's worked for you both, but not for me. I've tried to run your examples as shell scripts or using the awk -F option and I get really weird results if any results at all? However, when I tried to run the stand alone awk code that sk1418 gave as a second example it works fine on the small test example. I'll try to run this on the larger file and look into why the scripts aren't working when I use .awk and .sh  
Thank you very much for your help!

---------- Post updated 03-18-11 at 03:30 PM ---------- Previous update was 03-17-11 at 06:17 PM ----------

Hi,

I've been mucking around with your solutions to my problem today and have noticed that in both cases the output is ordered from greatest to smallest. Is there anyway to keep the original order from file1.txt? It's not exactly clear to me where the sorting of id's occurs?

Thanks again.

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 18, 2011, 4:52pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/11 "2011-03-18T16:52:09Z")

</div>

hi, check my output in previous post, the output did keep the file1 order, didn't it? what did u mean " the output is ordered from greatest to smallest."?

---

<div class="post-metadata">

**Author:** ![pravin27](https://community.unix.com/letter_avatar/pravin27/32/5_5575768a8748004e209b776fc1b2916d.png) [@pravin27](https://community.unix.com/u/pravin27)\
**Post date:** [March 18, 2011, 4:52pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/12 "2011-03-18T16:52:49Z")

</div>

Could this help you?

```nohighlight
 awk 'NR==FNR{a[$1]++;next} {if(FNR==1){for(i=1;i<=NF;i++){if(a[$i]){printf $i" ";b=$i}}}else{printf "\n";for(j=1;j<=NF;j++){if(b[j]) {printf $j" "}}}}END {printf "\n"}' file1 file2

```

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 5:44pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/13 "2011-03-18T17:44:33Z")

</div>

Hi,  
Given the following input files:

file1.txt

```nohighlight
41109297 
41109706 
43162207
41109808
41109377
41110441
41111192
43163011
43162367

```

file2.txt

```nohighlight
I Name 41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 41112086 41112086 41113889 41113889 41114003 41114003 41114656 41114656 41115162 41115162 41115561 41115561 41115979 41115979 41116248 41116248 41130607 41130607 41130611 41130611 41131240 41131240 41132167 41132167 41133800 41133800 41134462 41134462 41134623 41134623 42135335 42135335 42137664 42137664 42143490 42143490 42144170 42144170 42144339 42144339 42144650 42144650 42145389 42145389 42146088 42146088 42146090 42146090 42146879 42146879 42148154 42148154 43161219 43161219 43162207 43162207 43163011 43163011 43163878 43163878 43164830 43164830 43165768 43165768 43166228 43166228 43166330 43166330 43167557 43167557 43180900 43180900 43181675 43181675 43182287 43182287 43184255 43184255 43184401 43184401 
M 1080_COI B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 10668_CO B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1218_ND B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1546_CY B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1626_ND B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1637_ND B B B B B B B B B B B B B B B B B B B B B B B B A A B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 5831_ND2 A A A A A A A A A A A A A A A A A A A A A A A A 0 0 A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A 
M 8472_CO2 B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M GGal006 A A A A A B A B A B A A A A A B A B A A A B A B A A A B A B A A A B B B B B A B A A B B A B A A A B A B A A A A B B A B A B A B A A B B A B A B B B A B A B A A A B A B A B A A 

```

I get the following output:

@sk1418 & vgersh99

```nohighlight
41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 43162207 43162207 43163011 43163011 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
A A A A A A A A A A A A 
B B B B B B B B B B B B 
A A A A A B A B A B A A 

```

@pravin27

```nohighlight
41110441 41110441 41111192 41111192 43162207 43162207 43163011 43163011 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
A A A A A A A A 
B B B B B B B B 
A B A B A B A A 

```

In the first case it seems like it's using file2 to order the output? Any thoughts on how I can keep the output in the order of file1?

---

<div class="post-metadata">

**Author:** ![vgersh99](https://community.unix.com/user_avatar/community.unix.com/vgersh99/32/14851_2.png) [@vgersh99](https://community.unix.com/u/vgersh99)\
**Post date:** [March 18, 2011, 5:47pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/14 "2011-03-18T17:47:41Z")

</div>

> [@flotsam](#):
>
> Hi,
> 
> I've been mucking around with your solutions to my problem today and have noticed that in both cases the output is ordered from greatest to smallest. Is there anyway to keep the original order from file1.txt? It's not exactly clear to me where the sorting of id's occurs?
> 
> Thanks again.

file1 defines the list of columns to be matched.  
file2's header lines defines the column names.  
The script simply matches the the list from file1 to the header names in file2 in WHATEVER order the columns are defined in files2.

There's no "sorting" that's being done. The 'valid/matched' columns from file2 are being output in the SAME original order they appeared in file2.

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 6:23pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/15 "2011-03-18T18:23:07Z")

</div>

Thanks vgersh99,

I think I realised that and pointed it out in the last line of my post. I hadn't anticipated the problem when I originally posted, but require output to match the order of file1

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 18, 2011, 6:32pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/16 "2011-03-18T18:32:46Z")

</div>

> [@flotsam](#):
>
> Hi,  
> Given the following input files:
> 
> file1.txt
> 
> ```plaintext
> 41109297 
> 41109706 
> 43162207
> 41109808
> 41109377
> 41110441
> 41111192
> 43163011
> 43162367
> 
> ```
> 
> file2.txt
> 
> ```plaintext
> I Name 41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 41112086 41112086 41113889 41113889 41114003 41114003 41114656 41114656 41115162 41115162 41115561 41115561 
> 
> ```
> 
> In the first case it seems like it's using file2 to order the output? Any thoughts on how I can keep the output in the order of file1?

hi there, i think i understood what you mean. You want to get those relevant columns AND change the column order against the order defined in file1. E.g in your pasted output, 43162207 column should be before 41110441. right?

then take a look this, if it is what you want. (with output)

```plaintext
ArchT60 /tmp/test
kent$ awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' f2 |awk 'BEGIN{i=j=m=n=1;}
NR==FNR{a[$1]=$1;aa[i++]=$1;} 
NR>FNR{if ($1 in a ){b[n++]=$0;bb[m++]=$1;}} 
END{
la=length(a); lb=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa) print b[j]; } } 
}' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 
b=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa) print b[j]; } } }' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 

41109297 41109297 41109706 41109706 43162207 43162207 41110441 41110441 41111192 41111192 43163011 43163011 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
A A A A A A A A A A A A 
B B B B B B B B B B B B 
A A A A A B A B A B A A 

```

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 6:49pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/17 "2011-03-18T18:49:12Z")

</div>

Hi,  
Given the following input files:

file1.txt

```nohighlight
41109297 
41109706 
43162207
41109808
41109377
41110441
41111192
43163011
43162367

```

file2.txt

```nohighlight
I Name 41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 41112086 41112086 41113889 41113889 41114003 41114003 41114656 41114656 41115162 41115162 41115561 41115561 41115979 41115979 41116248 41116248 41130607 41130607 41130611 41130611 41131240 41131240 41132167 41132167 41133800 41133800 41134462 41134462 41134623 41134623 42135335 42135335 42137664 42137664 42143490 42143490 42144170 42144170 42144339 42144339 42144650 42144650 42145389 42145389 42146088 42146088 42146090 42146090 42146879 42146879 42148154 42148154 43161219 43161219 43162207 43162207 43163011 43163011 43163878 43163878 43164830 43164830 43165768 43165768 43166228 43166228 43166330 43166330 43167557 43167557 43180900 43180900 43181675 43181675 43182287 43182287 43184255 43184255 43184401 43184401 
M 1080_COI B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 10668_CO B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1218_ND B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1546_CY B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1626_ND B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 1637_ND B B B B B B B B B B B B B B B B B B B B B B B B A A B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M 5831_ND2 A A A A A A A A A A A A A A A A A A A A A A A A 0 0 A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A 
M 8472_CO2 B B B B B B B B B B B B B B B B B B B B B B B B 0 0 B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B 
M GGal006 A A A A A B A B A B A A A A A B A B A A A B A B A A A B A B A A A B B B B B A B A A B B A B A A A B A B A A A A B B A B A B A B A A B B A B A B B B A B A B A A A B A B A B A A 

```

I get the following output:

@sk1418 & vgersh99

```nohighlight
41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 43162207 43162207 43163011 43163011 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
A A A A A A A A A A A A 
B B B B B B B B B B B B 
A A A A A B A B A B A A 

```

@pravin27

```nohighlight
41110441 41110441 41111192 41111192 43162207 43162207 43163011 43163011 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
B B B B B B B B 
A A A A A A A A 
B B B B B B B B 
A B A B A B A A 

```

In the first case it seems like it's using file2 to order the output? Any thoughts on how I can keep the output in the order of file1?

---------- Post updated at 05:49 PM ---------- Previous update was at 05:41 PM ----------

Here is a python script that will do what I was after if anyone is interested, it's cumbersome and will probably be incredibly slow, but should work

```nohighlight
#!/usr/local/python
import sys

def argChk(input):
        warn='Takes id file and extracts matching columns from file 2 if headers match... USAGE: ./grabCol.py file1 file2 outfile'
        if '-h' in input:
                print warn
        elif len(input) == 3:
                print warn
        else:
                return input

def getPos(id,col):
        coLst=col.split()
        posLst=[]
        for i in id:
                for j in range(0,len(coLst),1):
                        if i.strip() == coLst[j]:
                                posLst.append(j)
        return posLst

############################MAIN#############################
if __name__ == ' __main__':
        args=argChk(sys.argv)
        idFile=open(args[1],'rU')
        id=idFile.readlines()
        idFile.close()
        colFile=open(args[2],'rU')
        col=colFile.readlines()
        colFile.close()
        oFile=open(args[3],'w')
        posLst=getPos(id,col[0])
        oStr=''
        for i in col:
                iLst=i.split()
                for j in posLst:
                        oStr=oStr+iLst[j]+' '
                oStr=oStr+'\n'
        oFile.write(oStr)
        oFile.close()

```

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 18, 2011, 6:50pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/18 "2011-03-18T18:50:45Z")

</div>

hi, have you tried my solution? does it work there? the post before your python post.

btw, your python codes lost all indents. 😃

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 7:06pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/19 "2011-03-18T19:06:55Z")

</div>

@sk1418  
Yeah that's exactly what I was after! Sorry I didn't articulate it well when I first posted. I tried to just copy and paste your example, but am coming up with an error message.

Is there something missing on the second line around b=length?

The python script seems to be doing it's job, but I think it's going to be very slow and these are some very large files.. Thanks again for all your help.

```nohighlight
awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' file2.txt |awk 'BEGIN{i=j=m=n=1;}NR==FNR{a[$1]=$1;aa[i++]=$1;} NR>FNR{if ($1 in a ){b[n++]=$0;bb[m++]=$1;}} END{la=length(a); lb=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa) print b[j]; } } }' file1.txt - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }'
b=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa) print b[j]; } } }' file1.txt - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }'

-bash: syntax error near unexpected token `('

```

---

<div class="post-metadata">

**Author:** ![sk1418](https://community.unix.com/user_avatar/community.unix.com/sk1418/32/6797_2.png) [@sk1418](https://community.unix.com/u/sk1418)\
**Post date:** [March 18, 2011, 7:09pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/20 "2011-03-18T19:09:58Z")

</div>

ok, i just reformatted a little bit on my code, maybe something went wrong then. I paste again, the one line version. looks ugly but should work. just try it:

```nohighlight
awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' f2 |awk 'BEGIN{i=j=m=n=1;}NR==FNR{a[$1]=$1;aa[i++]=$1;} NR>FNR{if ($1 in a ){b[n++]=$0;bb[m++]=$1;}} END{la=length(a); lb=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa) print b[j]; } } }' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 

```

---

<div class="post-metadata">

**Author:** ![flotsam](https://community.unix.com/letter_avatar/flotsam/32/5_5575768a8748004e209b776fc1b2916d.png) [@flotsam](https://community.unix.com/u/flotsam)\
**Post date:** [March 18, 2011, 7:18pm UTC](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791/21 "2011-03-18T19:18:06Z")

</div>

Works perfectly thank you very much! I'll race it against the python script!

[Next page](https://community.unix.com/t/extract-columns-where-header-matches-a-given-string/284791.md?page=2)