Replace values in cells with an integer from a column

Question

If I have a dataframe as follows:

id	risk	speciesA	speciesB	speciesC	speciesD	speciesE	speciesF
A	40	NA	NA	text1	NA	NA	text2
B	12	NA	text3	NA	NA	text4	NA
C	65	NA	NA	NA	text5	NA	text6

How can I programmatically replace the 'text' values wherever they occur (nb they are likely to be different words in each case) in each row with the value in the 'risk' column so that I get the following?

id	risk	speciesA	speciesB	speciesC	speciesD	speciesE	speciesF
A	40	NA	NA	40	NA	NA	40
B	12	NA	12	NA	NA	12	NA
C	65	NA	NA	NA	65	NA	65

Ronak Shah · Accepted Answer · 2021-03-03 06:59:46Z

2

Reshape , replace and reshape back :

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = starts_with('species')) %>%
  mutate(value = ifelse(is.na(value), NA, risk)) %>%
  pivot_wider()

#  id     risk speciesA speciesB speciesC speciesD speciesE speciesF
#  <chr> <int>    <int>    <int>    <int>    <int>    <int>    <int>
#1 A        40       NA       NA       40       NA       NA       40
#2 B        12       NA       12       NA       NA       12       NA
#3 C        65       NA       NA       NA       65       NA       65

answered Mar 3, 2021 at 6:59

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ThomasIsCoding · Accepted Answer · 2021-03-03 00:40:50Z

1

You can try the code below

df[-(1:2)] <- t(apply(df, 1, function(v) replace(v, !is.na(v), v["risk"])))[, -(1:2)]

which gives

> df
  id risk speciesA speciesB speciesC speciesD speciesE speciesF
1  A   40     <NA>     <NA>       40     <NA>     <NA>       40
2  B   12     <NA>       12     <NA>     <NA>       12     <NA>
3  C   65     <NA>     <NA>     <NA>       65     <NA>       65

Data

> dput(df)
structure(list(id = c("A", "B", "C"), risk = c(40L, 12L, 65L),
    speciesA = c(NA, NA, NA), speciesB = c(NA, "text3", NA),
    speciesC = c("text1", NA, NA), speciesD = c(NA, NA, "text5"
    ), speciesE = c(NA, "text4", NA), speciesF = c("text2", NA,
    "text6")), class = "data.frame", row.names = c(NA, -3L))

edited Mar 3, 2021 at 0:40

answered Mar 2, 2021 at 23:44

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

2 Comments

Harry Over a year ago

Thanks - this seems to rely on the text starting with "text" in order for it to work, is there a way of replacing any text that occurs with the value from the 'risk' column in that row?

Harry Over a year ago

Just tried it- it works perfectly, thanks!

B. Christian Kamgang · Accepted Answer · 2021-03-03 00:00:03Z

0

Here is one possible solution:

df[] <- lapply(df, function(x) ifelse(grepl("text", x), df$risk, x))
df <- type.convert(df)

# id risk speciesA speciesB speciesC speciesD speciesE speciesF
# A   40       NA       NA       40       NA       NA       40
# B   12       NA       12       NA       NA       12       NA
# C   65       NA       NA       NA       65       NA       65

answered Mar 3, 2021 at 0:00

B. Christian Kamgang

6,5348 silver badges11 bronze badges

Collectives™ on Stack Overflow

Replace values in cells with an integer from a column

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related