How convert data into wider table and calculate Dunnet test between samples with multiple concentration?

Question

[star]

I have data like the one below and am trying to convert it into a data table using pivot_wider() but I don`t want to repeat rows for each column separately.

Actually, I have two questions:

Input:

df <- data.frame(
 wells = c("A", "B", "C", "D", "E", "F", "G", "H", "A", "B", "C", "D", "E", "F", "G", "H"),
 variable = rep(c(1, 2), each = 8),
 value = c(64743, 35197, 18240, 68, 23825, 16701, 11519, 100,
           65928, 34862, 19610, 104, 24293, 18552, 12117, 98),
 Names = c( "TS", "TS", "TS", "TS",
                    "WZ", "WZ", "WZ", "WZ" ),
 Conc = c(10,20,30,40,10,20,30,40,10,20,30,40,10,20,30,40),
 Rep = rep(1, 16)
)

Q 1: I would like "Names" as columns and "Conc" as rows. Also, columns are separated based on the number of times that they are repeated.

Desired output:

      rep       TS_1       TS_2      WZ_1   WZ_2

10     1        64743      65928    23825  24293
20     1        35197      34862     16701   18552
30     1        18240      19610     11519   12117
40     1         68        104       100      98

I used :

df %>%
 tidyr::pivot_wider( names_from = Names, 
       values_from = value)

but rows are repeated for each column and fill value with NA.

Q 2: I would like to know how I run a two-way ANOVA with the Dunnet tes` between "Names" for each "Conc", while "TS" is the control group?

I am using below code for Anova but I am not sure how I can run the Dunnet test.

Anova <- aov(value ~ Name * Conc, df)

Answer 1

[Ignasi]

For Q1 I don't see the column "wells" in the desired output so I removed from the original df:

library(dplyr)
library(tidyr)

df <- data.frame(
  variable = rep(c(1, 2), each = 8),
  value = c(64743, 35197, 18240, 68, 23825, 16701, 11519, 100,
            65928, 34862, 19610, 104, 24293, 18552, 12117, 98),
  Names = c( "TS", "TS", "TS", "TS",
             "WZ", "WZ", "WZ", "WZ" ),
  Conc = c(10,20,30,40,10,20,30,40,10,20,30,40,10,20,30,40))

# You were missing the unite function
df <- df %>%
  unite(new_column, Names, variable, sep = "_") %>%
  pivot_wider(names_from = new_column, values_from = value)

# Adding a duplicated row to test
row_to_duplicate <- df[3, ]
df <- rbind(df, row_to_duplicate)

# add Rep
df <- df %>%
  group_by_all() %>%
  summarise(Rep = n()) %>%
  ungroup()

# Conc as rownames (optionall to get the exact desired output)
df <- as.data.frame(df)
rownames(df) <- df$Conc
df$Conc <- NULL

For Q2 check this blog, you have to make use of DunnettTest() function from the DescTools package.

[Ram]

I've merged your answers and deleted the one addressing just Q2.