2
$\begingroup$

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;
Improve this question
$\endgroup$
0

1 Answer 1

Reset to default
0
$\begingroup$

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)
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.