Cleaning Data — Turning Blank Values into 0

Reason
#

I found that data exported from Tencent Questionnaire had an incorrect format for multiple-choice questions and was not suitable for importing into SPSS for analysis. The main problems were:

Unselected options should be 0, but here they were blank values.
Other columns should be 1 if selected, but here they were directly filled with the entered content.

Problem example:

Therefore, I used Python code to clean the data.

Preparation Before Use
#

Rename variables: first, rename the variables, such as Q1, Q2, and so on. For multiple-choice questions, name them Q3_1, Q3_2, and so on. Variable names should use English abbreviations of the variables; for example, perceived ease of use can be named PEOU1, PEOU2.
Save the Excel file as CSV. The steps are:
1. Start Excel and open the target file.
2. Click the “File” menu in the upper-left corner.
3. Choose “Save As” from the menu. Some versions may directly show “Save as.”
4. In the dialog box, choose where to save the file.
5. In the “Save as type” drop-down menu, choose CSV (Comma delimited) (*.csv). Here is an example of a prepared data format:
Make sure Python has the numpy and pandas libraries installed. If not, enter the following commands in the command-line terminal:
1 2
pip install numpy pip install pandas
Copy the file path.
- Select the target file or folder.
- Press Ctrl + Shift + C to copy the full path of the file or folder to the clipboard.
- Note: the copied path will include quotation marks, which must be removed later.

Code
#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
import pandas as pd

# Read the CSV file. Replace 'input.csv' with your file path, and remember to remove quotation marks from the path.
# For example, if the copied path is "C:\Users\xcyy5\Desktop\test.csv", remove the quotation marks and use C:\Users\xcyy5\Desktop\test.csv.
# Note: do not change or delete the single quotation marks in the code.
df = pd.read_csv(r'input.csv')

# Define the column range to process, from A to B. Replace A with the first column name of the multiple-choice questions, and B with the last column name.
# For example, if my multiple-choice questions run from question 6 to question 8, and question 8 has 4 options, replace A with Q6_1 and B with Q8_4.
columns_to_process = df.loc[:, 'A':'B'].columns

# Process the specified columns: replace values other than 1, including blanks, with 0.
df[columns_to_process] = df[columns_to_process].apply(lambda col: col.apply(lambda x: 1 if x == 1 else 0))

# Save the processed result to a new file and ensure Chinese characters can be output correctly. Replace 'output.csv' with the export file path.
# For example, if you want to export to the works folder on drive D and name it "cleaned-blank-values-as-0.csv", the path is D:\works\cleaned-blank-values-as-0.csv.
# Note: do not change or delete the single quotation marks in the code.
df.to_csv(r'output.csv', index=False, encoding='utf_8_sig')

print("Processing complete. The file has been saved!")

Reason#

Preparation Before Use#

Code#

Related

Reason
#

Preparation Before Use
#

Code
#