R - Selecting columns based on a condition

Question

I am trying to find an easy way to select different columns (db$Item1, db$Item2, db$Item3, db$Item4) from a data set based on a condition (db$Scale). Preferably using dplyr. Here's a reproducible example.

Scale <- c(1, 1, 1, 2, 2, 3, 3)
Item1 <- c(4, 5, 4, 6, 7, 7, 6)
Item2 <- c(2, 3, 6, 3, 4, 5, 4)
Item3 <- c(6, 7, 3, 4, 5, 6, 5)
Item4 <- c(2, 5, 3, 5, 2, NA, NA)

db <- data.frame(Scale, Item1, Item2, Item3, Item4)

Brief description: I have a data set with three different scales (1, 2, and 3) each of which has a different number of items. The scale 1 is composed of 4 items, the scale 2 is composed of 4 items, and the scale 3 is composed of 3 items.

#  Scale Item1 Item2 Item3 Item4
#1     1     4     2     6     2
#2     1     5     3     7     5
#3     1     4     6     3     3
#4     2     6     3     4     5
#5     2     7     4     5     2
#6     3     7     5     6    NA
#7     3     6     4     5    NA

What I need to do is to create a new data set (let's say: db.X) for which I have the old column Scale (new name: Scale.X), and then a new column Item1.X composed of the values under the column Item 1 for the scales 1 and 2, and the values under the column Item 3 for the scale 3. Also, I want to report the values for db$Item3 under the new column Item2.X

#  Scale.X Item1.X Item2.X
#1      1      4      6
#2      1      5      7
#3      1      4      3
#4      2      3      4
#5      2      4      5
#6      3      5      6
#7      3      4      5

You said : Item1.X composed of the values under the column Item 1 for the scales 1 and 2. This is not what should showed in the final output — Sonny
– Sonny, Commented Mar 21, 2019 at 19:12

Sonny · Accepted Answer · 2019-03-21 19:10:28Z

1

Is this what you are looking for:

db %>%
  mutate(Scale.X = Scale) %>%
  mutate(Item1.X = ifelse(Scale == 1, Item1, Item2),
         Item2.X = Item3) %>%
  select(Scale.X, Item1.X, Item2.X)
  Scale.X Item1.X Item2.X
1       1       4       6
2       1       5       7
3       1       4       3
4       2       3       4
5       2       4       5
6       3       5       6
7       3       4       5

answered Mar 21, 2019 at 19:10

Sonny

3,1651 gold badge13 silver badges19 bronze badges

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

Comments

Rushabh Patel · Accepted Answer · 2019-03-21 19:21:01Z

1

using data.table -

setDT(db)
db[,Item1.x:=ifelse(Scale == 1, Item1, Item2)]
setnames(db,c("Scale","Item3"),c("Scale.x","Item2.x"))
db <- db[,.(Scale.x,Item2.x,Item1.x)]

Output-

> db
   Scale.x Item2.x Item1.x
1:       1       6       4
2:       1       7       5
3:       1       3       4
4:       2       4       3
5:       2       5       4
6:       3       6       5
7:       3       5       4

answered Mar 21, 2019 at 19:21

Rushabh Patel

2,7741 gold badge18 silver badges37 bronze badges

Collectives™ on Stack Overflow

R - Selecting columns based on a condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related