Getting error message when running a pivot_wide

sarah_lee123 · February 23, 2023, 3:41am

Hello,

I am brand new to this entire world of R, but I am determined to learn. I am having a hard time because I encounter so many errors. It seems like when I replicate something I see in a course or video, it doesn't work for me and I don't know how to understand the error messages or what to do about them.

I am replicating a video instruction from coursera about the tidyverse, but I am using a different data set than the video to test this on a data set that I need to change from long data to wide data in order to use it.
Ultimately I want it to pivot on the TGO_ID, and I need it to show me the "RESULT_TEST_NAME" and "RESULT") but there are other columns in the table. Is this error happening because I am not indicating what to do with the other columns in the df?

Thanks in advance for any beginner level support you can provide me

sarah_lee123 · February 23, 2023, 3:42am

This is my dataframe, How do I create a wide data table from this long data version?

Thank you

Flm · February 23, 2023, 9:49am

can you share part of your df using dput(head(yourdf, 20)) so that I can replicate it?

jrkrideau · February 23, 2023, 1:03pm

Hi, welcome to the forum.

Just to add a bit more detail to @ Flm's request
A handy way to supply some sample data is the dput() function. In the case of a large dataset something like dput(head(mydata, 100)) should supply the data we need. Just do dput(mydata) where mydata is your data. Copy the output and paste it here.

For some general guidelines on how to ask questions on the forum have a look at FAQ: How to do a minimal reproducible example ( reprex ) for beginners

sarah_lee123 · February 23, 2023, 2:33pm

Basically I have 5 samples submitted to a lab, That lab tests for multiple analytes. You can see here that the long data has each analyte as a separate row, but I want the TGO_ID to be one row, and each analyte to be a column containing the value for the analyte for the associates row.

Hope that makes sense.

TGO_ID is uniques value that distinguish records, RESULT_TEST_NAME for the column name, then the RESULT of each RESULT_TEST_NAME is the number populated into each cell.

I want it to look like this

Columns --- TGO_ID | CBC | CBD | CBDA | CBDV | CBG | CBGA | CBN | d8-THC | d9-THC | THCA
Row 1 ---TGOHI2201| 0 | 0 | 0 | 0. | 0.082| 0.44. | 0 | 0 | 0.9788 | 20.43 |
Row 2--- TGOHI2202|
Row 3--- TGOHI2203|

And so on

Basically need to understand the fundamentals of how to code the table to pivot_wide based on what exactly? I see the cheat sheet formula, but still don't understand practically what words to write into the code.

Hope that all makes sense and helps the process

Cheers,

andresrcs · February 23, 2023, 2:54pm

We can't copy the sample data from a screenshot unless we manually type each value and that is unlikely to happen. (that is why they are considered a bad practice here).

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

Flm · February 23, 2023, 7:23pm

copy and paste the output here

sarah_lee123 · February 23, 2023, 9:36pm

dput(head(Hawaii_LabData_2022, 100))
structure(list(TGO_ID = c("TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202"), REQ_ACCESSION = c(5465, 5465, 5465,
5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
5465, 5465, 5465, 5465, 5465, 5465, 5484, 5484, 5484, 5484, 5484,
5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484), REQ_BATCH ID = c("John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "John Park", "John Park", "John Park", "John Park",
"John Park", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
"Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores"), RESULT_TEST_CODE = c("CBC-P",
"CBD-P", "CBDA-P", "CBDV-P", "CBG-P", "CBGA-P", "CBN-P", "D8-THC-P",
"D9-THC-P", "THCA-P", "TOTALCAN-P", "TOTALCBD-P", "TOTALTHC-P",
"D10R-THC-P", "D10S-THC-P", "CBCA-P", "CBDVA-P", "THCV-P", "THCVA-P",
"CARENE-P", "A_TERPINENE_P", "A_BISABOLOL-P", "A_HUMULENE-P",
"A_PINENE-P", "B_CARYOPHYLLENE-P", "B_MYRCENE-P", "B_PINENE-P",
"CAMPHENE-P", "CARYOPHYLLENE_OXIDE-P", "C_OCIMENE-P", "D_LIMONENE-P",
"EUCALYPTOL-P", "G_TERPINENE-P", "GERANIOL-P", "GUAIOL-P", "ISOPULEGOL-P",
"LINALOOL-P", "NEROLIDOL-P", "P_CYMENE-P", "TERPINOLENE-P", "T_OCIMENE-P",
"Total Terpenes", "CBC-P", "CBC-MGG", "CBD-MGG", "CBD-P", "CBDA-P",
"CBDA-MGG", "CBDV-MGG", "CBDV-P", "CBG-P", "CBG-MGG", "CBGA-MGG",
"CBGA-P", "CBN-P", "CBN-MGG", "D8-THC-MGG", "D8-THC-P", "D9-THC-P",
"D9-THC-MGG", "THCA-MGG", "THCA-P", "TOTALCAN-P", "TOTALCAN-MGG",
"TOTALCBD-P", "TOTALCBD-MGG", "TOTALTHC-P", "TOTALTHC-MGG", "D10R-THC-P",
"D10R-THC-MGG", "D10S-THC-MGG", "D10S-THC-P", "CBCA-MGG", "CBCA-P",
"CBDVA-P", "CBDVA-MGG", "THCV-P", "THCV-MGG", "THCVA-MGG", "THCVA-P",
"CARENE-P", "CARENE-MGG", "A_TERPINENE_P", "A_TERPINENE-MGG",
"A_BISABOLOL-MGG", "A_BISABOLOL-P", "A_HUMULENE-P", "A_HUMULENE-MGG",
"A_PINENE-P", "A_PINENE-MGG", "B_CARYOPHYLLENE-MGG", "B_CARYOPHYLLENE-P",
"B_MYRCENE-P", "B_MYRCENE-MGG", "B_PINENE-P", "B_PINENE-MGG",
"CAMPHENE-MGG", "CAMPHENE-P", "CARYOPHYLLENE_OXIDE-MGG", "CARYOPHYLLENE_OXIDE-P"
), RESULT_TEST_NAME = c("CBC", "CBD", "CBDA", "CBDV", "CBG",
"CBGA", "CBN", "d8-THC", "d9-THC", "THCA", "Total Cannabinoids ~ %",
"Total CBD ~ %", "Total THC ~ %", "(6aR,9R)-d10-THC", "(6aR,9S)-d10-THC",
"CBCA", "CBDVA", "THCV", "THCVA", "(+)-3-Carene", "a-Terpinene",
"alpha-Bisabolol", "alpha-Humulene", "alpha-Pinene", "beta-Caryophyllene",
"beta-Myrcene", "beta-Pinene", "Camphene", "Caryophyllene oxide",
"Cis-beta-Ocimene", "D-Limonene", "Eucalyptol", "gamma-Terpinene",
"Geraniol", "Guaiol", "Isopulegol", "Linalool", "Nerolidol",
"p-Cymene", "Terpinolene", "trans-beta-Ocimene", "Total Terpenes",
"CBC", "CBC", "CBD", "CBD", "CBDA", "CBDA", "CBDV", "CBDV", "CBG",
"CBG", "CBGA", "CBGA", "CBN", "CBN", "d8-THC", "d8-THC", "d9-THC",
"d9-THC", "THCA", "THCA", "Total Cannabinoids ~ %", "Total Cannabinoids ~ mg/g",
"Total CBD ~ %", "Total CBD ~ mg/g", "Total THC ~ %", "Total THC ~ mg/g",
"(6aR,9R)-d10-THC", "(6aR,9R)-d10-THC", "(6aR,9S)-d10-THC", "(6aR,9S)-d10-THC",
"CBCA", "CBCA", "CBDVA", "CBDVA", "THCV", "THCV", "THCVA", "THCVA",
"(+)-3-Carene", "(+)-3-Carene", "a-Terpinene", "a-Terpinene",
"alpha-Bisabolol", "alpha-Bisabolol", "alpha-Humulene", "alpha-Humulene",
"alpha-Pinene", "alpha-Pinene", "beta-Caryophyllene", "beta-Caryophyllene",
"beta-Myrcene", "beta-Myrcene", "beta-Pinene", "beta-Pinene",
"Camphene", "Camphene", "Caryophyllene oxide", "Caryophyllene oxide"
), RESULT = c(0, 0, 0, 0, 0.08157, 0.4405, 0, 0, 0.9788, 20.43,
22.05, 0, 18.89, 0, 0, 0, 0, 0, 0.1212, 0, 0, 0.02, 0.05149,
0.02, 0.18, 0.55, 0.03, 0.01, 0, 0, 0.19, 0, 0, 0, 0.02, 0, 0.06,
0.01, 0, 0, 0, 1.14, 0, 0, 0, 0, 0, 0, 0, 0, 0.1266, 1.266, 9.365,
0.9365, 0, 0, 0, 0, 0.4853, 4.853, 145.9, 14.59, 16.14, 161.4,
0, 0, 13.28, 132.8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0.27, 0.03, 0.04535, 0.45, 0.03, 0.25, 1.65, 0.17, 0.2,
2.01, 0.04, 0.39, 0.07, 0.01, 0, 0), ...7 = c(NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
), ...8 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA), ...9 = c(NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Lege0 = c("P=Percent",
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA)), row.names = c(NA, -100L), class = c("tbl_df", "tbl",
"data.frame"))

