Week 3  - Culling the data

Comments

Trying to get the data formatted in an appealing way is the big challenge this week. I found that converting data such as 6 which indicated "I don't know" to a NaN would make a "1" appear as "1.000000". Same numerical value, just not as pleasing to look at.

I overcame this by creating a new data frame that excluded unusable data. An example would be

1.   sub1=data[(data['num_grade_retained']>0) & (data['num_grade_retained']<4)]  

This data frame selected only valid data with responses 0 to 4. 

 I had to create a column showing the number of times retained since each grade level has it's own response. This allowed me to efficiently pull only respondents who had been retained at least once.

1.   data['num_grade_retained'] =  data['H1ED6F'] +  data['H1ED6G'] +  data['H1ED6H']  

2.   data['num_grade_retained'] +=  data['H1ED6I'] +  data['H1ED6J'] +  data['H1ED6K']  

I first examined students who reported being retained at least one grade. Initially, I included all of the grade-levels but the display was split and difficult to read. Since my original question is about only the upper grades, I just display data for grades 6-11.

1.   retained = sub1[[  

2.       'H1ED6F', 'H1ED6G', 'H1ED6H', 'H1ED6I', 'H1ED6J', 'H1ED6K', 'num_grade_retained'  

    ]]

I used the same process to look at 3 more variables including respondents who reported violent behavior such as fighting "H1FV5", pulling a gun or knife on someone "H1FV7" and shooting or stabbing somoneone "H1FV8".

The actual code

**Note that I left in a lot of comments that I made to myself including commented out blocks of code that I tried but chose not to use because of poorly formatted output.

1. # import modules to process math  
2.   
3. import numpy  
4. import pandas as pd  
5.   
6. #to prevent runtime errors use the following code  
7. pd.set_option('display.float_format', lambda x: '%f'%x)  
8.   
9. #create a dataset from original csv file  
10. data = pd.read_csv('addhealth_pds.csv', low_memory=False)  
11.   
12. #ensure all data column headers are uppercase with the following  
13. data.columns = map(str.upper, data.columns)  
14.   
15. #convert columns to numeric rather than data  
16. pd.to_numeric(data['H1ED5']) #failed any grade  
17. pd.to_numeric(data["H1ED6A"])#failed 1ST grade  
18. pd.to_numeric(data["H1ED6B"]) #failed 2ND grade  
19. pd.to_numeric(data["H1ED6C"]) #failed 3RD grade  
20. pd.to_numeric(data["H1ED6D"]) #failed 4th grade  
21. pd.to_numeric(data["H1ED6E"])#failed 5th grade  
22. pd.to_numeric(data["H1ED6F"])#failed 6th grade  
23. pd.to_numeric(data["H1ED6G"]) #failed 7th grade  
24. pd.to_numeric(data["H1ED6H"]) #failed 8th grade  
25. pd.to_numeric(data["H1ED6I"]) #failed 9th grade  
26. pd.to_numeric(data["H1ED6J"]) #failed 10th grade  
27. pd.to_numeric(data["H1ED6K"]) #failed 11th grade  
28. pd.to_numeric(data["H1FV5"]) #got into fight  
29. pd.to_numeric(data["H1FV7"]) #pulled a knife or gun  
30. pd.to_numeric(data["H1FV8"]) #shot or stabbed someone  
31.   
32. """ 
33. This block of code generates the num_grade_retained column that counts all grades 1-11 
34. I decided to pare it down to just 6-11 as in the block of code after this comment section 
35. #data['num_grade_retained'] =  data['H1ED6A'] +  data['H1ED6B'] +  data['H1ED6C'] +  data['H1ED6D'] 
36. #data['num_grade_retained'] +=  data['H1ED6E'] +  data['H1ED6F'] +  data['H1ED6G'] +  data['H1ED6H'] 
37. #data['num_grade_retained'] +=  data['H1ED6I'] +  data['H1ED6J'] +  data['H1ED6K'] 
38. """  
39. data['num_grade_retained'] =  data['H1ED6F'] +  data['H1ED6G'] +  data['H1ED6H']  
40. data['num_grade_retained'] +=  data['H1ED6I'] +  data['H1ED6J'] +  data['H1ED6K']  
41.   
42. #This creates a data frame with only respondents who indicated being retained a grade at least once  
43. sub1=data[(data['num_grade_retained']>0) & (data['num_grade_retained']<4)]  
44.   
45. #This shows only respondents who were retained in 6th to 11th grades  
46. retained = sub1[[  
47.     'H1ED6F', 'H1ED6G', 'H1ED6H', 'H1ED6I', 'H1ED6J', 'H1ED6K', 'num_grade_retained'  
48.     ]]   
49.   
50. a = retained.head (n=10)  
51. print("Columns are row#, 6th, 7th, 8th, 9th, 10th, 11th, Total number of times retained")  
52. print(a)  
53.   
54. #times_retained={1: 'Once', 2: 'Twice', 3: 'Three times'}  
55. #retained['num_grade_retained']=retained['num_grade_retained'].map(times_retained)  
56. check_retained = retained['num_grade_retained'].value_counts(sort=True, normalize=False)  
57.   
58. print("\n\nNumber of times retained between 6th and 11th grades.\n")  
59. print(check_retained)  
60.   
61. """ 
62. This section looks at violence replace values 6, 8, and 9 with NA to account 
63. for data that cannot be used 
64.  
65. data['H1FV5'] = data['H1FV5'].replace(6, numpy.nan) 
66. data['H1FV5'] = data['H1FV5'].replace(8, numpy.nan) 
67. data['H1FV5'] = data['H1FV5'].replace(9, numpy.nan) 
68.  
69. data['H1FV7'] = data['H1FV7'].replace(6, numpy.nan) 
70. data['H1FV7'] = data['H1FV7'].replace(8, numpy.nan) 
71. data['H1FV7'] = data['H1FV7'].replace(9, numpy.nan) 
72.  
73. data['H1FV8'] = data['H1FV8'].replace(6, numpy.nan) 
74. data['H1FV8'] = data['H1FV8'].replace(8, numpy.nan) 
75. data['H1FV8'] = data['H1FV8'].replace(9, numpy.nan) 
76. """  
77. sub2=data[  
78.     (data['H1FV5']>=0) & (data['H1FV5']<4) &  
79.     (data['H1FV7']>=0) & (data['H1FV7']<4) &  
80.     (data['H1FV8']>=0) & (data['H1FV8']<4)  
81.     ]  
82. fight = sub2['H1FV5'].value_counts(sort=False, dropna=False)  
83. print("\nThe number of people who report fighting in the last 12 months")  
84. print(" 0 = none, 1 = once, 2 = more than once")  
85. print(fight)  
86.   
87. pulled = sub2['H1FV7'].value_counts(sort=False, dropna=True)  
88. print("\nThe number of people who report pulling a knife or gun on someone in the last 12 months")  
89. print(" 0 = none, 1 = once, 2 = more than once")  
90. print(pulled)  
91.   
92. shot = sub2['H1FV8'].value_counts(sort=False, dropna=True)  
93. print("\nThe number of people who report stabbing or shooting someone in the last 12 months")  
94. print(" 0 = none, 1 = once, 2 = more than once")  
95. print(shot)  
96.   
97. violence = sub2[['H1FV5', 'H1FV7', 'H1FV8']]  
98. b = violence.head (10)  
99. print("\nColumn headers 'H1FV5'' is fighting, 'H1FV7' is pulling a knife or gun, 'H1FV8' is shooting or stabbing someone")  
100. print("\tColumn responses 0 = none, 1 = once, 2 = more than once")  
101. print(b)  

