I want to gather data from experiments from data frame into columns. Data is in the following form;
and I want to arrange data in the format given below;
Is there any simple method available to do it in R/RStudio? I tried tidyr, rbind and cbind as suggested in different examples. But I am unable to do ad found that these are not much relevant. It will be great if someone help me to understand.
Thanks
Using data.table function melt with patterns in measure.vars, you can try the following:
library(data.table)
#Some data in the same format as you have
df <- read.table(text="
1 0.8525099 0.5598105 0.4242143 0 0.06016425 0.678719492 0.4852765 0.4970301 0.1657070
2 0.1237982 0.2853534 0.8281460 0.42586728 0.31214568 0.647306659 0.5445816 0.4250520 0.9975251
3 0.4907858 0.4925835 0.6689135 0.06042183 0.47391134 0.002571686 0.5267215 0.4291427 NA
4 0.8524778 0.1091856 0.6529887 0.24606198 0.44869099 0.540201766 0.6263992 0.1448730 NA
")
#assigning names to columns
colnames(df) <- c("Run", rep(c("Logging", "Salinity","surface"),3))
setDT(df) #converting df into a data.table
df #Similar as your initial data frame
Run Logging Salinity surface Logging Salinity surface Logging Salinity surface
1: 1 0.8525099 0.5598105 0.4242143 0.00000000 0.06016425 0.678719492 0.4852765 0.4970301 0.1657070
2: 2 0.1237982 0.2853534 0.8281460 0.42586728 0.31214568 0.647306659 0.5445816 0.4250520 0.9975251
3: 3 0.4907858 0.4925835 0.6689135 0.06042183 0.47391134 0.002571686 0.5267215 0.4291427 NA
4: 4 0.8524778 0.1091856 0.6529887 0.24606198 0.44869099 0.540201766 0.6263992 0.1448730 NA
df2 <- melt(df, #melting data, converting from wide to long
id.vars = 1, # here we attempt to fix the first column "Runs"
measure.vars = patterns(Logging="Logging", # here we look up for a pattern of column names to convert into measure
Salinity= "Salinity",
surface="surface")
)
#Output
df2
Run variable Logging Salinity surface
1: 1 1 0.85250990 0.55981050 0.424214300
2: 2 1 0.12379820 0.28535340 0.828146000
3: 3 1 0.49078580 0.49258350 0.668913500
4: 4 1 0.85247780 0.10918560 0.652988700
5: 1 2 0.00000000 0.06016425 0.678719492
6: 2 2 0.42586728 0.31214568 0.647306659
7: 3 2 0.06042183 0.47391134 0.002571686
8: 4 2 0.24606198 0.44869099 0.540201766
9: 1 3 0.48527650 0.49703010 0.165707000
10: 2 3 0.54458160 0.42505200 0.997525100
11: 3 3 0.52672150 0.42914270 NA
12: 4 3 0.62639920 0.14487300 NA
And finally remove column variable
#Removing column variable (second column in df2) you get your result
df2[, -2]
Run Logging Salinity surface
1: 1 0.85250990 0.55981050 0.424214300
2: 2 0.12379820 0.28535340 0.828146000
3: 3 0.49078580 0.49258350 0.668913500
4: 4 0.85247780 0.10918560 0.652988700
5: 1 0.00000000 0.06016425 0.678719492
6: 2 0.42586728 0.31214568 0.647306659
7: 3 0.06042183 0.47391134 0.002571686
8: 4 0.24606198 0.44869099 0.540201766
9: 1 0.48527650 0.49703010 0.165707000
10: 2 0.54458160 0.42505200 0.997525100
11: 3 0.52672150 0.42914270 NA
12: 4 0.62639920 0.14487300 NA
You can use tidyr::pivot_longer. Using @Chriss Paul's data.
tidyr::pivot_longer(df, cols = -Run, names_to = '.value')
# Run Logging Salinity surface
# <int> <dbl> <dbl> <dbl>
# 1 1 0.853 0.560 0.424
# 2 1 0 0.0602 0.679
# 3 1 0.485 0.497 0.166
# 4 2 0.124 0.285 0.828
# 5 2 0.426 0.312 0.647
# 6 2 0.545 0.425 0.998
# 7 3 0.491 0.493 0.669
# 8 3 0.0604 0.474 0.00257
# 9 3 0.527 0.429 NA
#10 4 0.852 0.109 0.653
#11 4 0.246 0.449 0.540
#12 4 0.626 0.145 NA
PS - It is not advised to have data with duplicate column names.
I created a similar data set and applied the following code based on binding since you mentioned it in your question, it may sound verbose but it gets you to the desired output:
library(dplyr)
df <- tibble(
runs = c(1, 2, 3, 4),
col1 = c(3, 4, 5, 5),
col2 = c(5, 3, 1, 4),
col3 = c(6, 4, 9, 2),
col1 = c(0, 2, 2, 1),
col2 = c(2, 3, 1, 7),
col3 = c(2, 4, 9, 9),
col1 = c(3, 4, 5, 7),
col2 = c(3, 3, 1, 4),
col3 = c(3, 2, NA, NA), .name_repair = "minimal")
df %>%
select(2:4) %>%
bind_rows(df %>%
select(5:7)) %>%
bind_rows(df %>%
select(8:10)) %>%
select(run, col1:col3)
Ok there are two other ways I thought you might be interested to know, since it was your question. These are not my codes completely and I got help for that but there are great alternative ways of dealing with the same problem:
df %>%
pivot_longer(cols = starts_with("col"), names_to = c(".value")) %>% # Pay attention to the `.value` sentinel it indicates that component of the name defines the name of the column containing the cell values, overriding values_to.
group_by(runs) %>%
mutate(id = row_number()) %>%
ungroup() %>%
arrange(id) %>%
select(-id)
# A tibble: 12 x 4
runs col1 col2 col3
<dbl> <dbl> <dbl> <dbl>
1 1 3 5 6
2 2 4 3 4
3 3 5 1 9
4 4 5 4 2
5 1 0 2 2
6 2 2 3 4
7 3 2 1 9
8 4 1 7 9
9 1 3 3 3
10 2 4 3 2
11 3 5 1 NA
12 4 7 4 NA
The above code is proposed by @ Ashley G which was amazing. And the base R alternative:
data.frame(df$runs,
sapply(split.default(df[-1], names(df)[-1]), unlist),
row.names = NULL)
df.runs col1 col2 col3 # Here `split.default` splits the columns of data frame whereas `split` splits the rows.
1 1 3 5 6
2 2 4 3 4
3 3 5 1 9
4 4 5 4 2
5 1 0 2 2
6 2 2 3 4
7 3 2 1 9
8 4 1 7 9
9 1 3 3 3
10 2 4 3 2
11 3 5 1 NA
12 4 7 4 NA
The base R code is written by @Ronak Shah, for which I'm very grateful.