<p>You need to bring together the information in the two tables, I prefer <code>R</code> for such actions. The common columns between the two tables do not quite contain the same information hence additional data cleaning was required. Moreover the table with treatment information had to be changed to long format with <code>melt</code>. And I have filtered the <code>merged_data</code> so as to conation only "pretreatment" info but you can add a if/else statement to the for loop to control how/where you will move your files.</p>

<pre><code>library(data.table)

clin_name &lt;- fread("clin_name.txt", header = TRUE)
clin_name<span class="math-container">$sample_id &lt;- sub("/E/T.", "", clin_name$</span>sample_id)

pre_post &lt;- fread("pre_post.list", header = TRUE)
print(pre_post)
pre_post &lt;- melt(pre_post,
     measure.vars = c("pre-treated", "post-treated"),
     variable.name = "treatment",
     value.name = "sample_id")

&gt; print(pre_post)
      treatment sample_id
1:  pre-treated OC/AH/120
2:  pre-treated OC/AH/126
3:  pre-treated OC/AH/136
4:  pre-treated OC/AH/160
5: post-treated OC/AH/065
6: post-treated OC/AH/069
7: post-treated OC/AH/074
8: post-treated OC/AH/084


merged_data &lt;- merge(clin_name, pre_post)
print(merged_data)

pre &lt;- merged_data[merged_data$treatment == "pre-treated", "file_name"]

&gt; print(merged_data)
   sample_id         file_name   treatment
1: OC/AH/120 LP6005409-DNA_F01 pre-treated
2: OC/AH/126 LP2000325-DNA_A01 pre-treated
3: OC/AH/136 LP6005409-DNA_E02 pre-treated

# invoking a terminal command to move each file to a directory called "pre"
for (i in pre$file_name) {
  command &lt;- paste0("mv ", i, " pre/")
  system(command)
}
</code></pre>

CC BY-SA 4.0
2019-12-07T19:21:23.767Z
Thank merged_data returns nothing with zero dimension
Zizogolu
CC BY-SA 4.0
2019-12-07T19:57:21.390Z
`merge` expects shared column names in the two tables to be merged, if this is not the case you will need to explicitly state the columns to be used when merging, i.e. by using `merge(table1, table2, by.x = "column_name_in_table1", by.y = "column_name_in_table2)`. Line 4 in my code is creating a column called `sample_id` in `clin_name`. This column is also present in `pre_post` (after "melting").
haci
CC BY-SA 4.0
2019-12-07T20:07:20.260Z
sample_id in clin_name is all NA
Zizogolu
CC BY-SA 4.0
2019-12-07T20:24:14.923Z
You should find a way to create a `sample_id` column in `clin_table` and this column should have the same sample encoding as `pre_post`. The last 5 or so characters of your sample ids in the first table should be removed to make them same. Be careful with the column names, your first table has none.
haci
CC BY-SA 4.0
2019-12-06T17:46:02.277Z
2019-12-06T17:52:49.117Z
2019-12-06T17:52:49.117Z
haci
<blockquote>
  <p>EDITED</p>
</blockquote>

<p>I have list of my samples in first column and corresponding file name for my .vcf files in second column of a file name <code>clin_name.txt</code> like below. For example name of one vcf is <code>LP6008031-DNA_A04_vs_LP6008032-DNA_D03.snp.pass.vcf</code></p>

<pre><code>OC/AH/120/E/T3  LP6005409-DNA_F01
OC/AH/126/E/T1  LP2000325-DNA_A01
OC/AH/136/E/T3  LP6005409-DNA_E02
</code></pre>

<p>This is the link of my file <a href="https://www.dropbox.com/s/mvzf47feiqe7x69/clin_name.txt.txt?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/mvzf47feiqe7x69/clin_name.txt.txt?dl=0</a></p>

<p>In the second file name <code>pre_post.list</code> I have information of pre or post treatment for each sample like this</p>

<pre><code>pre-treated post-treated
OC/AH/120   OC/AH/065
OC/AH/126   OC/AH/069
OC/AH/136   OC/AH/074
OC/AH/160   OC/AH/084
</code></pre>

<p>This is link of this <a href="https://www.dropbox.com/s/nhv3odjkdjdp7hz/pre_post.list.txt?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/nhv3odjkdjdp7hz/pre_post.list.txt?dl=0</a></p>

<p>For moving different .vcf files based on the pre or post treatment to separate folders I typed </p>