andresrcs · February 23, 2023, 9:58pm

The problem is RESULT_TEST_NAME has repeated values for some TGO_ID if I select another column with unique values like RESULT_TEST_CODE it works just fine. Otherwise, you would need to decide what to do with repeated values per RESULT_TEST_NAME/TGO_ID combination.

library(dplyr)
library(tidyr)

# Sample data (replace this with your own data)
Hawaii_LabData_2022 <- structure(list(TGO_ID = c("TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
                          "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
                          "TGOHI2202", "TGOHI2202"), REQ_ACCESSION = c(5465, 5465, 5465,
                                                                       5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
                                                                       5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
                                                                       5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465, 5465,
                                                                       5465, 5465, 5465, 5465, 5465, 5465, 5484, 5484, 5484, 5484, 5484,
                                                                       5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
                                                                       5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
                                                                       5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
                                                                       5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484,
                                                                       5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484, 5484), REQ_BATCH_ID = c("John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "John Park", "John Park", "John Park", "John Park",
                                                                                                                                               "John Park", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores",
                                                                                                                                               "Jan Flores", "Jan Flores", "Jan Flores", "Jan Flores"), RESULT_TEST_CODE = c("CBC-P",
                                                                                                                                                                                                                             "CBD-P", "CBDA-P", "CBDV-P", "CBG-P", "CBGA-P", "CBN-P", "D8-THC-P",
                                                                                                                                                                                                                             "D9-THC-P", "THCA-P", "TOTALCAN-P", "TOTALCBD-P", "TOTALTHC-P",
                                                                                                                                                                                                                             "D10R-THC-P", "D10S-THC-P", "CBCA-P", "CBDVA-P", "THCV-P", "THCVA-P",
                                                                                                                                                                                                                             "CARENE-P", "A_TERPINENE_P", "A_BISABOLOL-P", "A_HUMULENE-P",
                                                                                                                                                                                                                             "A_PINENE-P", "B_CARYOPHYLLENE-P", "B_MYRCENE-P", "B_PINENE-P",
                                                                                                                                                                                                                             "CAMPHENE-P", "CARYOPHYLLENE_OXIDE-P", "C_OCIMENE-P", "D_LIMONENE-P",
                                                                                                                                                                                                                             "EUCALYPTOL-P", "G_TERPINENE-P", "GERANIOL-P", "GUAIOL-P", "ISOPULEGOL-P",
                                                                                                                                                                                                                             "LINALOOL-P", "NEROLIDOL-P", "P_CYMENE-P", "TERPINOLENE-P", "T_OCIMENE-P",
                                                                                                                                                                                                                             "Total Terpenes", "CBC-P", "CBC-MGG", "CBD-MGG", "CBD-P", "CBDA-P",
                                                                                                                                                                                                                             "CBDA-MGG", "CBDV-MGG", "CBDV-P", "CBG-P", "CBG-MGG", "CBGA-MGG",
                                                                                                                                                                                                                             "CBGA-P", "CBN-P", "CBN-MGG", "D8-THC-MGG", "D8-THC-P", "D9-THC-P",
                                                                                                                                                                                                                             "D9-THC-MGG", "THCA-MGG", "THCA-P", "TOTALCAN-P", "TOTALCAN-MGG",
                                                                                                                                                                                                                             "TOTALCBD-P", "TOTALCBD-MGG", "TOTALTHC-P", "TOTALTHC-MGG", "D10R-THC-P",
                                                                                                                                                                                                                             "D10R-THC-MGG", "D10S-THC-MGG", "D10S-THC-P", "CBCA-MGG", "CBCA-P",
                                                                                                                                                                                                                             "CBDVA-P", "CBDVA-MGG", "THCV-P", "THCV-MGG", "THCVA-MGG", "THCVA-P",
                                                                                                                                                                                                                             "CARENE-P", "CARENE-MGG", "A_TERPINENE_P", "A_TERPINENE-MGG",
                                                                                                                                                                                                                             "A_BISABOLOL-MGG", "A_BISABOLOL-P", "A_HUMULENE-P", "A_HUMULENE-MGG",
                                                                                                                                                                                                                             "A_PINENE-P", "A_PINENE-MGG", "B_CARYOPHYLLENE-MGG", "B_CARYOPHYLLENE-P",
                                                                                                                                                                                                                             "B_MYRCENE-P", "B_MYRCENE-MGG", "B_PINENE-P", "B_PINENE-MGG",
                                                                                                                                                                                                                             "CAMPHENE-MGG", "CAMPHENE-P", "CARYOPHYLLENE_OXIDE-MGG", "CARYOPHYLLENE_OXIDE-P"
                                                                                                                                               ), RESULT_TEST_NAME = c("CBC", "CBD", "CBDA", "CBDV", "CBG",
                                                                                                                                                                       "CBGA", "CBN", "d8-THC", "d9-THC", "THCA", "Total Cannabinoids ~ %",
                                                                                                                                                                       "Total CBD ~ %", "Total THC ~ %", "(6aR,9R)-d10-THC", "(6aR,9S)-d10-THC",
                                                                                                                                                                       "CBCA", "CBDVA", "THCV", "THCVA", "(+)-3-Carene", "a-Terpinene",
                                                                                                                                                                       "alpha-Bisabolol", "alpha-Humulene", "alpha-Pinene", "beta-Caryophyllene",
                                                                                                                                                                       "beta-Myrcene", "beta-Pinene", "Camphene", "Caryophyllene oxide",
                                                                                                                                                                       "Cis-beta-Ocimene", "D-Limonene", "Eucalyptol", "gamma-Terpinene",
                                                                                                                                                                       "Geraniol", "Guaiol", "Isopulegol", "Linalool", "Nerolidol",
                                                                                                                                                                       "p-Cymene", "Terpinolene", "trans-beta-Ocimene", "Total Terpenes",
                                                                                                                                                                       "CBC", "CBC", "CBD", "CBD", "CBDA", "CBDA", "CBDV", "CBDV", "CBG",
                                                                                                                                                                       "CBG", "CBGA", "CBGA", "CBN", "CBN", "d8-THC", "d8-THC", "d9-THC",
                                                                                                                                                                       "d9-THC", "THCA", "THCA", "Total Cannabinoids ~ %", "Total Cannabinoids ~ mg/g",
                                                                                                                                                                       "Total CBD ~ %", "Total CBD ~ mg/g", "Total THC ~ %", "Total THC ~ mg/g",
                                                                                                                                                                       "(6aR,9R)-d10-THC", "(6aR,9R)-d10-THC", "(6aR,9S)-d10-THC", "(6aR,9S)-d10-THC",
                                                                                                                                                                       "CBCA", "CBCA", "CBDVA", "CBDVA", "THCV", "THCV", "THCVA", "THCVA",
                                                                                                                                                                       "(+)-3-Carene", "(+)-3-Carene", "a-Terpinene", "a-Terpinene",
                                                                                                                                                                       "alpha-Bisabolol", "alpha-Bisabolol", "alpha-Humulene", "alpha-Humulene",
                                                                                                                                                                       "alpha-Pinene", "alpha-Pinene", "beta-Caryophyllene", "beta-Caryophyllene",
                                                                                                                                                                       "beta-Myrcene", "beta-Myrcene", "beta-Pinene", "beta-Pinene",
                                                                                                                                                                       "Camphene", "Camphene", "Caryophyllene oxide", "Caryophyllene oxide"
                                                                                                                                               ), RESULT = c(0, 0, 0, 0, 0.08157, 0.4405, 0, 0, 0.9788, 20.43,
                                                                                                                                                             22.05, 0, 18.89, 0, 0, 0, 0, 0, 0.1212, 0, 0, 0.02, 0.05149,
                                                                                                                                                             0.02, 0.18, 0.55, 0.03, 0.01, 0, 0, 0.19, 0, 0, 0, 0.02, 0, 0.06,
                                                                                                                                                             0.01, 0, 0, 0, 1.14, 0, 0, 0, 0, 0, 0, 0, 0, 0.1266, 1.266, 9.365,
                                                                                                                                                             0.9365, 0, 0, 0, 0, 0.4853, 4.853, 145.9, 14.59, 16.14, 161.4,
                                                                                                                                                             0, 0, 13.28, 132.8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                                                                                                                                             0, 0, 0.27, 0.03, 0.04535, 0.45, 0.03, 0.25, 1.65, 0.17, 0.2,
                                                                                                                                                             2.01, 0.04, 0.39, 0.07, 0.01, 0, 0), ...7 = c(NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                           NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
                                                                                                                                                             ), ...8 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                         NA, NA, NA, NA, NA, NA, NA), ...9 = c(NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                               NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Lege0 = c("P=Percent",
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                  NA, NA, NA)), row.names = c(NA, -100L), class = c("tbl_df", "tbl",
                                                                                                                                                                                                                                                                                                                                    "data.frame"))

