How do I create a new categorical variable from continuous multiple observations?

Question

This is my data:

I want to go through each ID, and create a new column ("state") for the larger value for each ID call it "high" and for the lower value, call it "low".

What's the best way to do this?

Jilber Urbina · Accepted Answer · 2018-09-25T16:20:47.070

2

Using R base

> transform(df1, state = ave(dist, ID, FUN= function(x)ifelse(x==max(x), "high", "low")))

  ID dist state
1  1   23  high
2  1   10   low
3  2   12   low
4  2   20  high
5  3   14   low
6  3   33  high

edited Sep 25 '18 at 16:20

answered Sep 25 '18 at 16:08

Jilber Urbina

58,147
10
114
138

1

If it is inside `transform`, you don't need the `df1$` Could be made a bit more compact ith `transform(df1, state = c('low', 'high')[(ave(dist, ID, FUN = max) == dist) + 1])` – akrun Sep 25 '18 at 16:19

score 1 · Answer 2 · answered Sep 25 '18 at 16:02

We can create a condition with max/min

library(dplyr)
df1 %>%
  group_by(ID) %>%
  mutate(state = case_when(dist == max(dist) ~  "high",
                           dist == min(dist) ~ "low",
                           TRUE  ~ NA_character_))

As there are two values per each 'ID', the second condition is not needed

df1 %>%
  group_by(ID) %>%
  mutate(state = case_when(dist == max(dist) ~  "high",
                         TRUE  ~"low"))

data

df1 <- structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 3L), dist = c(23L, 
10L, 12L, 20L, 14L, 33L)), class = "data.frame", row.names = c(NA, 
-6L))

score 0 · Answer 3 · answered Sep 25 '18 at 16:39

0

With data.table...

library(data.table)
setDT(DF)

DF[order(ID, dist), v := c("lo", "hi")]

   ID dist  v
1:  1   23 hi
2:  1   10 lo
3:  2   12 lo
4:  2   20 hi
5:  3   14 lo
6:  3   33 hi

answered Sep 25 '18 at 16:39

Frank

66,179
8
96
180

How do I create a new categorical variable from continuous multiple observations?

3 Answers3

data