<pre><code>cat pre_post.list | awk '{print <span class="math-container">$2}' | sed 's@.*/@@'  &gt; post_list.txt
cat pre_post.list | awk '{print $</span>1}' | sed 's@.*/@@'  &gt; pre_list.txt
</code></pre>

<p>Then I have </p>

<pre><code>pre-treated
120
126
136
160
182

post-treated
065
069
074
084
</code></pre>

<p>I then typed <code>./test.sh</code> in which I wrote</p>

<pre><code>for item in  <span class="math-container">$(cat  pre_list.txt | grep -Eo '[0-9]{1,4}');
    do if grep -q $</span>item clin_name.txt ;
    then out=<span class="math-container">$(grep $</span>item clin_name.txt | awk  '{print <span class="math-container">$1}');
         cp *$</span>out* /temp/hgig/fi1d18/TRG45/snp/
    else echo post_list $item;  fi;  done;
</code></pre>

<p>That returns</p>

<pre><code>cp: cannot stat `*OC/AH/069/S/T3*': No such file or directory
[fi1d18@cyan01 snp]$
</code></pre>

<p>Or I run </p>

<pre><code>for item in  <span class="math-container">$(cat  post_list.txt | grep -Eo '[0-9]{1,4}');
    do if grep -q $</span>item clin_name.txt ;
    then out=<span class="math-container">$(grep $</span>item clin_name.txt | awk  '{print <span class="math-container">$1}');
         cp *$</span>out* /temp/hgig/fi1d18/TRG45/snp/pre/
    else cp *$out* /temp/hgig/fi1d18/TRG45/snp/post/;  fi;  done;
</code></pre>

<p>That returns</p>

<pre><code>cp: cannot stat `OC/ST/026/S/T1*': No such file or directory
[fi1d18@cyan01 snp]$
</code></pre>

<p>I really don't know how to solve this :(</p>

