Answers.Body: <p>When I loaded these files into R, I got the following result, which matches your error (except that it's been translated into German):</p>
  <pre><code>&gt; df1 &lt;- read.csv(&quot;df1.csv&quot;)
  &gt; df2 &lt;- read.csv(&quot;df2.csv&quot;)
  &gt; merge(df1, df2, by=c(&quot;start&quot;,&quot;end&quot;,&quot;function&quot;))
  Fehler in fix.by(by.x, x) : 'by' muss gültige Spalte(n) spezifizieren
  </code></pre>
  <p>This error is obscure, and it took me a while to get my head around what was going on. The problem is that 'function' is a reserved word in R, so it renamed the <code>function</code> column to add a period [<code>.</code>] at the end of it:</p>
  <pre><code>&gt; colnames(df1)
  [1] &quot;chr&quot;       &quot;RM&quot;        &quot;sim&quot;       &quot;start&quot;     &quot;end&quot;       &quot;stat&quot;      &quot;strand&quot;   
  [8] &quot;id&quot;        &quot;function.&quot;
  &gt; colnames(df2)
  [1] &quot;start&quot;     &quot;end&quot;       &quot;function.&quot;
  </code></pre>
  <p>So R was looking for a column name called &quot;function&quot;, didn't find one, and got confused.</p>
  <p>I think the easiest fix to this is to use the new names that R has given the tables:</p>
  <pre><code>&gt; merge(df1, df2, by=c(&quot;start&quot;,&quot;end&quot;,&quot;function.&quot;))
  [1] start     end       function. chr       RM        sim       stat      strand    id       
  &lt;0 Zeilen&gt; (oder row.names mit Länge 0)
  </code></pre>
  <p>[note: this has no output, because the examples you provided have no common start/end/function]</p>
  <p>Alternatively, let R work out the common fields itself, as its default for <code>merge</code> is to intersect the column names (which is what you want in this case):</p>
  <pre><code>&gt; merge(df1, df2)
  [1] start     end       function. chr       RM        sim       stat      strand   id       
  &lt;0 Zeilen&gt; (oder row.names mit Länge 0)
  </code></pre>
  <p>Which, for completeness, is equivalent to this:</p>
  <pre><code>&gt; merge(df1, df2, by=intersect(names(df1), names(df2)))
  [1] start     end       function. chr       RM        sim       stat      strand    id       
  &lt;0 Zeilen&gt; (oder row.names mit Länge 0)
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-19T11:10:25.910Z
Answers.LastActivityDate: 2022-11-19T11:10:25.910Z
Answers.UserDisplayName: gringer
Body: <p>I have two csv formatted data frames that I want to merge. First data frame contains 9 columns and the second contains three columns. Based on column names: start, end, function I am trying to merge the two data frames. Following is what I tried:</p>
  <pre><code>df1&lt;-read.csv(file = &quot;LuC_A_TE_gff.csv&quot;)
  df2&lt;-read.csv(file = &quot;LuC_A_TE_1kb_range_ids.csv&quot;)
  merged.data&lt;-merge(df1, df2, by.x=c(&quot;start&quot;,&quot;end&quot;,&quot;function&quot;), by.y=c(&quot;start&quot;,&quot;end&quot;,&quot;function&quot;))
  </code></pre>
  <blockquote>
  <p>Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column.</p>
  </blockquote>
  <pre><code>df1
   
  </code></pre>
  <p>Output</p>
  <pre><code>chr,RM,sim,start,end,stat,strand,id,function
  Chr01A,RepeatMasker,similarity,1,1122,4438,+,.,Target=rnd-4_family-4692 2 652;Class=LTR;PercDiv=4.9;PercDel=9.9;PercIns=5.4;
  Chr01A,RepeatMasker,similarity,1123,2001,6668,+,.,Target=25:218931..219866_LTR 171 936;Class=LTR/Gypsy;PercDiv=6.5;PercDel=6.5;PercIns=0.7;
  Chr01A,RepeatMasker,similarity,1998,2421,3164,+,.,Target=rnd-4_family-4692 102 559;Class=LTR;PercDiv=6.1;PercDel=8.0;PercIns=0.0;
  Chr01A,RepeatMasker,similarity,2002,2119,1182,+,.,Target=rnd-5_family-5429 198 324;Class=DNA/Tc1-Mariner;PercDiv=5.1;PercDel=5.7;PercIns=0.0;
  Chr01A,RepeatMasker,similarity,2284,3113,6265,+,.,Target=25:218931..219866_LTR 32 908;Class=LTR/Gypsy;PercDiv=4.6;PercDel=6.4;PercIns=0.7;
  Chr01A,RepeatMasker,similarity,3003,3103,1238,+,.,Target=rnd-5_family-5429 159 202;Class=DNA/Tc1-Mariner;PercDiv=9.1;PercDel=5.5;PercIns=1.6;
  </code></pre>
  <p>.</p>
  <pre><code>df2
  </code></pre>
  <p>output</p>
  <pre><code>start,end,function
  10000060,10000222,Target=rnd-6_family-1538 599 686;Class=DNA/CMC-EnSpm;PercDiv=24.8;PercDel=1.1;PercIns=3.2;
  10000190,10000200,Target=33:34356884..34364107_INT-int 202 270;Class=LTR;PercDiv=20.5;PercDel=5.9;PercIns=1.6;
  10000201,10000302,Target=rnd-6_family-3392 448 555;Class=DNA;PercDiv=10.9;PercDel=6.9;PercIns=0.9;
  10000223,10000356,Target=rnd-6_family-1538 196 343;Class=DNA/CMC-EnSpm;PercDiv=20.3;PercDel=11.2;PercIns=0.7;
  10000312,10000423,Target=fa.05_5_22233 5 115;Class=DNA;PercDiv=3.6;PercDel=0.0;PercIns=0.9;
  10001256,10001332,Target=rnd-4_family-2291 200 273;Class=DNA;PercDiv=17.6;PercDel=0.0;PercIns=4.0;
  10001590,10001909,Target=rnd-4_family-274 376 722;Class=DNA/CMC-EnSpm;PercDiv=12.3;PercDel=9.4;PercIns=0.9;
  10001650,10001757,Target=rnd-6_family-7385 84 183;Class=DNA;PercDiv=16.3;PercDel=0.9;PercIns=9.0;
  10002071,10002421,Target=rnd-3_family-11 116 430;Class=DNA/hAT-Ac;PercDiv=4.2;PercDel=0.0;PercIns=11.4;
  10002933,10003171,Target=rnd-5_family-572 47 262;Class=DNA;PercDiv=17.4;PercDel=4.6;PercIns=15.7;
  10003024,10003085,Target=Chr23:11562195..11564516 1491 1552;Class=RC/Helitron;PercDiv=4.8;PercDel=0.0;PercIns=0.0;
  10003030,10003207,Target=rnd-4_family-825 65 227;Class=DNA;PercDiv=12.9;PercDel=7.3;PercIns=17.2;
  10003132,10003142,Target=rnd-5_family-1284 903 950;Class=DNA/Kolobok;PercDiv=16.7;PercDel=7.7;PercIns=2.4;
  10003143,10003941,Target=fa.24_3_1586 1 753;Class=DNA;PercDiv=17.3;PercDel=4.8;PercIns=11.2;
  10003209,10003451,Target=rnd-5_family-502 491 715;Class=DNA/IS3EU;PercDiv=19.6;PercDel=7.4;PercIns=16.0;
  10003271,10003496,Target=rnd-6_family-3401 557 818;Class=DNA/IS3EU;PercDiv=22.4;PercDel=19.5;PercIns=3.0;
  10003414,10003567,Target=rnd-1_family-1158 5 166;Class=Unknown;PercDiv=25.1;PercDel=9.7;PercIns=4.3;
  10003494,10003560,Target=rnd-4_family-825 2 63;Class=DNA;PercDiv=13.7;PercDel=4.5;PercIns=12.9;
  10003631,10003854,Target=rnd-1_family-378 1 406;Class=DNA/P;PercDiv=4.2;PercDel=0.0;PercIns=0.1;
  10004081,10004200,Target=fa.00_5_3944 125 245;Class=DNA;PercDiv=10.8;PercDel=0.8;PercIns=0.0;
  10004147,10004282,Target=fa.13_3_23430 1409 1547;Class=DNA/hAT;PercDiv=6.7;PercDel=2.9;PercIns=0.7;
  10004172,10004199,Target=fa.02_2_13330 434 524;Class=DNA;PercDiv=16.6;PercDel=3.1;PercIns=7.6;
  10004200,10004427,Target=rnd-1_family-377 2 229;Class=DNA;PercDiv=11.3;PercDel=3.1;PercIns=3.1;
  10004279,10004320,Target=fa.13_3_23430 882 923;Class=DNA/hAT;PercDiv=6.8;PercDel=0.0;PercIns=0.0;
  10004321,10004656,Target=fa.15_1_6197 348 728;Class=DNA/Tc1-Mariner;PercDiv=13.1;PercDel=13.4;PercIns=0.0;
  10004428,10004480,Target=fa.02_2_13330 525 709;Class=DNA;PercDiv=16.6;PercDel=3.1;PercIns=7.6;
  10004482,10004804,Target=rnd-1_family-77 1 331;Class=SINE/tRNA-Core-RTE;PercDiv=7.5;PercDel=4.0;PercIns=1.5;
  10004515,10004566,Target=rnd-6_family-2042 304 370;Class=DNA/TcMar-Tc1;PercDiv=7.7;PercDel=28.9;PercIns=0.0;
  10004658,10005425,Target=rnd-5_family-3762 370 1163;Class=DNA/hAT-Ac;PercDiv=7.8;PercDel=3.8;PercIns=0.4;
  </code></pre>
  
Tags: <r><merge>
Title: How to resolve Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column while using merge function in R
ThreadText: # How to resolve Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column while using merge function in R
  
  (Asked by: username_0 on 2022-11-17)
  
  I have two csv formatted data frames that I want to merge. First data frame contains 9 columns and the second contains three columns. Based on column names: start, end, function I am trying to merge the two data frames. Following is what I tried:
  
  ```
  df1<-read.csv(file = "LuC_A_TE_gff.csv")
  df2<-read.csv(file = "LuC_A_TE_1kb_range_ids.csv")
  merged.data<-merge(df1, df2, by.x=c("start","end","function"), by.y=c("start","end","function"))
  
  ```
  
  > Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column.
  
  ```
  df1
   
  
  ```
  
  Output
  
  ```
  chr,RM,sim,start,end,stat,strand,id,function
  Chr01A,RepeatMasker,similarity,1,1122,4438,+,.,Target=rnd-4_family-4692 2 652;Class=LTR;PercDiv=4.9;PercDel=9.9;PercIns=5.4;
  Chr01A,RepeatMasker,similarity,1123,2001,6668,+,.,Target=25:218931..219866_LTR 171 936;Class=LTR/Gypsy;PercDiv=6.5;PercDel=6.5;PercIns=0.7;
  Chr01A,RepeatMasker,similarity,1998,2421,3164,+,.,Target=rnd-4_family-4692 102 559;Class=LTR;PercDiv=6.1;PercDel=8.0;PercIns=0.0;
  Chr01A,RepeatMasker,similarity,2002,2119,1182,+,.,Target=rnd-5_family-5429 198 324;Class=DNA/Tc1-Mariner;PercDiv=5.1;PercDel=5.7;PercIns=0.0;
  Chr01A,RepeatMasker,similarity,2284,3113,6265,+,.,Target=25:218931..219866_LTR 32 908;Class=LTR/Gypsy;PercDiv=4.6;PercDel=6.4;PercIns=0.7;
  Chr01A,RepeatMasker,similarity,3003,3103,1238,+,.,Target=rnd-5_family-5429 159 202;Class=DNA/Tc1-Mariner;PercDiv=9.1;PercDel=5.5;PercIns=1.6;
  
  ```
  
  .
  
  ```
  df2
  
  ```
  
  output
  
  ```
  start,end,function
  10000060,10000222,Target=rnd-6_family-1538 599 686;Class=DNA/CMC-EnSpm;PercDiv=24.8;PercDel=1.1;PercIns=3.2;
  10000190,10000200,Target=33:34356884..34364107_INT-int 202 270;Class=LTR;PercDiv=20.5;PercDel=5.9;PercIns=1.6;
  10000201,10000302,Target=rnd-6_family-3392 448 555;Class=DNA;PercDiv=10.9;PercDel=6.9;PercIns=0.9;
  10000223,10000356,Target=rnd-6_family-1538 196 343;Class=DNA/CMC-EnSpm;PercDiv=20.3;PercDel=11.2;PercIns=0.7;
  10000312,10000423,Target=fa.05_5_22233 5 115;Class=DNA;PercDiv=3.6;PercDel=0.0;PercIns=0.9;
  10001256,10001332,Target=rnd-4_family-2291 200 273;Class=DNA;PercDiv=17.6;PercDel=0.0;PercIns=4.0;
  10001590,10001909,Target=rnd-4_family-274 376 722;Class=DNA/CMC-EnSpm;PercDiv=12.3;PercDel=9.4;PercIns=0.9;
  10001650,10001757,Target=rnd-6_family-7385 84 183;Class=DNA;PercDiv=16.3;PercDel=0.9;PercIns=9.0;
  10002071,10002421,Target=rnd-3_family-11 116 430;Class=DNA/hAT-Ac;PercDiv=4.2;PercDel=0.0;PercIns=11.4;
  10002933,10003171,Target=rnd-5_family-572 47 262;Class=DNA;PercDiv=17.4;PercDel=4.6;PercIns=15.7;
  10003024,10003085,Target=Chr23:11562195..11564516 1491 1552;Class=RC/Helitron;PercDiv=4.8;PercDel=0.0;PercIns=0.0;
  10003030,10003207,Target=rnd-4_family-825 65 227;Class=DNA;PercDiv=12.9;PercDel=7.3;PercIns=17.2;
  10003132,10003142,Target=rnd-5_family-1284 903 950;Class=DNA/Kolobok;PercDiv=16.7;PercDel=7.7;PercIns=2.4;
  10003143,10003941,Target=fa.24_3_1586 1 753;Class=DNA;PercDiv=17.3;PercDel=4.8;PercIns=11.2;
  10003209,10003451,Target=rnd-5_family-502 491 715;Class=DNA/IS3EU;PercDiv=19.6;PercDel=7.4;PercIns=16.0;
  10003271,10003496,Target=rnd-6_family-3401 557 818;Class=DNA/IS3EU;PercDiv=22.4;PercDel=19.5;PercIns=3.0;
  10003414,10003567,Target=rnd-1_family-1158 5 166;Class=Unknown;PercDiv=25.1;PercDel=9.7;PercIns=4.3;
  10003494,10003560,Target=rnd-4_family-825 2 63;Class=DNA;PercDiv=13.7;PercDel=4.5;PercIns=12.9;
  10003631,10003854,Target=rnd-1_family-378 1 406;Class=DNA/P;PercDiv=4.2;PercDel=0.0;PercIns=0.1;
  10004081,10004200,Target=fa.00_5_3944 125 245;Class=DNA;PercDiv=10.8;PercDel=0.8;PercIns=0.0;
  10004147,10004282,Target=fa.13_3_23430 1409 1547;Class=DNA/hAT;PercDiv=6.7;PercDel=2.9;PercIns=0.7;
  10004172,10004199,Target=fa.02_2_13330 434 524;Class=DNA;PercDiv=16.6;PercDel=3.1;PercIns=7.6;
  10004200,10004427,Target=rnd-1_family-377 2 229;Class=DNA;PercDiv=11.3;PercDel=3.1;PercIns=3.1;
  10004279,10004320,Target=fa.13_3_23430 882 923;Class=DNA/hAT;PercDiv=6.8;PercDel=0.0;PercIns=0.0;
  10004321,10004656,Target=fa.15_1_6197 348 728;Class=DNA/Tc1-Mariner;PercDiv=13.1;PercDel=13.4;PercIns=0.0;
  10004428,10004480,Target=fa.02_2_13330 525 709;Class=DNA;PercDiv=16.6;PercDel=3.1;PercIns=7.6;
  10004482,10004804,Target=rnd-1_family-77 1 331;Class=SINE/tRNA-Core-RTE;PercDiv=7.5;PercDel=4.0;PercIns=1.5;
  10004515,10004566,Target=rnd-6_family-2042 304 370;Class=DNA/TcMar-Tc1;PercDiv=7.7;PercDel=28.9;PercIns=0.0;
  10004658,10005425,Target=rnd-5_family-3762 370 1163;Class=DNA/hAT-Ac;PercDiv=7.8;PercDel=3.8;PercIns=0.4;
  
  ```
  
  **Tags:** r, merge
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2022-11-19. Score: 1 | Accepted answer
  
  When I loaded these files into R, I got the following result, which matches your error (except that it's been translated into German):
  
  ```
  > df1 <- read.csv("df1.csv")
  > df2 <- read.csv("df2.csv")
  > merge(df1, df2, by=c("start","end","function"))
  Fehler in fix.by(by.x, x) : 'by' muss gültige Spalte(n) spezifizieren
  
  ```
  
  This error is obscure, and it took me a while to get my head around what was going on. The problem is that 'function' is a reserved word in R, so it renamed the `function` column to add a period [`.`] at the end of it:
  
  ```
  > colnames(df1)
  [1] "chr"       "RM"        "sim"       "start"     "end"       "stat"      "strand"   
  [8] "id"        "function."
  > colnames(df2)
  [1] "start"     "end"       "function."
  
  ```
  
  So R was looking for a column name called "function", didn't find one, and got confused.
  
  I think the easiest fix to this is to use the new names that R has given the tables:
  
  ```
  > merge(df1, df2, by=c("start","end","function."))
  [1] start     end       function. chr       RM        sim       stat      strand    id       
  <0 Zeilen> (oder row.names mit Länge 0)
  
  ```
  
  [note: this has no output, because the examples you provided have no common start/end/function]
  
  Alternatively, let R work out the common fields itself, as its default for `merge` is to intersect the column names (which is what you want in this case):
  
  ```
  > merge(df1, df2)
  [1] start     end       function. chr       RM        sim       stat      strand   id       
  <0 Zeilen> (oder row.names mit Länge 0)
  
  ```
  
  Which, for completeness, is equivalent to this:
  
  ```
  > merge(df1, df2, by=intersect(names(df1), names(df2)))
  [1] start     end       function. chr       RM        sim       stat      strand    id       
  <0 Zeilen> (oder row.names mit Länge 0)
  
  ```
  
  
  ---
  
  