Extracting a table from a spreadsheet given a certain condition

user_9829398 0
Viewed 72

I've hundreds of Excel (.xls) files and each one of them has (at some random line) a "table", that has three columns each (Station, Parameter and Unit). I want to write a script that reads all the files and when it reads these three cells (Station, Parameter, Unit) together in one line, it starts copying each line below until it reaches the end of the table. At the end of each table that always is three NA's. But the end of the table isn't the end of the file, that are more data below, but I don't want that.

My biggest problem is that since I don't know in each line the table will be, I have to tell R to read all lines and detect it, which I'm not being able to do.

Below I made visualization of a sample table, and also wrote the code so you could reproduce it in R.

|NA |NA |NA | | Station | Parameter | Unit | |EST1 | P1 |M | |NA | P2 |KM | |NA | P3 |MM | |EST2 | P1 |M | |NA | P2 |KM | |NA | P3 |MM | |EST3 | P1 |M | |NA | P2 |KM | |NA | P3 |MM | |NA |NA |NA |

code to reproduce the table:

a= c(NA, 'Station', 'EST1', NA, NA, "EST2", NA, NA, "EST3", NA, NA, NA)

b= c(NA, 'Parameter', 'P1', 'P2', 'P3', 'P1', 'P2', 'P3','P1','P2','P3', NA)

c= c(NA, 'Unit', 'M', 'KM', 'MM','M', 'KM', 'MM','M', 'KM', 'MM', NA)

data =(data.frame(a,b,c))
r
1 Answers

Perhaps this will do the trick, assuming the Parameter column has no NAs. 

library(magrittr)

# find start and ending row of the table
starting_row <- row(data)[which(data$a == "Station"), ][1]
ending_row <- row(data)[which(is.na(data$b)), ][,1] %>% max() -1


# slice the data
table1 <- data[ starting_row:ending_row, ]

table1
>         a         b    c
> 2  Station Parameter Unit
> 3     EST1        P1    M
> 4     <NA>        P2   KM
> 5     <NA>        P3   MM
> 6     EST2        P1    M
> 7     <NA>        P2   KM
> 8     <NA>        P3   MM
> 9     EST3        P1    M
> 10    <NA>        P2   KM
> 11    <NA>        P3   MM
user_35888760
Related