Create new column using condition on another existing column

Question

I have data like this

Time    chamber
9       1
10      2
11      3
12      4
13      5
14      6
15      7
16      8
17      9
18      10
19      11
20      12
21      1
22      2
23      3
24      4

I want to create a new column using conditions on another existing column (chamber).

It should look something like this

Time    chamber treatment
9       1       c2t2
10      2       c2t2
11      3       c0t0r
12      4       c2t2r
13      5       c2t2r
14      6       c0t0
15      7       c0t0r
16      8       c0t0r
17      9       c2t2
18      10      c2t2r
19      11      c0t0
20      12      c0t0
21      1       c2t2
22      2       c2t2
23      3       c0t0r
24      4       c2t2r

For chambers 1,2,9: Treatment is c2t2
For chambers 3,7,8: Treatment is c0t0r.
For chambers 4,5,10: Treatment is c2t2r
For chambers 6,11,12: Treatment is c0t0.

I have also made a lookup table, but I don't know how to use it:

lookup_table <- data.frame(row.names = c("1", "2", "3","4", "5", "6","7", "8", "9","10", "11", "12"),
                           new_col = c("C2T2", "C2T2", "C0T0R","C2T2R", "C2T2R", "C0T0","C0T0R", "C0T0R", "C2T2","C2T2R", "C0T0", "C0T0"), 
                           stringsAsFactors = FALSE)

For chambers 1,2,9 the treatment is c2t2. For chambers 3,7,8 the treatment is c0t0r. For chambers 4,5,10 its c2t2r. and for chambers 6,11,12 its c0t0. — Kathiravan Meeran, Dec 09 '17 at 13:42
Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — pogibas, Dec 09 '17 at 13:47

score 2 · Accepted Answer · answered Dec 09 '17 at 14:30

Assuming "dt" is your dataframe name, then you can use dplyr with case_when

library(tidyverse)
dt %>% 
  mutate(newcol = case_when(dt$chamber %in% c(1, 2, 9) ~ "c2t2",
                            dt$chamber %in% c(3, 7, 8) ~ "c0t0r",
                            dt$chamber %in% c(4, 5, 10) ~ "c2t2r",
                            dt$chamber %in% c(6, 11, 12) ~ "c0t0"))

Output:

   Time chamber newcol
1     9       1   c2t2
2    10       2   c2t2
3    11       3  c0t0r
4    12       4  c2t2r
5    13       5  c2t2r
6    14       6   c0t0
7    15       7  c0t0r
8    16       8  c0t0r
9    17       9   c2t2
10   18      10  c2t2r
11   19      11   c0t0
12   20      12   c0t0
13   21       1   c2t2
14   22       2   c2t2
15   23       3  c0t0r
16   24       4  c2t2r
>

@Kathiravan Meeran , please accept the answer if its worked for you by clucking the check mark — PKumar, Dec 10 '17 at 17:21

score 1 · Answer 2 · answered Dec 09 '17 at 14:16

You can merge your df with the lookup_table. In my experience, if you want to combine different data.frames, merge() is the command I like to use. Do note that there are many different ways and specialised packages you can use for the same purpose!

You need to specify which column you use as the 'matching column' and also that you want to keep all records in df:

merge(df, lookup_table, all.x = TRUE, by.x = "chamber", by.y = "row.names")

Data:

df <- structure(list(Time = 9:24, chamber = c(1L, 2L, 3L, 4L, 5L, 6L, 
                     7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L)), 
                    .Names = c("Time", "chamber"), class = "data.frame", 
                    row.names = c(NA, -16L))
lookup_table <- structure(list(new_col = c("C2T2", "C2T2", "C0T0R", "C2T2R", 
                                          "C2T2R", "C0T0", "C0T0R", "C0T0R", 
                                          "C2T2", "C2T2R", "C0T0", "C0T0")), 
                                .Names = "new_col", 
                                row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"), class = "data.frame")

Hi Ken S. Thanks for your solution. I tried it but ended up getting only 12 rows instead of 16 rows. I guess I shouldnt have used lookup table for this problem. But thanks for your advice on merging two dataframes. — Kathiravan Meeran, Dec 10 '17 at 16:54

Create new column using condition on another existing column

2 Answers2