Hawaii_LabData_2022 %>% 
    pivot_wider(id_cols = TGO_ID, names_from = RESULT_TEST_CODE, values_from = RESULT)
#> # A tibble: 2 × 72
#>   TGO_ID `CBC-P` `CBD-P` CBDA-…¹ CBDV-…² `CBG-P` CBGA-…³ `CBN-P` D8-TH…⁴ D9-TH…⁵
#>   <chr>    <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#> 1 TGOHI…       0       0       0       0  0.0816   0.440       0       0   0.979
#> 2 TGOHI…       0       0       0       0  0.127    0.936       0       0   0.485
#> # … with 62 more variables: `THCA-P` <dbl>, `TOTALCAN-P` <dbl>,
#> #   `TOTALCBD-P` <dbl>, `TOTALTHC-P` <dbl>, `D10R-THC-P` <dbl>,
#> #   `D10S-THC-P` <dbl>, `CBCA-P` <dbl>, `CBDVA-P` <dbl>, `THCV-P` <dbl>,
#> #   `THCVA-P` <dbl>, `CARENE-P` <dbl>, A_TERPINENE_P <dbl>,
#> #   `A_BISABOLOL-P` <dbl>, `A_HUMULENE-P` <dbl>, `A_PINENE-P` <dbl>,
#> #   `B_CARYOPHYLLENE-P` <dbl>, `B_MYRCENE-P` <dbl>, `B_PINENE-P` <dbl>,
#> #   `CAMPHENE-P` <dbl>, `CARYOPHYLLENE_OXIDE-P` <dbl>, `C_OCIMENE-P` <dbl>, …

