editbl (‘edit tibble’)
allows you to modify tables in a spreadsheet-like fashion. Not just
in-memory data.frame objects, but also data living in a
database.
install.packages('editbl')remotes::install_github("https://github.com/openanalytics/editbl", ref = "main", subdir = "editbl")Choose a dataset of your liking and use eDT to
interactively explore and modify it!
modifiedData <- editbl::eDT(mtcars)
print(modifiedData)Run some demo apps
editbl::runDemoApp()
More introductory examples can be found here. Advanced examples can be found in the vignettes.
tbl_dbi class and
non in-place editing).Sometimes you want to restrict certain columns of your table to only contain specific values. Many of these restrictions would be implemented at database level through the use of foreign keys to other tables.
editbl allows you to specify similar rules through the
use of foreignTbls as an argument to eDT().
Note that you can additionally hide surrogate keys by the use of
naturalKey and columnDefs if you wish to.
a <- tibble::tibble(
first_name = c("Albert","Donald","Mickey"),
last_name_id = c(1,2,2)
)
b <- foreignTbl(
a,
tibble::tibble(
last_name = c("Einstein", "Duck", "Mouse"),
last_name_id = c(1,2,3)
),
by = "last_name_id",
naturalKey = "last_name"
)
eDT(a,
foreignTbls = list(b),
options = list(columnDefs = list(list(visible=FALSE, targets="last_name_id")))
)dplyr code is used for all needed data manipulations and
it is recommended to pass on your data as a tbl. This
allows editbl to support multiple backends through the usage of other
packages like dtplyr, dbplyr etc.
In case you pass on other tabular objects like
data.frame or data.table the function will
internally automatically cast back and forth to tbl. Small
side effects may occur because of this (like loosing rownames), so it
might be better to cast yourself to tbl explicitly
first.
# tibble support
modifiedData <- editbl::eDT(tibble::as_tibble(mtcars))
# data.frame support
modifiedData <- editbl::eDT(mtcars)
# data.table support
modifiedData <- editbl::eDT(data.table::data.table(mtcars))
# database support
tmpFile <- tempfile(fileext = ".sqlite")
file.copy(system.file("extdata", "chinook.sqlite", package = 'editbl'), tmpFile)
conn <- editbl::connectDB(dbname = tmpFile)
modifiedData <- editbl::eDT(dplyr::tbl(conn, "Artist"), in_place = TRUE)
DBI::dbDisconnect(conn)
unlink(tmpFile)
Note that there are some custom methods in the package itself for
rows_update / rows_delete /
rows_insert. The goal would be to fully rely on
dplyr once these functions are not experimental anymore and
support all needed requirements. These functions also explain the high
amount of ‘suggested’ packages, while the core functionality of
editbl has few dependencies.
Let’s say you already use DT::datatable() to display
your data, but want to switch to editbl::eDT() to be able
to edit it. Would this be a lot of effort? No! In fact,
eDT() accepts the exact same arguments. So it is almost as
easy as replacing the functions and you are done. Should you run into
problems take a look here
for some pointers to look out for.
eDT with dtplyr. If
possible convert to normal tibble first.editbl assumes that all rows in your table are
unique. This assumption is the key (ba dum tss) to allow for
only having the data partially in memory.editbl does not attempt to detect/give notifications on
concurrent updates by other users to the same data, nor does it ‘lock’
the rows you are updating. It just sends its updates to the backend by
matching on the keys of a row. If other users have in the meantime made
conflicting adjustments, the changes you made might not be executed
correctly or errors might be thrown.dplyr compatibility so support for different
backends is easily facilitated. Now there are 2 methods
(e_rows_update, e_rows_insert) that need to be
implemented to support a new backend.DT compatibility, including all extensions.These are other popular CRUD packages in R. Depending on your needs, they might be better alternatives.
DT extension