Could someone help me with this transformation in R? I would like to transform this table

into this table

To create a table of like-against like

Any help would be appreciated. Thank you. Phil,

CodePudding user response：

You can do:

with(dat, split(Count, Condition)) |>
  table() |>
  data.frame()

  A B Freq
1 0 0    1
2 1 0    2
3 0 1    2
4 1 1    3

Data:

dat <- structure(list(ID = c(1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6, 7, 
7, 8, 9), Condition = c("A", "B", "A", "B", "A", "B", "A", "B", 
"A", "B", "A", "B", "A", "B", "A", "B"), Count = c(1, 0, 1, 1, 
0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0)), class = "data.frame", row.names = c(NA, 
-16L))

CodePudding user response：

Here is a tidyverse solution. I filled missing values with 0, please note that this leads to a different count than in your table (do you mean to have 8, 8 as the last two IDs and not 8, 9?):

data <- read.table(text = "ID   Condition   Count
1   A   1
1   B   0
2   A   1
2   B   1
3   A   0
3   B   1
4   A   1
4   B   1
5   A   1
5   B   1
6   A   1
6   B   0
7   A   0
7   B   1
8   A   0
9   B   0", header = TRUE)

library(tidyr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

data %>% 
  pivot_wider(
    id_cols = ID,
    names_from = Condition,
    values_from = Count,
    values_fill = 0
  ) %>% 
  count(A, B, name = "Count of ID")
#> # A tibble: 4 × 3
#>       A     B `Count of ID`
#>   <int> <int>         <int>
#> 1     0     0             2
#> 2     0     1             2
#> 3     1     0             2
#> 4     1     1             3

^{Created on 2023-01-20 by the reprex package (v1.0.0)}