The Output

*Note that row numbers in the first selection represent only those rows whose respondents had indicated being retained in at least one grade 6-11, hence the last column will only be a 1 or higher.

runfile('C:/Users/Christopher/OneDrive for Business/Data studies/week3.py', wdir='C:/Users/Christopher/OneDrive for Business/Data studies', post_mortem=True)

Columns are row#, 6th, 7th, 8th, 9th, 10th, 11th, Total number of times retained

     H1ED6F  H1ED6G  H1ED6H  H1ED6I  H1ED6J  H1ED6K  num_grade_retained

4         1       0       0       0       0       0                   1

13        0       0       0       1       0       0                   1

32        0       1       0       0       0       0                   1

45        0       1       0       0       1       0                   2

71        1       0       0       0       1       0                   2

77        1       0       0       0       0       0                   1

83        0       1       0       0       0       0                   1

88        0       0       0       1       0       0                   1

119       0       0       0       1       0       0                   1

127       0       1       0       0       0       0                   1

Number of times retained between 6th and 11th grades.

1    411

2     26

3      1

Name: num_grade_retained, dtype: int64

The number of people who report fighting in the last 12 months

 0 = none, 1 = once, 2 = more than once

0    4409

1    1189

2     855

Name: H1FV5, dtype: int64

The number of people who report pulling a knife or gun on someone in the last 12 months

 0 = none, 1 = once, 2 = more than once

0    6139

1     232

2      82

Name: H1FV7, dtype: int64

The number of people who report stabbing or shooting someone in the last 12 months

 0 = none, 1 = once, 2 = more than once

0    6331

1      86

2      36

Name: H1FV8, dtype: int64

Column headers 'H1FV5'' is fighting, 'H1FV7' is pulling a knife or gun, 'H1FV8' is shooting or stabbing someone

        Column responses 0 = none, 1 = once, 2 = more than once

   H1FV5  H1FV7  H1FV8

0      2      0      0

1      0      0      0

2      0      0      0

3      1      1      0

4      0      0      0

5      1      1      0

6      2      0      0

7      2      0      0

8      0      0      0

9      0      0      0