Unable to combine two dataframes in R

3559 views r
-3

I am trying to combine two dataframes using rbind. Below are the two dfs

ab1

        Product Apr 2017 May 2017 Jun 2017 Jul 2017 Aug 2017 Sep 2017 Oct 2017 Nov 2017
1 Black Menthol     0.02     0.02     0.02     0.02     0.02     0.02     0.02     0.02
2         Gold      0.07     0.07     0.08     0.07     0.07     0.07     0.07     0.07
3        Green         0        0        0        0        0        0        0        0
4          Red      0.09     0.08     0.07     0.09     0.09     0.09     0.08     0.09

ab2

            Product Apr 2017 May 2017 Jun 2017 Jul 2017 Aug 2017 Sep 2017 Oct 2017 Nov 2017
1     Black Menthol     0.02     0.02     0.02     0.02     0.02     0.02     0.02     0.02
2 Black Non-Menthol     0.17     0.17     0.18     0.17     0.16     0.16     0.14     0.15
3            Green      0.07     0.06     0.07     0.06     0.06     0.06     0.07     0.08
4              Red      0.23     0.24     0.25     0.24     0.23     0.23     0.22     0.22

Below is the code I am using to combine them:

library(shiny)
library(dplyr)

ab3 <- reactive({rbind.data.frame(ab1,ab2)})

ab4<- reactive({ ab3 %>% group_by(`Product`) %>%summarize_all(funs(sum))})

When I do this I get the following error : Evaluation error: ‘sum’ not meaningful for factors

The output I am tying to get is:

  Product Apr.2017 May.2017 Jun.2017 Jul.2017 Aug.2017 Sep.2017 Oct.2017
#  <chr>      <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
#1 Black …     0.04     0.04     0.04    0.04      0.04     0.04     0.04
#2 Black …     0.17     0.17     0.18    0.17      0.16     0.16     0.14
#3 Gold        0.07     0.07     0.08    0.07      0.07     0.07     0.07
#4 Green       0.07     0.06     0.07    0.06      0.06     0.06     0.07
#5 Red         0.32     0.32     0.32    0.330     0.32     0.32     0.3

answered question

The rbind isn't the problem. It's that you're trying to do sum() on a factor (categorical) variable in your ab4 <- ... step. The equivalent of "apple" + "orange". This is because your first "Product" column is not a numeric column.

So I did try to convert all the factors to numeric after rbindand couldnt get it working : ab3_convert<- # data.frame(lapply(ab3, function(x) as.numeric(as.character(x))), # check.names=F, row.names = rownames(ab3))

1 Answer

8

Is this what you're after?

library(tidyverse)
bind_rows(ab1, ab2) %>%
    group_by(Product) %>%
    summarise_all(sum)
## A tibble: 5 x 9
#  Product Apr.2017 May.2017 Jun.2017 Jul.2017 Aug.2017 Sep.2017 Oct.2017
#  <chr>      <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
#1 Black …     0.04     0.04     0.04    0.04      0.04     0.04     0.04
#2 Black …     0.17     0.17     0.18    0.17      0.16     0.16     0.14
#3 Gold        0.07     0.07     0.08    0.07      0.07     0.07     0.07
#4 Green       0.07     0.06     0.07    0.06      0.06     0.06     0.07
#5 Red         0.32     0.32     0.32    0.330     0.32     0.32     0.3
## ... with 1 more variable: Nov.2017 <dbl>

This assumes that ab1 and ab2 have the same column structure.


Sample data

ab1 <- read.table(text =
    "        Product 'Apr 2017' 'May 2017' 'Jun 2017' 'Jul 2017' 'Aug 2017' 'Sep 2017' 'Oct 2017' 'Nov 2017'
1 'Black Menthol'     0.02     0.02     0.02     0.02     0.02     0.02     0.02     0.02
2         Gold      0.07     0.07     0.08     0.07     0.07     0.07     0.07     0.07
3        Green         0        0        0        0        0        0        0        0
4          Red      0.09     0.08     0.07     0.09     0.09     0.09     0.08     0.09
", header = T)
ab2 <- read.table(text =
    "            Product 'Apr 2017' 'May 2017' 'Jun 2017' 'Jul 2017' 'Aug 2017' 'Sep 2017' 'Oct 2017' 'Nov 2017'
1     'Black Menthol'     0.02     0.02     0.02     0.02     0.02     0.02     0.02     0.02
2 'Black Non-Menthol'     0.17     0.17     0.18     0.17     0.16     0.16     0.14     0.15
3            Green      0.07     0.06     0.07     0.06     0.06     0.06     0.07     0.08
4              Red      0.23     0.24     0.25     0.24     0.23     0.23     0.22     0.22
", header = T)

posted this

Have an answer?

JD

Please login first before posting an answer.