add a new column based on grouping attributes

Question

I want to add a new column in R which summarizes my subgroups into groups.

Here my example:

id = c(1,2,2,3,4,4,4,5,5,5,6,6,6)
subgroup = c("lightred","lightblue","darkblue","lightred","darkred","darkred","lightblue","darkgreen","darkgreen","lightgreen","darkred","darkblue","lightgreen")
data = data.frame(cbind(id,subgroup))

> data
   id   subgroup
1   1   lightred
2   2  lightblue
3   2   darkblue
4   3   lightred
5   4    darkred
6   4    darkred
7   4  lightblue
8   5  darkgreen
9   5  darkgreen
10  5 lightgreen
11  6    darkred
12  6   darkblue
13  6 lightgreen

Now I want to add a new column "colour" which groups the attributes into 3 gropus "red", "green" and "blue", regardless if they are light- or dark-coloured.

It should look like this at the end:

   id   subgroup colour
1   1   lightred    red
2   2  lightblue   blue
3   2   darkblue   blue
4   3   lightred    red
5   4    darkred    red
6   4    darkred    red
7   4  lightblue   blue
8   5  darkgreen  green
9   5  darkgreen  green
10  5 lightgreen  green
11  6    darkred    red
12  6   darkblue   blue
13  6 lightgreen  green

and what can I do if the subgruop doesn't contain the word red,blue or green. For example if I want to group cyan to blue as well? — A_beginner, Jul 04 '18 at 03:46
Then you can update the regular expressions accordingly to match the desired pattern(s) for each group. See my updated post. — MHammer, Jul 04 '18 at 04:09
https://stackoverflow.com/questions/51165825/r-add-a-new-column-and-group-attributes — A_beginner, Jul 04 '18 at 04:35

Tim Biegeleisen · Accepted Answer · 2018-07-04T02:25:07.677

2

I think sub should be workable here:

data$colour <- sub("^(?:light|dark)", "", data$subgroup)

Demo

edited Jul 04 '18 at 02:25

answered Jul 04 '18 at 02:23

Tim Biegeleisen

502,043
27
286
360

2

@RonakShah I wanted to do that, but for some reason couldn't articulate the code. – Tim Biegeleisen Jul 04 '18 at 02:25
and what can I do if the subgroup doesn't contain the word red,blue or green. For example if I want to group cyan to blue as well? Can I define a group of attributes which belong to one group (=Colour) and then assign them ? For example blue = cyan, lightblue, darkblue, marine, water, navy, sky, teal... – A_beginner Jul 04 '18 at 03:53
That is a different question and would invalidate the answers already given. You might want to ask a new question. – Tim Biegeleisen Jul 04 '18 at 04:00
https://stackoverflow.com/questions/51165825/r-add-a-new-column-and-group-attributes – A_beginner Jul 04 '18 at 04:35

MHammer · Answer 2 · 2018-07-04T04:04:45.020

0

While this method isn't as slick as others, it's quite flexible. I tweaked the ops's sample data to show how you can combine multiple groups that don't follow the light/dark paradigm.

Edit:

Updated post to answer the op's question in the comments.

id = c(1,2,2,3,4,4,4,5,5,5,6,6,6)
subgroup = c("lightred","lightblue","cyan","lightred","water","darkred","lightblue","darkgreen","darkgreen","lightgreen","darkred","darkblue","lightgreen")
data = data.frame(cbind(id,subgroup))


library(dplyr)
data <- data %>% 
  dplyr::mutate(
    colour = dplyr::case_when(
      grepl("red"  , subgroup, fixed = TRUE) ~ "red",
      grepl("(blue)|(cyan)|(water)", subgroup, perl = TRUE) ~ "blue",
      grepl("green", subgroup, fixed = TRUE) ~ "green",
      TRUE ~ "else"
    )
  )
data

edited Jul 04 '18 at 04:04

answered Jul 04 '18 at 02:23

MHammer

1,274
7
12

and what can I do if the subgroup doesn't contain the word red,blue or green. For example if I want to group cyan to blue as well? Can I define a group of attributes which belong to one group (=Colour) and then assign them ? For example blue = cyan, lightblue, darkblue, marine, water, navy, sky, teal... – A_beginner Jul 04 '18 at 03:53
@A_beginner Yes. You can use () and | to or statements in regular expressions. I updated my post accordingly. – MHammer Jul 04 '18 at 04:15

score 0 · Answer 3 · answered Jul 04 '18 at 02:44

From stringr

stringr::str_extract(data$subgroup,"red|green|blue")
 [1] "red"   "blue"  "blue"  "red"   "red"   "red"   "blue"  "green" "green" "green" "red"   "blue"  "green"



data$color=stringr::str_extract(data$subgroup,"red|green|blue")
data
   id   subgroup color
1   1   lightred   red
2   2  lightblue  blue
3   2   darkblue  blue
4   3   lightred   red
5   4    darkred   red
6   4    darkred   red
7   4  lightblue  blue
8   5  darkgreen green
9   5  darkgreen green
10  5 lightgreen green
11  6    darkred   red
12  6   darkblue  blue
13  6 lightgreen green

add a new column based on grouping attributes

3 Answers3

Demo

Linked