CODEWITHSUNDEEP
rexceldataframetidyrtibble
Daniel Aviles
Daniel Aviles

Reputation: 73

From tibble to txt or excel file in R

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:

TableinExcel

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.

Upvotes: 7

Views: 17392

Answers (2)

r2evans
r2evans

Reputation: 160417

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.

Upvotes: 5

Dave2e
Dave2e

Reputation: 24079

This is just a matter of converting your data from the wide format into a long format and then into the desired wide format again. There are a couple of possible solutions, in this case I used gather and spread from the tidyr package.

df<-structure(list(Treatment = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 
        1L, 1L, 1L, 1L), .Label = "I Control ", class = "factor"), Pressure = c(0, 
        0.689, 1.38, 2.07, 2.76, 3.45, 4.14, 4.83, 5.52, 6.21), mean = c(97.2, 
        94.1, 90.9, 89.5, 88.8, 87.6, 86.9, 83.9, 83.1, 81.9), std = c(1.03, 
        1.35, 2.01, 2.2, 2.45, 2.88, 3.22, 5.53, 5.55, 6.24)), .Names = c("Treatment", 
        "Pressure", "mean", "std"), class = "data.frame", row.names = c(NA, -10L))


library(tidyr)
long<-gather(df, variable, value, 3:4)
answer<-spread(long, Pressure, value)

write.csv(answer, "Answer.csv")

Of course if the pressure is different from treatment to treatment the final dataframe will be quite messy.

Upvotes: 5

Related Questions