Repeating calculations based on values in a different data frame R-CodePudding

I have one data frame with aircraft types, speed and range.

Aircraft <- data.frame(type=c("X","Y","Z"),
                    Range=c(100,200,300),
                    Speed=c(50,60,70))

Aircraft

and a second long data frame with a list of destinations and distances

Destination <- data.frame(Location=c("A","B","C", "D"),
                    Distance=c(50,150,200,400))

Destination

I would like to use the first data frame to add multiple columns to the second data frame based on a calculation, eg, the time taken based on the speed (Destination$Distance/Aircraft$Speed). Ideally I would also like the new column names to take part of their label from the first data frame. So that Destination would end up with the following columns Destination, Distance, TimeX, TimeY, TimeZ.

I am just getting started in R and have no idea if this is possible.

Thanks

CodePudding user response：

You first have to merge your datasets together, then, after computing your new variables, restructure to wide format.

For the merge, it looks like you want to match all rows in Aircraft with all rows in Destnation. This is called a “Cartesian” or “cross join,” and can be done via dplyr::full_join(by = character()).

library(dplyr)
library(tidyr)

merged <- full_join(Destination, Aircraft, by = character())

   Location Distance type Range Speed
1         A       50    X   100    50
2         A       50    Y   200    60
3         A       50    Z   300    70
4         B      150    X   100    50
5         B      150    Y   200    60
6         B      150    Z   300    70
7         C      200    X   100    50
8         C      200    Y   200    60
9         C      200    Z   300    70
10        D      400    X   100    50
11        D      400    Y   200    60
12        D      400    Z   300    70

Next, you can pivot to wide using tidyr::pivot_wider():

merged_wide <- merged %>%
  mutate(
    Time = Distance/Speed, 
    InRange = Distance <= Range
  ) %>%
  pivot_wider(
    id_cols = Location:Distance,
    names_from = type, 
    values_from = Time:InRange
  )

# A tibble: 4 × 8
  Location Distance Time_X Time_Y Time_Z InRange_X InRange_Y InRange_Z
  <chr>       <dbl>  <dbl>  <dbl>  <dbl> <lgl>     <lgl>     <lgl>    
1 A              50      1  0.833  0.714 TRUE      TRUE      TRUE     
2 B             150      3  2.5    2.14  FALSE     TRUE      TRUE     
3 C             200      4  3.33   2.86  FALSE     TRUE      TRUE     
4 D             400      8  6.67   5.71  FALSE     FALSE     FALSE

CodePudding user response：

Would this work?

library(dplyr)
Destination<-mutate(Destination
                ,TimeX=Distance/50
                ,TimeY=Distance/60
                ,TimeZ=Distance/70 )

Destination
     Location Distance TimeX     TimeY     TimeZ
         A       50     1      0.8333333 0.7142857
         B      150     3      2.5000000 2.1428571
         C      200     4      3.3333333 2.8571429
         D      400     8      6.6666667 5.7142857

CodePudding user response：

Try this, to dynamically create the column names and avoid hardcoding the speed:

for (i in 1:nrow(Aircraft)) {
  varname <- paste("Time",Aircraft$type[i] , sep="_")
  Destination[[varname]] <-with(Destination, Distance/Aircraft$Speed[i])
}

Destination




 Location Distance Time_X    Time_Y    Time_Z
1        A       50      1 0.8333333 0.7142857
2        B      150      3 2.5000000 2.1428571
3        C      200      4 3.3333333 2.8571429
4        D      400      8 6.6666667 5.7142857