CODEWITHSUNDEEP
rtidyr
Forge
Forge

Reputation: 1687

keep a column when expanding dataframe to fill using tidyr::complete

Regarding how to fill missing rows in a data frame i used this example

df <- read.table(textConnection("car,year,month,country,amount
    Mazda,2012,02,JP,2344
    Ford,2012,04,US,235234
    Mazda,2012,03,JP,3455
    Mazda,2012,04,JP,43554
    Mazda,2012,05,JP,9854
    Mazda,2012,06,JP,32556
    Ford, 2013,01,US,345"), sep = ",", header = TRUE)

> df
                                       car year month country amount
1                                    Mazda 2012     2      JP   2344
2                                     Ford 2012     4      US 235234
3                                    Mazda 2012     3      JP   3455
4                                    Mazda 2012     4      JP  43554
5                                    Mazda 2012     5      JP   9854
6                                    Mazda 2012     6      JP  32556
7                                     Ford 2013     1      US    345

I use tidyr::complete to fill missing rows for month and year this way:

tidyr::complete(df, car = unique(car), year = 2012:2014, month=1:12, fill=list(amount=0))

but country is lost. i've read tidyr documentation but it's really short and could'nt find any other SO answer on this.

# A tibble: 108 x 5
   car                                        year month country amount
   <fct>                                     <int> <int> <fct>    <dbl>
 1 "                                   Ford"  2012     1 NA           0
 2 "                                   Ford"  2012     2 NA           0
 3 "                                   Ford"  2012     3 NA           0
 4 "                                   Ford"  2012     4 US      235234
 5 "                                   Ford"  2012     5 NA           0
 6 "                                   Ford"  2012     6 NA           0
 7 "                                   Ford"  2012     7 NA           0
 8 "                                   Ford"  2012     8 NA           0
 9 "                                   Ford"  2012     9 NA           0
10 "                                   Ford"  2012    10 NA           0
# ... with 98 more rows

How to preserve it?

Upvotes: 2

Views: 66

Answers (2)

hrbrmstr
hrbrmstr

Reputation: 78842

Since you neglected to note that you opened a new question in the second-ask on the original, just maintain a metadata data frame:

read.table(textConnection("car,year,month,amount
Mazda,2012,02,2344
Ford,2012,04,235234
Mazda,2012,03,3455
Mazda,2012,04,43554
Mazda,2012,05,9854
Mazda,2012,06,32556
Ford,2013,01,2345"),
           sep = ",", header = TRUE, stringsAsFactors = FALSE) -> xdf


merge(
  expand.grid(car = unique(xdf$car), year =2012:2014, month=1:12),
  xdf, by = c("car", "year", "month"), all.x = TRUE
) -> x2
x2$amount <- ifelse(is.na(x2$amount), 0, x2$amount)

data.frame(
  car = c("Mazda", "Ford"),
  country = c("JP", "US"),
  stringsAsFactors = FALSE
) -> car2country_df

merge(x2, car2country_df)

or via tidyverse:

tidyr::complete(
  xdf, car = unique(car), year = 2012:2014, month=1:12, fill=list(amount=0)
) %>% 
  dplyr::left_join(car2country_df)

Upvotes: 1

akrun
akrun

Reputation: 887971

We can place it in nesting

library(tidyverse)
df %>% 
  complete(car = unique(car), year = 2012:2014, month = 1:12, 
            nesting(country), fill = list(amount = 0))

Upvotes: 2

Related Questions