0

I have a question regarding some data I am working with in R. I have two data frames. The second data frame is a list of changes that I want to make in the first data frame.

df1
       date  plot.no      sp1       sp2    weight   tag
1     14oct       06    green                  70     1
2     14oct       05   yellow                  63     2
3     14oct       04      red     red01        41     3
4     14oct       04      red                  41     3

df2
       sp1      sp2 
1    green   orange  
2      red     blue  
3   yellow   yellow

If a color of df1$sp1 matches a color in df2$sp1, I would like to fill the corresponding blank cells of df1$sp2 with what is written in the corresponding cells in the df2$sp2. I don't want to overwrite what is already written in df1$sp2.

I tried merging the dataframes by the sp1 column think that that would add the sp2 column to the corresponding entries, but that didn't seem to work.

This is what I want the data frame to look like after the changes:

df.final
       date  plot.no      sp1       sp2    weight   tag
1     14oct       06    green    orange        70     1
2     14oct       05   yellow    yellow        63     2
3     14oct       04      red     red01        41     3
4     14oct       04      red      blue        41     3

Not sure if this should be done with ifelse statements or loops instead?

italia
  • 25
  • 3
  • I believe this is the best solution: df1$col2 <- df2$col2[match(df1$col1, df2$col1)]. For some reason the ifelse statement doesn't work, it looks like it makes the codes fill randomly to the column2? Can't figure out why. https://intellipaat.com/community/31833/r-add-a-new-column-to-a-dataframe-using-matching-values-of-another-dataframe This helped, although I do realize that it ended up being the same as your answer without the ifelse. So I can re-accept your answer if you want? – italia Dec 08 '20 at 16:31
  • If you use the data that I have shared under my post and try my answer then it does give you the expected answer. It is advisable to share your data with `dput` so that we can make sure that answer works on your data. Maybe you have factor columns and if you change them to character it might work. Also you show your empty data as `''` but you actually might have `NA`'s. There is a difference between the two. Can't really be sure without knowing the details of the data that you are using – Ronak Shah Dec 09 '20 at 01:22

2 Answers2

0

You can do a left_join on df1 and df2. Replace blank cells in sp2 column in df1 with NA and use coalesce to get the first non-NA value.

library(dplyr)

df1 %>%
  left_join(df2, by = 'sp1') %>%
  mutate(sp2 = coalesce(replace(sp2.x, sp2.x == '', NA), sp2.y)) %>%
  select(-sp2.x, -sp2.y)

You can also do this without a join in base R using ifelse and match.

df1$sp2 <- ifelse(df1$sp2 == '', df2$sp2[match(df1$sp1, df2$sp1)], df1$sp2)
df1

#   date plot.no    sp1    sp2 weight tag
#1 14oct       6  green orange     70   1
#2 14oct       5 yellow yellow     63   2
#3 14oct       4    red  red01     41   3
#4 14oct       4    red   blue     41   3

data

df1 <- structure(list(date = c("14oct", "14oct", "14oct", "14oct"), 
    plot.no = c(6L, 5L, 4L, 4L), sp1 = c("green", "yellow", "red", 
    "red"), sp2 = c("", "", "red01", ""), weight = c(70L, 63L, 
    41L, 41L), tag = c(1L, 2L, 3L, 3L)), 
class = "data.frame", row.names = c("1", "2", "3", "4"))

df2 <- structure(list(sp1 = c("green", "red", "yellow"), sp2 = c("orange", 
"blue", "yellow")), class = "data.frame", row.names = c("1", 
"2", "3"))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

I would first rename the column df2$sp2 to df2$sp2_corr, join the tables, use your if statement and then remove the sp2_corr column.

In the tidyverse it would be:

library(tidyverse)

df3 <- df2 %>%
  rename(sp2_corr = sp2) %>%
  left_join(df1, .) %>%
  mutate(sp2 = if_else(sp2 == "", sp2_corr, sp2)) %>%
  select(-sp2_corr)

> df3
   date plot.no    sp1    sp2 weight tag
1 14oct       6  green orange     70   1
2 14oct       5 yellow yellow     63   2
3 14oct       4    red  red01     41   3
4 14oct       4    red   blue     41   3
GaelS
  • 630
  • 6
  • 15