CODEWITHSUNDEEP
r
user10672282
user10672282

Reputation: 25

Count the number of times each value appears in a row dataframe r

I have the following dataframe (79000 rows):

ID       P1      P2      P3      P4        P5        P6      P7     P8  
1       38005   28002   38005   38005    28002    34002      NA     NA
2       28002   28002   28002   38005    28002    NA         NA     NA

I want to count the number of times each number(code) appears in a row of dataframe. So the ouput something like this:

38005 appears 3   28002 appears 2    34002 appears 1     NA appears 2 
28002 appears 3   38005 appears 1    28002 appears 1     NA appears 3

So far I tried to find the most frequent number (code):

df$frequency <-apply(df,1,function(x) names(which.max(table(x))))

But I don't know how to count the number of times each number(code) appears in a row.

Upvotes: 0

Views: 3190

Answers (4)

tmfmnk
tmfmnk

Reputation: 40171

Using tidyverse and reshape2 you can do:

df %>%
 gather(var, val, -ID) %>% #Transforming the data from wide to long format
 group_by(val, ID) %>% #Grouping 
 summarise(count = n()) %>% #Performing the count
 dcast(ID~val, value.var = "count") #Reshaping the data

  ID 28002 34002 38005 NA
1  1     2     1     3  2
2  2     4    NA     1  3

Showing the first two non-NA columns with the biggest count according ID:

df %>%
 gather(var, val, -ID) %>% #Transforming the data from wide to long format
 group_by(val, ID) %>% #Grouping
 mutate(temp = n()) %>% #Performing the count
 group_by(ID) %>% #Grouping
 mutate(temp2 = dense_rank(temp)) %>% #Creating the rank based on count
 group_by(ID, val) %>% #Grouping
 summarise(temp3 = first(temp2), #Summarising 
           temp = first(temp)) %>%
 arrange(ID, desc(temp3)) %>% #Arranging
 na.omit() %>% #Deleting the rows with NA
 group_by(ID) %>%
 mutate(temp4 = ifelse(temp3 == first(temp3) | temp3 == nth(temp3, 2), 1, 0)) %>% #Identifying the highest and the second highest count
 filter(temp4 == 1) %>% #Selecting the highest and the second highest count
 dcast(ID~val, value.var = "temp") #Reshaping the data

  ID 28002 38005
1  1     2     3
2  2     4     1

Upvotes: 1

Wimpel
Wimpel

Reputation: 27792

data.table solution

#read sample data
dt <- fread( "ID       P1      P2      P3      P4        P5        P6      P7     P8  
1       38005   28002   38005   38005    28002    34002      NA     NA
             2       28002   28002   28002   38005    28002    NA         NA     NA")
#melt
dt.melt <- melt(dt, id = 1, measure = patterns("^P"), na.rm = FALSE)
#and cast
dcast( dt.melt, ID ~ value, fun = length, fill = 0 )

#    ID 28002 34002 38005 NA
# 1:  1     2     1     3  2
# 2:  2     4     0     1  3

Upvotes: 0

jay.sf
jay.sf

Reputation: 73782

I think you're looking for this.

sort(table(unlist(df1[-1])), decreasing=TRUE)
# 31002 38005 24003 34002 28002 
# 13222 13193 13019 13018 12625

This is, you're excluding column 1 that contains the IDs and "unlist" the rest of your data frame into a vector. The table() then counts the appearance of each value, which you also can sort(). Set option decreasing=TRUE and the first two values are the two most frequent ones.

If the output is getting to long because of a lot of values, you can include the code into a head(.). The default length of the output is six, but you can limit it to two by specifying n=2 which gives you exactly what you want. No need for any packages.

head(sort(table(unlist(df1[-1])), decreasing=TRUE), n=2)
# 31002 38005 
# 13222 13193

DATA:

set.seed(42)  # for sake of reproducibility
df1 <- data.frame(id=1:9750,
                  matrix(sample(c(38005, 28002, 34002, NA, 24003, 31002), 7.8e4, 
                                replace=TRUE), nrow=9750,
                         dimnames=list(NULL, paste0("P", 1:8))))

Upvotes: 0

Hunaidkhan
Hunaidkhan

Reputation: 1443

ID <- c("P1","P2","P3","P4","P5","P6","P7","P8","P1","P2","P3","P4","P5","P6","P7","P8","P1")
count <-c("38005","28002","38005","38005","28002","34002","NA","NA","2","28002","28002","28002","38005","28002","NA","NA","NA")

df<- cbind.data.frame(ID,count)

table(df$count)

Use this code to find out the count

Upvotes: 0

Related Questions