Hello everyone,
I have an excel file which has three columns. In the first column there are drug names (with repetition); In the second column there are cell line names in which these drugs are tested on. The same drug names as well cell line names can be found in multiple rows. In the third column I have the AUC value. The thing is that each drug has been tested on different nr of cell lines. I want to format this file so that i have a final matrix in which :
Drug names should be first column (one row to each drug), cell line names should be on the first row and the cells should be filled with AUC values. In those cells where a drug hasn't been tested on that specific cell line I want "NA" values.
Thank you in advance!
df <- data.frame(
'drug'=rep(paste0('drug',seq(5)),5),
'cell'=rep(paste0('cell',seq(5)),each=5)
)
df$auc <- rbinom(25, 100, 0.5)
> head(df)
drug cell auc
1 drug1 cell1 50
2 drug2 cell1 43
3 drug3 cell1 53
4 drug4 cell1 50
5 drug5 cell1 45
6 drug1 cell2 48
library(tidyr)
output <- df %>% spread(cell,auc)
output
drug cell1 cell2 cell3 cell4 cell5
1 drug1 50 48 53 51 47
2 drug2 43 42 55 53 52
3 drug3 53 42 46 46 50
4 drug4 50 54 51 54 53
5 drug5 45 41 46 47 49
If you want to read excel files as input (in my example I just made up some data) - use library(readxl); df <- read_excel('your_file.xls')
Unclear from their post but spread fills unknown cells with NA anyway
spread and gather are pivot_wider and pivot_longer now.
This was very useful. Thank you so much!
This is in part a rant... I don't understand why the "tidyverse" ecosystem has so many functions that seem to do the same thing. I mean, why is there spread, complete, gather, etc...? I don't use tidyr but I work with ggplot2 a lot and I have the same feeling. With data.table I can do pretty much everything dplyr does without learning a number of new functions. For example:
library(data.table)
df <- data.table(
drug=rep(paste0('drug',seq(5)),5),
cell=rep(paste0('cell',seq(5)),each=5)
)
set.seed(1234)
df$auc <- rbinom(25, 100, 0.5)
# Remove some observations
df <- df[auc < 50]
# Crosstabulate and use -1 to for missing values
dcast(data= df, drug ~ cell, value.var= 'auc', fill= -1)
drug cell1 cell2 cell3 cell4 cell5
1: drug1 47 -1 -1 -1 46
2: drug2 40 -1 42 -1 -1
3: drug3 48 -1 -1 48 48
4: drug4 47 -1 47 49 -1
5: drug5 48 47 -1 49 46
spread and gather are now pivot_wider and pivot_longer, which is same as data.table dcast and melt. complete has a different functionality - in data.table it is CJ - see data.table equivalent of tidyr::complete().
So it is 3 functions in tidyverse and 3 functions in data.table.
(Yes, I prefer data.table, too)
you want something like GNU datamash crosstab https://www.gnu.org/software/datamash/manual/html_node/Crosstab.html
Log in to answer this question.
mandatory picture...
Any R suggestions are welcomed as well. I am not necessarily looking to solve this on Excel.
See related StackOverflow post, using R,
tidyr::complete: