Finding a row from a dataframe

user_19717532 0
Viewed 49

I am trying to write a function get_value <- function(gyear, gmonth) that selects a row from already existing dataframe or a value if the month is between two time points. The dataframe is e.g.

df <- read.table(header = TRUE, text = "
year    month   var1    var2
2022    1       123     987
2021    4       234     876
2021    1       345     765
2020    7       456     654 
2020    3       567     543
2020    1       678     432
2019    1       789     321")

For example in year 2021 the same row

year    month   var1    var2
2021    1       345     765

is valid for months 1,2,3 and then comes a change and the next row

year    month   var1    var2
2021    4       234     876

is valid for months 4,5,6,7,8,9,10,11,12.

If the year & month are already in the dataframe, then I can have the row like

get_value <- function(gyear, gmonth){
  library(tidyverse)
  temp <- df %>% filter(year == gyear & month == gmonth)
}

get_value(gyear = 2020, gmonth = 1)

but what I want is also to be able to have rows (months) that are between the months that are included in the dataframe. For example I would like to be able to call

get_value(gyear = 2021, gmonth = 5)

that returns row

year    month   var1    var2
2021    4       234     876

because in year 2021 the month is between 4-12.

Thanks in advance for your help!

dplyr dataframe filter r
1 Answers

You could first create a new column month2 to indicate the ending months.

library(dplyr)

df2 <- df %>%
  group_by(year) %>%
  arrange(month, .by_group = TRUE) %>%
  mutate(month2 = lead(month-1, default = 12), .after = month) %>%
  ungroup()

# # A tibble: 7 × 5
#    year month month2 variable1 variable2
#   <int> <int>  <dbl>     <int>     <int>
# 1  2019     1     12       789       321
# 2  2020     1      2       678       432
# 3  2020     3      6       567       543
# 4  2020     7     12       456       654
# 5  2021     1      3       345       765
# 6  2021     4     12       234       876
# 7  2022     1     12       123       987

Then customize a filter function that takes a dataframe as input and extract those rows where gmonth >= month & gmonth <= month2.

get_value <- function(data, gyear, gmonth){
  data %>%
    filter(year == gyear & gmonth >= month & gmonth <= month2)
}

I don't encourage you to put the code where I manipulate df to get month2 into this function. If you do that, the data will be repeatedly manipulated whenever get_value() is run. It's out of efficiency.

Output
get_value(df2, gyear = 2020, gmonth = 1)

# # A tibble: 1 × 5
#    year month month2 variable1 variable2
#   <int> <int>  <dbl>     <int>     <int>
# 1  2020     1      2       678       432

get_value(df2, gyear = 2021, gmonth = 5)

# # A tibble: 1 × 5
#    year month month2 variable1 variable2
#   <int> <int>  <dbl>     <int>     <int>
# 1  2021     4     12       234       876
user_100689850
Related