Look up a value in different df and take max date in r-CodePudding

I have a DF, where I want to look up a value in another DF, but only taking the maximum Date of that observation.

DF_1 = as.data.frame(matrix(ncol = 3, nrow = 1))
colnames(DF_1) = c("Financials AAA", "Financials AA", "Financials BBB")

Date = as.Date(c("2022-07-29", "2021-01-01", "2020-01-01"))
Spreads_AAA = c(0.3, 0.5, 0.6)
Spreads_AA = c(0.1,0.2,0.3)
Spreads_BBB = c(1,2,3)
Spreads = data.frame(Date,Spreads_AAA,Spreads_AA, Spreads_BBB)

So at the end I should get 0.3 for AAA, 0.1 for AA and 1 for BBB in the DF1 in the only row I have

Thank you

CodePudding user response：

Try this:

Spreads_max <- Spreads[which(Spreads$Date == max(Spreads$Date)),]
DF_1 <- Spreads_max[,c(2:4)]

CodePudding user response：

Here's a tidyverse solution.

First, convert to long format

library(tidyverse)

Spreads %>% 
  pivot_longer(
    -Date,
    names_to="Spread",
    values_to="Value",
    names_prefix="Spreads_"
  )
# A tibble: 9 × 3
  Date       Spread Value
  <date>     <chr>  <dbl>
1 2022-07-29 AAA      0.3
2 2022-07-29 AA       0.1
3 2022-07-29 BBB      1  
4 2021-01-01 AAA      0.5
5 2021-01-01 AA       0.2
6 2021-01-01 BBB      2  
7 2020-01-01 AAA      0.6
8 2020-01-01 AA       0.3
9 2020-01-01 BBB      3

Now derive the summary you want

Spreads %>% 
  pivot_longer(
    -Date,
    names_to="Spread",
    values_to="Value",
    names_prefix="Spreads_"
  ) %>% 
  group_by(Spread) %>% 
  slice_max(Date)
# A tibble: 3 × 3
# Groups:   Spread [3]
  Date       Spread Value
  <date>     <chr>  <dbl>
1 2022-07-29 AA       0.1
2 2022-07-29 AAA      0.3
3 2022-07-29 BBB      1

Finally, convert to the format you want

Spreads %>% 
  pivot_longer(
    -Date,
    names_to="Spread",
    values_to="Value",
    names_prefix="Spreads_"
  ) %>% 
  group_by(Spread) %>% 
  slice_max(Date) %>% 
  pivot_wider(
    values_from=Value,
    names_from=Spread,
    names_prefix="Financials "
  ) %>% 
  select(-Date)
# A tibble: 1 × 3
  `Financials AA` `Financials AAA` `Financials BBB`
            <dbl>            <dbl>            <dbl>
1             0.1              0.3                1