CODEWITHSUNDEEP
rexcel
biomiha
biomiha

Reputation: 1422

R read in excel file with merged cells as column headers

In biological assays we often have replicate measurements from the same molecule and then plot a dose response curve using the average of 2 or 3 replicates. I would like to read into R an excel file, where the column headers of replicates have been merged - text example below, and link to example file. The read_excel function of the readxl package can read the file in but unmerges the header cells and replaces the empty cells with NAs. 

conc          |   Sample1    |  Sample2  
-------------------------------------------
10            |  1.5 |  2.5  |  3   |  4
------------------------------------------- 
100           |  15  |  25   |  30  |  40
-------------------------------------------
1000          |  150 |  250  |  300 |  400

Is there a way of either preserving the merged cell layout in R or alternatively reading in the columns and automatically replicating/renumbering the headers like below?

conc          | Sample1.1 | Sample1.2 | Sample2.1 | Sample2.2  
--------------------------------------------------------------
10            |    1.5    |    2.5    |     3     |     4 
-------------------------------------------------------------- 
100           |    15     |    25     |     30    |     40 
--------------------------------------------------------------
1000          |    150    |    250    |     300   |     400

Thanks.

Upvotes: 0

Views: 2119

Answers (1)

rosscova
rosscova

Reputation: 5600

Not a complete answer, but it is possible to have a list column, such that multiple values are contained within a single cell. This might serve the same function as the "merged columns" in Excel. Here's an example, just to show what I mean:

library(data.table)
new <- data.table("V1" = c(1,2), "V2" = list(c(1,2,5),c(2,3)) )

Notice that column V2 has 2 vectors within a list (each vector is even a unique length, and each one can be as long or short as you need). Now you can call all the values for a given cell:

> new$V2[[1]]
[1] 1 2 5

Or a specific replication:

> new$V2[[2]][2]
[1] 3

I don't know exactly what your spreadsheet looks like, and getting it from its current form into a "list column" form may be difficult depending on that. Hopefully this gives you some ideas though!

Upvotes: 1

Related Questions