Pivot_wider and back

[Andrzej]

Hi,
I have got two dataframes and I want to go from 1 to 2 and from 2 to 1.

So from:

to:

Seems to be quite simple but I have done probably all possible combinations between names_from and values_from but I have not achieved what I wanted.

How should I do it ?

DF1 <-  structure(list(Date = structure(c(1451865600, 1451865600, 1451865600,
1451865600, 1452988800, 1455148800, 1455148800, 1455494400, 1455494400,
1455494400, 1455494400, 1455667200, 1455667200, 1455667200, 1457308800,
1457308800, 1457308800, 1457308800, 1457827200, 1457827200, 1458604800,
1458604800, 1458604800, 1458604800, 1458691200, 1458691200, 1458691200,
1458691200, 1463875200, 1463875200, 1463875200, 1468281600, 1468281600,
1468281600, 1468281600, 1473465600, 1476316800, 1476316800, 1476316800,
1476316800, 1482192000, 1482192000, 1482192000, 1482192000), class = c("POSIXct",
"POSIXt"), tzone = "UTC"), Salesman = c("Edyta", "Edyta", "Edyta",
"Edyta", "Ewa", "Lena", "Lena", "Michal", "Michal", "Michal",
"Michal", "Piotr", "Piotr", "Piotr", "Jola", "Jola", "Jola",
"Jola", "Jola", "Jola", "Marek", "Marek", "Marek", "Marek", "Piotr",
"Piotr", "Piotr", "Piotr", "Jan", "Jan", "Jan", "Piotr", "Piotr",
"Piotr", "Piotr", "Edyta", "Edyta", "Edyta", "Edyta", "Edyta",
"Marek", "Marek", "Marek", "Marek"), Part_number = c("CA-1462-SXA-B",
"BS-5717-CR-C", "CA-1462-SXA-B", "BS-5717-CR-C", "BS-5717-CR-C",
"CCN-3277-ANA-C", "CIA-2966-FAC-A", "AYU-7812-PL-A", "AYU-7812-PL-A",
"BS-5717-CR-C", "AA-2618-RA-C", "CG-3503-PG-A", "C-2173-TF-B",
"CE-2213-LAR-A", "C-2173-TF-B", "CG-3503-PG-A", "BS-5717-CR-C",
"CIA-2966-FAC-A", "C-2573-TF-B", "CCN-3277-ANA-C", "BS-5717-CR-C",
"CCC-4299-XB-C", "AA-2618-RA-C", "AYU-7912-PL-A", "AA-2618-RA-C",
"BC-6707-ANE-A", "AYU-7712-PL-A", "CCN-3277-ANA-C", "BC-6707-ANE-A",
"CA-1462-SXA-B", "C-2173-TF-B", "CCC-4259-XB-C", "C-2173-TF-B",
"CCC-4299-XB-C", "C-2173-TF-B", "AYF-1720-XS-C", "CIA-2966-FAC-A",
"AA-2618-RA-C", "CG-3503-PG-A", "CCC-4299-XB-C", "CE-2213-LAR-A",
"BS-5717-CR-C", "C-2173-TF-B", "C-2173-TF-B"), Amount = c(44,
44, 31, 92, 97, 40, 33, 57, 46, 19, 30, 58, 41, 27, 96, 49, 23,
28, 67, 11, 13, 90, 70, 31, 89, 26, 89, 24, 100, 94, 41, 49,
100, 96, 23, 22, 91, 75, 18, 14, 63, 25, 51, 30)), class = c("tbl_df",
"tbl", "data.frame"), row.names = c(NA, -44L))

[Andrzej]

Trying to get to this:

DF %>%
    dplyr::group_by(Salesman, Date) %>%
    dplyr::mutate(rn = paste0("Amount_",dplyr::row_number())) %>%
  dplyr::mutate(rn2 = paste0("Part_number_",dplyr::row_number())) %>%
    dplyr::ungroup() %>%
    tidyr::pivot_wider(names_from = c(Amount, Part_number), values_from = Amount)

maybe anyone can correct my code ?

[nirgrahamuk]

step_1 <- group_by(DF1,Date,Salesman) |> 
  mutate(entry=row_number()) |> ungroup() 

result <- pivot_wider(step_1,
                      id_cols = c(Date,Salesman),
                      names_from="entry",
                      values_from = c(Part_number,Amount))

[Andrzej]

Thank you @nirgrahamuk very much indeed, so that's the one.

I have to use it id_cols. I have been spending whole the morning experimenting with pivot_wider, and two steps are needed as well.

One question, how do I do this in order to relocating Amount1 next to Part_number1 and so on, please ?

[nirgrahamuk]

There are likely other ways, you can handcraft the names, if its too arduous can make a function to do it.

(ordernames <- map(1:4, ~c(paste0("Part_number_",.x),
                           paste0("Amount_",.x))) |>
    unlist())

final <- relocate(result,ordernames,.after = Salesman)

[Andrzej]

Thank you, always learning from you.

I received this warning at the end:

Warning message:
Using an external vector in selections was deprecated in tidyselect 1.1.0.
ℹ Please use `all_of()` or `any_of()` instead.
  # Was:
  data %>% select(ordernames)

  # Now:
  data %>% select(all_of(ordernames))

See <https://tidyselect.r-lib.org/reference/faq-external-vector.html>.
This warning is displayed once every 8 hours.
Call `lifecycle::last_lifecycle_warnings()` to see where this warning was generated. 

nicely constructed warning message with an a bit of explanation.

[Andrzej]

Now if I would like to go back, it errors. What do I do wrong ?

final %>% pivot_longer(cols = -c(1,2),
                      names_to = "Part_number",
                      values_to = "Amount")

received that error:

Error in `pivot_longer_spec()`:
! Can't combine `Part_number_1` <character> and `Amount_1` <double>.
Run `rlang::last_error()` to see where the error occurred.

Should be pretty self-explanatory, but if you could elaborate a bit in plain English, please ?

Almost the same error with that code as well:

final %>% pivot_longer(cols = everything(),
                      names_to = "Part_number",
                      values_to = "Amount")

and this:

final %>% pivot_longer(cols = 3:10,
                      names_to = "Part_number",
                      values_to = "Amount")

[Andrzej]

I have done it in Excel Power Query so I am partially successful. Would be grateful for hint how to do it in R, I mean going from table 2 to table 1 this time.

[nirgrahamuk]

There may be other more elegant ways than what I got, though I don't think the could we be trivial.

(to_long_step_1 <- final |> pivot_longer(cols=starts_with("Part"),names_to = c("dummy","idnum"),
                                         names_pattern = "(.*)([[:digit:]])") |> 
    rename(Part_number = value) |> select(-dummy))
(result_long <- to_long_step_1 |> pivot_longer(cols=starts_with("Amount"),
                                               names_to = c("dummy","idnum2"),
                                               names_pattern = "(.*)([[:digit:]])") |> 
    rename(Amount = value) |> select(-dummy) |>
    filter(idnum==idnum2,!any()) |> drop_na() |> select(-starts_with("id")))

> waldo::compare(DF1,result_long)
v No differences

[Andrzej]

Thank you very much, I am very grateful,
that was difficult task.
best regards
Andrzej