Date management 101
How to prepare your survey
data for statistical analysis
ICME Yuki Izumi, D2

What we image (or hope)

Google Form

Excel

Statistical Analysis
Y. Izumi, 4/3/2024

What actually it is① ② ③ ④ ➄ ⑥ ⑦ ⑧ ⑨Google Form

Excel

Statistical Analysis

Y. Izumi, 4/3/2024

Steps to prepare your survey
data for statistical analysis
1. Review Data Structure
2. Handle missing values
3. Correct errors

Data cleaning

4. Deal with outliers
5. Format Variables
Today’s topic

6. Rename Variables
7. Organize Data
8. Remove Unnecessary Information
9. Create a data dictionary

Y. Izumi, 4/3/2024

Your goals
CorrectlyFormatted, organized,
and clean data

Data dictionary

Y. Izumi, 4/3/2024

Suggested data format

Y. Izumi, 4/3/2024

Suggested data format
1. All variables are represented with numbers.
2. Information for a single subject is contained within a single row.
3. Each column contains only one characteristic.
4. Numeric values used for analysis should be precomputed.
5. Delete all the unnecessary columns unrelated to the analysis.
6. All questions are displayed with an Item Number.
7. Use “general format” for all cells.
Y. Izumi, 4/3/2024

Suggested data format
1. All variables are represented with numbers.
2. One subject, one row.
3. One characteristic, one column.
4. Numeric values used for analysis should be precomputed.
5. Delete all the unnecessary columns unrelated to the analysis.
6. All questions are displayed with an Item Number.
7. Use “general format” for all cells.
Y. Izumi, 4/3/2024

1. Label all the categorical values with numbers
1: yes, 0:noMake sure to make a note for each label!

Y. Izumi, 4/3/2024

1. Label all the categorical values with numbers

Yes

Y. Izumi, 4/3/2024

The order of replacement is tricky!
 Strongly agree
 Agree Somewhat agree
If you replace all “agree” with 2 first, you will get..
 Strongly 2
 2
 Somewhat 2
Y. Izumi, 4/3/2024

The order of replacement is tricky!
Start by replacing “strongly agree” first.

Then, replace “somewhat agree” with3Replace “agree” with 2 at the end.



1AgreeSomewhat agree

1Agree3123
Y. Izumi, 4/3/2024

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

2. one subject, one row

Y. Izumi, 4/3/2024

2. One subject, one row
Mr. A: 1st response
Mr. B: 1st response
Mr. C: 1st response
Mr. D: 1st response

Mr. A: 1st response

Mr. A: 2nd response

Mr. A: 3 rd response

Mr. A: 2nd response

Mr. B: 1st response

Mr. B: 2nd response

Mr. B: 3 rd response

Mr. B: 2nd response

Mr. C: 1st response

Mr. C: 2nd response

Mr. C: 3 rd response

Mr. C: 2nd response

Mr. D: 1st response

Mr. D: 2nd response

Mr. D: 3 rdresponse

Mr. D: 2nd response
Mr. A: 3 rd response
Mr. B: 3 rd response
Mr. C: 3 rd response
Mr. D: 3 rd responseY. Izumi, 4/3/2024

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

3. One characteristic, one column

Y. Izumi, 4/3/2024

3. One characteristic, one column
Checkbox question

Q4. Please select all the medical fields below thatapply to the area you are regularly engaged with.

Q4.
Please select all the medical fields
below that apply to the ones you
are regularly engaged with toprovide prostheses/orthoses.

orthopedic, rehabilitation, others□ orthopedic□ rehabilitation□ lower extremity wound□ others

orthopedic, rehabilitation

orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others
orthopedic

orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others

Y. Izumi, 4/3/2024

3. One characteristic, one column
Engaged fields

Orthopedic

Rehabilitation

LE wounds

Othersorthopedic, rehabilitation, others

1101orthopedic, lower extremity wound1010

rehabilitation, lower extremity wound, others

0111orthopedic

1000orthopedic, rehabilitation

1100orthopedic, lower extremity wound1010

