Need help transforming columns

[msrose]

Need help transforming some columns. In the image, I have 4 lines. 3 of the items (93) are the same items and the 4th item is a different item (95). For item 93, all of the line information is the same EXCEPT for the Aun, denom and counter column. When the data was brought into the excel doc, it created duplicate rows because of the Aun, denom and counter columns.

What I am wanting to do, is i want the entries for the AUn column to become individual rows. So instead of having three rows for the same item, i would like one row for item 93. For example, 93 would have all of the same columns before the "gross weight" column and then "HL denom", "HL counter", "Lay denom", "lay counter" etc. Is there a way to do this in Rstudio?

Thanks!

[jonspring]

This can be done with pivot_wider from the tidyr package.

Given some data:

df <- data.frame(Material = c(93, 93, 93, 95),
           Weight = c(11.627, 11.627, 11.627, 12.000),
           AUn = c("HL", "LAP", "PAL", "HL"),
           Denom = c(10605,1,1,11352),
           Counter = c(99999,18,90,99999))

We can load the tidyr package and reshape the data:

library(tidyr)
df %>%
  pivot_wider(names_from = AUn, 
              values_from = c(Denom, Counter), 
              names_glue = "{AUn}_{.value}",   # order of words in headers
              names_vary = "slowest")          # order of headers

Result

# A tibble: 2 × 8
  Material Weight HL_Denom HL_Counter LAP_Denom LAP_Counter PAL_Denom PAL_Counter
     <dbl>  <dbl>    <dbl>      <dbl>     <dbl>       <dbl>     <dbl>       <dbl>
1       93   11.6    10605      99999         1          18         1          90
2       95   12      11352      99999        NA          NA        NA          NA

---

BTW, it is best practice to provide data in your question in the form of code, not as a picture. Not only does that save work for people who want to help you, but it also helps reduce the potential for miscommunication. If you have an existing example of data you can share, the quickest way to do that is often with the magical dput function. For instance, if I had the df object in R and wanted to share the first two rows in a reproducible way, I could type dput(head(df, 2)) in the console in R and get some output that I could share. That code will be a recipe for others to recreate the exact same object.

[msrose]

Hi!

Thank you for your quick response. I thought an image would be easier to understand. Next time i will provide both. As for your suggestion, I have transformed a .txt in excel into an excel doc called md. I loaded that excel doc in Rstudio and am now trying to use your code:

df %>%
pivot_wider(names_from = AUn,
values_from = c(Denom, Counter),
names_glue = "{AUn}_{.value}", # order of words in headers
names_vary = "slowest")

right now i have:

df <- md %>% pivot_wider(names_from = AUn,
values_from = c(Denom., Counter),
names_glue = '{"HL_denom"}{"HL_counter"}_{.value}',
names_vary = "slowest")

and im getting the error:

Error in check_pivot_spec():
! The .name column of spec must be unique.
Backtrace:

1. md %>% ...
2. tidyr:::pivot_wider.data.frame(...)
3. tidyr::pivot_wider_spec(...)
4. tidyr::check_pivot_spec(spec)

[jonspring]

I'd suggest dropping the names_glue line and see if that works. The point of that line is to control the order of the words in your header columns, but I think the way you've changed it is causing the error. (The version I suggested was going to use the value originally from AUn to start the header, combining it with each of the "value" columns (Denom and Counter), whereas you've changed it to reference two potential header values, which is not what the function expects).

If you have other issues with this question, please include the output of dput(md) or dput(head(md)) so that we can all work with the same data.