CODEWITHSUNDEEP
rdataframe
Yu Guo
Yu Guo

Reputation: 371

How to arrange a data frame based on the order from another data frame in R?

I have a data frame recording the temperature on different locations and another data frame recording the date when the temperature data was observed.

For instance,

temperature <- read.table(header=TRUE, text='
               NewYork Boston   Madison
                  32     22  7
                  27     13  28
                  15     0 5 ')


date <- read.table(header=TRUE, text='
        NewYork          Boston      Madison
        2013-08-09     2002-04-01  2003-08-09
        2004-07-11     2003-09-12  2002-12-23
        2006-08-05     2005-11-09  2005-02-05 ')

I used “apply” function to get the order of temperature data but how can I arrange my date data based on the order I get.

apply(temperature, 2, order)

Upvotes: 1

Views: 86

Answers (3)

alistaire
alistaire

Reputation: 43334

If you add a row index to maintain row location information and reshape your data frames to long form, you can join them. With tidyr::gather and dplyr::inner_join,

library(tidyverse)

temperature <- read_table(
'NewYork   Boston   Madison
      32       22         7
      27       13        28
      15        0         5')

date <- read_table(
  'NewYork         Boston     Madison
2013-08-09     2002-04-01  2003-08-09
2004-07-11     2003-09-12  2002-12-23
2006-08-05     2005-11-09  2005-02-05')

temp_date <- inner_join(
    date %>% rowid_to_column('i') %>% gather(city, date, -i),
    temperature %>% rowid_to_column('i') %>% gather(city, temp, -i)
)
#> Joining, by = c("i", "city")

temp_date
#> # A tibble: 9 x 4
#>       i    city       date  temp
#>   <int>   <chr>     <date> <int>
#> 1     1 NewYork 2013-08-09    32
#> 2     2 NewYork 2004-07-11    27
#> 3     3 NewYork 2006-08-05    15
#> 4     1  Boston 2002-04-01    22
#> 5     2  Boston 2003-09-12    13
#> 6     3  Boston 2005-11-09     0
#> 7     1 Madison 2003-08-09     7
#> 8     2 Madison 2002-12-23    28
#> 9     3 Madison 2005-02-05     5

Upvotes: 0

akrun
akrun

Reputation: 887118

We can use Map to order the corresponding columns of one dataset from the order in another dataset

date[] <- Map(function(x, y) x[order(y)], date, temperature)

Upvotes: 1

Len Greski
Len Greski

Reputation: 10855

Here is an approach using reshape2 that matches the observations for each city with the dates recorded for that city. The result is 9 rows of data, with columns for city name and date in addition to the temperature value.

temperature <- read.table(header = TRUE, text = '
               NewYork Boston   Madison
                          32     22  7
                          27     13  28
                          15     0 5 ')

date <- read.table(header = TRUE, text = '
                   NewYork          Boston      Madison
                   2013-08-09     2002-04-01  2003-08-09
                   2004-07-11     2003-09-12  2002-12-23
                   2006-08-05     2005-11-09  2005-02-05 ')

library(reshape2)

meltTemp <- melt(temperature)
meltTemp$date <- melt(date,measure.vars=c("NewYork","Boston","Madison"))[,"value"]
meltTemp

...and the output.

> meltTemp
  variable value       date
1  NewYork    32 2013-08-09
2  NewYork    27 2004-07-11
3  NewYork    15 2006-08-05
4   Boston    22 2002-04-01
5   Boston    13 2003-09-12
6   Boston     0 2005-11-09
7  Madison     7 2003-08-09
8  Madison    28 2002-12-23
9  Madison     5 2005-02-05
>

Upvotes: 0

Related Questions