How to fill a dataframe with values from another matching columns names and rows names in R?

Question

I have the following dataframes DF1 and DF2. I am trying to fill DF2 with values from the Close column in DF1. But it turns out that the output is zero when running the loop. Don't know what is wrong, but it seems it doesn't read the Asset column values.

DF1:

    Data        Asset   Close
1   1986-11-27  ABC 6   5.95
2   1986-12-01  ABC 6   5.90
3   1986-12-03  ABC 6   5.90
4   1986-12-04  ABC 6   5.90
5   1986-12-05  ABC 6   5.00
6   1986-12-08  ABC 6   5.00
7   1986-12-09  ABC 6   4.78
8   1986-10-31  ABC 8   3.90
9   1986-11-03  ABC 8   3.70
10  1986-11-04  ABC 8   3.70
.        .        .      .
.        .        .      .

DF2:

            ABC 6  ABC 8
1986-11-27   NA     NA
1986-12-01   NA     NA
1986-12-03   NA     NA
1986-12-04   NA     NA
1986-12-05   NA     NA
1986-12-08   NA     NA
1986-12-09   NA     NA
1986-12-10   NA     NA
1986-12-11   NA     NA
1986-12-12   NA     NA
    .        .      .
    .        .      .

for (i in 1:length(DF2))
  {
    for (m in 1:nrow(DF2))
    {
      for (n in 1:nrow(DF1))
        {
          if ((names(DF2[i]) == DF1[n,2]) & (row.names(DF2[m,0])==as.character(DF1[n,1])))
          {
            DF2[m,i] <- DF1[n,3]
          } else{DF2[m,i] <- 0}
        }
    }
  }

Output:

            ABC 6  ABC 8
1986-11-27   0      0
1986-12-01   0      0
1986-12-03   0      0
1986-12-04   0      0
1986-12-05   0      0
1986-12-08   0      0
1986-12-09   0      0
1986-12-10   0      0
1986-12-11   0      0
1986-12-12   0      0
    .        .      .
    .        .      .

score 0 · Answer 1 · answered Dec 16 '19 at 09:10

I am not sure if this is what you want to achieve. A solution with base R is given as below:

DF2 <- cbind(DF2[1],sapply(names(DF2)[-1], 
                           function(v) DF1$Close[match(DF2$Date,subset(DF1,Asset==v)$Date)]))

such that

> DF2
         Date ABC 6 ABC 8
1  1986-11-27  5.95    NA
2  1986-12-01  5.90    NA
3  1986-12-03  5.90    NA
4  1986-12-04  5.90    NA
5  1986-12-05  5.00    NA
6  1986-12-08  5.00    NA
7  1986-12-09  4.78    NA
8  1986-12-10    NA    NA
9  1986-12-11    NA    NA
10 1986-12-12    NA    NA

DATA

DF1 <- structure(list(Date = structure(c(4L, 5L, 6L, 7L, 8L, 9L, 10L, 
                                         1L, 2L, 3L), .Label = c("1986-10-31", "1986-11-03", "1986-11-04", 
                                                                 "1986-11-27", "1986-12-01", "1986-12-03", "1986-12-04", "1986-12-05", 
                                                                 "1986-12-08", "1986-12-09"), class = "factor"), Asset = structure(c(1L, 
                                                                                                                                     1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), .Label = c("ABC 6", "ABC 8"
                                                                                                                                     ), class = "factor"), Close = c(5.95, 5.9, 5.9, 5.9, 5, 5, 4.78, 
                                                                                                                                                                     3.9, 3.7, 3.7)), row.names = c(NA, -10L), class = "data.frame")
DF2 <- structure(list(Date = structure(1:10, .Label = c("1986-11-27", 
                                                        "1986-12-01", "1986-12-03", "1986-12-04", "1986-12-05", "1986-12-08", 
                                                        "1986-12-09", "1986-12-10", "1986-12-11", "1986-12-12"), class = "factor"), 
                      "ABC 6" = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), "ABC 8" = c(NA, 
                                                                                   NA, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 
                                                                                                                                                             -10L))

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

If I understand correctly, the OP wants to reshape the data from long to wide format.

This is a very common operation and questions have been asked and answered many times before, e.g., here.

However, just for demonstration, here is a collection of solutions which provide the expected result:

Base R

reshape(DF1, idvar = "Data", timevar = "Asset", v.names = "Close", direction = "wide")

         Data Close.ABC 6 Close.ABC 8
1  1986-11-27        5.95          NA
2  1986-12-01        5.90          NA
3  1986-12-03        5.90          NA
4  1986-12-04        5.90          NA
5  1986-12-05        5.00          NA
6  1986-12-08        5.00          NA
7  1986-12-09        4.78          NA
8  1986-10-31          NA         3.9
9  1986-11-03          NA         3.7
10 1986-11-04          NA         3.7

`reshape2` package

reshape2::dcast(DF1, Data ~ Asset)

Using Close as value column: use value.var to override.
         Data ABC 6 ABC 8
1  1986-10-31    NA   3.9
2  1986-11-03    NA   3.7
3  1986-11-04    NA   3.7
4  1986-11-27  5.95    NA
5  1986-12-01  5.90    NA
6  1986-12-03  5.90    NA
7  1986-12-04  5.90    NA
8  1986-12-05  5.00    NA
9  1986-12-08  5.00    NA
10 1986-12-09  4.78    NA

`data.table` package

library(data.table)
dcast(setDT(DF1), Data ~ Asset)

Using 'Close' as value column. Use 'value.var' to override
          Data ABC 6 ABC 8
 1: 1986-10-31    NA   3.9
 2: 1986-11-03    NA   3.7
 3: 1986-11-04    NA   3.7
 4: 1986-11-27  5.95    NA
 5: 1986-12-01  5.90    NA
 6: 1986-12-03  5.90    NA
 7: 1986-12-04  5.90    NA
 8: 1986-12-05  5.00    NA
 9: 1986-12-08  5.00    NA
10: 1986-12-09  4.78    NA

`tidyr` package

library(tidyr)
DF1 %>% 
  pivot_wider(names_from = Asset, values_from = Close)

# A tibble: 10 x 3
   Data       `ABC 6` `ABC 8`
   <chr>        <dbl>   <dbl>
 1 1986-11-27    5.95    NA  
 2 1986-12-01    5.9     NA  
 3 1986-12-03    5.9     NA  
 4 1986-12-04    5.9     NA  
 5 1986-12-05    5       NA  
 6 1986-12-08    5       NA  
 7 1986-12-09    4.78    NA  
 8 1986-10-31   NA        3.9
 9 1986-11-03   NA        3.7
10 1986-11-04   NA        3.7

Data

DF1 <- as.data.frame(readr::read_table("rn    Data        Asset   Close
1   1986-11-27  ABC 6   5.95
2   1986-12-01  ABC 6   5.90
3   1986-12-03  ABC 6   5.90
4   1986-12-04  ABC 6   5.90
5   1986-12-05  ABC 6   5.00
6   1986-12-08  ABC 6   5.00
7   1986-12-09  ABC 6   4.78
8   1986-10-31  ABC 8   3.90
9   1986-11-03  ABC 8   3.70
10  1986-11-04  ABC 8   3.70", col_types = "_ccd"))

How to fill a dataframe with values from another matching columns names and rows names in R?

2 Answers2

Base R

reshape2 package

data.table package

tidyr package

Data

`reshape2` package

`data.table` package

`tidyr` package