UPCOMING WEBINAR: Build it once. Use it again: Create reusable charts with AI For various reasons, as outlined in Find the Best Possible Data File, ideally, you would not be importing your data using an Excel file. However, in reality, sometimes this is unavoidable. This article describes how to format your Excel file (.xlsx or .csv) that contains raw data to aid Displayr in correctly importing and setting up your data.
Requirements
- A Displayr document.
- An Excel data set with raw data (i.e., not aggregated or tabular data)
- If you are working with aggregated or tabular data in Excel, see:
Method - Before importation
1. Each variable from your survey should have its own column (e.g., Age is in one column, Income is in another).
2. The first row of the Excel file should have the names for each variable. Ensure:
- Headings begin with letters or words, not numbers or symbols (#!?&)
- Headings describe what is in each column (e.g., Age or D1 - Age rather than just D1). This will make things a lot easier when the data is in Displayr.
- Not to name a heading one of R's common functions. Using one may conflict with some of our automations and cause unintended results. These names include: list, length, exists, assign, names, rep, any, stop, min, max, matrix, attr, sum.
3. Ensure there is only a single header row.
4. For multiple-response questions or grid-style questions, use a common prefix in the header row label to tell Displayr to group these as the same variable set.
5. Each person’s responses should be contained in a separate row with a unique identifier column included.
6. Do not leave any blank rows between respondents/records. Any blank rows may cause a break in the file, and respondents/records after it will not be loaded into Displayr.
7. Where possible, ensure that categories are stored as labels rather than numbers (e.g., use Male and Female instead of 1 and 2 in the column for the gender question).
8. Data should be stored as values and not created using formulas or references to other cells.
9. You should not have any merged cells in the table.
10. If loading data that was created internally or modified after export, ensure that blank rows and columns are truly blank. In Excel, this is akin to using Clear Contents vs right-click and Delete - you need to be sure to right-click and Delete any cells that may have had data or formatting on them previously.
11. When you have a multiple-response question, the data should be stored as one column for each of the possible options that were shown in the survey. Be sure to use the same two labels for selected and not selected for each of the options (Aware and Not Aware below). Avoid storing multiple responses within a single column, as this will make the data very difficult to analyze in Displayr.
12. When a respondent has not given an answer to a question or skipped the question, then they should have an entry of NA in that cell. When working with categories, other kinds of responses that indicate the respondent saw the question but did not give an answer are generally best given a label of Don’t know or whatever phrasing matches your questionnaire.
Note about importing Excel/CSV data files
If you are importing an Excel or CSV file, Displayr can automatically detect the option best suited for your data, but you have the option to select one of the following options:
- As summary tables: your data will be imported as summary tables and can be used to create tables, calculations, and visualizations. This option is best for files that contain aggregated data tables. See How to Import Excel Tables into Displayr for more information.
- As a data set: your data will be imported as a data set where each column is a variable, and the variables can be used as inputs for further analyses, calculations, and visualizations.
By default, Displayr will automatically detect the best importing option. You can adjust the data format importing option from within the Where is your data? window. Expand the Advanced data file options section and update the Data format to your desired option. See Data Formats in Displayr for examples if you aren't sure which option is best suited for your data.
If you wish to change the data format after importing, you will need to follow these steps:
- In the Data Sources tree, right-click the data set name and select Delete.
- Still within the Data Sources tree, click + or + Add Data.
- Expand the Advanced data file options section.
- Select a different Data format.
- Upload your file via the Upload file button or one of the other importing options.
Method - After importation
1. When Displayr sees a column full of text in the Excel file, it needs to make a decision about whether the text in the column represents a set of category labels or represents open-ended responses. Displayr makes an educated guess based on the number of unique responses in the column and the lengths of the responses. Sometimes Displayr can guess incorrectly, particularly if, for example, you have a really large brand list.
- In these cases, you can select your variable(s) in the Data Sources tree and go to Data > Attributes > Structure in Properties
to change the data format. For example, changing a variable from Nominal to Numeric will display an average instead of percentages, and changing Text to Nominal will display category percentages instead of raw text.
2. When you import categorical data as labels from Excel, it does not include any metadata to tell Displayr what values to assign each category. Values are therefore automatically assigned based on alphabetical order.
- If you wish to amend category values or labels, you can select your variable(s) in the Data Sources tree and, from Properties
- You can additionally edit variable labels directly in the Data Sources tree by right-clicking a variable and selecting Rename.
3. If variables are meant to be grouped, as in a multiple-response question or grid, you can group them as follows:
- Hold down your Ctrl key and from the Data Sources tree, select each of the variables that you want to group.
- Right-click > Combine, and the variables will be grouped into a single variable set.
- Expand the combined variable set in the Data Sources tree to view the individual variables.
Technical note
On import, Displayr automatically removes whitespace (i.e., leading and trailing spaces) from variable and value names and labels from Excel and CSV files.
Next
How to Import Data from Your Computer
How to Enter Data from Paper Surveys