How to aggregate specific row ranges into a new data frame?

user_10857924 0
Viewed 55

am trying to write a bit of code to look at three imported csv2 tables; each table has a column titled 'Year'. The code will look at the Year in each and calculate the compatible year 'range' accross all table. Please see below:

table_a <- Football
min_a <- min(Football$Year)
max_a <- max(Football$Year)


table_b <- UK_Population
min_b <- min(UK_Population$Year)
max_b <- max(UK_Population$Year)


table_c <- filter(UK_House_Prices, Quarter == 'Q4')
min_c <- min(UK_House_Prices$Year)
max_c <- max(UK_House_Prices$Year)

min_high <- max(min_a,min_b,min_c)
max_low <- min(max_a,max_b,max_c)

which(with(table_a, Year == min_high))
which(with(table_b, Year == min_high))
which(with(table_c, Year == min_high))

which(with(table_a, Year == max_low))
which(with(table_b, Year == max_low))
which(with(table_c, Year == max_low))

Once I assign the which function (currently unassigned) I will have the start and end row for each table I want to use to bring that row 'range' into a data frame.

So I would like to create a data frame that combines the relevant row range from each table (lets says each table has a column called 'xyz' to import into the new table (so the new table has four columns 'Year' and the 'xyz_[1:3]' table from each of the three).

I am a bit puzzled about how to do this, should I be using a loop to create the aggregate data frame? Or is the a more sensible way to do it? Any guidance would be very much appreciated.

dataframe r
1 Answers

We may place the datasets in a list and apply the code once in the list

# place the datasets in a list
lst1 <- list(Football, UK_Population, filter(UK_House_Prices, Quarter == 'Q4')) 
# loop over the list, get the range in a matrix
m1 <- sapply(lst1, \(x) range(x$Year, na.rm = TRUE))
# find the max of the mins from the first column
min_high <- max(m1[,1], na.rm = TRUE)
# find the min of the maxs from the second column
max_low <- min(m1[,2], na.rm = TRUE)
# loop over the list, get the index from each of the list elements
lapply(lst1, \(x) which(with(x, Year == min_high)))
lapply(lst1, \(x) which(with(x, Year == max_low)))
user_37322710
Related