This article describes how to manually enter or paste data from other sources, such as Excel. Copying and pasting in data works when you have raw data sets (use Method 1 - Paste as a source in the Data Sources tree) as well as summary tables (where data have already been calculated or where the table is used as a lookup/reference). Note that pasted data is the formatted text shown when copied, and the underlying precision of numbers is not preserved. If you need this precision in your raw data, you can import the data from Excel instead of pasting it in. If you also have lots of tables you want to add at once, you can import all the tables at once from an Excel file.
For example, you can paste in a table like is shown below directly into your report using Method 2 below:
This article covers:
Method 1 - Paste as a source in the Data Sources tree
Method 2 - Paste a table directly into your Report
Method - Specifying the type of data pasted in
Method - Adjusting the look of your pasted summary table
Requirements
- A Displayr document
- Data in another location, such as an Excel workbook
Method 1 - Paste as a source in the Data Sources tree
This method can be used for both raw data as well as summary tables.
-
From the Data Sources tree, click + to add a new data source.
- Click Paste or Enter Data.
- In the spreadsheet window that pops up, manually enter in or use Ctrl+V (Cmd + V on a Mac) to paste in your data and click OK. Note that pasted/entered tables are limited to 5,000 rows and 250 columns.
- By default, Displayr will automatically detect the type of data you pasted in: 1) a raw data set with variables vs 2) a summary table.
-
Your new data source will appear in the Data Sources tree and will denote what type of data it is, such as the data.set or summary.table.1 in the pic below. If the type detected is not what you wish, see Method - Specifying the type of data pasted in.
- Drag the summary.table data source or a variable from the data set onto a Page or into the Report tree to create a table output in your report.
- [Optional] If your data source is a summary.table, you can modify what is treated as row and column headers in your table, sorting, and more, see Method - Adjusting the look of your pasted summary table.
Method 2 - Paste a table directly in your report
This method is good for adding ad hoc tables of data that have already been calculated (like pivot tables) or for tables that are used as a lookup/reference for other outputs. If you want to use the table as a raw data set with variables, use Method 1.
- In the other data source, such as Excel, copy your table, including any headers.
- Navigate to the location you want to paste in the table (either on a Page or an output off a Page directly in the Report tree) and use right click > Paste to create the output. If pasting a table on a Page, you can also use Ctrl+V (Cmd+V on a Mac).
- A table output will be created where you selected, as well as a summary.table in the Data Sources tree.
- [Optional] If needed, you can modify what is treated as row and column headers in your table, sorting, and more, see Method - Adjusting the look of your pasted summary table.
Method - Specifying the type of data pasted in
If you use Method 1 above via the Data Sources + button, you can force the data being pasted in to be a certain type, either a summary table or a raw data set.
This is done in the advanced settings in the Data Sources > + or Add data + dialogue. To do so:
- Follow Method 1, but before going to Step 2, expand the Advanced data file options menu.
- Change the Data format field to your desired setting:
- Detect automatically - runs checks to determine the data format.
- Summary table - imports the data as a summary.table source that you can use as a reference or to display directly in your report.
- Data set - imports the data as a data.set source with raw data where each column is a variable.
- Proceed with the rest of the steps.
Method - Adjusting the look of your summary.table output
Note these are options for how to modify tables created when the Data Source is a summary.table. For summary tables created from variables, see the Summary Tables and Crosstabs Video.
Modifying data selections
Each summary.table data source has settings to choose the range of data available in table outputs and which cells of the data have headers. Click on the summary.table in the Data Sources tree, and the object inspector pops up with these settings on the Data tab. The Data source section lists which cell ranges of the pasted data have the title, data, and caption information. You can also click Edit Data to change any of the pasted data.
Using the Sub-Selection > Select data window (shown below), you can visually see which cells of your pasted data are used in tables and which of those are used as headers in tables. Click the top left corner to select all rows and columns, or click and drag the correct data to keep in the table.
If you have two statistics in your table but only want to show one, or it has included more rows or columns than are required, for example, you can select the relevant rows and columns by holding down CTRL and clicking the correct ones.
If there are a different number of column or row headers than automatically detected, adjust the Header row(s) and Header column(s) drop-downs on the right accordingly.
If you have multiple levels of headers, for example, for Country and State, this will then concatenate the labels from both into a combined label for your table. You can also set these fields to 0 to not include headers from the data if desired, and instead modify headers on the table itself using the Table options.
Once you've made any changes, click Save.
Table options
Table options are listed in the object inspector when selecting a table output and only affect the selected table. On the Data tab, these include:
-
Data Manipulation section, which can tidy labels and adjust data formats.
- Automatically tidy the data - performs a variety of cleaning operations, such as removing missing values, and changing the underlying type of the data table to convert it into a numeric form useful for charting.
- Tidy labels - extracts common prefixes from the row labels or vector names to shorten the labels in the chart.
- Row Manipulations and Column Manipulations sections, which control which rows/columns are shown, how they're sorted, and where you can override any header labels.
- Autofit - This option is found by clicking Show Advanced Options and is in the Output section. Checking Autofit will resize the table to fit into available space in its container on the page, and adds a new Format tab in the object inspector where you can set the style for parts of the table.
On the Appearance tab, settings include:
- Appearance section which includes font, number, and table formatting. (These are overridden by settings in the Format tab when Autofit is checked.)
- Caption section which controls the footnote shown.
- Office Export and Update sections are where you configure the formatting of the table when exported.
Technical Notes
- You may enter a table up to 5,000 rows and 250 columns. If you need to enter more data, then upload it as an Excel/CSV file.
- An attempt will be made to interpret the entered data as a numeric vector or matrix. The numeric vector may or may not contain named elements, while the numeric matrix may either be unnamed or have row and column names. If this attempt fails, a character vector or matrix is produced instead.
- Note that the data does not need to be entered at the top left of the spreadsheet, as any empty rows and columns will be automatically removed.