^{Created on 2023-02-23 with reprex v2.0.2}

sarah_lee123 · February 23, 2023, 10:27pm

**pivot_wider(REV_Hawaii_LabData_TGO_2022, names_from = "RESULT_TEST_NAME", **
+ values_from = "RESULT")

A tibble: 6 × 51

TGO_ID ...4 ...5 ...6 Lege0 CBC CBD CBDA CBDV CBG CBGA CBN d8-TH…¹

1 TGOHI22… NA NA NA P=Pe…
2 TGOHI22… NA NA NA NA
3 TGOHI22… NA NA NA NA
4 TGOHI22… NA NA NA NA
5 TGOHI22… NA NA NA NA
6 TGOHI22… NA NA NA NA

… with 38 more variables: `d9-THC` , THCA , `Total Cannabinoids ~ %` ,

`Total CBD ~ %` , `Total THC ~ %` , `(6aR,9R)-d10-THC` ,

`(6aR,9S)-d10-THC` , CBCA , CBDVA , THCV , THCVA ,

`(+)-3-Carene` , `a-Terpinene` , `alpha-Bisabolol` ,

`alpha-Humulene` , `alpha-Pinene` , `beta-Caryophyllene` ,

`beta-Myrcene` , `beta-Pinene` , Camphene ,

`Caryophyllene oxide` , `Cis-beta-Ocimene` , `D-Limonene` , …

Use `colnames()` to see all variable names

Warning message:
Values from RESULT are not uniquely identified; output will contain list-cols.
• Use values_fn = list to suppress this warning.
• Use values_fn = {summary_fun} to summarise duplicates.
• Use the following dplyr code to identify duplicates.
{data} %>%
dplyr::group_by(TGO_ID, ...4, ...5, ...6, Lege0, RESULT_TEST_NAME) %>%
dplyr::summarise(n = dplyr::n(), .groups = "drop") %>%
dplyr::filter(n > 1L)

andresrcs · February 23, 2023, 10:32pm

I already told you "RESULT_TEST_NAME" has some repeated values per row, you can't use that variable to pivot wider unless you want to aggregate repeated values in some way (mean, sum, min, max, etc)

sarah_lee123 · February 23, 2023, 11:15pm

OK, I am not understanding what the solution is and probably not communicating this correctly.

There are a set of variables that are repeated for each TGO_ID which is supposed to be the unique ID in the table. I only want the TGO_ID to appear one time (5 rows, one ID per row). and make one set of the repeated values from RESULT_TEST_NAME become the columns. None of my columns have unique numbers and that is one of my problems. How do I get it into a format I can use?

here is a screenshot of the data table sorted for RESULT_TEST_NAME You can see that it is repeated for each TGO ID.

I need the TGO_ID's to not be repeated 20 times.

ROWS:
TGOHI2201
TGOHI2202
TGOHI2203
TGOHI2204
TGOHI2205

COLUMNS:
CBC
CBD
CBDA
CBDV
CBG
CBGA
CBN
d8-THC
d9-THC
THCA
Total Cannabinoids ~ %
Total CBD ~ %
Total THC ~ %
(6aR,9R)-d10-THC
(6aR,9S)-d10-THC
CBCA
CBDVA
THCV
THCVA
(+)-3-Carene
a-Terpinene
alpha-Bisabolol
alpha-Humulene
alpha-Pinene
beta-Caryophyllene
beta-Myrcene
beta-Pinene
Camphene
Caryophyllene oxide
Cis-beta-Ocimene
D-Limonene
Eucalyptol
gamma-Terpinene
Geraniol
Guaiol
Isopulegol
Linalool
Nerolidol
p-Cymene
Terpinolene
trans-beta-Ocimene
Total Terpenes

Then the number in the RESULTS column will also appear horizontally under each variable name for the respective Row ID.

Do I need to take one set and import it so I can make it wide? if so, then I will need to add the other 4 peoples data manually?

Thanks for your patience,

andresrcs · February 23, 2023, 11:32pm

Just by inspecting the subset of the data you have provided as sample data, I can find all these RESULT_TEST_NAME values that appear more than once per TGO_ID value so if you pivot wider , it would be more than one value per "cell", that is why you get a "list" of values.

Hawaii_LabData_2022 %>% 
    count(TGO_ID, RESULT_TEST_NAME) %>% 
    filter(n > 1)
#> # A tibble: 26 × 3
#>    TGO_ID    RESULT_TEST_NAME     n
#>    <chr>     <chr>            <int>
#>  1 TGOHI2202 (+)-3-Carene         2
#>  2 TGOHI2202 (6aR,9R)-d10-THC     2
#>  3 TGOHI2202 (6aR,9S)-d10-THC     2
#>  4 TGOHI2202 CBC                  2
#>  5 TGOHI2202 CBCA                 2
#>  6 TGOHI2202 CBD                  2
#>  7 TGOHI2202 CBDA                 2
#>  8 TGOHI2202 CBDV                 2
#>  9 TGOHI2202 CBDVA                2
#> 10 TGOHI2202 CBG                  2
#> # … with 16 more rows

In the sample data it seems that RESULT_TEST_CODE has unique values per TGO_ID so I think you could use that variable to pivot by instead.

sarah_lee123 · February 23, 2023, 11:34pm

Weird, I don't know what just happened to my last post. ButI'll try again.

Trying to go from this

TGO Repeated multiple times, Unique ID's in RESULTS_TEST_NAME repeated for each TGO_ID.

sarah_lee123 · February 23, 2023, 11:35pm

What I want is for not TGO_ID repetition.
One set of the RESULTS_TEST_NAME to become the column headers, and the RESULTS values to be horizontally associated with its respective TGO_ID

As in the following Excel sheet.

andresrcs · February 23, 2023, 11:41pm

I do understand what you mean but you have multiple values per "cell" for some of the TGO_ID vs RESULTS_TEST_NAME combinations, please take a closer look, I'm going to collapse the values separated by commas so it becomes more evident to you

Hawaii_LabData_2022 %>% 
    add_count(TGO_ID, RESULT_TEST_NAME) %>%
    filter(n > 1) %>% 
    group_by(TGO_ID, RESULT_TEST_NAME) %>% 
    summarise(
        multiple_test_codes = paste(RESULT_TEST_CODE, collapse = ", "),
        multiple_values = paste(RESULT, collapse = ", "))
