DataEditR is an R
package that makes it easy to interactively view, enter, filter and edit
data. The data editor is a shiny application built on the MIT-licensed
Tabulator JavaScript library, so it
stays fast with large datasets, and it can edit tables stored in a
database as well as data in your R session. In this vignette we will
explore the key features that are available through the
data_edit() function.
In order to get started with DataEditR, we will need to install the package from CRAN and load it into our current R session:
If you would like to use the development version of DataEditR it can be installed directly from GitHub:
Simply supply your data in the form of a matrix, data.frame, tibble
or data.table to data_edit() to view it in an interactive
table. For example, if we wanted to take a look at the
mtcars dataset:
The data editor has four parts:
cars <- mtcars %>% ...) and exported files are named
after it;Summary),
select the columns to show (Select), filter rows
(Filters, with the active filters next to it), see a row as
a form (Form), review changes (Review), see
the code that reproduces your edits (Code), export the data
and see the keyboard shortcuts;+ above them to add a row or a column), a type
badge (abc for text, 123 for numbers,
date for dates, dttm for date-times,
time for times, T/F for logicals) next to each
column name, a summary of each column (see Column summaries) and a filter box under
each column name;The data editor will open in an RStudio "dialog" box by
default, but can be optionally displayed in the RStudio viewer pane or a
web browser by setting viewer to "pane" or
"browser" respectively. The size of the dialog box is
controlled by viewer_width and
viewer_height.
Row names (such as the car names in mtcars) are moved
inside the table to a frozen row names column so that they
can be edited, and the row indices are always displayed on the left-hand
side to aid navigation. Only the visible rows are drawn in the browser,
so scrolling through 100,000 rows is as smooth as scrolling through
10.
Once you are finished, close the data editor by hitting the
Done button in the top right corner and the edited data
will be returned. If you wish to discard all changes made to the data,
select the Cancel button in the top left corner and the
unedited data will be returned instead (or the value supplied to the
cancel argument).
Under each column name is a small summary of the column, worked out from all of its rows (not only those on screen):
TRUE/FALSE columns and columns with a few
distinct values, as a bar split by how often each value appears;Hover over a bar to see what it counts. Click a bar to show only those rows, click the share of missing values to show the rows where the value is missing, or click the number of distinct values for the details: the smallest, largest, mean and median values and the standard deviation of numbers, or the earliest and latest dates.
The summaries are updated as you edit. Hide them with the
Summary button in the toolbar (the editor remembers your
choice), or set col_summary = FALSE to leave them out.
Large data can be shown a page at a time. Click Pages in the
status bar and choose how many rows and how many columns to show at
once; the status bar then shows which rows and columns are on screen,
with buttons to move to the previous, next, first and last page. Set
page_rows and page_cols to open the editor in
pages:
Pages only change what is on screen. Filters count the rows of every page, Find in data goes to the page of each match, a paste wider than the page fills the columns of the next page, a new row is shown on the last page, and Done, Export and “Only what’s on screen” cover every page.
data_edit() can read in any form of tabular data from
file for viewing and editing. By default data_edit() will
use read.csv from the utils package to read in
files, but this can be changed to any reading function by supplying the
name of the function to the read_fun argument. If you need
to pass any additional arguments to your reading function, these can be
supplied as a named list to the read_args argument.
mtcars <- data_edit("mtcars.csv",
read_fun = "read.csv",
read_args = list(header = TRUE,
row.names = 1))
head(mtcars)#> mpg cyl disp hp drat wt qsec vs am gear carb
#> Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
#> Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
#> Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#> Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
#> Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
#> Valiant 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1
The name of an object in your R session can also be supplied as a
character string, e.g. data_edit("mtcars").
The Open menu in the toolbar switches to other data
without having to close the data editor. You can load any data frame,
matrix or dbplyr table from your R session by name, upload
a file (read with read_fun and read_args) or
create a new empty table with a chosen number of rows and columns.
Opening other data replaces the data in the editor, and
Done returns the data that is open at the time.
data_edit() reads SPSS (.sav,
.por), Stata (.dta) and SAS
(.sas7bdat, .xpt) files with the
haven package, from a file name or from the
Open menu, and edits data that was read with
haven (or labelled with the labelled or
sjlabelled packages) without losing its labels:
# Read and edit an SPSS file, and save it again with its labels
data_edit("survey.sav", save_as = "survey.sav")
# Or edit data read with haven
survey <- haven::read_sav("survey.sav", user_na = TRUE)
survey <- data_edit(survey)Form tab.1 Male), and columns with value labels have an
lbl badge. The cell editor lists the labels, and you can
type or paste a label (Female) instead of its value
(2). Other values can still be typed.-99 No answer) are shown in grey italics, count as
missing in the column summaries and are kept as values.Choose Labels... from a column’s menu to change its
variable label or its value labels, written one per line as
value = label. The dialog shows how many rows have each
value, and the change is recorded as one step in the code:
survey <- survey %>%
mutate(sex = structure(sex, labels = c("Male" = 1, "Female" = 2, "Refused" = 9, "Other" = 3)))Change type turns a labelled column into a factor or
text using its labels (haven::as_factor()), or into plain
numbers (haven::zap_labels()). The returned data keeps the
variable labels, value labels and missing values, and so does the
generated code. When save_as ends in .sav,
.dta or .xpt the data is written with
haven, and the Export menu can download the
data as an SPSS or Stata file.
Columns that hold a list per row, such as the geometry of an
sf object, are shown as text
(e.g. MULTIPOLYGON (((-81.47 36.23, ...) and cannot be
edited, while the other columns can. sf objects are
returned as sf objects with their geometry, so the
attributes of spatial data can be edited in place:
Data without any rows opens with one empty row to type in. The row is only returned once something has been entered in it.
data_edit() provides a variety of spreadsheet-style
tools to edit your data. We will explore the use of each of these tools
below, using mtcars as an example:
Many of the tools can also be reached through the keyboard; press
? inside the data editor or click the ? button
in the toolbar to see the full list of shortcuts (see Keyboard shortcuts).
Double-click a cell to edit it, then press Enter to save
the new value or Esc to cancel. Values are checked against
the type of the column: numbers are stored as numbers, dates as dates
and TRUE/FALSE as logicals. Typing
NA or clearing a cell stores a missing value.
Values typed in other common formats are converted for you:
1,234.5 or 45% in a number column,
15/03/2024 in a date column (see Copy and paste with
spreadsheets for how the order of day and month is decided) and
yes or no in a
TRUE/FALSE column.
If you type something that does not fit a number, date or
TRUE/FALSE column, data_edit()
asks what you would like to do instead of silently converting the whole
column to text. You can store a missing value, convert the column to
text or cancel the edit.
Click and drag (or Shift + click) to select a range of
cells. The status bar shows the sum, mean and count of the selected
numbers, just like a spreadsheet. Selected cells can be copied
(Ctrl+C) and pasted (Ctrl+V) within the data
editor or to and from other software such as Excel, cleared with
Delete, or filled down with the value in the first row of
the selection (Ctrl+D). A paste, fill or clear is a single
step that can be undone. Click a column header to select the whole
column.
To fill cells like in a spreadsheet, drag the small square at the bottom-right corner of the selection down, up or across. The cells it passes over are outlined and filled when you let go:
1, 2 gives
3, 4, 5, a week of dates gives the following weeks, month
ends give the following month ends and Week 1, Week 2 gives
Week 3, Week 4.TRUE, FALSE, TRUE, FALSE.Ctrl when you let go to copy the selection without
carrying on a series.Like a paste, a fill is one step that can be undone and values that
do not fit a column are asked about first. Ctrl+D (or
Fill down in the right-click menu) copies the first row of
the selection into the rows below it.
Copy cells in Excel, Google Sheets, LibreOffice or any other software
that copies tables as text, click a cell in the data editor and press
Ctrl+V:
row_edit = FALSE). If it has more columns than there are to
the right, columns named V1, V2, … are added
at the end (unless col_edit = FALSE), in the same
step.Ctrl+Shift+V instead to use the first row of the
block as column names: the columns the block lands in are renamed
(unless their names cannot be edited) and the columns it adds are named
after it.1,234.5, $12.50 or 45%
become 1234.5, 12.5 and 0.45.
Dates such as 15/03/2024, 15.03.2024 or
2024/3/15 become 2024-03-15: whether the day
or the month comes first is worked out from values such as
15/03 that can only be read one way, and otherwise follows
the language of your browser (month first for US English).
yes/no, TRUE/FALSE
and 1/0 become logicals.n/a in a numeric column, or a value that is not one of the
options of a dropdown menu), the data editor asks once for the whole
paste whether to store them as missing values, convert the column to
text or cancel the paste.Ctrl+C copies the selected cells both as text and as a
table, so they can be pasted into spreadsheets, documents or back into
the data editor. Missing values are copied as empty cells, logicals as
TRUE and FALSE, and cells containing line
breaks or tabs are quoted, as spreadsheets expect.
Every change can be undone with Ctrl+Z (or the undo
button in the toolbar) and redone with Ctrl+Y (or the redo
button). This includes edits, pasted ranges, cleared or filled cells,
added or removed rows and columns, renamed columns and changes reverted
from the review panel. A paste, fill or clear of many cells is undone in
a single step, as shown at the end of the animation above. Undo and redo
are also available from the right-click menus.
Rows or columns can be added to the data from within the data editor by right clicking on a cell or a column header. Right-clicking a cell offers to insert rows above or below, delete the selected rows, fill down and insert a column to the left or right of the cell; right-clicking a column header offers to rename, insert, hide or delete columns.
New rows and columns can also be added at the end of the data from
the + in the corner above the row numbers: choose
Add row or Add column. New columns start empty
and their type is worked out from the values you enter when the data is
returned.
To move cells rather than whole rows, select the cells and choose
Insert cells, shift down from the right-click menu: empty
cells are inserted and the cells below them, in the selected columns
only, move down (a row is added at the end when values would otherwise
fall off). Delete cells, shift up removes the selected
cells and moves the cells below them up. Both are one step that can be
undone; clear the filters first, so that cells are not moved past rows
that are hidden.
To prevent users from adding or removing rows or columns, set
row_edit = FALSE or col_edit = FALSE
respectively.
data_edit() has full support for editing row and column
names. Double-click the name of a column (or choose
Rename column from its menu) to rename it; the new name
must be unique and cannot be empty. Row names are edited like any other
cell in the row names column, and must be unique: a name
that another row already has is refused and the cell goes back to its
old name. The row indices cannot be edited. To keep the row names out of
sight (they are still returned), set row_names = FALSE.
To prevent users from editing column names, set
col_names = FALSE or supply the names of the columns that
cannot be renamed. For example, if we only wanted to prevent users from
renaming the mpg column:
To resize columns, go to the right-hand border of the header of that
column and drag the cursor to the desired width (shown at the end of the
animation above). Columns keep their width while you edit, including
when changes are undone or redone. Columns start as wide as their
values, but no wider than 320 pixels, so long text does not push the
other columns off screen. Set the starting widths in pixels with
col_widths, either one width for every column or widths by
column name:
To make the columns fill the width of the editor instead, see Stretch columns to fill page.
Press Ctrl+F or click Find in data in the
status bar to search the rows and columns on screen. The first matching
cell is selected and the number of matches is shown
(e.g. 1 of 7); press Enter to jump to the next
match, Shift+Enter to go back and Esc to clear
the search.
data_edit() can perform rbind and
cbind operations internally to append new rows or columns
to the data prior to editing. The new rows or columns can be supplied as
a matrix or data.frame, a named list or, for empty rows or columns, a
vector of names to the row_bind or col_bind
arguments. If binding both rows and columns, it is important to note
that rows are bound before columns.
# New column to add
new_col <- matrix(rep(NA, nrow(mtcars)),
ncol = 1,
dimnames = list(NULL, "test"))
# Edit data with new column added
data_edit(mtcars,
col_bind = new_col)
# Add two empty rows and an empty column called "notes"
data_edit(mtcars,
row_bind = c("New car 1", "New car 2"),
col_bind = "notes")data_edit() fully supports data frames with factor
columns, including ordered factors. Factor columns are edited as text,
so you can freely enter new values without encountering errors, and are
returned as factors with their original levels in their original order.
New values become new levels after the existing ones, and levels that
are no longer used are kept, as R does when values of a factor are
replaced.
# Create data with a factor column
df <- data.frame(
name = c("Alice", "Bob", "Charlie"),
group = factor(c("A", "B", "A"))
)
# Change Bob's group to "C" in the editor
df_edited <- data_edit(df)
levels(df_edited$group)
#> [1] "A" "B" "C"Character columns are returned as text. Set
col_factor = TRUE to turn them into factors as well (their
levels are sorted, as factor() does).
Click the ⋮ button in a column’s header (or right-click
the header) for actions that change the whole column at once. Each
action opens a dialog that previews the result before you apply it:
round(mpg * 0.425, 1), paste(first, last)
or
case_when(ph < 7 ~ "acidic", ph > 7 ~ "basic", .default = "neutral").
Formulas can use column names, numbers, text in quotes and common
functions (arithmetic, round(), ifelse(),
paste(), substr(), as.Date(),
case_when(), if_else(), lag(),
row_number(), …), but not other R code, so they are safe to
offer in shiny apps.Find empty to fill missing values, or
Replace with empty to clear the cells.TRUE/FALSE, date or factor. If some values
cannot be converted (e.g. "x" to a date), you are told
which ones before they become missing.Each action is recorded as a single dplyr verb, so the
code that reproduces your edits (see Exporting
Code) says what you did, rather than listing every value that
changed:
samples <- samples %>%
mutate(ph_class = case_when(ph < 7 ~ "acidic", ph > 7 ~ "basic", .default = "neutral"), .after = ph) %>%
mutate(site = case_match(site, "North" ~ "north", .default = site))The editor applies an action by running that same verb on your data,
so the code always gives exactly the result you saw. Actions can be
undone like any other change, and appear in the History tab
with their code. Set col_actions = FALSE to remove them
from the column menu.
Wide tables are easier to fill in one record (row) at a time. Click
Form in the toolbar, or the Form tab of the
side panel, to see the selected row as a form with a labelled field for
each visible column:
TRUE/FALSE, …) and the same checks, so a value
that does not fit its column asks whether to store NA.Enter to save a field and move to the next
one.New adds a row at the end and puts the cursor in its
first field.Changes made in the form are ordinary edits: they can be undone, are listed in the review panel and appear in the code.
You can also use data_edit() to interactively create
data.frames from scratch without any coding.
If no data is supplied to data_edit() an empty
data.frame will be constructed with 10 rows and columns. You can then
build your data.frame from scratch by adding new rows and columns and
annotating the cells.
The dimensions of the template can be controlled by supplying a
vector of the form c(nrow, ncol) to indicate the number of
rows and columns to use.
Alternatively, you can pass a vector of column names to create a
data.frame template with pre-defined column names. Use the
+ above the row numbers (Add row) to add as
many rows as you need.
The columns of a template are typed automatically when the data is returned, so a column in which you only entered numbers is returned as a numeric column.
data_edit() makes it easy to view and edit a subset of
your data: filter rows by typing under the column headers or in the
Filters panel, and show or hide columns from the
Select menu. Edits made to a subset are applied directly to
the complete dataset.
Every column has a filter box under its name and the box depends on the type of the column:
min and max
boxes to keep values within a range;cyl or gear) have a list to pick one or
more values (Any means no filter);! (e.g. !set) to keep the
values that do not contain it.Clicking a bar of a column’s summary (see Column summaries) sets its filter for you,
and clicking the share of missing values shows only the rows where the
value is missing. Rows must match every filter to be shown. Active
filters are listed as chips in the toolbar; click the x on
a chip to remove that filter or Clear filters to remove
them all. With more than four filters the toolbar shows the first three
and a +2 more button that opens the Filters
panel. The status bar shows how many of the rows are on screen.
Click Filters in the toolbar to see and change every
filter in one place, which is the easiest way to build a complex
combination of filters. The panel opens beside the grid, and the grid
follows it as you work: there is nothing to apply.
Click Add filter and pick a column: type to find it, and
use the arrow keys and Enter or click it. Each filter is a
card that asks for what suits its column:
Page Up, Page Down,
Home and End), or type them in the boxes below
it. The bars of the rows that are kept are highlighted.TRUE,
FALSE and missing buttons with their
counts.Enter, or pick one of the values of the column listed under
the box with how many rows have each (the most common when there are
many). Rows that contain any of the words are kept. A pattern can start
from one of those values too.The menu at the top of a card changes the condition, and a column can have more than one card:
| Condition | Keeps the rows where the value | Columns |
|---|---|---|
is any of, is none of |
is (or is not) one of the values | all |
is above, is below,
is at least, is at most |
compares that way with the value | numbers, dates, times |
is between, is not between |
lies (or does not lie) between two values | numbers, dates, times |
contains, does not contain |
includes (or does not include) any of the words | all except logicals |
matches the pattern,
does not match the pattern |
matches (or does not match) a regular expression | all except logicals |
is missing, is not missing |
is (or is not) missing | all |
is between includes both ends, like
dplyr::between() and the boxes under the column headers,
and the ends can be given in either order. Use is above and
is below to leave them out.contains looks for plain text and ignores case, so
merc finds Merc 230. It searches what the
cells show: the names of dropdown choices and the text of HTML columns
without their tags. matches the pattern takes a regular
expression and respects case, so ^Merc [0-9]+$ finds
Merc 230 and not Merc 450SE. The patterns are
JavaScript regular expressions, so classes such as
[[:alpha:]] are written [a-zA-Z].is none of, is not between,
does not contain and
does not match the pattern also keep the rows where the
value is missing.Each card says how many rows it keeps on its own, and the bottom of
the panel how many rows are shown. A card that is
not set yet, or has a mistake (text where a number is
needed, a pattern that is not a regular expression), is left out until
it is finished. Click the name of a column to fold or open its card, the
x to remove it, and Clear all to remove every
filter.
With two filters or more, choose whether rows must match
all of them (the default) or any of
them. In the toolbar, filters that match any are listed after
Any of:.
Filters set under the column headers or from a column’s summary are
listed in the panel too (!text in a header box as
does not contain), with where they come from. They stay
where they are while you leave them unchanged; if you change one, it
becomes a filter of the panel and its box under the header is cleared.
Choosing any makes them filters of the panel as well, so
that they are part of the “or”.
Click Select in the toolbar to show or hide columns, or
choose Hide column from the menu of a column header.
Show all and Hide all change every column at
once, and the button shows how many columns are visible.
Columns can also be hidden before the data editor opens
(col_hide, see Read-only and hidden columns);
those columns are not listed in the Select menu and cannot
be shown by the user.
Filtered rows and hidden columns are only views onto your data, so any change you make while a filter is active is made directly to the complete dataset, without requiring any additional action (earlier versions of DataEditR needed a sync button for this). Rows added while a filter is active stay visible until the filters change, so you can fill them in even if they do not match the filters yet.
data_edit() will return the complete dataset as an R
object for downstream use by default. If you would like to return just
the rows and columns that are on screen, open the menu next to the
Done button and choose Only what's on screen
before pressing Done. The menu shows the size of the data
that each option returns.
#> 'data.frame': 14 obs. of 7 variables:
#> $ mpg : num 21 21 22.8 21.4 24.4 22.8 32.4 30.4 33.9 21.5 ...
#> $ cyl : num 6 6 4 6 4 4 4 4 4 4 ...
#> $ disp: num 160 160 108 258 147 ...
#> $ hp : num 110 110 93 110 62 95 66 52 65 97 ...
#> $ wt : num 2.62 2.88 2.32 3.21 3.19 ...
#> $ gear: num 4 4 4 3 4 4 4 4 4 3 ...
#> $ carb: num 4 4 1 1 2 2 1 2 1 1 ...
data_edit() will automatically return the edited data
with appropriate column classes as an R object for use within R.
Matrices, tibbles and data.tables are returned with their original
class, and the type of each column is kept: numbers stay numbers, dates
stay dates and text that looks like a number (e.g. "001")
stays text. Factor columns are returned as factors with their levels,
while character columns are not converted to factors unless
col_factor = TRUE is set (see Factor columns).
# Add character column
mtcars_new <- cbind(rownames(mtcars), mtcars)
colnames(mtcars_new)[1] <- "car"
# Convert characters to factors
mtcars_new <- data_edit(mtcars_new,
col_factor = TRUE)
str(mtcars_new)#> 'data.frame': 32 obs. of 12 variables:
#> $ car : Factor w/ 32 levels "AMC Javelin",..: 18 19 5 13 14 31 7 21 20 22 ...
#> $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
#> $ cyl : num 6 6 4 6 8 6 8 4 4 6 ...
#> $ disp: num 160 160 108 258 360 ...
#> $ hp : num 110 110 93 110 175 105 245 62 95 123 ...
#> $ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
#> $ wt : num 2.62 2.88 2.32 3.21 3.44 ...
#> $ qsec: num 16.5 17 18.6 19.4 17 ...
#> $ vs : num 0 0 1 1 0 1 0 1 1 1 ...
#> $ am : num 1 1 1 0 0 0 0 0 0 0 ...
#> $ gear: num 4 4 4 3 3 3 3 4 4 4 ...
#> $ carb: num 4 4 1 1 2 1 4 2 2 4 ...
The edited data can also be written to a file of any format by
specifying the name of the file to the save_as argument and
specifying the name of the writing function to use through
write_fun. The default is to use write.csv
from the utils package. You can also pass any additional
arguments to your write function in a named list to
write_args. The data will be written to file when the
Done button is selected, and a message says where it was
saved; untick Also save to in the Done menu to
close the editor without saving. Data that has no row names (only row
numbers) is written without them unless write_args sets
row.names. read_fun and write_fun
can name functions of other packages,
e.g. read_fun = "readxl::read_excel", and
save_as does not have to be a file: with
write_fun = "googlesheets4::write_sheet" it can be a Google
Sheet.
mtcars_new <- data_edit(mtcars,
save_as = "mtcars.csv",
write_fun = "write.table",
write_args = list(sep = ",",
row.names = TRUE))Alternatively, data can be downloaded to file at any time from the
Export menu in the toolbar, either all data or only what is
on screen. Files are named after save_as (with
-subset added for subsets), or after today’s date if
save_as is not supplied, and are written with
write_fun and write_args.
data_edit() can also be used to accept complex inputs
from users, and these inputs can be restricted, by giving columns a
specific input such as checkboxes, dropdown menus, date pickers, numbers
within limits or ratings. To use this feature, the inputs for the
columns must be supplied in a named list through the
col_options argument. Columns with options can still be
renamed, and users can still add or remove other columns.
col_options entry |
Input | Returned as |
|---|---|---|
c(TRUE, FALSE) or "checkbox" |
checkbox | logical |
c("A", "B", "C") |
dropdown menu of the values | character (numeric for numbers in a numeric column) |
"dropdown" |
dropdown menu of the values in the column (the levels of a factor) | as the column |
"date" |
date picker | Date |
"datetime" |
date and time picker | POSIXct |
"time" |
time picker | character ("HH:MM") |
"textarea" |
text over several lines | character |
"password" |
hidden text | character |
"url" |
web address with a button to open it | character |
"colour" (or "color") |
colour with a swatch | character |
c(min = 0, max = 10, step = 0.5) |
number within limits (any of the three), in steps of
step from min |
numeric |
"integer" or
list(type = "integer", min = 0, max = 10) |
whole number (within limits) | integer |
"slider" or
c(type = "slider", min = 0, max = 1, step = 0.05) |
slider and bar for numbers within limits (0 to 100 by default) | numeric |
"rating" or
c(type = "rating", stars = 10) |
star rating (5 stars by default) | numeric |
Columns are converted to the type their input needs when the data editor opens, and values that do not fit a column’s input, whether typed or pasted, are caught before they are stored. Values already in the data that do not fit (for example a value above the maximum of a slider, or a value that is not one of the options of a dropdown menu) are kept but marked with a red line, and hovering over them says why.
Columns named in col_options, col_readonly,
col_hide or col_widths that are not in the
data are ignored, with a message, so the same settings can be used for
different data (for example in a shiny app where the data changes).
The limits of sliders and numbers and the number of stars of ratings can be customised by giving the type together with its settings:
data_edit(samples,
col_options = list(
# proportions from 0 to 1 in steps of 0.05
done = c(type = "slider", min = 0, max = 1, step = 0.05),
# a rating out of 10 stars
quality = c(type = "rating", stars = 10),
# numbers from 0 to 14 (type = "number" is optional here)
ph = c(min = 0, max = 14, step = 0.1)
))list(type = "slider", min = 0, max = 1, step = 0.05)
works too. A slider without settings runs from 0 to 100 in steps of 1,
and a rating has 5 stars.
The animations in this section use the following data, with all four
kinds of input and a read-only mpg column:
cars <- data.frame(
car = rownames(mtcars)[1:10],
mpg = mtcars$mpg[1:10],
fast = mtcars$mpg[1:10] > 21,
colour = c("red", "blue", "black", "green", "red",
"orange", "blue", "black", "green", "red"),
bought = as.Date("2024-01-15") + c(0, 31, 45, 90, 120, 151, 200, 230, 260, 301),
pin = c("4821", "1937", "5560", "0042", "7713",
"2468", "1357", "9090", "3141", "2718")
)
data_edit(cars,
col_options = list(fast = c(TRUE, FALSE),
colour = c("red", "blue", "orange", "green", "black"),
bought = "date",
pin = "password"),
col_readonly = "mpg")Checkboxes can be used to obtain TRUE or
FALSE value inputs from users. Double-click a checkbox cell
and click the box (or press Space) to change its value;
ticked cells are returned as TRUE. A new checkbox column
can be added to existing data with col_bind:
Dates can be entered interactively by selecting the relevant day from
a calendar. To convert a column to accept interactive date inputs,
supply a list with the name of the column and "date" as the
option to col_options. The column is returned as a
Date column.
data_edit(c("date", "time", "region", "temperature"),
col_options = list(date = "date", time = "time"))If you would like to keep your data entries a secret, you can convert
relevant columns to accept passwords as inputs. The data is entered as
per usual, but the values are displayed as dots to conceal the entered
data. To convert a column to accept password inputs, supply a list with
the name of the column and "password" as the option to
col_options.
The values are only hidden on screen: they are returned (and saved
with save_as) as plain text.
The animation below uses sample records with the other column types:
samples <- data.frame(
sample = c("S-01", "S-02", "S-03", "S-04", "S-05"),
notes = c("Clear.\nStored at 4\u00b0C", "Slightly cloudy", NA,
"Re-run\nfollowing QC", "Clear"),
received = c("2024-03-15 09:30:00", "2024-03-15 11:05:00",
"2024-03-16 08:45:00", NA, "2024-03-18 14:20:00"),
start = c("09:30", "10:15", NA, "13:00", "15:45"),
ph = c(7.1, 6.8, 7.4, NA, 7.0),
done = c(100, 60, 25, 0, 80),
quality = c(5, 3, 4, NA, 2),
protocol = c("https://www.protocols.io", NA, "https://www.r-project.org",
NA, NA),
label = c("#1D4ED8", "orange", "seagreen", "#E11D48", "purple")
)
data_edit(samples,
col_options = list(notes = "textarea",
received = "datetime",
start = "time",
ph = c(min = 0, max = 14, step = 0.1),
done = "slider",
quality = "rating",
protocol = "url",
label = "colour"))"textarea"):
the cell grows to show every line. Enter saves the text
like in any other cell, while Shift+Enter or
Alt+Enter starts a new line."datetime",
"time"): pickers for both. Date-time columns are returned
as POSIXct; times are returned as text such as
"14:30", which as.difftime() or the
hms package can convert. Columns that are already
POSIXct can be typed into without an option
(e.g. 2024-03-15 14:30).c(min = , max = , step = )): values outside the limits are
flagged, and the step sets how much the arrows of the
number box change the value."slider",
"rating"): a bar shows each value; double-click to drag the
slider (or use the arrow keys) or to click a star. Clear a cell with
Delete to store a missing value."url"): addresses
starting with http://, https:// or
mailto: get a button that opens them in a new tab;
double-click the cell to edit the address."html"): the cells are shown as
HTML,
e.g. Photo:<br/><a href="https://...">details</a>
as a line break and a link that opens in a new tab. Text formatting,
lists, links and images are shown; scripts, styles, forms and event
handlers are removed. Double-click a cell to edit its HTML."colour"): a swatch is shown
next to each colour, for R colour names such as "grey50" or
"steelblue" and hex codes such as "#1D4ED8";
values that are not colours get a dashed swatch. Double-click a cell to
type a colour or pick one: the swatches offer the colours already in the
column, R’s palette and the colour-blind-safe Okabe-Ito palette, and
Other colour... opens the browser’s colour picker. Set the
swatches with
e.g. list(type = "colour", palette = c("steelblue", "darkorange", "#009E73")).Column inputs stop values that do not fit a single cell. Rules check
the data as a whole: that values are filled in, unique or within limits,
or that columns agree with each other. Supply them to rules
as one-sided formulas that are TRUE for valid rows, named
with the message to show:
data_edit(samples,
rules = list(
"pH is between 0 and 14" = ~ ph >= 0 & ph <= 14,
"The end is after the start" = ~ end >= start,
"Every sample has an ID" = ~ !is.na(sample),
"Sample IDs are unique" = ~ !duplicated(sample)
))Rules are checked after every change. Cells that break a rule get a
red corner and a tooltip with the message, the status bar counts the
problems and the Problems tab lists them; click a row to go
to it. Missing values pass a rule unless it says otherwise, so
end >= start only checks rows where both dates are
filled in.
You can also add rules from a column’s menu (Rules...):
make the column required or unique, set the smallest and largest values
of numbers and dates, give a pattern that text must match (a regular
expression such as S-[0-9]{2}) or write a formula. The
dialog shows how many rows break the rules before you save them.
When rules are broken, Done asks whether to return the
data anyway or keep editing. In shiny apps,
dataEditProblems() returns the problems, for example to
disable a Save button until they are fixed.
Conditional formats colour cells by their values, to make the data
easier to read. They change how cells look, not the data. Supply them to
formats:
data_edit(mtcars,
formats = list(
# cells of mpg above 22 in green
"Fast" = list(~ mpg > 22, fill = "#DCFCE7", color = "#166534"),
# whole rows of V8 cars in yellow
"V8" = list(~ cyl == 8, fill = "lightyellow", columns = "all"),
# hp shaded from white (lowest) to red (highest)
list(scale = c("white", "#F87171"), columns = "hp")
))A rule is a one-sided formula that is TRUE for the rows
to colour, with a fill, a text color and/or
bold = TRUE (a formula on its own gets a yellow fill). It
colours the cells of the columns the formula uses; give
columns to colour other columns, or
columns = "all" to colour whole rows. A colour scale shades
the numbers of its columns from the first colour (the
lowest value) to the last (the highest); give three colours for a middle
colour, limits = c(0, 300) to fix the values of the end
colours and mid for the value of the middle one. Colours
can be R colour names or hex codes. When several formats colour a cell,
the later one wins.
Formats are updated after every change. You can also add them from a
column’s menu (Conditional formatting...): highlight the
cells of the column, or whole rows, where the value is greater or less
than a value, between two values, equal to a value, contains some text,
is blank, duplicated, above or below average, among the highest or
lowest, or makes a formula TRUE; or shade the column with a
colour scale. The dialog shows how many rows match before the format is
added, and lists the column’s formats with the code to add each one to
formats, so that it can be kept in your script.
data_edit() has been designed to be simple, but does
come with some customisation options including column stretching,
addition of logos and/or titles, custom themes and the option to hide
the menus.
If you would like to make full use of the space available to you, you
can set col_stretch = TRUE to stretch the columns to fill
the full width of the display.
data_edit() does also have support for adding a logo
and/or title to the data editor. For example, if you wanted to use
data_edit() within your own package you can customise it
with your package logo (the path or URL of an image) and
instructions to the user through title. Use
logo_size to set the width of the logo in pixels and
logo_side to place it on the "left" or
"right" of the title.
data_edit() uses Bootstrap 5 through the
bslib package. You can explore the different themes by
supplying the name of a Bootswatch
theme to the theme argument. Alternatively, you can create
your own custom bs_theme and supply that object to
theme instead.
# Preset theme
data_edit(
mtcars,
theme = "flatly"
)
# Custom theme
library(bslib)
custom_theme <- bs_theme(
version = 5,
primary = "#7C3AED",
base_font = font_google("Lato")
)
data_edit(
mtcars,
theme = custom_theme
)Set hide = TRUE to hide the Open and
Export menus and the options of the Done
button, for example when data_edit() is used to collect
data from users. When save_as is supplied the data is still
written to file when Done is pressed.
data_edit() keeps track of every change you make, so you
can see what has changed before you hit Done and revert any
change on its own.
The status bar summarises your changes
(e.g. 2 cells edited · 1 row added) and the
Review button shows how many changes there are. Click it to
open the review panel, which lists every edited, added, removed or
renamed cell, row and column with its old and new value. Click the name
of a row to jump to it in the grid, Revert (or
Remove / Restore) to undo that one change, or
Revert all to go back to the original data. Reverts can be
undone with Ctrl+Z like any other change.
Cells that have been edited or added compared to the original data are highlighted with a blue tint and border. Any cell whose value differs from the original, as well as all cells in newly added rows or columns, is highlighted. Highlights follow undo, redo and reverts, and inserting rows or columns does not cause other cells to appear changed.
You can pass any valid CSS color to track to customise
the highlight colour, or track = FALSE to turn the
highlighting off. For example, to use red:
The History tab of the review panel lists every change
in the order it was made, newest first: what was changed, by whom (hover
over the time) and how, so a paste, a fill, a cleared range, an undo or
a revert each show as one step. Undoing a change does not remove it from
the history; it is added as a new step, so the history is a complete
record of the session.
Click Add note under a step to say why the change was
made, for example where a corrected value came from. Press
Enter to save the note or Escape to leave it
as it was.
Supply the name of a csv file to log to keep the history
when you press Done. Each change becomes one row with the
data that was edited, the time, the
user, the step and how it was
made (and the code of column actions), the
action (edit, add row,
remove row, add column,
remove column or rename column), the
row (its row name, or its number in the data you opened),
the column, the old and new
values and the note. New changes are added to the end of
the file, so one file can keep the history of every editing session of a
dataset or a project:
lab <- data_edit("lab_results.csv",
save_as = "lab_results.csv",
log = "lab_results_log.csv")
log <- read.csv("lab_results_log.csv")
log[, c("time", "user", "how", "row", "column", "old", "new", "note")]
#> time user how row column old new note
#> 1 2026-11-01 14:02:11+1100 ana edit 3 pH 7.9 7.4 Re-measured, meter recalibrated
#> 2 2026-11-01 14:03:40+1100 ana paste 12 mass 1.2 1.25 <NA>Each editing session gets its own session value (when it
started), and the History tab lists the earlier sessions of
the same data kept in the file below the changes of this session, newest
first. Click a session to see its steps and notes:
log can also be a function, which is called with the log
as a data frame, for example to write it to a database. Nothing is
written when Cancel is pressed. The user name is the name
you are logged in with; set
options(DataEditR.user = "Your Name") to record another
name.
While you edit, data_edit() saves your changes as a
draft a moment after each change. If the editor is closed without
pressing Done or Cancel (the window was closed
by accident, R crashed or the computer restarted), open the same data
again and you are offered your unsaved changes:
Restore changes brings back the data as you left it,
with its history and notes, and each restored step can still be undone.
Discard changes throws the draft away. Pressing
Cancel asks before your changes are thrown away.
Drafts are kept in
tools::R_user_dir("DataEditR", "data") and are removed when
you press Done or Cancel, when they are
restored or discarded, and after 30 days. Use
options(DataEditR.drafts = "path/to/folder") to keep them
elsewhere, or autosave = FALSE to turn drafts off.
Change tracking is also available when the data editor is used in
your own shiny applications through dataEditServer().
dataEditChanges() returns a summary of the changes that
have not been saved yet, dataEditLog() returns the change
log and dataEditCommit() saves the changes (and returns the
log of what it saved), after which the highlights are cleared. Set
autosave = TRUE in dataEditServer() to keep
drafts for each user of the app:
library(shiny)
library(DataEditR)
ui <- fluidPage(
actionButton("save", "Save"),
textOutput("unsaved"),
dataEditUI("edit")
)
server <- function(input, output, session) {
edited <- dataEditServer("edit",
data = reactive(mtcars),
track = TRUE)
output$unsaved <- renderText({
paste(dataEditChanges(edited)$total, "unsaved changes")
})
observeEvent(input$save, {
dataEditCommit(edited)
})
}
shinyApp(ui, server)| Action | Shortcut |
|---|---|
| Edit cell | Double-click |
| Save / cancel edit | Enter / Esc |
| New line in a text area | Shift+Enter or Alt+Enter |
| Undo / redo | Ctrl+Z / Ctrl+Y |
| Select a range | Click and drag, Shift+click |
| Copy / paste range (also to and from spreadsheets) | Ctrl+C / Ctrl+V |
| Clear selected cells | Delete |
| Fill down | Ctrl+D |
| Find in data | Ctrl+F, then Enter /
Shift+Enter |
| Rename column | Double-click column name |
| Row and column menus | Right-click |
| Show the shortcuts | ? |
Use Cmd instead of Ctrl on a Mac.
DataEditR also ships with an RStudio add-in should you
prefer to interact with your data in this way. Simply highlight the name
of a dataset in an active script or the console and select
Interactive Data Editor from the list of
Addins in your RStudio session. If nothing is highlighted,
an empty table is opened instead. The add-in can be bound to a keyboard
shortcut through
Tools > Modify Keyboard Shortcuts....
data_edit() can also edit tables stored in a database.
Connect with DBI, pass a dbplyr lazy table
(optionally filtered) to data_edit() and the rows are
loaded into the editor. Changes are staged while you edit and written to
the database in a single transaction when you press Done
and confirm the summary of changes.
library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), "cars.sqlite")
data_edit(tbl(con, "cars") %>% filter(cyl == 6))The same editor can be added to your own shiny apps, where
dataEditCommit() writes the changes to the database (for
example when a Save button is pressed) and
dataEditChanges() summarises what will be written. See
vignette("databases", package = "DataEditR") for how to
connect to different databases, how rows are matched to the database,
saving changes from shiny apps and what happens when someone else
changes the data while you are editing it.
DataEditR can also be used to interactively edit
googlesheets using googlesheets4. Simply authorise
googlesheets4, create a new sheet, edit the data using
data_edit() and write the edited data to the created
sheet.
library(googlesheets4)
# Authorise
gs4_auth(
email = "[email protected]"
)
# Create a sheet
ss <- gs4_create(
"mtcars"
)
# Edit data
mtcars_new <- data_edit(
mtcars,
save_as = ss,
write_fun = "write_sheet",
write_args = list(sheet = "mtcars")
)For reproducibility, it is possible to export the code required to
generate the final edited version of your data. The generated code uses
tidyverse-style dplyr verbs to transform the original data
into the edited version.
Click Code in the toolbar to see the code that
reproduces your edits so far. The code updates as you edit and can be
copied with the Copy button. Column actions such as
formulas, find and replace, recoding, type changes and sorting (see Change whole columns) appear as one
dplyr verb each; other edits are listed as the values that
changed.
When rows are filtered or columns hidden, the code continues with the
rows and columns on screen, written as filter() and
select() so that you can take the subset into your script.
The filters under the column headers, those from column summaries and
those of the Filters panel (matching all or any of them)
are all included:
# The rows and columns on screen
mtcars_view <- mtcars %>%
filter(between(mpg, 15, 25), cyl %in% c(4, 6),
!grepl("merc", tolower(rownames(.)), fixed = TRUE)) %>%
select(mpg, cyl, disp, hp)The code is checked by running it on the data before it is shown.
When the filters cannot explain the rows on screen (rows added while the
data was filtered stay on screen until the filters change), the rows are
picked by their position with slice() instead, and the code
says so.
If you rename the data in the header, the code gives the result the
new name, e.g. cars <- mtcars %>% ....
Set code = TRUE within data_edit() to print
the generated code to the console when you press Done. The
code uses dplyr verbs such as rename(),
select(), mutate(), slice(), and
tibble::add_row() to replicate your edits. Copying and
pasting this code into the console will recreate the edited version of
your data. If you return only what’s on screen (see Return or export data
subsets), the code ends with the filter() and
select() that give those rows and columns.
For example, renaming a column, modifying a cell value, removing a row and adding a new row might produce code like:
library(dplyr)
mtcars <- mtcars %>%
tibble::rownames_to_column(var = ".rownames") %>%
rename(horsepower = hp) %>%
slice(-5) %>%
mutate(
mpg = replace(mpg, 1, 21.5),
horsepower = replace(horsepower, 3, 95)
) %>%
tibble::add_row(.rownames = "My car", mpg = 26, cyl = NA, disp = NA,
horsepower = NA, drat = NA, wt = NA, qsec = NA, vs = NA,
am = NA, gear = NA, carb = NA) %>%
tibble::column_to_rownames(var = ".rownames")For database tables the code uses rows_update(),
rows_append() and rows_delete() to make the
same changes to the database. If other data is opened from the
Open menu, the code starts from that data instead
(e.g. iris <- iris %>% ...), and a new table is
created from scratch.
Alternatively, users can get DataEditR to automatically
create a new R script to store this code by providing a name for the R
script to code. For example, the below code creates a new R
script called mtcars_edit.R and inserts the code required
to programmatically generate the final edited version of your data.
data_code() directlyThe data_code() function can also be used outside of
data_edit() to generate code from any pair of original and
edited data frames:
library(DataEditR)
# Original data
x <- data.frame(
A = c(1, 2, 3),
B = c("x", "y", "z"),
stringsAsFactors = FALSE
)
# Edited data (changed a value and renamed a column)
x_edit <- data.frame(
A = c(1, 5, 3),
Beta = c("x", "y", "z"),
stringsAsFactors = FALSE
)
# Generate code
cat(data_code(x, x_edit, name = "x"))
#> library(dplyr)
#>
#> x <- x %>%
#> rename(Beta = B) %>%
#> mutate(A = replace(A, 2, 5))The data editor is also available as a shiny module, so you can add
an editable table to your own shiny applications with
dataEditUI() and dataEditServer():
library(shiny)
library(DataEditR)
ui <- fluidPage(
dataEditUI("edit")
)
server <- function(input, output, session) {
edited <- dataEditServer("edit", data = reactive(mtcars))
observe(print(head(edited())))
}
shinyApp(ui, server)Call dataEditCommit(edited) (for example when a
Save button is pressed) to save the changes: for database
tables this writes them to the database, and for other data the current
data becomes the new starting point for the review panel.
dataEditChanges(edited) returns the number of changes that
have not been saved yet, e.g. to enable the Save button only
when there is something to save. See
vignette("shiny-modules", package = "DataEditR") for the
options of the module, saving changes and how it works with the other
modules in DataEditR.
DataEditR is a powerful and interactive tool to make your data viewing, entry, filtering and editing as seamless as possible. If you need any help or encounter any issues with DataEditR please let me know by posting an issue on GitHub.
3.14 Comments on cells
Right-click a cell and choose
Add comment(or select it and pressShift+F2) to leave a comment on it: why a value was changed, what still needs checking or where it came from. PressCtrl+EnterorSaveto keep the comment andEscapeto close it without saving. Commented cells have an amber corner; rest the pointer on the cell (or open the record form) to read the comment, and right-click it to edit or delete it. To start afresh, right-click any cell and chooseDelete all comments; the editor asks first, as deleted comments cannot be brought back with undo.Comments are returned with the data as the
commentsattribute, a data frame with therow(its number in the returned data), thecolumn, thecomment, theuserwho wrote it and thetime. Data that has this attribute opens with its comments, so they can be kept from one session to the next. Rows can also be given by their row names:Comments are kept in drafts with the changes, but they are not changes to the data: they are not undone and not listed in the review panel.