CODEWITHSUNDEEP
rdata.table
Erik Steiner
Erik Steiner

Reputation: 601

R Show empty groups with cut()

I have a set of data:

   Abweichung BW_Gesamt
76        236   1137747
77       2000   1149019
78       2000   1227972
79       2331   1346480
80       4000   2226810
81       5272   2874114
82       8585   4418070
83      15307   5389585

Now I want to group them. The difficulty is that I can apply flexible breaks, by entering MIN/MAX of x-Axis and amount of groups. So it will cut the data into groups that are "MYSCHRTW" wide:

bins <- 4 # Amount of groups
MYMIN <- 0
MYMAX <- 20000
MYSCHRTW <- (-MYMIN+MYMAX)%/%bins # Wide of one group 5000
GRENZEN <- seq(from = MYMIN, by = MYSCHRTW, length.out = bins)
GRENZEN <- c(GRENZEN, MYMAX+1) #Brakes: 0 5000 10000 15000 20001

I use the cut function:

setDT(mydata)[ , Gruppen := cut(mydata$Abweichung,breaks=GRENZEN,dig.lab = 5)]

The problem is, that one group is missing, because it is empty and so not being displayed. Plotting the data without that group can bias the result So how can I add group (10000,15000], with Abweichung and BW_Gesamt 0:

   Abweichung BW_Gesamt       Gruppen
1:        236   1137747      (0,5000]
2:       2000   1149019      (0,5000]
3:       2000   1227972      (0,5000]
4:       2331   1346480      (0,5000]
5:       4000   2226810      (0,5000]
6:       5272   2874114  (5000,10000]
7:       8585   4418070  (5000,10000]
8:      15307   5389585 (15000,20001]

Upvotes: 0

Views: 669

Answers (2)

Erik Steiner
Erik Steiner

Reputation: 601

I guess I found an answer by myself: So continue at my initial post at:

setDT(mydata)[ , Gruppen := cut(mydata$Abweichung,breaks=GRENZEN,dig.lab = 5)]
> print(mydata)
   Abweichung BW_Gesamt       Gruppen
1:        236   1137747      (0,5000]
2:       2000   1149019      (0,5000]
3:       2000   1227972      (0,5000]
4:       2331   1346480      (0,5000]
5:       4000   2226810      (0,5000]
6:       5272   2874114  (5000,10000]
7:       8585   4418070  (5000,10000]
8:      15307   5389585 (15000,20000]

> class(mydata$Abweichung)
[1] "numeric"
> class(mydata$BW_Gesamt)
[1] "numeric"

library(dplyr)

mydata <- levels(mydata$Gruppen) %>%  #get distinct levels of the Gruppen variable
  data.frame(Gruppen = .) %>%  # create a data frame
  left_join(mydata %>%    # join with
              group_by(Gruppen) %>%    # for each value that exists
              summarise(Abweichung = n(), BW_Gesamt = sum(BW_Gesamt)), by = "Gruppen") %>%      # get occurrence of Abweichung and sum of BW_Gesamt just for fun 
  mutate(Abweichung = coalesce(Abweichung, 0L)) %>%  # replace NAs with 0s
  mutate(BW_Gesamt = coalesce(as.integer(BW_Gesamt), 0L))

> class(mydata$Abweichung)
[1] "integer"
> class(mydata$BW_Gesamt)
[1] "integer"

> print(mydata)
        Gruppen Abweichung BW_Gesamt
1      (0,5000]          5   7088028
2  (5000,10000]          2   7292184
3 (10000,15000]          0         0
4 (15000,20000]          1   5389585

There is a difference in mutate Abweichung and mutate BW_Gesamt, because I found out that Abweichung will be changed to integer, while BW_Gesamt remains numeric.

I don't know how efficient this method is, I found it here: LINK Thanks to AntoniosK

Maybe someone has an idea how it could be optimized. In my opinion it has the advantage of changing the result of the groups. So I can show the sum of BW_Gesamt while showing the number of occurrence of Abweichung at the same time.

Upvotes: 1

Mbr Mbr
Mbr Mbr

Reputation: 734

Ok I don't know if it's efficient but there is a way :

library(data.table)

The data you work on :

mydata <- data.table(Abweichung = c(236,2000,2000,2331,4000,5272,8585,15307),
                     BW_Gesamt = c(1137747,1149019,1227972,1346480,2226810,2874114,4418070,5389585))


> mydata
   Abweichung BW_Gesamt
1:        236   1137747
2:       2000   1149019
3:       2000   1227972
4:       2331   1346480
5:       4000   2226810
6:       5272   2874114
7:       8585   4418070
8:      15307   5389585

First create a data.table that contains all the groups from cut() :

groups_cut <- data.table(Gruppen = levels(cut(mydata[, Abweichung],breaks=GRENZEN,dig.lab = 5)))

> groups_cut
         Gruppen
1:      (0,5000]
2:  (5000,10000]
3: (10000,15000]
4: (15000,20001]

Then a second data.table in which you count the number of occurrences by the variable Gruppen :

mydata <- mydata[ , Gruppen := cut(mydata[, Abweichung],breaks=GRENZEN,dig.lab = 5)][, .N, by = Gruppen]

         Gruppen N
1:      (0,5000] 5
2:  (5000,10000] 2
3: (15000,20001] 1

Now you can merge the two data.table :

merge_dt<- mydata[groups_cut, on = "Gruppen"]

> merge_dt
         Gruppen  N
1:      (0,5000]  5
2:  (5000,10000]  2
3: (10000,15000] NA
4: (15000,20001]  1

If you don't want to keep the NA value, you can add a little syntax after the merge :

merge_dt <- mydata[groups_cut, on = "Gruppen"][, N := replace(N, is.na(N), 0)]

> merge_dt
         Gruppen N
1:      (0,5000] 5
2:  (5000,10000] 2
3: (10000,15000] 0
4: (15000,20001] 1

Upvotes: 1

Related Questions