#> `summarise()` has grouped output by 'TGO_ID'. You can override using the
#> `.groups` argument.
#> # A tibble: 26 × 4
#> # Groups:   TGO_ID [1]
#>    TGO_ID    RESULT_TEST_NAME multiple_test_codes      multiple_values
#>    <chr>     <chr>            <chr>                    <chr>          
#>  1 TGOHI2202 (+)-3-Carene     CARENE-P, CARENE-MGG     0, 0           
#>  2 TGOHI2202 (6aR,9R)-d10-THC D10R-THC-P, D10R-THC-MGG 0, 0           
#>  3 TGOHI2202 (6aR,9S)-d10-THC D10S-THC-MGG, D10S-THC-P 0, 0           
#>  4 TGOHI2202 CBC              CBC-P, CBC-MGG           0, 0           
#>  5 TGOHI2202 CBCA             CBCA-MGG, CBCA-P         0, 0           
#>  6 TGOHI2202 CBD              CBD-MGG, CBD-P           0, 0           
#>  7 TGOHI2202 CBDA             CBDA-P, CBDA-MGG         0, 0           
#>  8 TGOHI2202 CBDV             CBDV-MGG, CBDV-P         0, 0           
#>  9 TGOHI2202 CBDVA            CBDVA-P, CBDVA-MGG       0, 0           
#> 10 TGOHI2202 CBG              CBG-P, CBG-MGG           0.1266, 1.266  
#> # … with 16 more rows

#> 10 TGOHI2202 CBG              CBG-P, CBG-MGG           0.1266, 1.266

For example, how would you solve the discrepancy between these two values of CBG for TGOHI2202? Which of the two should appear on the corresponding cell? They come from different RESULT_TEST_CODE (CBG-P and CBG-MGG) but the same RESULT_TEST_NAME (CBG).

sarah_lee123 · February 24, 2023, 1:16pm

Ok, that's weird. Each of those are supposed to be separate columns. I have no idea how they became multiples in those cells.

Thoroughly confused about this.

sarah_lee123 · February 24, 2023, 1:37pm

Ok, I think I fixed the problem and reloaded the data.

Can you please the data again and see if it's better.

dput(head(Simplified_HI_Lab_Data_2022, 100))
structure(list(TGO_ID = c("TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201",
"TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2201", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202", "TGOHI2202",
"TGOHI2202", "TGOHI2202"), RESULT_TEST_NAME = c("CBC", "CBD",
"CBDA", "CBDV", "CBG", "CBGA", "CBN", "d8-THC", "d9-THC", "THCA",
"Total Cannabinoids ~ %", "Total CBD ~ %", "Total THC ~ %", "(6aR,9R)-d10-THC",
"(6aR,9S)-d10-THC", "CBCA", "CBDVA", "THCV", "THCVA", "(+)-3-Carene",
"a-Terpinene", "alpha-Bisabolol", "alpha-Humulene", "alpha-Pinene",
"beta-Caryophyllene", "beta-Myrcene", "beta-Pinene", "Camphene",
"Caryophyllene oxide", "Cis-beta-Ocimene", "D-Limonene", "Eucalyptol",
"gamma-Terpinene", "Geraniol", "Guaiol", "Isopulegol", "Linalool",
"Nerolidol", "p-Cymene", "Terpinolene", "trans-beta-Ocimene",
"Total Terpenes", "CBC", "CBC", "CBD", "CBD", "CBDA", "CBDA",
"CBDV", "CBDV", "CBG", "CBG", "CBGA", "CBGA", "CBN", "CBN", "d8-THC",
"d8-THC", "d9-THC", "d9-THC", "THCA", "THCA", "Total Cannabinoids ~ %",
"Total Cannabinoids ~ mg/g", "Total CBD ~ %", "Total CBD ~ mg/g",
"Total THC ~ %", "Total THC ~ mg/g", "(6aR,9R)-d10-THC", "(6aR,9R)-d10-THC",
"(6aR,9S)-d10-THC", "(6aR,9S)-d10-THC", "CBCA", "CBCA", "CBDVA",
"CBDVA", "THCV", "THCV", "THCVA", "THCVA", "(+)-3-Carene", "(+)-3-Carene",
"a-Terpinene", "a-Terpinene", "alpha-Bisabolol", "alpha-Bisabolol",
"alpha-Humulene", "alpha-Humulene", "alpha-Pinene", "alpha-Pinene",
"beta-Caryophyllene", "beta-Caryophyllene", "beta-Myrcene", "beta-Myrcene",
"beta-Pinene", "beta-Pinene", "Camphene", "Camphene", "Caryophyllene oxide",
"Caryophyllene oxide"), RESULT = c(0, 0, 0, 0, 0.08157, 0.4405,
0, 0, 0.9788, 20.43, 22.05, 0, 18.89, 0, 0, 0, 0, 0, 0.1212,
0, 0, 0.02, 0.05149, 0.02, 0.18, 0.55, 0.03, 0.01, 0, 0, 0.19,
0, 0, 0, 0.02, 0, 0.06, 0.01, 0, 0, 0, 1.14, 0, 0, 0, 0, 0, 0,
0, 0, 0.1266, 1.266, 9.365, 0.9365, 0, 0, 0, 0, 0.4853, 4.853,
145.9, 14.59, 16.14, 161.4, 0, 0, 13.28, 132.8, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.27, 0.03, 0.04535, 0.45, 0.03,
0.25, 1.65, 0.17, 0.2, 2.01, 0.04, 0.39, 0.07, 0.01, 0, 0)), row.names = c(NA,
-100L), class = c("tbl_df", "tbl", "data.frame"))

sarah_lee123 · February 24, 2023, 2:07pm

Here is the exact table I am trying to transition. I think I'm missing a step that would allow me to use the pivot_wider function due to the repeated values in RESULT_TEST_NAME. I am confused though because I thought that the "names_from" command calls upon a column with repeating values and that those repeating values become the column names in the pivot_wider table. In some of the video examples, it goes from one column called "Year" and the values in the column are repeated; 2019, 2020, 2019, 2020, 2019, 2020. Then, in the wider table the 2019 and 2020 go from being values in the same column called "year", into their own columns called "2019" and "2020".

I feel like I am doing the same thing, so I am having trouble understanding how to shift the data into a form that would be acceptable for the pivot_wider function.

Do I need to use groupby() or summarize() first? It seems like a pretty basic thing that I am trying to accomplish, so I don't understand why it's so difficult for me to figure out.

I have been reading online and looking to my resources, and they do not address this specific situation.

I appreciate any support with finding a solution.

