CODEWITHSUNDEEP
rdataframe
Student of the Digital World
Student of the Digital World

Reputation: 3041

Replace a value by a value another data-frame

I have two data frames:

Data Frame 1:

ColA ColB
Mat  Student
Por  Yes

Data Frame 2:

ColA     ColB
Mat      0
Student  1
Por      2
Yes      3
No       4

And I want to create a new data frame by replacing the values in the data frame 1 by data-frame 2

Expected output:

 ColA    ColB
 0        1
 2        3

Upvotes: 3

Views: 66

Answers (3)

Dave2e
Dave2e

Reputation: 24069

Here is a slightly clunky solution using the dplyr library and performing a join between the two data frames.

df1<-read.table(header=TRUE, text="ColA ColB
Mat  Student
Por  Yes", stringsAsFactor=FALSE)

df2<-read.table(header=TRUE, text="ColA     ColB
Mat      0
Student  1
Por      2
Yes      3
No       4", stringsAsFactor=FALSE)

library(dplyr)

newColA<-left_join(df1, df2, by="ColA")
newColB<-left_join(df1, df2, by=c("ColB" = "ColA"))

answer<-data.frame(ColA= newColA[, "ColB.y"], ColB= newColB[, "ColB.y"])
# ColA ColB
# 1    0    1
# 2    2    3

Upvotes: 3

AntoniosK
AntoniosK

Reputation: 16121

An alternative solution using some reshaping and joining:

dt1 = read.table(text = "
ColA ColB
Mat  Student
Por  Yes
", header=T, stringsAsFactors=F)

dt2 = read.table(text = "
ColA     ColB
Mat      0
Student  1
Por      2
Yes      3
No       4
", header=T, stringsAsFactors=F)

library(tidyverse)

dt1 %>%
  gather(x,y) %>%                        # reshape data
  left_join(dt2, by=c("y"="ColA")) %>%   # join values from 2nd table
  group_by(x) %>%                        # for each value of this column
  mutate(y = row_number()) %>%           # count rows (useful to reshape again)
  spread(x,ColB) %>%                     # reshape again
  select(-y)                             # remove column

# # A tibble: 2 x 2
#    ColA  ColB
#   <int> <int>
# 1     0     1
# 2     2     3

And another solution using a user created function that is applied to all columns:

# function that gets ColB from 2nd dataset based on what ColA is
# Vectorised version
f = function(x) dt2$ColB[dt2$ColA==x]
f = Vectorize(f)

# apply function to all columns
dt1 %>% mutate_all(f)

#   ColA ColB
# 1    0    1
# 2    2    3

Upvotes: 4

zx8754
zx8754

Reputation: 56004

Loop through column and subset:

# example data
df1 <- read.table(text = "
  ColA ColB
Mat  Student
Por  Yes
", header = TRUE, stringsAsFactors = FALSE)

df2 <- read.table(text = "
  ColA     ColB
Mat      0
Student  1
Por      2
Yes      3
No       4
", header = TRUE, stringsAsFactors = FALSE)

# matrix output
sapply(df1, function(i) df2[ df2$ColA %in% i, "ColB"])
#      ColA ColB
# [1,]    0    1
# [2,]    2    3

# data.frame output
data.frame(lapply(df1, function(i) df2[ df2$ColA %in% i, "ColB"]))
#   ColA ColB
# 1    0    1
# 2    2    3

Upvotes: 3

Related Questions