CC BY-SA 4.0
2019-12-05T17:21:58.113Z
You have quite a few bad shell practices here (never do `for i in $(cat file)`, use `while read i; do ... ;done < file` instead; you don't need `cat file | grep`, you can just do `grep file`), but those shouldn't be causing issues here. Why are you expecting a file to match `OC/ST/026/S/T1`? Isn't your file name the other column?
terdon
CC BY-SA 4.0
2019-12-05T17:25:39.957Z
Also, are you really `cat`ing an excel file? Don't you have it saved as text?
terdon
CC BY-SA 4.0
2019-12-05T17:38:05.433Z
And what is your sample name? For example, you have `OC/AH/120/E/T3` in `clin_name.txt` but `OCCAMS/AH/120` in `pre_post.list.xlsx`. The sample names don't match. All of the names in the `pre_post.list.xlsx` file start with `OCCAMS` but `OCCAMS` doesn't exist in any of the other files.
terdon
CC BY-SA 4.0
2019-12-06T10:02:11.673Z
Thank you @terdon for comments. I changed excel to txt also OCCAMS changed to OC. For example OC/AH/120 is patient key saying pre treated which matches with LP6005409-DNA_F01 in .vcf file. potentially i want to move vcf files based on pre or post treated to separate folders
Zizogolu
<shell>
move files of a different group to separate directory
# move files of a different group to separate directory

(Asked by: username_0 on 2019-12-05)

EDITED

I have list of my samples in first column and corresponding file name for my .vcf files in second column of a file name clin_name.txt like below. For example name of one vcf is LP6008031-DNA_A04_vs_LP6008032-DNA_D03.snp.pass.vcf
OC/AH/120/E/T3  LP6005409-DNA_F01
OC/AH/126/E/T1  LP2000325-DNA_A01
OC/AH/136/E/T3  LP6005409-DNA_E02

This is the link of my file https://www.dropbox.com/s/mvzf47feiqe7x69/clin_name.txt.txt?dl=0
In the second file name pre_post.list I have information of pre or post treatment for each sample like this
pre-treated post-treated
OC/AH/120   OC/AH/065
OC/AH/126   OC/AH/069
OC/AH/136   OC/AH/074
OC/AH/160   OC/AH/084

This is link of this https://www.dropbox.com/s/nhv3odjkdjdp7hz/pre_post.list.txt?dl=0
For moving different .vcf files based on the pre or post treatment to separate folders I typed 
cat pre_post.list | awk '{print $2}' | sed 's@.*/@@'  > post_list.txt
cat pre_post.list | awk '{print $1}' | sed 's@.*/@@'  > pre_list.txt

Then I have 
pre-treated
120
126
136
160
182

post-treated
065
069
074
084

I then typed ./test.sh in which I wrote
for item in  $(cat  pre_list.txt | grep -Eo '[0-9]{1,4}');
    do if grep -q $item clin_name.txt ;
    then out=$(grep $item clin_name.txt | awk  '{print $1}');
         cp *$out* /temp/hgig/fi1d18/TRG45/snp/
    else echo post_list $item;  fi;  done;

That returns
cp: cannot stat `*OC/AH/069/S/T3*': No such file or directory
[fi1d18@cyan01 snp]$

Or I run 
for item in  $(cat  post_list.txt | grep -Eo '[0-9]{1,4}');
    do if grep -q $item clin_name.txt ;
    then out=$(grep $item clin_name.txt | awk  '{print $1}');
         cp *$out* /temp/hgig/fi1d18/TRG45/snp/pre/
    else cp *$out* /temp/hgig/fi1d18/TRG45/snp/post/;  fi;  done;

That returns
cp: cannot stat `OC/ST/026/S/T1*': No such file or directory
[fi1d18@cyan01 snp]$

I really don't know how to solve this :(

**Tags:** shell
> **Comments:**
> **username_1**: You have quite a few bad shell practices here (never do `for i in $(cat file)`, use `while read i; do ... ;done < file` instead; you don't need `cat file | grep`, you can just do `grep file`), but those shouldn't be causing issues here. Why are you expecting a file to match `OC/ST/026/S/T1`? Isn't your file name the other column?  
> **username_1**: Also, are you really `cat`ing an excel file? Don't you have it saved as text?  
> **username_1**: And what is your sample name? For example, you have `OC/AH/120/E/T3` in `clin_name.txt` but `OCCAMS/AH/120` in `pre_post.list.xlsx`. The sample names don't match. All of the names in the `pre_post.list.xlsx` file start with `OCCAMS` but `OCCAMS` doesn't exist in any of the other files.  
> **username_0**: Thank you @terdon for comments. I changed excel to txt also OCCAMS changed to OC. For example OC/AH/120 is patient key saying pre treated which matches with LP6005409-DNA_F01 in .vcf file. potentially i want to move vcf files based on pre or post treated to separate folders  

---

## 1 answers

### Answer by username_2 on 2019-12-06. Score: 1

You need to bring together the information in the two tables, I prefer R for such actions. The common columns between the two tables do not quite contain the same information hence additional data cleaning was required. Moreover the table with treatment information had to be changed to long format with melt. And I have filtered the merged_data so as to conation only "pretreatment" info but you can add a if/else statement to the for loop to control how/where you will move your files.
library(data.table)

clin_name <- fread("clin_name.txt", header = TRUE)
clin_name$sample_id <- sub("/E/T.", "", clin_name$sample_id)

pre_post <- fread("pre_post.list", header = TRUE)
print(pre_post)
pre_post <- melt(pre_post,
     measure.vars = c("pre-treated", "post-treated"),
     variable.name = "treatment",
     value.name = "sample_id")

> print(pre_post)
      treatment sample_id
1:  pre-treated OC/AH/120
2:  pre-treated OC/AH/126
3:  pre-treated OC/AH/136
4:  pre-treated OC/AH/160
5: post-treated OC/AH/065
6: post-treated OC/AH/069
7: post-treated OC/AH/074
8: post-treated OC/AH/084


merged_data <- merge(clin_name, pre_post)
print(merged_data)

pre <- merged_data[merged_data$treatment == "pre-treated", "file_name"]

> print(merged_data)
   sample_id         file_name   treatment
1: OC/AH/120 LP6005409-DNA_F01 pre-treated
2: OC/AH/126 LP2000325-DNA_A01 pre-treated
3: OC/AH/136 LP6005409-DNA_E02 pre-treated

# invoking a terminal command to move each file to a directory called "pre"
for (i in pre$file_name) {
  command <- paste0("mv ", i, " pre/")
  system(command)
}

> **Comments:**
> **username_0**: Thank merged_data returns nothing with zero dimension  
> **username_2**: `merge` expects shared column names in the two tables to be merged, if this is not the case you will need to explicitly state the columns to be used when merging, i.e. by using `merge(table1, table2, by.x = "column_name_in_table1", by.y = "column_name_in_table2)`. Line 4 in my code is creating a column called `sample_id` in `clin_name`. This column is also present in `pre_post` (after "melting").  
> **username_0**: sample_id in clin_name is all NA  
> **username_2**: You should find a way to create a `sample_id` column in `clin_table` and this column should have the same sample encoding as `pre_post`. The last 5 or so characters of your sample ids in the first table should be removed to make them same. Be careful with the column names, your first table has none.  

---

