CODEWITHSUNDEEP
rdataframemerge
T.Jensen
T.Jensen

Reputation: 35

R: match row name in data frame x with column name in data frame y for a given variable value in column shared by the two data frames

Say i have two data frames which looks like the ones below

> df1
        date firm1 firm2 firm3
1 01-01-2017     1     2    3
2 01-02-2017     4     5    6
3 01-03-2017     7     8    9

> df2
            date
firm1 01-02-2017
firm2 01-01-2017
firm3 01-03-2017

Is it possible to extract a new data frame with the values from df1 where the column name and date value of df1 matches the row name and date of df2?

The data frame is was hoping to get would look like:

      Value
firm1     2
firm2     4
firm3     9

Any suggestions would be greatly appreciated!

Upvotes: 2

Views: 2039

Answers (2)

amonk
amonk

Reputation: 1795

library(reshape2)
#regenerating the initial datasets
date <- c("01-01-2017","01-02-2017","01-03-2017")
firm1 <- c(1,4,7)
firm2 <- c(2,5,8)
firm3 <- c(3,6,9)
df1 <- data.frame(date,firm1,firm2,firm3)

df1
        date  firm1  firm2  firm3
1: 01-01-2017     1     2     3
2: 01-02-2017     4     5     6
3: 01-03-2017     7     8     9

variable <- c("firm1","firm2","firm3")
date <- c("01-02-2017","01-01-2017","01-03-2017")
df2 <- data.frame(date,variable)

df2
      date      variable
1: 01-01-2017    firm1
2: 01-02-2017    firm2
3: 01-03-2017    firm3

#changing the format from wide to long

df1b <- melt(df1,id.vars = "date")
df1b
       date variable value
1: 01-01-2017    firm1     1
2: 01-02-2017    firm1     4
3: 01-03-2017    firm1     7
4: 01-01-2017    firm2     2
5: 01-02-2017    firm2     5
6: 01-03-2017    firm2     8
7: 01-01-2017    firm3     3
8: 01-02-2017    firm3     6
9: 01-03-2017    firm3     9

res <- merge(df2,df1b,by=c("date","variable"))

res  
    date variable value
1: 01-01-2017    firm2     2
2: 01-02-2017    firm1     4
3: 01-03-2017    firm3     9

Upvotes: 1

akrun
akrun

Reputation: 887048

We can use row/column indexing to extract the values from 'df1' and create a data.frame

df3 <- data.frame(Value = df1[-1][cbind(1:nrow(df1), match(df2$date, df1$date))])
row.names(df3) <- row.names(df2)
df3
#      Value
#firm1     2
#firm2     4
#firm3     9

Upvotes: 2

Related Questions