Change OTU names to taxon names

Question

[mollysil]

I have two .csv tables:

Table 1 depicts the abundance of each OTU of interest (columns) found at each sampling site (rows). The column headers are the arbitrary OTU names (ex: OTU_1) and rows are labeled by site (ex: AF141 stands for American Farm sampled in 2014, replicate #1)

               OTU_1      OTU_2        OTU_3
AF141            0          2            0             
RC141            3          0           170
LI141            0         200           0

Table 2 is a list of the arbitrary OTU names (first column of table) with their taxonomic assignments at the family level (second column of table), as below:

OTU_1       Acaulosporaceae
OTU_2       Acaulosporaceae
OTU_3       Diversisporaceae

How can I get a new abundance table with the taxon information instead of the arbitrary OTU names as the column headers? I have 500 OTUs in my table #1. I want my new table to look like this:

               Acaulosporaceae      Acaulosporaceae    Diversisporaceae
AF141            0                     2                           0             
RC141            3                     0                         170
LI141            0                    200                          0

I'm using Linux command line.

[Istvan Albert]

I have tried to reformat your post to make it more readable. Please verify that the column formats that I ended up with conform to what you have. I would suggest to specify a simple but complete example. Where the result matches the input. In this case it does not seem to.

Show the two inputs then the desired output.

[mollysil]

Thanks. I edited my post.

[mollysil]

Is the example better explained now?

Answer 1

[Pierre Lindenbaum]

use awk to create a sed pattern-file and use it to convert your second table:

sed -f <(awk '{printf("s/\\([\t]\\)*%s\\([\t]\\)*/%s/g\n",$1,$2);}' file1.txt ) table2.txt

[mollysil]

I modified my question to better explain the table I wish to make. Let me know if you have any ideas for this. You code didn't make the table I envisioned because I didn't explain my question correctly.