rreadxl
How to read rectangle surrounded by whitespace using readxl
I have an excel sheet with the following data which I am trying to read into r:
I would like to read in the table at A3:C6. However, the dimensions of this data may change. I want to define it as the data starting at A3 in the top left, stopping at whitespace to the right, and whitespace at the bottom.
I am currently using read_xlsx(skip = 2) but this also reads in row 8, which I do not want.
Solution
The following code seems to do what the question asks for. It's based on the fact that readxl::read_xlsx returns NA's for empty cells.
df1 <- readxl::read_xlsx(xl_filename, skip = 2)
i_col <- which(sapply(df1, function(x) all(is.na(x))))
i_col <- i_col[which(i_col == min(i_col))]
i_row <- which(apply(df1, 1, function(x) all(is.na(x))))
i_row <- i_row[which(i_row == min(i_row))]
df1[seq_len(i_row)[-i_row], seq_len(i_col)[-i_col]]
- Is there a way to pass a string as a variable/column name to my function and use in a call to mutate?
- Connecting R Leaflet to esri vector tile service
- Add a secundary reverse x-axis ggplot2
- how sort in reverse order xts object
- ifelse() stripping POSIXct attribute from vector of timestamps?
- How to mutate a year quarter str into date?
- Change text of full_screen button ("Expand") in bslib card
- Returning a matrix after removing duplicate combinations of elements
- How can I get fine control over the position of a ggplot legend, now that a numeric 'legend.position' argument is deprecated?
- Adding percentage instead of observed counts on y axis on R figure
- patchwork - inset_element(): size and position
- R optimize function not reaching optimal value
- Why is E(g) not identical to itself
- control x position using ggrepel
- YAML parser error when rendering Quarto document
- Filter list of lists with purrr::keep
- How can I deal with the Lavaan error: "syntax error in lavaan model syntax"?
- Strange behavior of pickerInput, how to resolve it?
- R diagrammeR using html for formatting while reading text from r variable
- How to get a warning on "shiny app will not work if the same output is used twice"
- Condition facetting of ggplot2 plot
- Can we retrieve entries from a tibble using index matrix?
- Adding conditions to loop to generate class intervals using ClassInt in R for areas with fewer n classes
- ggsurvplot - reposition strata color indicators of risk table
- How to rearrange value boxes at a certain screen size?
- Remove last word from string
- Are doubly-linked structures allowed?
- Mermaid diagram line break
- Using gsub to extract only capital letters of a certain length
- How do I style a shinyWidgets::multiInput()