0

when I merge dataframes, I write this code:

library(readxl)
df1 <- read_excel("C:/Users/PC/Desktop/precipitaciones_4Q.xlsx")
df2 <- read_excel("C:/Users/PC/Desktop/libro_copia_1.xlsx")

df1 = data.frame(df1) 
df2 = data.frame(df2)

df1$codigo = toupper(df1$codigo)
df2$codigo = toupper(df2$codigo)      


dat = merge.data.frame(df1,df2,by= "codigo", all.y =  TRUE,sort = TRUE)

the data has rainfall counties, df1 has less counties than df2. I want to paste counties that has rainfall data from df1 to df2. The problem occurs when counties data are paste into df2, repeat counties appears.

df1:

df1 image

df2:

df2 image

kev
  • 127
  • 6
  • It's not recommended to read straight from `xlsx`. Do you have example data? – MAPK Aug 24 '19 at 22:51
  • I modified the publication, I put two images – kev Aug 24 '19 at 23:17
  • 1
    [See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on making an R question that folks can help with. That includes a sample of data that people can work with (not pictures of it), all necessary code, and a clear explanation of what you're trying to do and what hasn't worked. – camille Aug 24 '19 at 23:21
  • ok, that´s true. where can I upload data base ? – kev Aug 24 '19 at 23:24

1 Answers1

1

Instead "id" you must specify the column names for join from the first and second table. You can use the data.table package and code below:

library(data.table)
dat <- merge(df1, df2, by.x = "Columna1", by.y = "prov", all.y = TRUE)

also, you can use funion function:

dat <- funion(df1, df2)

or rbind function:

dat <- rbind(df1, df2)
dat <- unique(dat)

Note: column names and the number of columns of the two dataframes needs to be same.

red_quark
  • 971
  • 5
  • 20
  • I get this error: Error in .set_ops_arg_check(x, y, all, block_list = !all) : x and y must both be data.tables – kev Aug 24 '19 at 23:48
  • You must convert your objects to datatable. To do this, run the following command: `df1 <- as.data.table(name_of_your_first_table)`, `df2 <- as.data.table(name_of_your_second_table)` – red_quark Aug 24 '19 at 23:52
  • I get a new error : x and y must have the same column classes – kev Aug 24 '19 at 23:59
  • Check which columns have different classes in the two tables. This can be done in a RStudio environment. Columns, that will have different classes must be reduced to the same class (you can use the `as.type` function to replace the type of an object, for example, `df1$codigo <- as.character(df1$codigo)`) – red_quark Aug 25 '19 at 00:10
  • 1
    In the second table, you don't have a column named `codigo`. You need rename the column `departamento` to `codigo`, or use the method that I indicated above (merge the tables by columns with different names). – red_quark Aug 25 '19 at 00:50