From tibble to txt or excel file in R

Question

Hi all: I am working with tibbles to summarise my data and now I have a problem. I need to send the data I have in a tibble to a partner in an excel or csv file format. The thing is that it requires the csv (or excel) file to be in a specific arrangement (no tidy data) so I was wondering if you can help me a little bit to, at least, have my tibble into a csv file in a way that would be easy to edit it in excel itself.

The tibble looks like this:

# A tibble: 1,024 x 4
# Groups:   Treatment [16]
   Treatment    Pressure  mean   std
   <chr>           <dbl> <dbl> <dbl>
 1 "I Control "    0.     97.2  1.03
 2 "I Control "    0.689  94.1  1.35
 3 "I Control "    1.38   90.9  2.01
 4 "I Control "    2.07   89.5  2.20
 5 "I Control "    2.76   88.8  2.45
 6 "I Control "    3.45   87.6  2.88
 7 "I Control "    4.14   86.9  3.22
 8 "I Control "    4.83   83.9  5.53
 9 "I Control "    5.52   83.1  5.55
10 "I Control "    6.21   81.9  6.24

I have 16 different values for the variable "Treatment". I would like to have a csv file that looks like this:

As you can see from the picture, each value from the variable "Treatment" has 2 rows, one containing the values of the variable "mean" and the other containing the values from the variable "std". In the csv table each column would represent each different value from the variable "Pressure".

Any idea or suggestion? Thanks in advance for the time.

Answer 1

Your data wasn't easily copied and under-represented the variability of the treatments, so I made some random data:

set.seed(2)
dat <- data_frame(
  Treatment = rep(letters[1:2], each=4),
  Pressure = sample(100, size=8),
  mean = sample(100, size=8),
  std = sample(100, size=8)
)
library(dplyr)
library(tidyr)

Here's a way:

dat %>%
  gather(k, v, -Treatment) %>%
  group_by(Treatment, k) %>%
  nest() %>%
  mutate(data = map(data, ~ as.data.frame(t(.$v)))) %>%
  unnest()
# # A tibble: 6 x 6
#   Treatment k           V1    V2    V3    V4
#   <chr>     <chr>    <int> <int> <int> <int>
# 1 a         Pressure    19    70    57    17
# 2 b         Pressure    91    90    13    78
# 3 a         mean        47    55    99    24
# 4 b         mean        74    18    39    80
# 5 a         std         98    23    44     8
# 6 b         std         64    37    79    14

This works well even if your treatments are not well-balanced:

dat <- dat[-8,]
dat %>%
  gather(k, v, -Treatment) %>%
  group_by(Treatment, k) %>%
  nest() %>%
  mutate(data = map(data, ~ as.data.frame(t(.$v)))) %>%
  unnest()
# # A tibble: 6 x 6
#   Treatment k           V1    V2    V3    V4
#   <chr>     <chr>    <int> <int> <int> <int>
# 1 a         Pressure    19    70    57    17
# 2 b         Pressure    91    90    13    NA
# 3 a         mean        47    55    99    24
# 4 b         mean        74    18    39    NA
# 5 a         std         98    23    44     8
# 6 b         std         64    37    79    NA

From here, just append %>% write.csv(path, na="") or %>% readr::write_csv(path, na=""), so that the empty cells are not filled in excel.