This is a long answer, and somewhat clunky, but it works.
This post Using R to read out excel-colorinfo shows how to pull the color information as a vector, but we want to align this vector with our data frame and then create an index to filter our cells by.
First read in DataSet as a dataframe (which we will work with later) and the same workbook wb with xlsx::loadWorkbook which will allow us to pull color information (following the above link).
library(openxlsx)
library(xlsx)
library(tidyr)
DataSet<-openxlsx::read.xlsx("SpeciesByYear_Colored.xlsx")
wb<-xlsx::getSheets(xlsx::loadWorkbook("SpeciesByYear_Colored.xlsx"))[[1]]
Then from the above SO link, we pull the color information out as a vector:
rows<-getRows(wb)
cells<-getCells(rows)
styles <- sapply(cells, getCellStyle)
cellColor <- function(style)
{
fg <- style$getFillForegroundXSSFColor()
rgb <- tryCatch(fg$getRgb(), error = function(e) NULL)
rgb <- paste(rgb, collapse = "")
return(rgb)
}
colors<-sapply(styles, cellColor)
Now let's look at the vector
> head(colors,n=100)
1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8
"" "" "" "" "" "" "" ""
1.9 1.10 1.11 1.12 1.13 1.14 1.15 1.16
"" "" "" "" "" "" "" ""
1.17 1.18 1.19 1.20 1.21 1.22 1.23 1.24
"" "" "" "" "" "" "" ""
...
2.32 2.33 2.34 2.35 2.36 2.37 2.38 2.39
"" "" "" "" "" "" "" ""
2.40 2.41 2.42 2.43 2.44 2.45 2.46 2.47
"" "" "" "" "70ad47" "70ad47" "" "70ad47"
2.48 2.49 3.1 3.2
"70ad47" "70ad47" "" ""
The first number in this named vector is the row number and the second (after the .) is the column number. We need to pull this information out. Within quotes is the color code.
So we turn this into a dataframe and add row and column information with regular expressions (specifically backreferencing):
dat<-as.data.frame(colors)
dat$rows<-as.numeric(gsub(pattern = "([0-9]+).([0-9]+)",replacement ="\\1", row.names(dat)))
dat$cols<-as.numeric(gsub(pattern = "([0-9]+).([0-9]+)",replacement ="\\2", row.names(dat)))
> head(dat)
colors rows cols
1.1 1 1
1.2 1 2
1.3 1 3
1.4 1 4
1.5 1 5
1.6 1 6
Now we can check that the dimensions make sense:
> dim(dat)
[1] 3332 3
There are 3332 color values which almost corresponds to our DataSet dimensions:
> dim(DataSet)
[1] 67 49
> 67*49
[1] 3283
Why doesn't this match? The color values include the header row (Years), while in DataSet, they are 'outside' the dataframe and contained in column names, so we need to add a row to our calculation to get the values to match:
> (67+1)*49
[1] 3332
This is important to keep in mind as we will see shortly.
Here I only have two colors "70ad47" == green and "" == no fill
So I will create a blanks object that will include cells to be removed.
blanks<-dat[dat$colors!="70ad47",] ## OR: blanks<-dat[dat$colors=="",]
I then remove the first column, which is a valuable index (col 1) == Species. Then same for the first row, which includes the headers (Year). We don't want to remove these, which is why we remove them here from the removal list (a little convoluted).
blanks<-blanks[which(blanks$cols!=1),]
blanks<-blanks[which(blanks$rows!=1),]
Now here is the trick, we now have to move the index down one because DataSet starts with row 1 of data as row = 1, whereas in the other dataset called dat, the header row was row 1 and the first row of data is row 2.
blanks$rows<-blanks$rows-1
Now we turn every observation in DataSet with no fill color to having a value of -1 (or any other useful value that we can later filter by)
for(i in 1:dim(blanks)[1]){
DataSet[blanks$rows[i],blanks$cols[i]]<- -1
}
We can then turn to long format to easily remove cells we aren't interested in
DataSet.Long<-DataSet %>% pivot_longer(cols=`1967`:`2019`,names_to = "Year")
> DataSet.Long
# A tibble: 3,216 x 3
Species Year value
<chr> <chr> <dbl>
1 Albacore 1967 -1
2 Albacore 1972 -1
3 Albacore 1973 -1
4 Albacore 1974 -1
5 Albacore 1975 -1
6 Albacore 1977 -1
7 Albacore 1978 -1
8 Albacore 1979 -1
9 Albacore 1980 -1
10 Albacore 1981 -1
# ... with 3,206 more rows
Now only keep values above -1
DataSet.Truncated<-DataSet.Long[DataSet.Long$value>-1,]
> DataSet.Truncated
# A tibble: 134 x 3
Species Year value
<chr> <chr> <dbl>
1 Albacore 2014 38
2 Albacore 2015 30
3 Albacore 2017 24
4 Albacore 2018 49
5 Albacore 2019 18
6 Blue Shark 1999 1
7 Blue Shark 2000 9
8 Blue Shark 2002 18
9 Blue Shark 2003 2
10 Blue Shark 2006 11
# ... with 124 more rows
