Filling missing dates by group

by

You can use data.table from R. Assuming that ‘time’ column is of ‘Date’ class,

library(data.table)#v1.9.5+
DT1 <- setDT(df1)[, list(time=seq(min(time), max(time), by ='day')),
                    by =.(shop_id, item_id)]
setkeyv(df1, names(df1)[1:3])[DT1][is.na(value), value:=0]
#   shop_id item_id       time value
#1:     150       1 2015-07-10     3
#2:     150       1 2015-07-11     5
#3:     150       1 2015-07-12     0
#4:     150       1 2015-07-13     2
#5:     150       2 2015-07-10    15
#6:     150       2 2015-07-11     0
#7:     150       2 2015-07-12    12

In the devel version, you can also do this without setting the ‘key’. Instructions to install the devel version are here

 df1[DT1, on =c('shop_id', 'item_id', 'time')][is.na(value), value:=0]
 #   shop_id item_id       time value
 #1:     150       1 2015-07-10     3
 #2:     150       1 2015-07-11     5
 #3:     150       1 2015-07-12     0
 #4:     150       1 2015-07-13     2
 #5:     150       2 2015-07-10    15
 #6:     150       2 2015-07-11     0
 #7:     150       2 2015-07-12    12

Or as @Arun suggested, a more efficient option would be

 DT1[, value := 0L][df1, value := i.value, on = c('shop_id', 'item_id', 'time')]
 DT1

More Related Contents:

  1. 'Must declare the scalar variable "@thisuser".' [closed]
  2. SQL Server Char/VarChar DateTime Error [closed]
  3. summing values of a column based on id [closed]
  4. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  5. How to get last inserted id?
  6. MSSQL Error ‘The underlying provider failed on Open’
  7. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  8. How to monitor SQL Server table changes by using c#?
  9. What is the connection string for localdb for version 11
  10. SQL WHERE clause matching values with trailing spaces
  11. SqlConnection SqlCommand SqlDataReader IDisposable
  12. Mapping composite keys using EF code first
  13. SqlBulkCopy from a List
  14. WHERE IN (array of IDs)
  15. SqlConnection.Close() inside using statement
  16. ExecuteScalar vs ExecuteNonQuery when returning an identity value
  17. Accessing directly a Sql Server Database in Xamarin.Forms
  18. SqlBulkCopy Insert with Identity Column
  19. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  20. How to pass string array in SQL parameter to IN clause in SQL
  21. Horrible performance using SqlCommand Async methods with large data
  22. Unable to load SqlServerSpatial.dll
  23. “The operation is not valid for the state of the transaction” error and transaction scope
  24. Database Error: There is no row at position 0
  25. How do I safely store database login and password in a C# application?
  26. Passing array to a SQL Server Stored Procedure
  27. Bulk Insert Sql Server millions of record
  28. Loading PictureBox Image From Database
  29. EF Core takes a lot of time, sometimes, to perform SELECT query
  30. Capturing count from an SQL query

Leave a Comment