TGO_ID	RESULT_TEST_NAME	RESULT
TGOHI2201	CBC	0
TGOHI2201	CBD	0
TGOHI2201	CBDA	0
TGOHI2201	CBDV	0
TGOHI2201	CBG	0.08157
TGOHI2201	CBGA	0.4405
TGOHI2201	CBN	0
TGOHI2201	d8-THC	0
TGOHI2201	d9-THC	0.9788
TGOHI2201	THCA	20.43
TGOHI2201	Total Cannabinoids ~ %	22.05
TGOHI2201	Total CBD ~ %	0
TGOHI2201	Total THC ~ %	18.89
TGOHI2201	(6aR,9R)-d10-THC	0
TGOHI2201	(6aR,9S)-d10-THC	0
TGOHI2201	CBCA	0
TGOHI2201	CBDVA	0
TGOHI2201	THCV	0
TGOHI2201	THCVA	0.1212
TGOHI2201	(+)-3-Carene	0
TGOHI2201	a-Terpinene	0
TGOHI2201	alpha-Bisabolol	0.02
TGOHI2201	alpha-Humulene	0.05149
TGOHI2201	alpha-Pinene	0.02
TGOHI2201	beta-Caryophyllene	0.18
TGOHI2201	beta-Myrcene	0.55
TGOHI2201	beta-Pinene	0.03
TGOHI2201	Camphene	0.01
TGOHI2201	Caryophyllene oxide	0
TGOHI2201	Cis-beta-Ocimene	0
TGOHI2201	D-Limonene	0.19
TGOHI2201	Eucalyptol	0
TGOHI2201	gamma-Terpinene	0
TGOHI2201	Geraniol	0
TGOHI2201	Guaiol	0.02
TGOHI2201	Isopulegol	0
TGOHI2201	Linalool	0.06
TGOHI2201	Nerolidol	0.01
TGOHI2201	p-Cymene	0
TGOHI2201	Terpinolene	0
TGOHI2201	trans-beta-Ocimene	0
TGOHI2201	Total Terpenes	1.14
TGOHI2202	CBC	0
TGOHI2202	CBC	0
TGOHI2202	CBD	0
TGOHI2202	CBD	0
TGOHI2202	CBDA	0
TGOHI2202	CBDA	0
TGOHI2202	CBDV	0
TGOHI2202	CBDV	0
TGOHI2202	CBG	0.1266
TGOHI2202	CBG	1.266
TGOHI2202	CBGA	9.365
TGOHI2202	CBGA	0.9365
TGOHI2202	CBN	0
TGOHI2202	CBN	0
TGOHI2202	d8-THC	0
TGOHI2202	d8-THC	0
TGOHI2202	d9-THC	0.4853
TGOHI2202	d9-THC	4.853
TGOHI2202	THCA	145.9
TGOHI2202	THCA	14.59
TGOHI2202	Total Cannabinoids ~ %	16.14
TGOHI2202	Total Cannabinoids ~ mg/g	161.4
TGOHI2202	Total CBD ~ %	0
TGOHI2202	Total CBD ~ mg/g	0
TGOHI2202	Total THC ~ %	13.28
TGOHI2202	Total THC ~ mg/g	132.8
TGOHI2202	(6aR,9R)-d10-THC	0
TGOHI2202	(6aR,9R)-d10-THC	0
TGOHI2202	(6aR,9S)-d10-THC	0
TGOHI2202	(6aR,9S)-d10-THC	0
TGOHI2202	CBCA	0
TGOHI2202	CBCA	0
TGOHI2202	CBDVA	0
TGOHI2202	CBDVA	0
TGOHI2202	THCV	0
TGOHI2202	THCV	0
TGOHI2202	THCVA	0
TGOHI2202	THCVA	0
TGOHI2202	(+)-3-Carene	0
TGOHI2202	(+)-3-Carene	0
TGOHI2202	a-Terpinene	0
TGOHI2202	a-Terpinene	0
TGOHI2202	alpha-Bisabolol	0.27
TGOHI2202	alpha-Bisabolol	0.03
TGOHI2202	alpha-Humulene	0.04535
TGOHI2202	alpha-Humulene	0.45
TGOHI2202	alpha-Pinene	0.03
TGOHI2202	alpha-Pinene	0.25
TGOHI2202	beta-Caryophyllene	1.65
TGOHI2202	beta-Caryophyllene	0.17
TGOHI2202	beta-Myrcene	0.2
TGOHI2202	beta-Myrcene	2.01
TGOHI2202	beta-Pinene	0.04
TGOHI2202	beta-Pinene	0.39
TGOHI2202	Camphene	0.07
TGOHI2202	Camphene	0.01
TGOHI2202	Caryophyllene oxide	0
TGOHI2202	Caryophyllene oxide	0
TGOHI2202	Cis-beta-Ocimene	0
TGOHI2202	Cis-beta-Ocimene	0
TGOHI2202	D-Limonene	0.12
TGOHI2202	D-Limonene	1.2
TGOHI2202	Eucalyptol	0
TGOHI2202	Eucalyptol	0
TGOHI2202	gamma-Terpinene	0
TGOHI2202	gamma-Terpinene	0
TGOHI2202	Geraniol	0
TGOHI2202	Geraniol	0
TGOHI2202	Guaiol	0.25
TGOHI2202	Guaiol	0.02
TGOHI2202	Isopulegol	0
TGOHI2202	Isopulegol	0
TGOHI2202	Linalool	1.16
TGOHI2202	Linalool	0.12
TGOHI2202	Nerolidol	0.01
TGOHI2202	Nerolidol	0.13
TGOHI2202	p-Cymene	0
TGOHI2202	p-Cymene	0
TGOHI2202	Terpinolene	0.02
TGOHI2202	Terpinolene	0
TGOHI2202	trans-beta-Ocimene	0
TGOHI2202	Trans-beta-Ocimene	0
TGOHI2202	Total Terpenes	0.79
TGOHI2203	CBC	0
TGOHI2203	CBC	0
TGOHI2203	CBD	0
TGOHI2203	CBD	0
TGOHI2203	CBDA	0
TGOHI2203	CBDA	0
TGOHI2203	CBDV	0
TGOHI2203	CBDV	0
TGOHI2203	CBG	0
TGOHI2203	CBG	0
TGOHI2203	CBGA	0.551
TGOHI2203	CBGA	5.51
TGOHI2203	CBN	0
TGOHI2203	CBN	0
TGOHI2203	d8-THC	0
TGOHI2203	d8-THC	0
TGOHI2203	d9-THC	0.64
TGOHI2203	d9-THC	6.4
TGOHI2203	THCA	180.3
TGOHI2203	THCA	18.03
TGOHI2203	Total Cannabinoids ~ %	19.39
TGOHI2203	Total Cannabinoids ~ mg/g	193.9
TGOHI2203	Total CBD ~ %	0
TGOHI2203	Total CBD ~ mg/g	0
TGOHI2203	Total THC ~ %	16.45
TGOHI2203	Total THC ~ mg/g	164.5
TGOHI2203	(6aR,9R)-d10-THC	0
TGOHI2203	(6aR,9R)-d10-THC	0
TGOHI2203	(6aR,9S)-d10-THC	0
TGOHI2203	(6aR,9S)-d10-THC	0
TGOHI2203	CBCA	0
TGOHI2203	CBCA	0
TGOHI2203	CBDVA	0
TGOHI2203	CBDVA	0
TGOHI2203	THCV	0
TGOHI2203	THCV	0
TGOHI2203	THCVA	1.647
TGOHI2203	THCVA	0.1647
TGOHI2203	(+)-3-Carene	0
TGOHI2203	(+)-3-Carene	0
TGOHI2203	a-Terpinene	0
TGOHI2203	a-Terpinene	0
TGOHI2203	alpha-Bisabolol	0.03
TGOHI2203	alpha-Bisabolol	0.34
TGOHI2203	alpha-Humulene	0.09
TGOHI2203	alpha-Humulene	0.9
TGOHI2203	alpha-Pinene	0.14
TGOHI2203	alpha-Pinene	0.01
TGOHI2203	beta-Caryophyllene	0.27
TGOHI2203	beta-Caryophyllene	2.74
TGOHI2203	beta-Myrcene	0.27
TGOHI2203	beta-Myrcene	2.71
TGOHI2203	beta-Pinene	0.03
TGOHI2203	beta-Pinene	0.27
TGOHI2203	Camphene	0.04
TGOHI2203	Camphene	0
TGOHI2203	Caryophyllene oxide	0
TGOHI2203	Caryophyllene oxide	0
TGOHI2203	Cis-beta-Ocimene	0
TGOHI2203	Cis-beta-Ocimene	0
TGOHI2203	D-Limonene	0.09
TGOHI2203	D-Limonene	0.9
TGOHI2203	Eucalyptol	0
TGOHI2203	Eucalyptol	0
TGOHI2203	gamma-Terpinene	0
TGOHI2203	gamma-Terpinene	0
TGOHI2203	Geraniol	0
TGOHI2203	Geraniol	0
TGOHI2203	Guaiol	0.03
TGOHI2203	Guaiol	0.3
TGOHI2203	Isopulegol	0
TGOHI2203	Isopulegol	0
TGOHI2203	Linalool	0.62
TGOHI2203	Linalool	0.06
TGOHI2203	Nerolidol	0
TGOHI2203	Nerolidol	0
TGOHI2203	p-Cymene	0
TGOHI2203	p-Cymene	0
TGOHI2203	Terpinolene	0
TGOHI2203	Terpinolene	0
TGOHI2203	Trans-beta-Ocimene	0
TGOHI2203	trans-beta-Ocimene	0
TGOHI2203	Total Terpenes	0.9
TGOHI2204	CBC	0
TGOHI2204	CBC	0
TGOHI2204	CBD	0
TGOHI2204	CBD	0
TGOHI2204	CBDA	0
TGOHI2204	CBDA	0
TGOHI2204	CBDV	0
TGOHI2204	CBDV	0
TGOHI2204	CBG	0
TGOHI2204	CBG	0
TGOHI2204	CBGA	2.197
TGOHI2204	CBGA	0.2197
TGOHI2204	CBN	0
TGOHI2204	CBN	0
TGOHI2204	d8-THC	0
TGOHI2204	d8-THC	0
TGOHI2204	d9-THC	1.696
TGOHI2204	d9-THC	16.96
TGOHI2204	THCA	100.9
TGOHI2204	THCA	10.09
TGOHI2204	Total Cannabinoids ~ %	12
TGOHI2204	Total Cannabinoids ~ mg/g	120
TGOHI2204	Total CBD ~ %	0
TGOHI2204	Total CBD ~ mg/g	0
TGOHI2204	Total THC ~ %	10.54
TGOHI2204	Total THC ~ mg/g	105.4
TGOHI2204	(6aR,9R)-d10-THC	0
TGOHI2204	(6aR,9R)-d10-THC	0
TGOHI2204	(6aR,9S)-d10-THC	0
TGOHI2204	(6aR,9S)-d10-THC	0
TGOHI2204	CBCA	0
TGOHI2204	CBCA	0
TGOHI2204	CBDVA	0
TGOHI2204	CBDVA	0
TGOHI2204	THCV	0
TGOHI2204	THCV	0
TGOHI2204	THCVA	0
TGOHI2204	THCVA	0
TGOHI2204	(+)-3-Carene	0
TGOHI2204	(+)-3-Carene	0
TGOHI2204	a-Terpinene	0
TGOHI2204	a-Terpinene	0
TGOHI2204	alpha-Bisabolol	0.22
TGOHI2204	alpha-Bisabolol	0.02
TGOHI2204	alpha-Humulene	0.02942
TGOHI2204	alpha-Humulene	0.29
TGOHI2204	alpha-Pinene	0
TGOHI2204	alpha-Pinene	0
TGOHI2204	beta-Caryophyllene	1.01
TGOHI2204	beta-Caryophyllene	0.1
TGOHI2204	beta-Myrcene	0.04
TGOHI2204	beta-Myrcene	0.39
TGOHI2204	beta-Pinene	0.01
TGOHI2204	beta-Pinene	0.06
TGOHI2204	Camphene	0
TGOHI2204	Camphene	0
TGOHI2204	Caryophyllene oxide	0
TGOHI2204	Caryophyllene oxide	0
TGOHI2204	Cis-beta-Ocimene	0
TGOHI2204	Cis-beta-Ocimene	0
TGOHI2204	D-Limonene	0.21
TGOHI2204	D-Limonene	0.02
TGOHI2204	Eucalyptol	0
TGOHI2204	Eucalyptol	0
TGOHI2204	gamma-Terpinene	0
TGOHI2204	gamma-Terpinene	0
TGOHI2204	Geraniol	0
TGOHI2204	Geraniol	0
TGOHI2204	Guaiol	0.21
TGOHI2204	Guaiol	0.02
TGOHI2204	Isopulegol	0
TGOHI2204	Isopulegol	0
TGOHI2204	Linalool	0
TGOHI2204	Linalool	0
TGOHI2204	Nerolidol	0.14
TGOHI2204	Nerolidol	0.01
TGOHI2204	p-Cymene	0
TGOHI2204	p-Cymene	0
TGOHI2204	Terpinolene	0
TGOHI2204	Terpinolene	0
TGOHI2204	Trans-beta-Ocimene	0
TGOHI2204	trans-beta-Ocimene	0
TGOHI2204	Total Terpenes	0.25
TGOHI2205	CBC	0
TGOHI2205	CBC	0
TGOHI2205	CBD	0
TGOHI2205	CBD	0
TGOHI2205	CBDA	0
TGOHI2205	CBDA	0
TGOHI2205	CBDV	0
TGOHI2205	CBDV	0
TGOHI2205	CBG	0
TGOHI2205	CBG	0
TGOHI2205	CBGA	6.624
TGOHI2205	CBGA	0.6624
TGOHI2205	CBN	0
TGOHI2205	CBN	0
TGOHI2205	d8-THC	0
TGOHI2205	d8-THC	0
TGOHI2205	d9-THC	0.3639
TGOHI2205	d9-THC	3.639
TGOHI2205	THCA	171.7
TGOHI2205	THCA	17.17
TGOHI2205	Total Cannabinoids ~ %	18.32
TGOHI2205	Total Cannabinoids ~ mg/g	183.2
TGOHI2205	Total CBD ~ %	0
TGOHI2205	Total CBD ~ mg/g	0
TGOHI2205	Total THC ~ %	15.42
TGOHI2205	Total THC ~ mg/g	154.2
TGOHI2205	(6aR,9R)-d10-THC	0
TGOHI2205	(6aR,9R)-d10-THC	0
TGOHI2205	(6aR,9S)-d10-THC	0
TGOHI2205	(6aR,9S)-d10-THC	0
TGOHI2205	CBCA	0
TGOHI2205	CBCA	0
TGOHI2205	CBDVA	0
TGOHI2205	CBDVA	0
TGOHI2205	THCV	0
TGOHI2205	THCV	0
TGOHI2205	THCVA	1.275
TGOHI2205	THCVA	0.1275
TGOHI2205	(+)-3-Carene	0
TGOHI2205	(+)-3-Carene	0
TGOHI2205	a-Terpinene	0
TGOHI2205	a-Terpinene	0
TGOHI2205	alpha-Bisabolol	0.24
TGOHI2205	alpha-Bisabolol	0.02
TGOHI2205	alpha-Humulene	0.04338
TGOHI2205	alpha-Humulene	0.43
TGOHI2205	alpha-Pinene	0.22
TGOHI2205	alpha-Pinene	0.02
TGOHI2205	beta-Caryophyllene	0.14
TGOHI2205	beta-Caryophyllene	1.44
TGOHI2205	beta-Myrcene	3.15
TGOHI2205	beta-Myrcene	0.32
TGOHI2205	beta-Pinene	0.04
TGOHI2205	beta-Pinene	0.4
TGOHI2205	Camphene	0.06
TGOHI2205	Camphene	0.01
TGOHI2205	Caryophyllene oxide	0
TGOHI2205	Caryophyllene oxide	0
TGOHI2205	Cis-beta-Ocimene	0
TGOHI2205	Cis-beta-Ocimene	0
TGOHI2205	D-Limonene	0.2
TGOHI2205	D-Limonene	1.97
TGOHI2205	Eucalyptol	0
TGOHI2205	Eucalyptol	0
TGOHI2205	gamma-Terpinene	0
TGOHI2205	gamma-Terpinene	0
TGOHI2205	Geraniol	0
TGOHI2205	Geraniol	0
TGOHI2205	Guaiol	0.03
TGOHI2205	Guaiol	0.28
TGOHI2205	Isopulegol	0
TGOHI2205	Isopulegol	0
TGOHI2205	Linalool	0.1
TGOHI2205	Linalool	1.03
TGOHI2205	Nerolidol	0.21
TGOHI2205	Nerolidol	0.02
TGOHI2205	p-Cymene	0
TGOHI2205	p-Cymene	0
TGOHI2205	Terpinolene	0
TGOHI2205	Terpinolene	0.03
TGOHI2205	Trans-beta-Ocimene	0.11
TGOHI2205	trans-beta-Ocimene	0.01
TGOHI2205	Total Terpenes	0.95

