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.


1 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:


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
  • $\begingroup$ Thank you so much, this seems to be it! In the future I'll make sure to ask this sort of questions there . $\endgroup$ Apr 16, 2022 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.