<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>

CC BY-SA 4.0
2022-11-19T11:10:25.910Z
2022-11-19T11:10:25.910Z
gringer
<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>

<r><merge>
How to resolve Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column while using merge function in R
# 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)


---

