marching a value from a dataframe to another dataframe in R

Question

I have two dataframes: a parent dataframe called Norway_MM4 with 57043 rows, and another dataframe called Norway_averageweather.

I will like to merge both dataframes, Norway_averageweather has unique month, year, and latitudes_band columns, but Norway_MM4 does not have unique entries for these dates.

I want to match the average temperature entries in the weather data to each date and latitude_bands on the parent dataframe

Norway_MM4 <- 
  dplyr::tribble(
    ~date, ~month, ~year, ~Latitude, ~Longitude, ~Latitude_bands,
    2018-03-07, 03, 2018, 33.21580, -81.52677, 32, 
    2020-04-07, 04, 2020, 33.21608, -81.52699, 32, 
    2018-03-07, 03, 2018, 34.21592, -81.52706, 34, 
    2023-07-09, 07, 2023, 34.21521, -81.53458, 34, 
    2018-03-10, 03, 2018, 36.21737, -81.53036, 36, 
    2010-08-11, 08, 2010, 36.21171, -81.53041, 36, 
    2010-08-11, 08, 2010, 38.21171, -71.53041, 38, 
    2010-08-11, 08, 2010, 40.21167, -91.53041, 40
  )

Norway_averageweather <- 
  dplyr::tribble(
    ~date, ~month, ~year, ~Avg_temp, ~avg_precip, ~Latitude_bands,
    2018-03-07, 03, 2018, 33.21, 81.52 ,32 ,
    2020-04-07, 04, 2020, 33.21, 61.50 ,32 ,
    2018-05-07, 03, 2018, 34.41, 181.52, 34, 
    2023-07-09, 07, 2023, 34.21, 61.34 ,34 ,
    2018-03-10, 03, 2018, 36.31, 810.36, 36, 
    2010-08-11, 08, 2010, 36.21, 81.41 ,36 ,
    2010-08-11, 08, 2010, 37.21, 90.41 ,38 ,
    2010-08-11, 08, 2010, 38.21, 91.41 ,40
  )

Here is what I tried so far:

merged_df <- merge(unique(Norway_MM4), 
                   Norway_averageweather, 
                   by = c("Latitude_Bands", "month", "year"), 
                   all.x = TRUE)

And this is what I would like the result to look like:

merged_df <-
  dplyr::tribble(
    ~date, ~month, ~year,  ~Latitude, ~Longitude, ~Latitude_bands, ~Avg_temp, ~avg_precip,
    2018-03-07, 03, 2018, 33.21580, -81.52677, 32, 33.21, 81.52 ,
    2020-04-07, 04, 2020, 33.21608, -81.52699, 32, 33.21, 61.50,
    2018-03-07, 03, 2018, 34.21592, -81.52706, 34, 34.41, 181.52,
    2023-07-09, 07, 2023, 34.21521, -81.53458, 34, 34.21, 61.34 ,
    2018-03-10, 03, 2018, 36.21737, -81.53036, 36, 36.31, 810.36,
    2010-08-11, 08, 2010, 36.21171, -81.53041, 36, 36.21, 81.41 ,
    2010-08-11, 08, 2010, 38.21171, -71.53041, 38, 37.21, 90.41 ,
    2010-08-11, 08, 2010, 40.21167, -91.53041, 40, 38.21, 91.41
  )

Thanks - if you could add a few rows of each dataframe plus the desired ouput it would make for a good reproducible example, see here: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Paul Stafford Allen, Jul 24 '23 at 13:52
It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. — MrFlick, Jul 24 '23 at 13:53
I just edited the question to take in the observations about the question — Umeh Blessing, Jul 24 '23 at 14:55

score 0 · Answer 1 · answered Jul 24 '23 at 17:01

You can use dplyr::left_join() for this operation. I find its interface easier to work with than base::merge().

library(tidyverse)
  
Norway_averageweather_mod <- 
  Norway_averageweather |> 
  select(month, year, Avg_temp, avg_precip, Latitude_bands)

left_join(
  Norway_MM4,
  Norway_averageweather_mod)
#> Joining with `by = join_by(month, year, Latitude_bands)`
#> # A tibble: 8 × 8
#>   Norway_MM4 month  year Latitude Longitude Latitude_bands Avg_temp avg_precip
#>        <dbl> <dbl> <dbl>    <dbl>     <dbl>          <dbl>    <dbl>      <dbl>
#> 1       2008     3  2018     33.2     -81.5             32     33.2       81.5
#> 2       2009     4  2020     33.2     -81.5             32     33.2       61.5
#> 3       2008     3  2018     34.2     -81.5             34     34.4      182. 
#> 4       2007     7  2023     34.2     -81.5             34     34.2       61.3
#> 5       2005     3  2018     36.2     -81.5             36     36.3      810. 
#> 6       1991     8  2010     36.2     -81.5             36     36.2       81.4
#> 7       1991     8  2010     38.2     -71.5             38     37.2       90.4
#> 8       1991     8  2010     40.2     -91.5             40     38.2       91.4

marching a value from a dataframe to another dataframe in R

1 Answers1