Extracting the number of specific samples

Question

[zizigolu]

Hello

I have patient IDs in one column and if a given mutation is clonal or sub clonal like

SampleID    Mutation
A   Clonal
A   Clonal
A   Clonal
A   Subclonal
A   Subclonal
A   Subclonal
A   Clonal
A   Clonal
B   Clonal
B   Clonal
B   subclonal
C   subclonal
C   clonal

Here, sample A has 5 clonal and 3 sub clonal mutations, sample B has 2 clonal and 1 sub-clonal mutations and sample C has 1 clonal and 1 sub clonal mutation

I want to write this like

SampleID    Clonal  Subclonal
A   5   3
B   2   1
C   1   1

Do you know how I can do that in R?

Thank you

[Fatima]

You may be able to use table function in R.

https://www.rdocumentation.org/packages/base/versions/3.6.2/topics/table

[zizigolu]

Thank you Fatemeh Jan, unfortunately I am too poor in coding and I only have one column either clonal or sub-clonal

[zizigolu]

@ rpolicastro thanks a million

Answer 1

[rpolicastro]

The data.

df <- structure(list(SampleID = c("A", "A", "A", "A", "A", "A", "A",
"A", "B", "B", "B", "C", "C"), Mutation = c("Clonal", "Clonal",
"Clonal", "Subclonal", "Subclonal", "Subclonal", "Clonal", "Clonal",
"Clonal", "Clonal", "subclonal", "subclonal", "clonal")), class = "data.frame", row.names = c(NA,
-13L))

As Fatima pointed out you can use the table function in base R.

df$Mutation <- tolower(df$Mutation)
> table(df)
        Mutation
SampleID clonal subclonal
       A      5         3
       B      2         1
       C      1         1

A tidyverse solution.

library("tidyverse")

new_df <- df %>%
  mutate(Mutation=str_to_lower(Mutation)) %>%
  count(SampleID, Mutation) %>%
  pivot_wider(names_from=Mutation, values_from=n)

> new_df
# A tibble: 3 x 3
  SampleID clonal subclonal
  <chr>     <int>     <int>
1 A             5         3
2 B             2         1
3 C             1         1

data.table also.

library("data.table")

setDT(df)[, Mutation := tolower(Mutation)]
df <- df[, .(count=.N), by=.(SampleID, Mutation)]
new_df <- dcast(df, SampleID ~ Mutation, value.var="count")

> new_df
   SampleID clonal subclonal
1:        A      5         3
2:        B      2         1
3:        C      1         1