And I want it to look like

TGO_ID	CBG	CBGa	d9-THC	THCVa	THCa	Total Cannabinoids ~ %	Total_THC	alpha-bisabolol	alpha-humulene	alpha-pinene	beta-caryophyllene	beta-myrcene	beta-pinene	d-limonene	guaiol	linalool	nerolidol	camphene	trans-beta-ocimene	total_terpenes
TGOHI2201	0.08157	0.4405	0.9788	0.1212	20.43	22.05	18.89	0.02	0.05149	0.02	0.18	0.55	0.03	0.19	0.02	0.06	0.01	0.01	0	1.14
TGOHI2202	0.1266	0.9365	0.4853	0	14.59	16.14	13.28	0.03	0.04535	0.03	0.17	0.2	0.04	0.12	0.02	0.12	0.01	0.01	0	0.79
TGOHI2203	0	0.551	0.64	0.1647	18.03	19.39	16.45	0.03	0.09	0.01	0.27	0.27	0.03	0.09	0.03	0.06	0	0	0	0.9
TGOHI2204	0	0.2197	1.696	0	10.09	12	10.54	0.02	0.02942	0	0.1	0.04	0.01	0.02	0.02	0	0.01	0	0	0.25
TGOHI2205	0	0.6624	0.3639	0.1275	17.17	18.32	15.42	0.02	0.04338	0.02	0.14	0.32	0.04	0.2	0.03	0.1	0.02	0.01	0.01	0.95

andresrcs · February 24, 2023, 2:31pm

You still have the same problem, there are two rows per ID vs Test Name combination so when you pivot this, it is not clear which value (or combination of the two) to use. I honestly can't think on another way to explain this to you, maybe try to pivot these two lines manually so you can understand what the problem is. This has nothing to do with coding but with the logic of pivoting itself.

Getting error message when running a pivot_wide

A tibble: 6 × 51

… with 38 more variables: d9-THC , THCA , Total Cannabinoids ~ % ,

Total CBD ~ % , Total THC ~ % , (6aR,9R)-d10-THC ,

(6aR,9S)-d10-THC , CBCA , CBDVA , THCV , THCVA ,

(+)-3-Carene , a-Terpinene , alpha-Bisabolol ,

alpha-Humulene , alpha-Pinene , beta-Caryophyllene ,

beta-Myrcene , beta-Pinene , Camphene ,

Caryophyllene oxide , Cis-beta-Ocimene , D-Limonene , …

Use colnames() to see all variable names