The pivot_wider() produces more than one row for each observation. Problem with duplication?

Question

I am not able to get my data frame to pivot_wider into one row per recordset (Forward Template and Reverse Template values from prim).

The code below creates a separate column for each value of prim.

df <- 

tibble(

  "prim" = c("601881  ....................  601900", "601973  ......................  601952", "331595  ....................  331614", "331687  ......................  331666", "196557  ....................  196576", "196649  ......................  196628", "153933  ....................  153952", "154025  ......................  154004", "2100939  ....................  2100920", "2100847  ......................  2100868"), 

  "Accession" = c("CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1", "CP042983.1"), 

  "Genus" = c("Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus", "Histophilus"), 

  "Species" = c("somni", "somni", "somni", "somni", "somni", "somni", "somni", "somni", "somni", "somni"), 

  "Metric" = c("Forward Template", "Reverse Template", "Forward Template", "Reverse Template", "Forward Template", "Reverse Template", "Forward Template", "Reverse Template", "Forward Template", "Reverse Template")

)

tmp <- 

  df %>% 

  group_by(Accession) %>%

  mutate(AccessID = row_number()) %>%

  pivot_wider(names_from = Metric, values_from = prim)

Can I generate a data frame where both the Reverse Template and Forward Template columns are populated, and only one row per record is created?

Answer 1

You can simply group the data by the Metric, refer to the below code: 

library(dplyr)

df %>%

  group_by(Metric) %>%

  mutate(row = row_number()) %>%

  tidyr::pivot_wider(names_from = Metric, values_from = prim) %>%

  select(-row)

# A tibble: 5 x 5

#  Accession  Genus   Species `Forward Template`    `Reverse Template`   

#  <chr>      <chr>   <chr>   <chr>                 <chr>                

#1 CP042983.1 Histop… somni   601881  ............… 601973  ............…

#2 CP042983.1 Histop… somni   331595  ............… 331687  ............…

#3 CP042983.1 Histop… somni   196557  ............… 196649  ............…

#4 CP042983.1 Histop… somni   153933  ............… 154025  ............…

#5 CP042983.1 Histop… somni   2100939  ...........… 2100847  ...........…

If you are a beginner and want to know more about R, then do refer to the R programming tutorial.