Create New Dataframe Using Columns from Multiple Data Frames

Question

I have two data frames with the same column names (shortterm and longterm). The data is also time series.

Austria<-c(21000, 23400, 26800)
Aruba<-c(50282, 234934, 34634)
Date<- as.Date(c('2010-01-01','2010-04-01','2010-01-07'))
shortterm.df <- data.frame(Date,Austria, Aruba)


 Austria <- c(23423, 457, 45457)
 Aruba <- c(7768, 67679, 67979)
 Date <- as.Date(c('2010-01-01','2010-04-01','2010-01-07'))
longterm.df <- data.frame(Date,Austria, Aruba)

I would like to extract the Austrian Data such that I have a new dataset that looks like

  Date          shortterm longterm
  2010-01-01     21000    23423
  2010-04-01     23400      457
  2010-01-07     26800    45457

What I've tried so far is to combine the data into a list

df.list <-list(shortterm.df,longterm.df)

and used lapply

 setNames(do.call(cbind, lapply(df.list, `[`, 'Austria')), nm1)

But I would like this to be a data frame and I would like to keep the date element (which I've lost using this method)

Any help would be greatly appreciated.

Rename the columns to `shortterm` and `longterm`, then use `merge`. — , Apr 28 '18 at 09:36

score 1 · Answer 1 · answered Apr 28 '18 at 09:35

Here is one option using dplyr and tidyr.

library(dplyr)
library(tidyr)

dat <- list(shortterm.df, longterm.df) %>%
  setNames(c("shortterm", "longterm")) %>%
  bind_rows(.id = "Data") %>%
  select(-Aruba) %>%
  spread(Data, Austria)
dat
#         Date longterm shortterm
# 1 2010-01-01    23423     21000
# 2 2010-01-07    45457     26800
# 3 2010-04-01      457     23400

score 1 · Answer 2 · answered Apr 28 '18 at 09:42

Another dplyr and tidyr possibility

library(tidyr)
library(dplyr)

inner_join(gather(shortterm.df, key = country, value = shortterm, -Date),
           gather(longterm.df, key = country, value = longterm, -Date)) %>% 
  filter(country == "Austria")

Joining, by = c("Date", "country")
        Date country shortterm longterm
1 2010-01-01 Austria     21000    23423
2 2010-04-01 Austria     23400      457
3 2010-01-07 Austria     26800    45457

Thank you very much! This worked a treat. – StephenB Apr 28 '18 at 19:18 — StephenB, Apr 28 '18 at 19:18

score 0 · Answer 3 · answered Apr 28 '18 at 09:33

Would something like that work?

Austria.short <- c(21000, 23400, 26800)
Austria.long <- c(23423, 457, 45457)
Date <- as.Date(c('2010-01-01','2010-04-01','2010-01-07'))

df <- cbind(Date,Austria.long,Austria.short)
df <- as.data.frame(df)

Edit :

You could also use

df <- cbind(Date,longterm.df$Austria,shortterm.df$Austria)
df <- as.data.frame(df)

Given the structure of your initial dataframes.

Create New Dataframe Using Columns from Multiple Data Frames

3 Answers3