Flatten a dataframe, pivot to get one row per patientid and sets of repeating columns per visit date

In R studio, is it possible to pivot a merged dataframe in such a way that I get a set of repeating columns per measurement?

I have two dataframes, one with the patients and visit moment codes, and one with the patient, visit dates and measurements. The problem is that I don't know beforehand which is the maximum visits per patient, so I don't know beforehand what is the maximum needed columns. It could be that one patient has 3 visits and another can have just 1.

Also, the dataframe with the visits does have a measurement code column (1,2,3 etc.) so ideally, the sets of columns should be grouped using that as the column name postfix, so in the example like Chol_1, Chol_2, Chol_3 or something like that.

So I have the example code below

library(dplyr)
library(tidyr)

df_visits <- read.table(text = "
PATID,Moment,VisitDate
1001,1,2020-09-04
1001,2,2021-11-04
2002,1,2020-03-14
3003,1,2020-09-24
3003,2,2020-09-27
3003,3,2020-10-11
", header = TRUE, sep = ",") #  

df_measure <- read.table(text = "
PATID,VisitDate,Chol,Kreat,HbA1c
1001,2021-11-04,3.70,82,6.7
1001,2020-09-04,4.60,63,7.9
2002,2020-03-14,2.60,53,6.0
3003,2020-10-11,3.90,94,7.5
3003,2020-09-27,4.90,137,6.7
3003,2020-09-24,4.90,127,7.5
", header = TRUE, sep = ",") #  

# Merge the dataframes based on patient_id and visit_date
merged_df <- merge(df_visits, df_measure, by = c("PATID", "VisitDate"))

And then I tried using tidyr::pivot_wider(), see code below.

pivoted_df <- merged_df %>%
pivot_wider(names_from = c("Chol", "Kreat", "HbA1c"),
            values_from = c("Chol", "Kreat", "HbA1c"),
            names_sep = "_")

But that doesn't work, it doesn't "flatten" to PATID and VisitDate and it seems to generate column names based on the actual values. I think maybe pivot_wider_spec can be used, but I can't figure out the correct parameters to get the desired result or if it is even possible. The desired output dataframe is something like this:

| PATID | Moment_01 | Moment_02 | Moment_03 | VisitDate_01  | VisitDate_02 | VisitDate_03 | Chol_01 | Kreat_01 | HbA1c_01 | Chol_02 | Kreat_02 | HbA1c_02 | Chol_03 | Kreat_03 | HbA1c_03 |
|  1001 |         1 |         2 |           | 2020-09-04    | 2021-11-04   |              |    4.60 |       63 |      7.9 |    3.70 |       82 |      6.7 |         |          |          |
|  2002 |         1 |           |           | 2020-03-14    |              |              |    2.60 |       53 |      6.0 |         |          |          |         |          |          |
|  3003 |         1 |         2 |         3 | 2020-09-24    | 2020-09-27   | 2020-10-11   |    4.90 |      127 |      7.5 |    4.90 |      137 |      6.7 |    3.90 |       94 |      7.5 |

Can anyone point me in the right direction?

Are you looking for:

library(tidyverse)
merged_df %>%
  pivot_wider(names_from = 'Moment',
              values_from = c('Moment', 'VisitDate', 'Chol', 'Kreat', 'HbA1c'))

# A tibble: 3 x 16
  PATID Moment_1 Moment_2 Moment_3 VisitDate_1 VisitDate_2 VisitDate_3 Chol_1
  <int>    <int>    <int>    <int> <chr>       <chr>       <chr>        <dbl>
1  1001        1        2       NA 2020-09-04  2021-11-04  <NA>           4.6
2  2002        1       NA       NA 2020-03-14  <NA>        <NA>           2.6
3  3003        1        2        3 2020-09-24  2020-09-27  2020-10-11     4.9
# ... with 8 more variables: Chol_2 <dbl>, Chol_3 <dbl>, Kreat_1 <int>,
#   Kreat_2 <int>, Kreat_3 <int>, HbA1c_1 <dbl>, HbA1c_2 <dbl>, HbA1c_3 <dbl>