R: creating a categorical variable from a numerical variable and custom/open-ended/single-valued intervals

Question

I often find myself trying to create a categorical variable from a numerical variable + a user-provided set of ranges.

For instance, say that I have a data.frame with a numeric variable df$V and would like to create a new variable df$VCAT such that:

• df$VCAT = 0 if df$V is equal to 0
• df$VCAT = 1 if df$V is between 0 to 10 (i.e. (0,10))
• df$VCAT = 2 is df$V is equal to 10 (i.e. [10,10])
• df$VCAT = 3 is df$V is between 10 to 20 (i.e. (10,20))
• df$VCAT = 4 is df$V is greater or equal to than 20 (i.e. [20,Inf])

I am currently doing this by hard coding the "scoring function" myself by doing something like:

df = data.frame(V = seq(1,100))
df = df %>% mutate(VCAT = (V>0) + (V==10) + 2*(V>10) + (V>=20))

I am wondering if there is an easier hacky way to do this in R, preferably usingdplyr (so that I can chain commands). Ideally, I am looking for a short function that can be used in mutate that will take in the variable V and a vector describing the ranges such as buckets. Note that buckets may not be described in the best way here since it is not clear to me how it would allow users to customize the endpoints of the ranges.

Laccolith

Do you know about cut()? Check out ?cut or perhaps even Hmisc::cut2(). – Orvieto

Do you want your function to take a vector plus buckets and return a data frame that looks like the result of the above? Or do you want a function that takes a vector and buckets that can be passed to mutate? – Perineum

@Perineum preferably something that could be passed to mutate. – Laccolith

@Orvieto I wasn't aware of cut or cut2 but they seem to do the trick. That said, I'm sure how to deal with points (e.g. an interval like [0,0]), and whether it can be incorporated with mutate. – Laccolith

@BerkU. I would initially have suggested cut but it doesn't deal with your final value in the way your questions suggests. findInterval seems to be a better suggestion here thanks to @Henrik EDIT: you can also pass it to mutate so I would suggest it solves your problem. – Perineum

car::recode could be best here if you specify your "equal to" criteria first: recode(df$V, "0=0; 100=5; 50=3; 0:10=1; 10:50=2; 50:100=4") -- note: possible duplicate question -- see this post. – Orvieto

df$VCAT2 <- cut(df$V, c(0,9.999,10,20,Inf), labels=F) works just fine. @BerkU – Lamm

Answer 1

A way I bin numbers is to remove the remainder using the modulus opperator, %%. E.g. to bin into groups of 20:

#create raw data
unbinned<-c(1.1,1.53,5,8.3,33.5,49.22,55,57.9,79.6,81,95,201,213)
rawdata<-as.data.frame(unbinned)

#bin the data into groups of 20
binneddata<-mutate(rawdata,binned=unbinned-unbinned %% 20)

#print the data
binneddata

This produces the output:

   unbinned binned
1      1.10      0
2      1.53      0
3      5.00      0
4      8.30      0
5     33.50     20
6     49.22     40
7     55.00     40
8     57.90     40
9     79.60     60
10    81.00     80
11    95.00     80
12   201.00    200
13   213.00    200

So 0 represents 0-<20, 20 represents 20-<40, 40 ,40-<60 etc. (of course divide the binned value by 20 to get sequential groups like in the original question)

Bonus

If you want to use the binned values as categorical variables in ggplot etc. by converting them into strings, they will order strangely, e.g. 200 will come before 40, because '2' comes before '4' in the alphabet, to get around this, use the sprintf function to create leading zeros. (the 3 in %03d should be the number of digits you expect the longest number to be):

#convert the data into strings with leading zeros
binnedstring<-mutate(binneddata,bin_as_character=sprintf('%03d',binned))

#print the data
binnedstring

giving the output:

   unbinned binned bin_as_character
1      1.10      0              000
2      1.53      0              000
3      5.00      0              000
4      8.30      0              000
5     33.50     20              020
etc.

If you want to have 000-<020, create the upper bound using arithmetic and concatenate using the paste function:

#make human readable bin value
binnedstringband<-mutate(
    binnedstring,
    nextband=binned+20,
    human_readable=paste(bin_as_character,'-<',sprintf('%03d',nextband),sep='')
)

#print the data
binnedstringband

Giving:

   unbinned binned bin_as_character nextband     human_readable
1      1.10      0              000       20           000-<020
2      1.53      0              000       20           000-<020
3      5.00      0              000       20           000-<020
4      8.30      0              000       20           000-<020
5     33.50     20              020       40           020-<040
etc.

Ockeghem

You can get the binned value directly with (unbinned %/% 20) * 20. Or unbinned - (unbinned %% 20). These are the base quotient and remainder operators (for integers). Of course this only works if all bins are equal-size, i.e. not cut() with arbitrary breaks. – Lamm

Answer 2

Use cut(), already:

df$VCAT2 <- cut(df$V, c(0,9.999,10,20,Inf), labels=F)

Notice the trick I pull to define a very small bin at 10:

• (and if you need that bin to be infinitesimally narrow, use 10 - 10*.Machine$double.eps)
• you can manually define your desired labels '(0,10)','[10,10]',(10,20), [20,Inf]' with the cut(..., labels) argument.

Lamm