Reputation: 23
I can do it using group by with only one Column but I want the Count for every Column in the Dataframe. See example:
Dataframe with similar values in each Column: df =
C1 C2 C3
1 Positiv Negativ Neutral
2 NA Neutral Positiv
3 Positiv NA Negativ
4 Negativ Positiv NA
I thought about something like this:
df %>% group_by(names(df)) %>% count()
desired Output: Count Values in each Column
C1 C2 C3
1 Positive 2 1 1
2 Negative 1 1 1
3 Neutral 0 1 1
4 NA 1 1 1
Upvotes: 2
Views: 1177
Reputation: 887951
We can use base R
with table
sapply(df, function(x) table(factor(x,
levels = c('Negativ', 'Positiv', 'Neutral')), useNA = "always"))
# C1 C2 C3
#Negativ 1 1 1
#Positiv 2 1 1
#Neutral 0 1 1
#<NA> 1 1 1
Or using a vectorized approach
table(unlist(df), c(col(df)), useNA = 'always')[, -4]
# 1 2 3
# Negativ 1 1 1
# Neutral 0 1 1
# Positiv 2 1 1
# <NA> 1 1 1
df <- structure(list(C1 = c("Positiv", NA, "Positiv", "Negativ"), C2 = c("Negativ",
"Neutral", NA, "Positiv"), C3 = c("Neutral", "Positiv", "Negativ",
NA)), class = "data.frame", row.names = c("1", "2", "3", "4"))
Upvotes: 1
Reputation: 39613
Try this with some tidyverse
functions and reshaping your data to long and wide:
library(dplyr)
library(tidyr)
#Code
new <- df %>% pivot_longer(everything()) %>%
group_by(across(everything())) %>%
summarise(N=n()) %>%
pivot_wider(names_from = name,values_from=N,values_fill=0)
Output:
# A tibble: 4 x 4
value C1 C2 C3
<chr> <int> <int> <int>
1 Negativ 1 1 1
2 Positiv 2 1 1
3 NA 1 1 1
4 Neutral 0 1 1
Some data used:
#Data
df <- structure(list(C1 = c("Positiv", NA, "Positiv", "Negativ"), C2 = c("Negativ",
"Neutral", NA, "Positiv"), C3 = c("Neutral", "Positiv", "Negativ",
NA)), class = "data.frame", row.names = c("1", "2", "3", "4"))
Upvotes: 2