count of variable values across columns in a dataframe

user_10568575 0
Viewed 177

I want to do a count of the number of times a number appears in a df. I specifically want a variable that outputs the # and the frequency count. Here is some data, although this is an abridged version of the data. I have close to 45 columns.

a1  b1   c1
23  23   1
24  4    18
2   2    2
18  NA   NA

I want this:

value  freq
1       1
2       2
4       1
18      2
23      2
24      1

I tried this, but it's not doing what I want it to.

library(plyr)  
count(df, vars = NULL, wt_var = NULL)

Can someone help?

count r
1 Answers

We can unlist the data.frame, get the table, convert to a data.frame, and order the rows based on the 'Freq' column

out <- as.data.frame(table(unlist(df1)))
out[order(-out$Freq),]

Or with tidyverse, reshape to 'long' format pivot_longer and get the count of the 'value' column

library(tidyr)
library(dplyr)
df1 %>%
    pivot_longer(everything(), values_drop_na = TRUE) %>%
    count(value) %>%
    arrange(desc(n))

-output

# A tibble: 6 x 2
#  value     n
#  <int> <int>
#1     2     3
#2    18     2
#3    23     2
#4     1     1
#5     4     1
#6    24     1

data

df1 <- structure(list(a1 = c(23L, 24L, 2L, 18L), b1 = c(23L, 4L, 2L, 
NA), c1 = c(1L, 18L, 2L, NA)), class = "data.frame", row.names = c(NA, 
-4L))
user_37322710
Related