Data Manipulation
3
0
Entering edit mode
3 days ago
science03 ▴ 20

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.

Excel Analysis R Function Data • 423 views
2
Entering edit mode

mandatory picture...

0
Entering edit mode

Any R suggestions are welcomed as well. I am not necessarily looking to solve this on Excel.

0
Entering edit mode

See related StackOverflow post, using R, tidyr::complete:

2
Entering edit mode
3 days ago
benformatics ★ 2.6k
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  ADD COMMENT 0 Entering edit mode 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') ADD REPLY 0 Entering edit mode I think they want complete. ADD REPLY 0 Entering edit mode Unclear from their post but spread fills unknown cells with NA anyway ADD REPLY 0 Entering edit mode spread and gather are pivot_wider and pivot_longer now. ADD REPLY 0 Entering edit mode This was very useful. Thank you so much! ADD REPLY 2 Entering edit mode 2 days ago 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

1
Entering edit mode

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)

0
Entering edit mode

Thanks for the comment - I don't doubt there is a strong reasoning behind tidyverse since otherwise it wouldn't be so popular - it's probably just me. I think this post summarises my feelings pretty well even if I don't agree with everything in there.

0
Entering edit mode
3 days ago

you want something like GNU datamash crosstab https://www.gnu.org/software/datamash/manual/html_node/Crosstab.html