Hi, I have a big data frame for omics data. Samples are named as Genotype_Time_Replicate (e.g. AOX_1h_4). Each sample has 4 replicates for each time point.

E.g. data set

    df <- structure(list(AGI = c("ATCG01240", "ATCG01310", "ATMG00070"), aox2_0h__1 = c(15.79105291, 14.82652303, 14.70630068), aox2_0h__2 = c(16.06494674, 14.50610036, 14.52189807), aox2_0h__3 = c(14.64596287, 14.73266459, 13.07143141), aox2_0h__4 = c(15.71713641, 15.15430026, 16.32190068 ), aox2_12h__1 = c(14.99030606, 15.08046949, 15.8317372), aox2_12h__2 = c(15.15569857, 14.98996474, 14.64862254), aox2_12h__3 = c(15.12144791, 14.90111092, 14.59618842), aox2_12h__4 = c(14.25648197, 15.09832061, 14.64442686), aox2_24h__1 = c(15.23997241, 14.80968391, 14.22573239 ), aox2_24h__2 = c(15.57551513, 14.94861669, 15.18808897), aox2_24h__3 = c(15.04928714, 14.83758685, 13.06948037), aox2_24h__4 = c(14.79035385, 14.93873234, 14.70402827), aox5_0h__1 = c(15.8245918, 14.9351844, 14.67678306), aox5_0h__2 = c(15.75108628, 14.85867002, 14.45704948 ), aox5_0h__3 = c(14.36545859, 14.79296855, 14.82177912), aox5_0h__4 = c(14.80626019, 13.43330964, 16.33482718), aox5_12h__1 = c(14.66327372, 15.22571466, 16.17761867), aox5_12h__2 = c(14.58089039, 14.98545497, 14.4331578), aox5_12h__3 = c(14.58091828, 14.86139511, 15.83898617 ), aox5_12h__4 = c(14.48097297, 15.1420725, 13.39369381), aox5_24h__1 = c(15.41855602, 14.9890092, 13.92629626), aox5_24h__2 = c(15.78386057, 15.19372889, 14.63254456), aox5_24h__3 = c(15.55321382, 14.82013321, 15.74324956), aox5_24h__4 = c(14.53085803, 15.12196994, 14.81028556 ), WT_0h__1 = c(14.0535031, 12.45484834, 14.89102226), WT_0h__2 = c(13.64720361, 15.07144643, 14.99836235), WT_0h__3 = c(14.28295759, 13.75283646, 14.98220861), WT_0h__4 = c(14.79637443, 15.1108037, 15.21711524 ), WT_12h__1 = c(15.05711898, 13.33689777, 14.81064042), WT_12h__2 = c(14.83846779, 13.62497318, 14.76356308), WT_12h__3 = c(14.77215863, 14.72814995, 13.0835214), WT_12h__4 = c(14.70685445, 14.98527337, 16.12727292), WT_24h__1 = c(15.43813077, 14.56918572, 14.92146565 ), WT_24h__2 = c(16.05986898, 14.70583866, 15.64566505), WT_24h__3 = c(14.87721853, 13.22461859, 16.34119942), WT_24h__4 = c(14.92822133, 14.74382383, 12.79146694)), class = "data.frame", row.names = c(NA, -3L))

Please bear with me. **I have to** summarize the data ***for each time point;*** Mean, SE and do a multiple comparison (t-test; i.e. WT-aox2, WT-aox5, aox2-aox5). Then create a table as below figure. 

<a href="https://ibb.co/RNmWk91"><img src="https://i.ibb.co/RNmWk91/Picture1.jpg" alt="Picture1" border="0"></a>

My real df has more genotypes and time points, so difficult to work in Excel.

How can I do this in R? Could anyone help me with this?



I showed few tactics to simplify and visualise data in R using `tidyverse` You can explore more by taking this as start point. 

    library(tidyverse)
    ss <- df %>% 
    as_tibble() %>% 
    gather(key = "cond" , value = "value" , -AGI) %>%  ## wide to long format
    separate(cond , into = c("Genotype", "Time", "Replicate") , sep = "_+") ## separate each attribute 
    
    
    # A tibble: 108 x 5
       AGI       Genotype Time  Replicate value
       <chr>     <chr>    <chr> <chr>     <dbl>
     1 ATCG01240 aox2     0h    1          15.8
     2 ATCG01310 aox2     0h    1          14.8
     3 ATMG00070 aox2     0h    1          14.7
     4 ATCG01240 aox2     0h    2          16.1
     5 ATCG01310 aox2     0h    2          14.5
     6 ATMG00070 aox2     0h    2          14.5
     7 ATCG01240 aox2     0h    3          14.6
     8 ATCG01310 aox2     0h    3          14.7
     9 ATMG00070 aox2     0h    3          13.1
    10 ATCG01240 aox2     0h    4          15.7
    # … with 98 more rows


    ## average out replicates 
    ss_m <- ss %>% group_by(AGI, Genotype , Time) %>% summarise(replicates_mean = mean(value))
    
    ss_m
    # A tibble: 27 x 4
    # Groups:   AGI, Genotype [?]
       AGI       Genotype Time  replicates_mean
       <chr>     <chr>    <chr>           <dbl>
     1 ATCG01240 aox2     0h               15.6
     2 ATCG01240 aox2     12h              14.9
     3 ATCG01240 aox2     24h              15.2
     4 ATCG01240 aox5     0h               15.2
     5 ATCG01240 aox5     12h              14.6
     6 ATCG01240 aox5     24h              15.3
     7 ATCG01240 WT       0h               14.2
     8 ATCG01240 WT       12h              14.8
     9 ATCG01240 WT       24h              15.3
    10 ATCG01310 aox2     0h               14.8
    # … with 17 more rows


 
## comparing timepoint 
    bplot <- ss_m %>% ggplot() + geom_boxplot(aes(x = Time, y = replicates_mean , fill = Genotype)) +  theme_bw() + theme(text = element_text(size = 20))
    ggsave(filename = "boxplot.png" ,plot = bplot)
  
<a href="https://ibb.co/QXYz5JZ"><img src="https://i.ibb.co/Fn8NPh9/boxplot.png" alt="boxplot" border="0"></a><br /><a target='_blank' href='https://ast'> </a><br />

## comparing genotype 
    bplot2 <- ss_m %>% ggplot() + geom_boxplot(aes(x = Genotype, y = replicates_mean , fill = Time)) +  theme_bw() + theme(text = element_text(size = 20))
    ggsave(filename = "boxplot2.png" ,plot = bplot2)

<a href="https://ibb.co/KLPBspY"><img src="https://i.ibb.co/jWNqwPd/boxplot2.png" alt="boxplot2" border="0"></a><br /><a target='_blank' href='https://ast'> </a><br />

======================Update ===============================
##Convert the data in to the format you asked. ( std dev and mean only)


    ss_mm <- ss %>% group_by(AGI, Genotype , Time) %>% 
            summarise(replicates_mean = mean(value) , stddev = sd(value)) %>% ## add stddev and mean 
            unite(Genotype, Time , col = "Genotype_Time" , sep = "_") %>% ## unite genotype and time in a single column
            gather(key = summary_type , value = value , replicates_mean , stddev) %>% ## create summary_type variable 
            unite(Genotype_Time, summary_type , col = "Genotype_Time_summary_type",sep = "_") %>% ##create Genotype_Time_summary_type variable
            spread(Genotype_Time_summary_type , value) ## wide format 

    ## summary of final table. 
    glimpse(ss_mm)
    
    Observations: 3
    Variables: 19
    Groups: AGI [3]
    $ AGI                      <chr> "ATCG01240", "ATCG01310", "ATMG00070"
    $ aox2_0h_replicates_mean  <dbl> 15.55477, 14.80490, 14.65538
    $ aox2_0h_stddev           <dbl> 0.6240735, 0.2689779, 1.3299868
    $ aox2_12h_replicates_mean <dbl> 14.88098, 15.01747, 14.93024
    $ aox2_12h_stddev          <dbl> 0.42239203, 0.09092439, 0.60146632
    $ aox2_24h_replicates_mean <dbl> 15.16378, 14.88365, 14.29683
    $ aox2_24h_stddev          <dbl> 0.33059885, 0.07035039, 0.90767009
    $ aox5_0h_replicates_mean  <dbl> 15.18685, 14.50503, 15.07261
    $ aox5_0h_stddev           <dbl> 0.7175443, 0.7168420, 0.8547323
    $ aox5_12h_replicates_mean <dbl> 14.57651, 15.05366, 14.96086
    $ aox5_12h_stddev          <dbl> 0.07459644, 0.16231378, 1.28919700
    $ aox5_24h_replicates_mean <dbl> 15.32162, 15.03121, 14.77809
    $ aox5_24h_stddev          <dbl> 0.5483318, 0.1643006, 0.7481768
    $ WT_0h_replicates_mean    <dbl> 14.19501, 14.09748, 15.02218
    $ WT_0h_stddev             <dbl> 0.4794059, 1.2639163, 0.1382836
    $ WT_12h_replicates_mean   <dbl> 14.84365, 14.16882, 14.69625
    $ WT_12h_stddev            <dbl> 0.1521183, 0.8097963, 1.2471750
    $ WT_24h_replicates_mean   <dbl> 15.32586, 14.31087, 14.92495
    $ WT_24h_stddev            <dbl> 0.5509899, 0.7280381, 1.5358987

