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.
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
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.
Log in to answer this question.
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.
Thanks. I edited my post.
Is the example better explained now?