Grouping data and reassembling rows as columns

July 2, 2024

I have a table of times phone calls were made to people. If someone didn’t answer, an attempt is made to ring them again (and again if needs be) at another time. Each attempt to call is listed in the same column so the ‘person’ column has repeated values. Something like this:

Person	Time of Call	Answer
1	12:00	No answer
2	12:12	No answer
3	12:19	Answer
2	18:10	No answer
1	13:00	Answer
2	19:12	Answer

I want to split the data so that there is one row per person with all the times calls were made, like this:

Person	First Call	First Answer	Second Call	Second Answer	Third Call	Third Answer
1	12:00	No answer	13:00	Answer
2	12:12	No answer	18:10	No answer	19:12	Answer
3	12:19	Answer

>Solution :

You can add a column that defines the id of each call within a person, and then use pivot_wider to generate columns based on these newly created id.
By default, it will put a NA if there is no corresponding call for the id.

library(tidyverse)

df <- tibble(person = c(1, 1, 2, 3, 4, 4, 4))
df$call_time <- rnorm(nrow(df))
df %>%
  mutate(call_id = 1:n(), .by = person) %>%
  pivot_wider(id_cols = person, names_from = call_id, names_prefix = "call_",
              values_from = call_time)
#> # A tibble: 4 × 4
#>   person   call_1 call_2 call_3
#>    <dbl>    <dbl>  <dbl>  <dbl>
#> 1      1  1.26    -0.442  NA   
#> 2      2  0.356   NA      NA   
#> 3      3  0.241   NA      NA   
#> 4      4 -0.00412  0.926  -1.27

^{Created on 2024-07-02 with reprex v2.1.0}