CODEWITHSUNDEEP
rpivot
Rayan Sp
Rayan Sp

Reputation: 1020

how to Count the values frequency of 2 rows in R

I have a table like this : 

Menu name | Transcode
Menu 1    | a1
Menu 2    | a2
Menu 3    | a3
Menu 1    | a1
Menu 3    | a2
Menu 1    | a1
Menu 1    | a3

How can I achieve this pivot in R. to count the number of values frequency in column x (menu name) and the frequency of column y (Trancode) in relation with column x

enter image description here

Upvotes: 1

Views: 1097

Answers (2)

Navin Manaswi
Navin Manaswi

Reputation: 992

df <- data.frame(X= sample(c("Menu 1", "Menu 2"), 10, replace = TRUE), Y= sample(c("a1", "a2", "a3"), 10, replace = TRUE))
df
      X  Y
1  Menu 2 a2
2  Menu 1 a3
3  Menu 1 a3
4  Menu 2 a1
5  Menu 1 a1
6  Menu 2 a2
7  Menu 1 a1
8  Menu 1 a2
9  Menu 2 a2
10 Menu 2 a2

library(dplyr)
df %>% group_by(X,Y) %>% arrange(X,Y) %>% summarise(count = n())

Source: local data frame [5 x 3]
Groups: X

    X        Y    count
   1 Menu 1  a1     2
   2 Menu 1  a2     1
   3 Menu 1  a3     2
   4 Menu 2  a1     1
   5 Menu 2  a2     4

Upvotes: 1

akrun
akrun

Reputation: 887048

Instead of a two column output, it may be better to have it in three columns (for easier post-processing). We get the frequency counts using table, use addmargins to sum up the counts for each "Menu_name", convert to 'data.frame'. If needed, we can order the output ('res') by the first column.

 res <- as.data.frame(addmargins(table(df1), 2))
 res1 <- res[order(res[,1]),]
 row.names(res1) <- NULL
 res1[1:2] <- lapply(res1[1:2], as.character)

Then, use rbind to create the "Grand Sum" row

rbind(res1, list("Menu", "Grand Sum", sum(res1$Freq)))
#    Menu_name Transcode Freq
#1     Menu 1        a1    3
#2     Menu 1        a2    0
#3     Menu 1        a3    1
#4     Menu 1       Sum    4
#5     Menu 2        a1    0
#6     Menu 2        a2    1
#7     Menu 2        a3    0
#8     Menu 2       Sum    1
#9     Menu 3        a1    0
#10    Menu 3        a2    1
#11    Menu 3        a3    1
#12    Menu 3       Sum    2
#13      Menu Grand Sum   14

Upvotes: 1

Related Questions