CODEWITHSUNDEEP
r
Rags
Rags

Reputation: 133

How to get sum by each factor level?

I have filtered data and one of the columns has 5 factor levels and I want to get sum for each of the factor level.

I am using the below code 

levels(df_Temp$ATYPE)
[1] "a"  "b" "c"  "d"   "Unknown"

I am using the below code 

cast(df_Temp,ATYPE~AFTER_ADM, sum, value = "CHRGES")

but the output I am getting is as below 

  ATYPE 0          1
1 a     0 2368968.39
2 b     0 3206567.47
3 c     0   19551.19
4 e     0 2528688.12

I want to all the factor levels and sum as "0" for those missing data of factors level.

So the desired output is 

  ATYPE 0          1
1 a     0 2368968.39
2 b     0 3206567.47
3 c     0   19551.19
4 d     0          0
5 e     0 2528688.12

Upvotes: 4

Views: 2213

Answers (3)

akrun
akrun

Reputation: 887118

Using xtabs from base R

xtabs(CHRGES ~ ATYPE + AFTER_ADM, subset(df_Temp, ATYPE != "e"))
#   AFTER_ADM
#ATYPE           0           1
#    a  0.00000000 -5.92270971
#    b -1.68910431  0.05222349
#    c -0.26869311  0.16922669
#    d  1.44764443 -1.59011411
#    e  0.00000000  0.00000000

data

set.seed(24)
df_Temp <- data.frame(ATYPE = sample(letters[1:5], 20, replace = TRUE),
    AFTER_ADM = sample(0:1, 20, replace = TRUE), CHRGES = rnorm(20))

Upvotes: 3

Mike
Mike

Reputation: 4370

Another possible solution using dplyr and tidyr. Using count and complete from the two packages will help solve your problem. 

    library(dplyr)
    library(tidyr)
    #using iris as toy data
    iris2 <- iris %>% 
             filter(Species != "setosa")

    #count data and then fill n with 0 
    ir3 <- count(iris2, Species) %>% 
        complete(Species, fill = list(n =0))

Upvotes: 1

Andrew
Andrew

Reputation: 5138

If I understand your question correctly, you can use dplyr. First I created an example dataset:

set.seed(123)
x <- sample(letters[1:5], 1e3, replace = T)
x[x == "e"] <- "Unknown"
y <- sample(1:100, 1e3, replace = T)
df1 <- data.frame(ATYPE = factor(x), AFTER_ADM = y)
df1$AFTER_ADM[df1$ATYPE == "Unknown"] <- NA

head(df1, 10)
     ATYPE AFTER_ADM
1        b        28
2        d        60
3        c        17
4  Unknown        NA
5  Unknown        NA
6        a        48
7        c        78
8  Unknown        NA
9        c         7
10       c        45

And then use group_by and summarise to get the sum and the counts. I was not sure if you would want the counts for the factor levels but it is easy to take out if you are not interested:

library(dplyr)

df1 %>%
  group_by(ATYPE) %>%
  summarise(sum_AFTER_ADM = sum(AFTER_ADM, na.rm = T),
            n_ATYPE = n())
# A tibble: 5 x 3
  ATYPE   sum_AFTER_ADM n_ATYPE
  <fct>           <int>   <int>
1 a               10363     198
2 b               11226     206
3 c                9611     203
4 d                9483     195
5 Unknown             0     198

Upvotes: 1

Related Questions