CODEWITHSUNDEEP
r
Berbare
Berbare

Reputation: 144

How to overwrite some rows of a tibble with another tibble

Suppose I have data like the following:

# A tibble: 10 x 4
# Groups:   a.month, a.group [10]
   a.month     a.group  other.group  amount
   <date>      <chr>    <chr>        <dbl>
 1 2016-02-01  A        X            15320 
 2 2016-05-01  A        Z            50079 
 3 2016-06-01  A        Y            60564 
 4 2016-08-01  A        X            10540 
 5 2017-01-01  B        X            30020 
 6 2017-03-01  B        X            76310 
 7 2017-04-01  B        Y            44215
 8 2017-05-01  A        Y            67241 
 9 2017-06-01  A        Z            17180 
10 2017-07-01  B        Z            31720

And I want to produce rows for every possible combination of a.group, other.group and for every month in between (with amount being zero if not present on the data above)

I managed to produce a tibble with the default amounts through:

another.tibble <- as_tibble(expand.grid(
  a.month = months.list,
  a.group = unique.a.groups,
  other.group = unique.o.groups,
  amount = 0
));

How should I proceed to populate another.tibble with the values from the first one?

Upvotes: 1

Views: 247

Answers (1)

Patricio Moracho
Patricio Moracho

Reputation: 717

It is important to invoke expand.grid with stringsAsFactors=FALSE. Then, we simply make a LEFT_JOIN() to complete the combinations where we have data

library(tidyverse)

df <- tribble(
    ~a.month,      ~a.group,   ~other.group,   ~amount,
    '2016-02-01',  'A',        'X',            15320, 
    '2016-05-01',  'A',        'Z',            50079, 
    '2016-06-01',  'A',        'Y',            60564, 
    '2016-08-01',  'A',        'X',            10540, 
    '2017-01-01',  'B',        'X',            30020, 
    '2017-03-01',  'B',        'X',            76310, 
    '2017-04-01',  'B',        'Y',            44215,
    '2017-05-01',  'A',        'Y',            67241, 
    '2017-06-01',  'A',        'Z',            17180, 
    '2017-07-01',  'B',        'Z',            31720
)

another.tibble <- as_tibble(expand.grid(
    a.month = unique(df$a.month),
    a.group = unique(df$a.group),
    other.group = unique(df$other.group),
    amount = 0, stringsAsFactors=F)
)

another.tibble %>%
    left_join(df, by= c("a.month" = "a.month", "a.group" = "a.group", "other.group" = "other.group")) %>%
    mutate(amount.x = ifelse(is.na(amount.y), 0, amount.y)) %>%
    rename(amount = amount.x) %>%
    select(1:4)

Upvotes: 2

Related Questions