Tips to Get an Excel or CSV File Working Well in Displayr

The following specifications are designed to minimize the amount of time taken to analyze data from Excel and CSV raw data files in Displayr. If you are looking to instead import tables of data from Excel that are already calculated, then see How to Import Excel Tables into Displayr. Please note that Excel and CSV files are, in general, a very poor format, and it is preferable to use one of the File Formats Supported by Displayr.

Tips for getting an Excel or CSV file working well in Displayr

• All of your data should be in one sheet, with no empty columns.
• Each variable asked should have its own column (e.g., age is in one column, education is in another).
• The first row should contain headings. Make sure they go by the following rules:
  • Begin with letters or words, not numbers.
  • Describe what is in each column (e.g., 'Age' or 'Q1. Age' rather than just 'Q1'). This will make things a lot easier when the data is in Displayr. See the image below for how multiple-response questions should be set up.
  • Do not 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.
• Make sure there is a separate row for each respondent or record.
• Make sure no cells in the table have been merged.
• Where possible, use labels instead of numbers (e.g., Male and Female instead of 1 and 2).
• Do not leave empty rows between rows of data. The only empty rows should come at the very end of the file after the end of the final record.

See the sections below for more detailed information about how to store particular types of data.

Here is an example of a nicely formatted file. Note that Q1a, Q1b, and Q1c all form one multiple-response question. Displayr will know to group these together as one table because they are all labeled Q1:

Outfile labels rather than values

In general, it is preferable to outfile labels rather than values. For example, if the questionnaire asked:

Q1c	Do you have a credit card?
	No
	Yes

It is generally better to outfile all the 'No' responses as No in the columns of the data file, rather than as 0's.

Non-Response and other types of missing data

