# For each unique value in a column, count respective unique values in another column

I have a set of tabular data (e.g. csv) representing accesses to a server through a specific protocol . The data follows this format:

server_id | protocol
===================
s1         A
s1         C
s1         C
s1         B
s2         A
s2         B
s2         C
s2         A
s3         A
s3         B
s3         B


server_id can be one of: s1, s2, s3
protocol can be one of: A, B, C

In R, how can I get the following?

server_id | A | B | C
=====================
s1      1   1   2
s2      2   1   1
s3      1   2   0


A, B and C columns represent the amount of times a server was accessed with that protocol.

I cannot wrap my head around the declarative way of doing things in R and need some help.
Let me know if my question is not clear or if this is not the correct place to post it.

Since this is more of a programming question than a data science question it would be better suited for the stackoverflow stackexchange page, but this can done relatively easily using some of the functions from the tidyr library:

library(tidyr)

df <- data.frame(
server_id = c("s1", "s1", "s1", "s1", "s2", "s2", "s2", "s2", "s3", "s3", "s3"),
protocol = c("A", "C", "C", "B", "A", "B", "C", "A", "A", "B", "B")
)

df %>%
# count number of rows for each combination of server_id and protocol
group_by(server_id, protocol) %>%
tally() %>%
# pivot the protocol names over the columns
pivot_wider(names_from=protocol, values_from=n) %>%
# replace NA values in all columns with 0
mutate(across(everything(), .fns=~replace_na(., 0)))


Which returns the following dataframe:

server_id A B C
s1 1 1 2
s2 2 1 1
s3 1 2 0
• Thank you so much, this seems to be it! In the future I'll make sure to ask this sort of questions there . Apr 16 at 14:56