How to de-aggregate this data ?

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",
  ), 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.


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

Thank you very much indeed,

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

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

I have tried:

table3by2 |>

but this is not exactly as dat1.

There are excellent lessons here:
12 Tidy data | R for Data Science (

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

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.

table3by2 |> 
values_to = "Counts",names_to = "Child") |> 
mutate(Child=str_remove_all(Child,"Child_")) |> 

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.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.