Respondents who were not asked a particular question (i.e., were intentionally or unintentionally skipped), should have a NA. It is never appropriate to record all missing values in a data file as having a value of 0 (this is very important, as for many binary variables the No response is often coded as a 0, making it impossible to determine which respondents said No and which were not asked the question). Where there are multiple different types of missing data (e.g., where some questions were not asked of some respondents, while others were asked but not answered), they should be coded with different values (e.g., NA where not asked to respondents and -99 if asked but not answered. Sometimes it is appropriate to treat missing values for some of the questions as being equivalent to a No response (e.g., giving them a value of 0). For example, if people are asked which brands they have consumed, but are only shown brands that they are aware of. In this instance, the question should be included in the data file twice, once with the NA values and once with the “No” responses instead.

Don’t Know

The Don't Know code needs to be different from the non-response code.

Single Response Questions

Each single response question should be represented in the data file by a single variable (i.e., column). A data file that uses a different variable for each unique response code of a single response question is not useful.

Multiple Response Questions

Where there are multiple response variables, a binary variable should be created for each possible response. For example, a question in a questionnaire may have been:

Q1	Which of the following products do you own?
	MULTIPLE RESPONSE
	Savings account
	Checking Account
	Credit Card
	Home loan

but the data file should be structured as if you had asked the following four questions (coded as No = 0, Yes = 1, and not shown = blank in your file).

Q1a	Which of the following products do you own?  A savings account?
	No
	Yes

Q1b	Which of the following products do you own?  A checking account?
	No
	Yes

Q1c	Which of the following products do you own?  A credit card?
	No
	Yes

Q1d	Which of the following products do you own?  A home loan?
	No
	Yes

Multiple response questions with huge code frames

Some data files contain multiple response questions with extremely large code frames (e.g., 6,000 models of cars). Exporting these in the binary format results in data files that can be excessively large. In this situation, they can be exported in max-multi (binary-multi compact) format (i.e., essentially as multiple single response variables, each one recording a separate response). However, this format is, in general, much less flexible than the binary format and should be avoided where possible. In particular, with data in this format, there is no way of recording missing values (e.g., if a respondent is not shown as having selected an option, this may mean that they saw the option but did not choose it, or, it may mean that they were never shown the option).

Grids

Grid questions should contain names for variables that describe both the specific option being evaluated and also some common aspects of the wording. For example, the following labels are poor, because Displayr will not be able to recognize they should be grouped together:

Live a long life
Be rich
Have lots of friends

whereas these are much better:

How strongly do you agree that it is important to... Live a long life
How strongly do you agree that it is important to... Be rich
How strongly do you agree that it is important to... Have lots of friends

Rotations and randomizations – within questions

Rotations and randomizations that occur between respondents within a question need to be removed from the data prior to creating the data file. Generally, this should be done in accordance with the questionnaire’s coding. For example, if the questionnaire said:

Q1	Which of these have you eaten in the past week? RANDOMIZE ORDER OF 1 AND 2
	McDonald’s
	Taco Bell
	None of these

Then half the sample will have been asked:

Q1	Which of these have you eaten in the past week?
	McDonald’s
	Taco Bell
	None of these

And the other half will have been asked:

Q1	Which of these have you eaten in the past week?
	Taco Bell
	McDonald’s
	None of these

When the data is exported, it should be done using the order in the questionnaire. That is, even though Taco Bell was shown the first half the time and the second half the time, the data file should be created as if Taco Bell was always shown second.

Rotations and randomizations – between questions

Where different respondents see questions in different orders, this order needs to be removed from the data prior to creating the data file. For example, if the respondents have been asked to rate the appeal of a random selection of three of four different products and the order has been randomized or rotated, such as in this table:

ID	Q1		Q2		Q3
1	Microsoft	Apple		IBM
2	Apple		Microsoft	IBM
3	IBM		Google		Apple
4	Google		Microsoft	IBM

then the data should be exported as if people had been asked four different questions and all respondents had seen them in the same order:

ID	Q. Microsoft	Q. Apple	Q. IBM		Q. Google
1	Data from Q1	Data from Q2	Data from Q3	NA
2	Data from Q2	Data from Q1	Data from Q3	NA
3	NA		Data from Q3	Data from Q1	Data from Q2
4	Data from Q2	NA		Data from Q3	Data from Q1

Further, the order in which the data was collected should also be exported as additional variables. For example:

ID	Order Microsoft	Order Apple	Order IBM	Order Google
1	1		2		3		NA
2	2		1		3		NA
3	NA		3		1		2
4	2		NA		3		1

Rankings

Ranking questions need to be recorded with a single variable for each item being ranked. Ideally, the most preferred item will have the highest value and the least preferred the lowest, except where the questionnaire expressly indicates an alternative coding.

Un-coded open-ended questions and “other specify”

Verbatim responses to open-ended questions and “other specify” options should be stored as String variables if the data is text and Numeric variables if numeric.

Coded open-ended questions

Where open-ended questions have been coded, these are then included in the data file as if they are standard single or multiple-response variables (in particular, the binary format is appropriate for multiple-response questions). An additional string variable should store the raw responses.

Variable Names

Variable names should relate to the question numbers. It is often useful if separate question numbering is used for screeners, general questions, and classification variables (i.e., S1, S, …., Q1, Q2, …, C1, C2,…). Where a question is represented by multiple variables, use a common prefix (e.g., Q4a, Q4b, Q4c), rather than outfiling each variable with a different question number (e.g., Q231, Q232, Q233). Where a question is a loop of multiple-response questions, this is generally best represented via a common prefix and two separate looping suffixes (e.g., Q4a1, Q4a2, Q4b1, Q4b2). While these are only guidelines, the core principle is to employ an easily understandable convention, whereby the variable names are informative as to the structure of the data.

Variables To Be Excluded

Variables that have no possible meaning to the user of the data file should be excluded from the data file. Some data collection programs automatically export useless variables that only relate to how the questionnaire was set up. Examples of variables with no possible meaning that may be exported include:

• Looped variables, where one variable will have a value of 1 for every respondent, another will have a value of 2 for all respondents, and so on.
• Variables representing unused codes in multiple response questions.

Specifications for advanced data types

The following specifications are for more advanced data types:

• MaxDiff Specifications contains instructions for the specifications of MaxDiff (maximum difference experiments).
• Experiments Specifications contain specifications for the setup of conjoint, discrete choice, and other types of choice experiments.

Next

How to Import Data into Displayr

How to Set Up an Excel File for Importing into Displayr