rehabilitation, lower extremity wound, others

01

11=COUNTIF(A2,"*Orthopedic*")

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.

4. Calculate all the necessary variables
subscale1 subscale2 subscale3 subscale4 age

Yrs in practicesubscale1

subscale2

subscale3

subscale4

1

12

15 1.25 56

44

33

1

4

11 2.75 62

88

41

51

11 2.75 33

66

3240

9 2.25 58

43

414

4

13 3.25 70

78

totalaveage

Yrs in practice112156

44

3

3

1462

88

41513366

3240584341447078

53154397531514 3.543972111284321115 1.25 28

43=Sum(A2:D2)=(F2/4)

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

5. Delete unnecessary columnY. Izumi, 4/3/2024

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

6. Label questions with item numbers

Don’t: Q1, Q2, → DO: Q01, Q02 to avoid sorting Q1,Q10
Y. Izumi, 4/3/2024

6. Label questions with item numbers
Engaged fields
orthopedic, rehabilitation, others
orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others
orthopedic
orthopedic, rehabilitation
orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others

Orthopedic

Rehabilitation

LE wounds

Others

1101
1
10

10
1010101
1001
1

10
1000
1

Q.04
orthopedic, rehabilitation, others
orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others
orthopedic
orthopedic, rehabilitation
orthopedic, lower extremity wound
rehabilitation, lower extremity wound, others

Q.04-11101110

Q.04-21010101

Q.04-30110011

Q.04-4
10
10001

Y. Izumi, 4/3/2024

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

7. Use the “General” format for all cells

Y. Izumi, 4/3/2024

Suggested data format
1.All variables are represented with numbers.
2.One subject, one row.
3.One characteristic, one column.
4.Numeric values used for analysis should be precomputed.
5.Delete all the unnecessary columns unrelated to the analysis.
6.All questions are displayed with an Item Number.
7.Use “general format” for all cells.
Y. Izumi, 4/3/2024

Steps to prepare your survey
data for statistical analysis
1. Review Data Structure2. Handle missing values
3. Correct errors

Data cleaning

4. Deal with outliers
5. Format Variables
Today’s topic

6. Rename Variables
7. Organize Data
8. Remove Unnecessary Information
9. Create a data dictionary

Y. Izumi, 4/3/2024

Suggested data dictionary format
1. Transpose the item number
2. One item, one row
3. Use Arial font

Y. Izumi, 4/3/2024

Suggested data dictionary format
1. Transpose the item number
2. One item, one row
3. Use Arial font

Y. Izumi, 4/3/2024

1.Transpose the item number

Data

Data dictionaryY. Izumi, 4/3/2024

Suggested data dictionary format
1. Transpose the item number
2. One item, one row
3. Use Arial font

Y. Izumi, 4/3/2024

2. One item, one row

Y. Izumi, 4/3/2024

Suggested data dictionary format
1. Transpose the item number
2. One item, one row
3. Use Arial font

Y. Izumi, 4/3/2024

3. Use Arial Font

Y. Izumi, 4/3/2024

Suggested data format
1. All variables are represented with numbers.
2. One subject, one row.
3. One characteristic, one column.
4. Numeric values used for analysis should be precomputed.
5. Delete all the unnecessary columns unrelated to the analysis.
6. All questions are displayed with an Item Number.
7. Use “general format” for all cells.

Suggested data dictionary format
1. Transpose the item number
2. One item, one row
3. Use Arial font
Y. Izumi, 4/3/2024

Your goals
CorrectlyFormatted, organized,＆ Clean Data

Data dictionary
Y. Izumi, 4/3/2024

Steps to prepare your survey
data for statistical analysis
1. Review Data Structure
2. Handle missing values
3. Correct errors

Data cleaning

4. Deal with outliers
5. Format Variables
Today’s topic

6. Rename Variables
7. Organize Data
8. Remove Unnecessary Information
9. Create a data dictionary

Y. Izumi, 4/3/2024

Thank you for your attention!

Cherry blossom by Meguro river in Osaki