How to de-aggregate this data ?

[Andrzej]

Hi, I have got two dataframes:

table3by2 <-  tibble::tribble(
   ~Parent_smoking, ~Child_Yes,  ~Child_No,
   "Both",  401, 1381,
   "One",  417, 1824,
   "Neither",  189, 1169
 )

dat1 <- structure(list(
  Parent = c(
    "Both", "Both", "One", "One", "Neither",
    "Neither"
  ), Child = c("Yes", "No", "Yes", "No", "Yes", "No"),
  Counts = c("401", "1381", "417", "1824", "189", "1169")
), row.names = 2:7, class = "data.frame")

I would like to de-aggregate these data to the desired, following shape:

or instead of Yes or No coded like 0 and 1 and by analogy for Parent_smoking group 1, 2, 3 as well.
Thank you for any help.

[nirgrahamuk]

library(tidyverse)

dat1 |> 
     mutate(n=as.numeric(Counts)) |> 
     uncount(weights = n) |> 
     select(-Counts) |> 
     rename_with(.fn=~paste0(.x,"_smoking")) |> 
     relocate(starts_with("C"))

[Andrzej]

Thank you very much indeed,

can you please advise me, how to transform table3by2 into dat1 and vice-versa if possible ?

[nirgrahamuk]

what have you tried ?
I would thing they are pivots of each other i.e. pivot_wider, pivot_longer ... etc

[Andrzej]

I have tried:

table3by2 |> as.data.frame.table()

but this is not exactly as dat1.

[nirgrahamuk]

There are excellent lessons here:
12 Tidy data | R for Data Science (had.co.nz)

[Andrzej]

Thank you, but there are no better lessons than reading your code

Anyway I have tried:

table3by2$Parent_smoking <- factor(table3by2$Parent_smoking,, levels=c("Both", "One", "Neither"))

table3by2 |> reshape2::melt() |> arrange(Parent_smoking)

If you have better or other alternative please share.

[nirgrahamuk]

table3by2 |> 
pivot_longer(cols=c("Child_Yes","Child_No"),
values_to = "Counts",names_to = "Child") |> 
mutate(Child=str_remove_all(Child,"Child_")) |> 
rename(Parent=Parent_smoking)

[Andrzej]

Thank you very much Nir.
I have learnt a lot reading your solutions and trying a bit by myself.
Of course I will look into that link you sent about pivoting as well.

All the best.