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

Question

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.

Thank you for your help.

Answer 1

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