Adding multiple columns from a list table

Question

I've got two data tables as below:

> have1
        Column1  Column2  Column3
1:          100      200      159
2:          169      506      101
3:          100      200      636
---
123571      456      559      249
123572      654      883      423

> have2
      variable    value
1:       Apple     0.25
2:       Orange    2.68
3:       Pear      0.11
---
10:      Grape     5.27

What I'm trying to do is to add the variable from 'have2' as columns and value as constant to 'have1', so the 'want' is:

> want
        Column1  Column2  Column3   Apple  Orange   Pear.... Grape
1:          100      200      159    0.25    2.68   0.11      5.27
2:          169      506      101    0.25    2.68   0.11      5.27
3:          100      200      636    0.25    2.68   0.11      5.27
---
123571      456      559      249    0.25    2.68   0.11      5.27
123572      654      883      423    0.25    2.68   0.11      5.27

I'm hoping to not use dplyr and stick with base R or data.table. This merging will need to be applicable for different sets of 'have1' & 'have2' and 'want', so trying to avoid manually listing all the variables in the cbind (i.e., cbind(have1, Apple=0.25 etc...)...)

Any help, suggestions would be appreciated, thank you!!

so you are widening the data in table two ? and what is common between the two tables to link for any lookups? — anuanand
– anuanand, Commented Mar 28, 2022 at 5:22

AndrewGB · Accepted Answer · 2022-03-28 05:22:08Z

4

library(data.table)

cbind(have1, transpose(have2, make.names = "variable"))

Output

   Column1 Column2 Column3 Apple Orange Pear
1:     100     200     159  0.25   2.68 0.11
2:     169     506     101  0.25   2.68 0.11
3:     100     200     636  0.25   2.68 0.11

Data

have1 <- structure(list(Column1 = c(100L, 169L, 100L), Column2 = c(200L, 
506L, 200L), Column3 = c(159L, 101L, 636L)), class = c("data.table", 
"data.frame"), row.names = c(NA, -3L))
setDT(have1)

have2 <- structure(list(variable = c("Apple", "Orange", "Pear"), value = c(0.25, 
2.68, 0.11)), class = c("data.table", "data.frame"), row.names = c(NA, 
-3L))
setDT(have2)

answered Mar 28, 2022 at 5:22

AndrewGB

17k6 gold badges22 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2022-04-12 14:12:28Z

1

Another option is:

library(data.table)

have1[, have2$variable := as.list(have2$value)]

have1
#>    Column1 Column2 Column3 Apple Orange Pear
#> 1:     100     169     100  0.25   2.68 0.11
#> 2:     200     506     200  0.25   2.68 0.11
#> 3:     159     636     636  0.25   2.68 0.11
#> 4:     200     350     450  0.25   2.68 0.11

# data
have1 <- data.table(Column1 = c(100, 200, 159, 200),
                    Column2 = c(169,506, 636, 350),
                    Column3 = c(100, 200, 636, 450))

have2 <- data.table(variable = c("Apple", "Orange", "Pear"),
                    value = c(0.25, 2.68, 0.11))

^{Created on 2022-03-28 by the reprex package (v2.0.1)}

edited Apr 12, 2022 at 14:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 28, 2022 at 5:31

TimTeaFan

18.7k4 gold badges25 silver badges51 bronze badges

Collectives™ on Stack Overflow

Adding multiple columns from a list table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related