Analysis of two instance of database loaded in ODS system

analysis_PopulatedSandbox.md

Analysis of populated sample dataset provided by ODS

This data is loaded when we build the ODS system. We are using this database as our source for development

What we are looking at?

1. Student records
2. School records
3. Assessment record
4. Student assessment records

Connection with database

import pyodbc 
import pandas as pd

conn_str = (
    r'Driver={SQL Server};'
    r'Server=(local);'
    r'Database=EdFi_Ods_Sandbox_populatedSandbox;'
    r'Trusted_Connection=yes;'
    )


try:
    cnxn = pyodbc.connect(conn_str)
except Exception as er:
    print(er)

df = pd.read_sql('SELECT * FROM edfi.Student', con=cnxn)

df.count()

StudentUSI                                   962
PersonalTitlePrefix                          962
FirstName                                    962
MiddleName                                   495
LastSurname                                  962
GenerationCodeSuffix                          16
MaidenName                                     0
SexTypeId                                    962
BirthDate                                    962
BirthCity                                      0
BirthStateAbbreviationTypeId                   0
DateEnteredUS                                  0
MultipleBirthStatus                            0
ProfileThumbnail                               0
HispanicLatinoEthnicity                      962
OldEthnicityTypeId                           950
EconomicDisadvantaged                         23
SchoolFoodServicesEligibilityDescriptorId     23
LimitedEnglishProficiencyDescriptorId         12
DisplacementStatus                             0
LoginId                                      962
BirthInternationalProvince                     0
CitizenshipStatusTypeId                        0
StudentUniqueId                              962
Id                                           962
LastModifiedDate                             962
CreateDate                                   962
BirthCountryDescriptorId                       0
dtype: int64

pd.Series({c: df[c].unique() for c in df})

StudentUSI                                   [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14...
PersonalTitlePrefix                                                          [Mrs, Mr, Ms, Dr]
FirstName                                    [Shelby, Sarah, Gilbert, Debbie, Sandro, Alici...
MiddleName                                   [Ruby, None, Jesse, Francisco, Angela, Everett...
LastSurname                                  [Barajas, Stevens, Fritz, Savage, Miguel, Denn...
GenerationCodeSuffix                                                            [None, Jr, II]
MaidenName                                                                              [None]
SexTypeId                                                                               [1, 2]
BirthDate                                    [2005-12-15, 2005-06-12, 2005-12-16, 2005-11-0...
BirthCity                                                                               [None]
BirthStateAbbreviationTypeId                                                            [None]
DateEnteredUS                                                                           [None]
MultipleBirthStatus                                                                     [None]
ProfileThumbnail                                                                        [None]
HispanicLatinoEthnicity                                                          [True, False]
OldEthnicityTypeId                                              [1.0, 4.0, 5.0, 3.0, 2.0, nan]
EconomicDisadvantaged                                                      [None, True, False]
SchoolFoodServicesEligibilityDescriptorId                                  [nan, 644.0, 645.0]
LimitedEnglishProficiencyDescriptorId                                             [nan, 597.0]
DisplacementStatus                                                                      [None]
LoginId                                      [sbarajas, sstevens, gfritz, dsavage, smiguel,...
BirthInternationalProvince                                                              [None]
CitizenshipStatusTypeId                                                                 [None]
StudentUniqueId                              [605704, 555555, 604888, 605372, 777777, 60490...
Id                                           [1322C606-D644-497B-8548-207933921855, 9F5B105...
LastModifiedDate                             [2015-06-11T15:26:32.710000000, 2015-06-11T15:...
CreateDate                                   [2015-06-11T15:26:32.000000000, 2015-06-11T15:...
BirthCountryDescriptorId                                                                [None]
dtype: object

df.nunique()

StudentUSI                                   962
PersonalTitlePrefix                            4
FirstName                                    614
MiddleName                                   407
LastSurname                                  603
GenerationCodeSuffix                           2
MaidenName                                     0
SexTypeId                                      2
BirthDate                                    876
BirthCity                                      0
BirthStateAbbreviationTypeId                   0
DateEnteredUS                                  0
MultipleBirthStatus                            0
ProfileThumbnail                               0
HispanicLatinoEthnicity                        2
OldEthnicityTypeId                             5
EconomicDisadvantaged                          2
SchoolFoodServicesEligibilityDescriptorId      2
LimitedEnglishProficiencyDescriptorId          1
DisplacementStatus                             0
LoginId                                      932
BirthInternationalProvince                     0
CitizenshipStatusTypeId                        0
StudentUniqueId                              962
Id                                           962
LastModifiedDate                             331
CreateDate                                     8
BirthCountryDescriptorId                       0
dtype: int64

df2 = pd.read_sql('SELECT * FROM edfi.School', con=cnxn)

df2.count()

SchoolId                                    3
LocalEducationAgencyId                      3
SchoolTypeId                                3
CharterStatusTypeId                         3
TitleIPartASchoolDesignationTypeId          3
MagnetSpecialProgramEmphasisSchoolTypeId    0
AdministrativeFundingControlDescriptorId    3
InternetAccessTypeId                        0
CharterApprovalAgencyTypeId                 0
CharterApprovalSchoolYear                   0
dtype: int64

pd.Series({c: df2[c].unique() for c in df2})

SchoolId                                    [255901001, 255901044, 255901107]
LocalEducationAgencyId                                               [255901]
SchoolTypeId                                                              [2]
CharterStatusTypeId                                                       [4]
TitleIPartASchoolDesignationTypeId                                        [1]
MagnetSpecialProgramEmphasisSchoolTypeId                               [None]
AdministrativeFundingControlDescriptorId                                 [63]
InternetAccessTypeId                                                   [None]
CharterApprovalAgencyTypeId                                            [None]
CharterApprovalSchoolYear                                              [None]
dtype: object

df2.nunique()

SchoolId                                    3
LocalEducationAgencyId                      1
SchoolTypeId                                1
CharterStatusTypeId                         1
TitleIPartASchoolDesignationTypeId          1
MagnetSpecialProgramEmphasisSchoolTypeId    0
AdministrativeFundingControlDescriptorId    1
InternetAccessTypeId                        0
CharterApprovalAgencyTypeId                 0
CharterApprovalSchoolYear                   0
dtype: int64

df_assessment = pd.read_sql('SELECT * FROM edfi.Assessment', con=cnxn)

df_assessment.count()

AssessmentTitle                         491
AssessedGradeLevelDescriptorId          491
AssessmentCategoryDescriptorId          491
AcademicSubjectDescriptorId             491
LowestAssessedGradeLevelDescriptorId     40
AssessmentForm                            0
Version                                 491
RevisionDate                            446
MaxRawScore                             491
Nomenclature                              0
AssessmentPeriodDescriptorId              0
AssessmentFamilyTitle                     0
Namespace                               491
Id                                      491
LastModifiedDate                        491
CreateDate                              491
dtype: int64

df_assessment.nunique()

AssessmentTitle                         228
AssessedGradeLevelDescriptorId           13
AssessmentCategoryDescriptorId            5
AcademicSubjectDescriptorId              10
LowestAssessedGradeLevelDescriptorId      1
AssessmentForm                            0
Version                                 129
RevisionDate                             40
MaxRawScore                              41
Nomenclature                              0
AssessmentPeriodDescriptorId              0
AssessmentFamilyTitle                     0
Namespace                                 1
Id                                      491
LastModifiedDate                        162
CreateDate                               14
dtype: int64

(pd.Series({c: df_assessment[c].unique() for c in df_assessment}))

AssessmentTitle                         [2nd Grade ELA 2nd Six Weeks 2012-2013, 2nd Gr...
AssessedGradeLevelDescriptorId            [16, 18, 8, 7, 17, 15, 4, 20, 13, 19, 1, 6, 12]
AssessmentCategoryDescriptorId                                       [93, 69, 70, 80, 88]
AcademicSubjectDescriptorId                      [24, 28, 29, 32, 31, 22, 25, 33, 39, 34]
LowestAssessedGradeLevelDescriptorId                                           [nan, 4.0]
AssessmentForm                                                                     [None]
Version                                 [5221, 4874, 5317, 4913, 5114, 4854, 4863, 485...
RevisionDate                            [2012-10-23, 2012-09-26, 2012-10-26, 2012-10-2...
MaxRawScore                             [10, 13, 15, 16, 12, 19, 25, 30, 5, 1, 100, 80...
Nomenclature                                                                       [None]
AssessmentPeriodDescriptorId                                                       [None]
AssessmentFamilyTitle                                                              [None]
Namespace                                    [http://ed-fi.org/Assessment/Assessment.xml]
Id                                      [55B544F6-EF9B-4EB4-804D-306B8E4ABACF, 0D43481...
LastModifiedDate                        [2015-06-11T15:32:35.670000000, 2015-06-11T15:...
CreateDate                              [2015-06-11T15:32:35.000000000, 2015-06-11T15:...
dtype: object

(pd.Series({c: df_assessment[c].unique() for c in df_assessment}))

AssessmentTitle                         [2nd Grade ELA 2nd Six Weeks 2012-2013, 2nd Gr...
AssessedGradeLevelDescriptorId            [16, 18, 8, 7, 17, 15, 4, 20, 13, 19, 1, 6, 12]
AssessmentCategoryDescriptorId                                       [93, 69, 70, 80, 88]
AcademicSubjectDescriptorId                      [24, 28, 29, 32, 31, 22, 25, 33, 39, 34]
LowestAssessedGradeLevelDescriptorId                                           [nan, 4.0]
AssessmentForm                                                                     [None]
Version                                 [5221, 4874, 5317, 4913, 5114, 4854, 4863, 485...
RevisionDate                            [2012-10-23, 2012-09-26, 2012-10-26, 2012-10-2...
MaxRawScore                             [10, 13, 15, 16, 12, 19, 25, 30, 5, 1, 100, 80...
Nomenclature                                                                       [None]
AssessmentPeriodDescriptorId                                                       [None]
AssessmentFamilyTitle                                                              [None]
Namespace                                    [http://ed-fi.org/Assessment/Assessment.xml]
Id                                      [55B544F6-EF9B-4EB4-804D-306B8E4ABACF, 0D43481...
LastModifiedDate                        [2015-06-11T15:32:35.670000000, 2015-06-11T15:...
CreateDate                              [2015-06-11T15:32:35.000000000, 2015-06-11T15:...
dtype: object

df_assessment[(df_assessment['AssessmentTitle'] == 'ACT') | (df_assessment['AssessmentTitle'] == 'SAT') | (df_assessment['AssessmentTitle'] == 'TSI')]

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	AssessmentTitle	AssessedGradeLevelDescriptorId	AssessmentCategoryDescriptorId	AcademicSubjectDescriptorId	LowestAssessedGradeLevelDescriptorId	AssessmentForm	Version	RevisionDate	MaxRawScore	Nomenclature	AssessmentPeriodDescriptorId	AssessmentFamilyTitle	Namespace	Id	LastModifiedDate	CreateDate
85	ACT	20	69	22	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	87D1634A-2D3C-4086-A1CA-BEB397D97C0A	2015-06-11 15:31:14.310	2015-06-11 15:31:14
86	ACT	20	69	25	NaN	None	1	2005-03-12	30	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	D5BC38D3-B2D4-4E5B-B0BA-1B32FDE6C44F	2015-06-11 15:31:14.310	2015-06-11 15:31:14
87	ACT	20	69	28	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	92F85664-AE18-409D-9A83-B1A732884F87	2015-06-11 15:31:14.310	2015-06-11 15:31:14
88	ACT	20	69	29	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	4360240D-F64D-4410-83DC-739BFB6252F1	2015-06-11 15:31:14.310	2015-06-11 15:31:14
89	ACT	20	69	32	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	DDF9C4B6-BADF-47FA-982B-34866DC84EDF	2015-06-11 15:31:14.310	2015-06-11 15:31:14
90	ACT	20	69	33	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	EF5919DB-AFAF-4A69-B078-9A0390E97B00	2015-06-11 15:31:14.310	2015-06-11 15:31:14
253	SAT	20	69	28	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	24BBBBD6-5124-4275-AB17-A9F5C2E5768C	2015-06-11 15:51:52.140	2015-06-11 15:44:51
254	SAT	20	69	29	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	7FF98468-B1E0-41A6-A541-33CB029B71CD	2015-06-11 15:51:52.140	2015-06-11 15:44:51
255	SAT	20	69	33	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	7ADC8B86-CD1E-4093-826F-8A48C2D1DD58	2015-06-11 15:51:52.140	2015-06-11 15:44:51

There is no TSI defined in assessment table

df_student_assessment.count()

StudentUSI                            20851
AssessmentTitle                       20851
AcademicSubjectDescriptorId           20851
AssessedGradeLevelDescriptorId        20851
Version                               20851
AdministrationDate                    20851
AdministrationEndDate                     0
SerialNumber                          13170
AdministrationLanguageDescriptorId    18828
AdministrationEnvironmentTypeId       18828
RetestIndicatorTypeId                 13743
ReasonNotTestedTypeId                   190
WhenAssessedGradeLevelDescriptorId     3885
EventCircumstanceTypeId                   0
EventDescription                          0
Id                                    20851
LastModifiedDate                      20851
CreateDate                            20851
dtype: int64

print(pd.Series({c: df_student_assessment[c].unique() for c in df_student_assessment}))

StudentUSI                            [1, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, ...
AssessmentTitle                       [Language Assessment, 2nd Grade ELA 2nd Six We...
AcademicSubjectDescriptorId                    [29, 24, 28, 32, 31, 33, 39, 34, 22, 25]
AssessedGradeLevelDescriptorId          [6, 12, 16, 18, 8, 7, 17, 15, 4, 13, 19, 20, 1]
Version                               [2002, 5221, 4874, 5317, 4913, 2014, 5114, 485...
AdministrationDate                    [2010-12-02, 2011-03-03, 2009-12-03, 2012-10-2...
AdministrationEndDate                                                            [None]
SerialNumber                          [0, 101209098, 101209096, 101208909, 101209026...
AdministrationLanguageDescriptorId                                  [517.0, nan, 524.0]
AdministrationEnvironmentTypeId                                    [3.0, 1.0, nan, 2.0]
RetestIndicatorTypeId                                              [nan, 1.0, 2.0, 3.0]
ReasonNotTestedTypeId                             [nan, 13.0, 1.0, 7.0, 4.0, 14.0, 2.0]
WhenAssessedGradeLevelDescriptorId    [6.0, nan, 12.0, 16.0, 18.0, 8.0, 7.0, 17.0, 1...
EventCircumstanceTypeId                                                          [None]
EventDescription                                                                 [None]
Id                                    [B3B9E2BE-7771-43CF-ADFA-0473DAADEDDC, EACC48E...
LastModifiedDate                      [2015-06-11T15:56:59.300000000, 2015-06-11T15:...
CreateDate                            [2015-06-11T15:56:59.000000000, 2015-06-11T15:...
dtype: object

df_assessment[(df_assessment['AssessmentTitle'] == 'ACT') | (df_assessment['AssessmentTitle'] == 'SAT') | (df_assessment['AssessmentTitle'] == 'TSI')]

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	AssessmentTitle	AssessedGradeLevelDescriptorId	AssessmentCategoryDescriptorId	AcademicSubjectDescriptorId	LowestAssessedGradeLevelDescriptorId	AssessmentForm	Version	RevisionDate	MaxRawScore	Nomenclature	AssessmentPeriodDescriptorId	AssessmentFamilyTitle	Namespace	Id	LastModifiedDate	CreateDate
85	ACT	20	69	22	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	87D1634A-2D3C-4086-A1CA-BEB397D97C0A	2015-06-11 15:31:14.310	2015-06-11 15:31:14
86	ACT	20	69	25	NaN	None	1	2005-03-12	30	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	D5BC38D3-B2D4-4E5B-B0BA-1B32FDE6C44F	2015-06-11 15:31:14.310	2015-06-11 15:31:14
87	ACT	20	69	28	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	92F85664-AE18-409D-9A83-B1A732884F87	2015-06-11 15:31:14.310	2015-06-11 15:31:14
88	ACT	20	69	29	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	4360240D-F64D-4410-83DC-739BFB6252F1	2015-06-11 15:31:14.310	2015-06-11 15:31:14
89	ACT	20	69	32	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	DDF9C4B6-BADF-47FA-982B-34866DC84EDF	2015-06-11 15:31:14.310	2015-06-11 15:31:14
90	ACT	20	69	33	NaN	None	1	2005-03-12	25	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	EF5919DB-AFAF-4A69-B078-9A0390E97B00	2015-06-11 15:31:14.310	2015-06-11 15:31:14
253	SAT	20	69	28	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	24BBBBD6-5124-4275-AB17-A9F5C2E5768C	2015-06-11 15:51:52.140	2015-06-11 15:44:51
254	SAT	20	69	29	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	7FF98468-B1E0-41A6-A541-33CB029B71CD	2015-06-11 15:51:52.140	2015-06-11 15:44:51
255	SAT	20	69	33	NaN	None	1	2005-03-12	750	None	None	None	http://ed-fi.org/Assessment/Assessment.xml	7ADC8B86-CD1E-4093-826F-8A48C2D1DD58	2015-06-11 15:51:52.140	2015-06-11 15:44:51

print(pd.Series({c: df_student_assessment[c].unique() for c in df_student_assessment}))

StudentUSI                            [1, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, ...
AssessmentTitle                       [Language Assessment, 2nd Grade ELA 2nd Six We...
AcademicSubjectDescriptorId                    [29, 24, 28, 32, 31, 33, 39, 34, 22, 25]
AssessedGradeLevelDescriptorId          [6, 12, 16, 18, 8, 7, 17, 15, 4, 13, 19, 20, 1]
Version                               [2002, 5221, 4874, 5317, 4913, 2014, 5114, 485...
AdministrationDate                    [2010-12-02, 2011-03-03, 2009-12-03, 2012-10-2...
AdministrationEndDate                                                            [None]
SerialNumber                          [0, 101209098, 101209096, 101208909, 101209026...
AdministrationLanguageDescriptorId                                  [517.0, nan, 524.0]
AdministrationEnvironmentTypeId                                    [3.0, 1.0, nan, 2.0]
RetestIndicatorTypeId                                              [nan, 1.0, 2.0, 3.0]
ReasonNotTestedTypeId                             [nan, 13.0, 1.0, 7.0, 4.0, 14.0, 2.0]
WhenAssessedGradeLevelDescriptorId    [6.0, nan, 12.0, 16.0, 18.0, 8.0, 7.0, 17.0, 1...
EventCircumstanceTypeId                                                          [None]
EventDescription                                                                 [None]
Id                                    [B3B9E2BE-7771-43CF-ADFA-0473DAADEDDC, EACC48E...
LastModifiedDate                      [2015-06-11T15:56:59.300000000, 2015-06-11T15:...
CreateDate                            [2015-06-11T15:56:59.000000000, 2015-06-11T15:...
dtype: object

print(df_student_assessment)

       StudentUSI                                    AssessmentTitle  \
0               1                                Language Assessment   
1               1                                Language Assessment   
2               1                                Language Assessment   
3               3                                Language Assessment   
4               3                                Language Assessment   
5               3                                Language Assessment   
6               4                                Language Assessment   
7               4                                Language Assessment   
8               4                                Language Assessment   
9               6                                Language Assessment   
10              6                                Language Assessment   
11              6                                Language Assessment   
12              7                                Language Assessment   
13              7                                Language Assessment   
14              8                                Language Assessment   
15              8                                Language Assessment   
16              8                                Language Assessment   
17              9                                Language Assessment   
18              9                                Language Assessment   
19              9                                Language Assessment   
20             10                                Language Assessment   
21             10                                Language Assessment   
22             10                                Language Assessment   
23             11                                Language Assessment   
24             11                                Language Assessment   
25             11                                Language Assessment   
26             12                                Language Assessment   
27             12                                Language Assessment   
28             12                                Language Assessment   
29             13                                Language Assessment   
...           ...                                                ...   
20821         961                                   State Assessment   
20822         961                                   State Assessment   
20823         961                                   State Assessment   
20824         961                                   State Assessment   
20825         961                                   State Assessment   
20826         961                                   State Assessment   
20827         961  SY 2014,District: 32, RC Conrad Science Gr12 Q...   
20828         962                                Language Assessment   
20829         962                                Language Assessment   
20830         962                                Language Assessment   
20831         962                                Language Assessment   
20832         962                                   State Assessment   
20833         962                                   State Assessment   
20834         962                                   State Assessment   
20835         962                                   State Assessment   
20836         962                                   State Assessment   
20837         962                                   State Assessment   
20838         962                                   State Assessment   
20839         962                                   State Assessment   
20840         962                                   State Assessment   
20841         962                                   State Assessment   
20842         962                                   State Assessment   
20843         962                                   State Assessment   
20844         962                                   State Assessment   
20845         962                                   State Assessment   
20846         962                                   State Assessment   
20847         962                                   State Assessment   
20848         962                                   State Assessment   
20849         962                                   State Assessment   
20850         962  SY 2014,District: 32, RC Conrad Science Gr12 Q...   

       AcademicSubjectDescriptorId  AssessedGradeLevelDescriptorId  Version  \
0                               29                               6     2002   
1                               29                               6     2002   
2                               29                              12     2002   
3                               29                               6     2002   
4                               29                               6     2002   
5                               29                              12     2002   
6                               29                               6     2002   
7                               29                               6     2002   
8                               29                              12     2002   
9                               29                               6     2002   
10                              29                               6     2002   
11                              29                              12     2002   
12                              29                               6     2002   
13                              29                              12     2002   
14                              29                               6     2002   
15                              29                               6     2002   
16                              29                              12     2002   
17                              29                               6     2002   
18                              29                               6     2002   
19                              29                              12     2002   
20                              29                               6     2002   
21                              29                               6     2002   
22                              29                              12     2002   
23                              29                               6     2002   
24                              29                               6     2002   
25                              29                              12     2002   
26                              29                               6     2002   
27                              29                               6     2002   
28                              29                              12     2002   
29                              29                               6     2002   
...                            ...                             ...      ...   
20821                           31                               4     2007   
20822                           31                              19     2009   
20823                           32                               1     2010   
20824                           32                               1     2010   
20825                           32                               4     2007   
20826                           32                              19     2009   
20827                           32                              20     2014   
20828                           29                               1     2002   
20829                           29                              13     2002   
20830                           29                              19     2002   
20831                           29                              20     2002   
20832                           24                               1     2010   
20833                           24                               1     2010   
20834                           24                              19     2009   
20835                           28                               1     2010   
20836                           28                               1     2010   
20837                           28                               4     2007   
20838                           28                              13     2008   
20839                           28                              19     2009   
20840                           29                               4     2007   
20841                           29                              13     2008   
20842                           31                               1     2010   
20843                           31                               1     2010   
20844                           31                               4     2007   
20845                           31                              19     2009   
20846                           32                               1     2010   
20847                           32                               1     2010   
20848                           32                               4     2007   
20849                           32                              19     2009   
20850                           32                              20     2014   

      AdministrationDate AdministrationEndDate SerialNumber  \
0             2010-12-02                  None            0   
1             2011-03-03                  None    101209098   
2             2009-12-03                  None            0   
3             2010-12-02                  None            0   
4             2011-03-03                  None    101209096   
5             2009-12-03                  None            0   
6             2010-12-02                  None            0   
7             2011-03-03                  None    101208909   
8             2009-12-03                  None            0   
9             2010-12-02                  None            0   
10            2011-03-03                  None    101209026   
11            2009-12-03                  None            0   
12            2011-03-03                  None    101209090   
13            2010-12-02                  None            0   
14            2010-12-02                  None            0   
15            2011-03-03                  None    101209001   
16            2009-12-03                  None            0   
17            2010-12-02                  None            0   
18            2011-03-03                  None    101208908   
19            2009-12-03                  None            0   
20            2010-12-02                  None            0   
21            2011-03-03                  None    101208962   
22            2009-12-03                  None            0   
23            2010-12-02                  None            0   
24            2011-03-03                  None    101209000   
25            2009-12-03                  None            0   
26            2010-12-02                  None            0   
27            2011-03-03                  None    101208919   
28            2009-12-03                  None            0   
29            2010-12-02                  None            0   
...                  ...                   ...          ...   
20821         2007-04-01                  None        99999   
20822         2009-04-01                  None        99999   
20823         2010-04-01                  None        99999   
20824         2012-04-01                  None        99999   
20825         2007-04-01                  None        99999   
20826         2009-04-01                  None        99999   
20827         2011-02-11                  None      8650411   
20828         2009-12-03                  None            0   
20829         2008-12-06                  None            0   
20830         2008-12-04                  None            0   
20831         2010-12-02                  None            0   
20832         2010-04-01                  None        99999   
20833         2012-04-01                  None        99999   
20834         2009-04-01                  None        99999   
20835         2010-04-01                  None        99999   
20836         2012-04-01                  None        99999   
20837         2007-04-01                  None        99999   
20838         2008-04-01                  None        99999   
20839         2009-04-01                  None        99999   
20840         2007-04-01                  None        99999   
20841         2008-04-01                  None        99999   
20842         2010-04-01                  None        99999   
20843         2012-04-01                  None        99999   
20844         2007-04-01                  None        99999   
20845         2009-04-01                  None        99999   
20846         2010-04-01                  None        99999   
20847         2012-04-01                  None        99999   
20848         2007-04-01                  None        99999   
20849         2009-04-01                  None        99999   
20850         2011-02-11                  None      8650415   

       AdministrationLanguageDescriptorId  AdministrationEnvironmentTypeId  \
0                                   517.0                              3.0   
1                                   517.0                              1.0   
2                                   517.0                              3.0   
3                                   517.0                              3.0   
4                                   517.0                              1.0   
5                                   517.0                              3.0   
6                                   517.0                              3.0   
7                                   517.0                              1.0   
8                                   517.0                              3.0   
9                                   517.0                              3.0   
10                                  517.0                              1.0   
11                                  517.0                              3.0   
12                                  517.0                              1.0   
13                                  517.0                              3.0   
14                                  517.0                              3.0   
15                                  517.0                              1.0   
16                                  517.0                              3.0   
17                                  517.0                              3.0   
18                                  517.0                              1.0   
19                                  517.0                              3.0   
20                                  517.0                              3.0   
21                                  517.0                              1.0   
22                                  517.0                              3.0   
23                                  517.0                              3.0   
24                                  517.0                              1.0   
25                                  517.0                              3.0   
26                                  517.0                              3.0   
27                                  517.0                              1.0   
28                                  517.0                              3.0   
29                                  517.0                              3.0   
...                                   ...                              ...   
20821                               517.0                              2.0   
20822                               517.0                              2.0   
20823                               517.0                              2.0   
20824                               517.0                              2.0   
20825                               517.0                              2.0   
20826                               517.0                              2.0   
20827                                 NaN                              NaN   
20828                               517.0                              3.0   
20829                               517.0                              3.0   
20830                               517.0                              3.0   
20831                               517.0                              3.0   
20832                               517.0                              2.0   
20833                               517.0                              2.0   
20834                               517.0                              2.0   
20835                               517.0                              2.0   
20836                               517.0                              2.0   
20837                               517.0                              2.0   
20838                               517.0                              2.0   
20839                               517.0                              2.0   
20840                               517.0                              2.0   
20841                               517.0                              2.0   
20842                               517.0                              2.0   
20843                               517.0                              2.0   
20844                               517.0                              2.0   
20845                               517.0                              2.0   
20846                               517.0                              2.0   
20847                               517.0                              2.0   
20848                               517.0                              2.0   
20849                               517.0                              2.0   
20850                                 NaN                              NaN   

       RetestIndicatorTypeId  ReasonNotTestedTypeId  \
0                        NaN                    NaN   
1                        1.0                    NaN   
2                        NaN                    NaN   
3                        NaN                    NaN   
4                        1.0                    NaN   
5                        NaN                    NaN   
6                        NaN                    NaN   
7                        1.0                    NaN   
8                        NaN                    NaN   
9                        NaN                    NaN   
10                       1.0                    NaN   
11                       NaN                    NaN   
12                       1.0                    NaN   
13                       NaN                    NaN   
14                       NaN                    NaN   
15                       1.0                    NaN   
16                       NaN                    NaN   
17                       NaN                    NaN   
18                       1.0                    NaN   
19                       NaN                    NaN   
20                       NaN                    NaN   
21                       1.0                    NaN   
22                       NaN                    NaN   
23                       NaN                    NaN   
24                       1.0                    NaN   
25                       NaN                    NaN   
26                       NaN                    NaN   
27                       1.0                    NaN   
28                       NaN                    NaN   
29                       NaN                    NaN   
...                      ...                    ...   
20821                    1.0                    NaN   
20822                    1.0                    NaN   
20823                    NaN                    NaN   
20824                    1.0                    NaN   
20825                    1.0                    NaN   
20826                    1.0                    NaN   
20827                    NaN                    NaN   
20828                    NaN                    NaN   
20829                    NaN                    NaN   
20830                    NaN                    NaN   
20831                    NaN                    NaN   
20832                    NaN                    NaN   
20833                    1.0                    NaN   
20834                    1.0                    NaN   
20835                    NaN                    NaN   
20836                    1.0                    NaN   
20837                    1.0                    NaN   
20838                    1.0                    NaN   
20839                    1.0                    NaN   
20840                    1.0                    NaN   
20841                    1.0                    NaN   
20842                    NaN                    NaN   
20843                    1.0                    NaN   
20844                    1.0                    NaN   
20845                    1.0                    NaN   
20846                    NaN                    NaN   
20847                    1.0                    NaN   
20848                    1.0                    NaN   
20849                    1.0                    NaN   
20850                    NaN                    NaN   

       WhenAssessedGradeLevelDescriptorId EventCircumstanceTypeId  \
0                                     6.0                    None   
1                                     NaN                    None   
2                                    12.0                    None   
3                                     6.0                    None   
4                                     NaN                    None   
5                                    12.0                    None   
6                                     6.0                    None   
7                                     NaN                    None   
8                                    12.0                    None   
9                                     6.0                    None   
10                                    NaN                    None   
11                                   12.0                    None   
12                                    NaN                    None   
13                                   12.0                    None   
14                                    6.0                    None   
15                                    NaN                    None   
16                                   12.0                    None   
17                                    6.0                    None   
18                                    NaN                    None   
19                                   12.0                    None   
20                                    6.0                    None   
21                                    NaN                    None   
22                                   12.0                    None   
23                                    6.0                    None   
24                                    NaN                    None   
25                                   12.0                    None   
26                                    6.0                    None   
27                                    NaN                    None   
28                                   12.0                    None   
29                                    6.0                    None   
...                                   ...                     ...   
20821                                 NaN                    None   
20822                                 NaN                    None   
20823                                 NaN                    None   
20824                                 NaN                    None   
20825                                 NaN                    None   
20826                                 NaN                    None   
20827                                 NaN                    None   
20828                                 1.0                    None   
20829                                13.0                    None   
20830                                19.0                    None   
20831                                20.0                    None   
20832                                 NaN                    None   
20833                                 NaN                    None   
20834                                 NaN                    None   
20835                                 NaN                    None   
20836                                 NaN                    None   
20837                                 NaN                    None   
20838                                 NaN                    None   
20839                                 NaN                    None   
20840                                 NaN                    None   
20841                                 NaN                    None   
20842                                 NaN                    None   
20843                                 NaN                    None   
20844                                 NaN                    None   
20845                                 NaN                    None   
20846                                 NaN                    None   
20847                                 NaN                    None   
20848                                 NaN                    None   
20849                                 NaN                    None   
20850                                 NaN                    None   

      EventDescription                                    Id  \
0                 None  B3B9E2BE-7771-43CF-ADFA-0473DAADEDDC   
1                 None  EACC48E1-4641-4324-856D-3DF845C011AE   
2                 None  0D5F0B3D-8402-47C7-836F-21066DA17B43   
3                 None  26D8AC37-2E53-4341-B083-02787A8C2573   
4                 None  30CAE0AE-E3D5-4AD5-B799-CE4D8A80526E   
5                 None  24317064-AC7B-4DEB-AA81-750E91A35B81   
6                 None  8C71160D-162F-4D4E-98F5-AD04398AF083   
7                 None  EBF58F47-5BD5-4BE7-A01D-3A056CAD82C8   
8                 None  483CCF6C-19D8-4CC9-9E35-17A61902D36C   
9                 None  AB773AD7-E37B-4496-80D0-DC5EFB00E475   
10                None  CA358803-EDD0-466C-B800-60FB024B1F8B   
11                None  19717DF5-32AD-43A1-9357-7ACDC54BBEFC   
12                None  0AA1575C-A4B7-4E3A-A4E1-471CB1F8DF29   
13                None  47106F34-CA5B-422B-A297-7548A9D61061   
14                None  7473A3D9-58B0-4376-9D95-D8B4F1DB82DC   
15                None  DB8A799C-B3DA-4075-A598-578E90A3E111   
16                None  85D6C9A2-0285-4A23-87D6-6FA322137389   
17                None  C29240E0-316A-4778-85FA-764E67938736   
18                None  4943BEC1-ABE9-4F43-ADAD-A89DFFF02973   
19                None  A5142050-C5F9-4D0A-8953-7BFF03A71180   
20                None  61840329-30CC-4EC3-A8B0-C91DAAA4DE23   
21                None  FCC733C3-0031-4481-912B-3C48370AEB8B   
22                None  1B866BCD-FEB8-4273-BE9A-039A376A23A3   
23                None  76BF3BB9-F61A-463B-AE33-2A21420FA044   
24                None  B0431290-928D-4E72-B4FC-26368B9B8117   
25                None  E5145DB6-B4D8-44BE-B46D-A00224E22D5B   
26                None  1CBBCD61-F7FD-4A4B-874D-4350A7F6B51A   
27                None  C7AF6392-D674-4812-8657-9D6887C093E8   
28                None  111A340F-FB09-4037-A711-C8CBA23CBABE   
29                None  CBA413BC-6BD2-4D8A-8108-70C994B4E81B   
...                ...                                   ...   
20821             None  70D2F3C5-0133-45FE-A226-D1A7661360AC   
20822             None  4A8AE93A-EC38-4DAF-9AFB-CF570BCE5507   
20823             None  17D9D4F3-FA29-4FE0-A4ED-0F24D70CC18E   
20824             None  441F2D7F-F079-4253-AD61-DC88F19B458F   
20825             None  14D3DAC8-2D7E-4A37-B3B5-66350E9937D0   
20826             None  91E604AA-3111-4773-99A5-E99EA9621695   
20827             None  EB8F9FD4-5F36-40CE-8F7F-13E92463AD74   
20828             None  39F0C05F-6E47-4051-94EF-9C331B66165D   
20829             None  0244BF83-5EA0-444E-BD2B-730EFC60F8C4   
20830             None  8B172E3F-F179-4C89-8D92-7F4C595CE2C0   
20831             None  64CCAAC7-9F6A-440F-AD92-035E7AA2DBFE   
20832             None  1DD52037-BC0F-4A95-AF3D-564154BA4582   
20833             None  F88E8062-0CF9-42D4-AD81-82DE0C920EE8   
20834             None  74D1CF61-45BD-4852-8B4F-1F59192ECAB6   
20835             None  1E3525C8-1001-4DAD-8E82-0F9BD65B6EA2   
20836             None  02E57A7A-D941-4521-99EA-50245A03E45A   
20837             None  F96D39FD-E009-40AF-9837-3C63C854372A   
20838             None  FE12C79B-1189-44D7-BC3E-C86319990BC5   
20839             None  C90DDBBD-2DA1-442B-86D9-A542C1707AC9   
20840             None  DB2827AE-1ADC-4229-A489-CE3C481762D5   
20841             None  36CD8C62-86E4-4C3F-917E-863E6C48CE3A   
20842             None  1329A872-510C-4B9B-ADAE-7DBA3B1EB620   
20843             None  AAA9110E-E20C-45CB-B1B2-8DFA79DACF4D   
20844             None  1A95ADEA-2A6E-4C5B-A630-92D823AEEA3B   
20845             None  974B1073-DFAC-4AC4-A824-73E783966870   
20846             None  AEB019E2-F03A-4DB1-9F5F-015FB4E433A0   
20847             None  AC23F405-77E5-4034-A1E1-B7D36C017061   
20848             None  374CD1C8-C6F7-45FD-99F1-2CA4C9D2C205   
20849             None  F94E7ADD-06A3-4DD0-9E9E-E98DF8BCDFEE   
20850             None  F206C16D-C04E-41F4-BC48-7569DCD9F9D6   

             LastModifiedDate          CreateDate  
0     2015-06-11 15:56:59.300 2015-06-11 15:56:59  
1     2015-06-11 15:57:02.290 2015-06-11 15:57:02  
2     2015-06-11 15:57:00.240 2015-06-11 15:57:00  
3     2015-06-11 15:56:59.280 2015-06-11 15:56:59  
4     2015-06-11 15:57:02.290 2015-06-11 15:57:02  
5     2015-06-11 15:57:00.240 2015-06-11 15:57:00  
6     2015-06-11 15:56:59.310 2015-06-11 15:56:59  
7     2015-06-11 15:57:01.860 2015-06-11 15:57:01  
8     2015-06-11 15:57:00.240 2015-06-11 15:57:00  
9     2015-06-11 15:56:59.280 2015-06-11 15:56:59  
10    2015-06-11 15:57:02.180 2015-06-11 15:57:02  
11    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
12    2015-06-11 15:57:02.290 2015-06-11 15:57:02  
13    2015-06-11 15:56:59.240 2015-06-11 15:56:59  
14    2015-06-11 15:56:59.280 2015-06-11 15:56:59  
15    2015-06-11 15:57:02.160 2015-06-11 15:57:02  
16    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
17    2015-06-11 15:56:59.280 2015-06-11 15:56:59  
18    2015-06-11 15:57:01.860 2015-06-11 15:57:01  
19    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
20    2015-06-11 15:56:59.300 2015-06-11 15:56:59  
21    2015-06-11 15:57:02.070 2015-06-11 15:57:02  
22    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
23    2015-06-11 15:56:59.300 2015-06-11 15:56:59  
24    2015-06-11 15:57:02.160 2015-06-11 15:57:02  
25    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
26    2015-06-11 15:56:59.300 2015-06-11 15:56:59  
27    2015-06-11 15:57:02.040 2015-06-11 15:57:02  
28    2015-06-11 15:57:00.240 2015-06-11 15:57:00  
29    2015-06-11 15:56:59.300 2015-06-11 15:56:59  
...                       ...                 ...  
20821 2015-06-11 16:55:10.440 2015-06-11 15:58:41  
20822 2015-06-11 16:55:13.170 2015-06-11 15:58:44  
20823 2015-06-11 15:58:40.250 2015-06-11 15:58:40  
20824 2015-06-11 16:55:14.770 2015-06-11 16:01:42  
20825 2015-06-11 16:55:11.250 2015-06-11 15:58:42  
20826 2015-06-11 16:55:13.460 2015-06-11 15:58:44  
20827 2015-06-11 15:57:45.910 2015-06-11 15:57:45  
20828 2015-06-11 15:57:01.150 2015-06-11 15:57:01  
20829 2015-06-11 15:56:59.170 2015-06-11 15:56:59  
20830 2015-06-11 15:57:03.490 2015-06-11 15:57:03  
20831 2015-06-11 15:57:00.160 2015-06-11 15:57:00  
20832 2015-06-11 15:58:38.190 2015-06-11 15:58:38  
20833 2015-06-11 16:55:13.660 2015-06-11 15:59:19  
20834 2015-06-11 16:55:12.490 2015-06-11 15:58:43  
20835 2015-06-11 15:58:39.290 2015-06-11 15:58:39  
20836 2015-06-11 16:55:14.000 2015-06-11 16:01:42  
20837 2015-06-11 16:55:08.940 2015-06-11 15:58:41  
20838 2015-06-11 16:55:12.190 2015-06-11 15:58:42  
20839 2015-06-11 16:55:12.800 2015-06-11 15:58:44  
20840 2015-06-11 16:55:07.740 2015-06-11 15:58:41  
20841 2015-06-11 16:55:11.650 2015-06-11 15:58:42  
20842 2015-06-11 15:58:39.850 2015-06-11 15:58:39  
20843 2015-06-11 16:55:14.340 2015-06-11 16:01:41  
20844 2015-06-11 16:55:10.350 2015-06-11 15:58:41  
20845 2015-06-11 16:55:13.050 2015-06-11 15:58:44  
20846 2015-06-11 15:58:40.200 2015-06-11 15:58:40  
20847 2015-06-11 16:55:14.660 2015-06-11 16:01:42  
20848 2015-06-11 16:55:11.100 2015-06-11 15:58:41  
20849 2015-06-11 16:55:13.330 2015-06-11 15:58:44  
20850 2015-06-11 15:57:45.910 2015-06-11 15:57:45  

[20851 rows x 18 columns]

df_student_assessment[(df_student_assessment['AssessmentTitle'] == 'ACT') | (df_student_assessment['AssessmentTitle'] == 'SAT') | (df_student_assessment['AssessmentTitle'] == 'TSI')]

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	StudentUSI	AssessmentTitle	AcademicSubjectDescriptorId	AssessedGradeLevelDescriptorId	Version	AdministrationDate	AdministrationEndDate	SerialNumber	AdministrationLanguageDescriptorId	AdministrationEnvironmentTypeId	RetestIndicatorTypeId	ReasonNotTestedTypeId	WhenAssessedGradeLevelDescriptorId	EventCircumstanceTypeId	EventDescription	Id	LastModifiedDate	CreateDate
16168	801	ACT	22	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	430F3DCE-482E-410F-B882-843D3D435B20	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16169	801	ACT	25	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	3FD8CE0D-6BA1-4CB0-AAF3-A3696436C51C	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16170	801	ACT	28	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	30BAA832-E6D9-4C7D-9B72-01959AAE5AE6	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16171	801	ACT	29	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	421AFA80-6859-40D2-8408-22DD8B5CF5F3	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16172	801	ACT	32	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	C261003B-CCB6-48C3-9FBB-68CB1C0B902B	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16173	801	ACT	33	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	C378B074-BDC8-4DF0-8BB2-BDC3BFAD239E	2015-06-11 15:54:14.660	2015-06-11 15:54:14
16272	805	ACT	22	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	5E342B17-C41C-4813-8A06-72FF96AAC057	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16273	805	ACT	25	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	BDA333D2-6D22-4170-B8D2-E5A0DF1C1BF1	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16274	805	ACT	28	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	832E3A4F-B5E9-409F-A4F8-C8DBC5E19C2A	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16275	805	ACT	29	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	F64B7596-0D12-4BBF-9C03-79F3D1F9973A	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16276	805	ACT	32	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	547441C7-7CEF-4B28-9FFF-26D0092F3940	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16277	805	ACT	33	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	A0D7ECFB-8ED7-45AE-8A92-DA691B13BFC1	2015-06-11 15:54:14.400	2015-06-11 15:54:14
16306	806	ACT	22	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	5BED6CFA-7725-411A-A87B-A4D1DA8E819D	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16307	806	ACT	25	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	13E1249F-F9DB-4AEF-87B8-8D0752540509	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16308	806	ACT	28	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	2F08B1F9-A808-4A8F-BBA7-1FBEE9A69A15	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16309	806	ACT	29	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	55E4E974-6615-49AD-B331-6568CCA14D2E	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16310	806	ACT	32	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	919EA6BA-1B86-413C-8DD8-FD97EFF82981	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16311	806	ACT	33	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	F453A86A-8ED7-4FA8-8C7C-F1EBA3B952CB	2015-06-11 15:54:14.340	2015-06-11 15:54:14
16368	808	ACT	22	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	F82B0314-CB19-466E-877F-50686E056342	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16369	808	ACT	25	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	EE8BB125-6097-48AE-A796-B1BD79A86A8F	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16370	808	ACT	28	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	441A5E95-D17B-48D9-A3F7-538BC9AA9CD4	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16371	808	ACT	29	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	C8205009-5A46-41FC-A772-0C4170E75BB1	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16372	808	ACT	32	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	40F70E5F-AA4F-4BA8-AB7F-0DAA01C59AE2	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16373	808	ACT	33	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	C3235FFF-2186-447B-AFD5-264D120E792B	2015-06-11 15:54:14.010	2015-06-11 15:54:14
16402	809	ACT	22	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	DAC4E629-5344-4B7A-844B-DFFCB5DF9D00	2015-06-11 15:54:14.200	2015-06-11 15:54:14
16403	809	ACT	25	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	05929D14-0621-48EC-BEB9-0824FCC5766E	2015-06-11 15:54:14.200	2015-06-11 15:54:14
16404	809	ACT	28	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	CF3DE849-0C9A-4F80-A697-7ADE5D51FB17	2015-06-11 15:54:14.200	2015-06-11 15:54:14
16405	809	ACT	29	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	619FF1EC-FAD7-4747-974B-D52638CC81C6	2015-06-11 15:54:14.200	2015-06-11 15:54:14
16406	809	ACT	32	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	87FD6F74-35BF-4AB0-AF2C-0BA0F779B958	2015-06-11 15:54:14.200	2015-06-11 15:54:14
16407	809	ACT	33	20	1	2010-12-01	None	0	517.0	3.0	NaN	NaN	1.0	None	None	164B3847-2834-4F9B-8B6C-95125B6F5990	2015-06-11 15:54:14.200	2015-06-11 15:54:14
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
19794	918	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	378B4126-138F-4E89-94CE-82D881574C84	2015-06-11 15:58:06.680	2015-06-11 15:58:06
19795	918	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	657C748B-EEF1-44A6-86F7-FD97FCF116DB	2015-06-11 15:58:06.710	2015-06-11 15:58:06
19796	918	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	161539CB-114A-481D-9DE4-CAF06A7D156D	2015-06-11 15:58:06.710	2015-06-11 15:58:06
19820	919	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	CCCF9EA0-4EC7-4B80-9F33-906DE7630901	2015-06-11 15:58:06.650	2015-06-11 15:58:06
19821	919	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	A3FFB2C7-9BE8-43C0-A713-9880E19FD7F8	2015-06-11 15:58:06.650	2015-06-11 15:58:06
19822	919	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	4C54CC30-8E11-473A-BFE8-E642B4F628CA	2015-06-11 15:58:06.670	2015-06-11 15:58:06
19868	921	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	E0D86B21-F0A2-4324-98CB-73F7776A3B85	2015-06-11 15:58:06.600	2015-06-11 15:58:06
19869	921	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	931AACFB-A1C9-4AFA-972C-3362C24C85A8	2015-06-11 15:58:06.600	2015-06-11 15:58:06
19870	921	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	F0C6E179-4984-4103-BAAA-9C00D571247B	2015-06-11 15:58:06.600	2015-06-11 15:58:06
20210	936	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	3FA0D6FA-24FE-4577-BE96-C4ECD5EA4562	2015-06-11 15:58:06.600	2015-06-11 15:58:06
20211	936	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	5F61531A-CCA1-44BF-8D4A-6FDF26D97717	2015-06-11 15:58:06.600	2015-06-11 15:58:06
20212	936	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	FD2AA7A7-CA5C-4A83-BC03-697D63CB893F	2015-06-11 15:58:06.650	2015-06-11 15:58:06
20277	939	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	943A8CE8-2F90-4AF7-80D4-C0FF612834E6	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20278	939	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	CC4FF13B-71F0-40A3-9BE5-5439223E0319	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20279	939	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	923FF25B-7204-4DB0-AEA3-94788A122EE9	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20303	940	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	F7CCE48A-FCCD-4A73-8896-DFE9A57CBA3E	2015-06-11 15:58:06.680	2015-06-11 15:58:06
20304	940	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	6B98E007-5C9D-449D-9472-3C7DB7DFC351	2015-06-11 15:58:06.680	2015-06-11 15:58:06
20305	940	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	9D8A1F08-8713-4771-9B75-3363366B719B	2015-06-11 15:58:06.680	2015-06-11 15:58:06
20521	949	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	DB8076C4-B277-46A9-93E3-280F7E7A87FF	2015-06-11 15:58:06.730	2015-06-11 15:58:06
20522	949	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	326DE908-E4FC-4A86-950C-7A15E55D8C7D	2015-06-11 15:58:06.730	2015-06-11 15:58:06
20523	949	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	1CCEB549-53FB-4E0B-B09A-72AC17A2BEAC	2015-06-11 15:58:06.760	2015-06-11 15:58:06
20612	953	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	B128EC1A-EC14-4C92-AFE6-777F0E7658EC	2015-06-11 15:58:06.730	2015-06-11 15:58:06
20613	953	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	1B59DE5E-D0CD-4C76-854B-D9C398AB4352	2015-06-11 15:58:06.730	2015-06-11 15:58:06
20614	953	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	F9D04F57-C586-47BC-870E-C80A72F808E8	2015-06-11 15:58:06.730	2015-06-11 15:58:06
20734	958	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	62A1C7AF-0C2D-44F6-83E8-F6A578E51BA2	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20735	958	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	442B2CEC-A07B-45B7-AF9C-6165AC4DCD0C	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20736	958	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	2EFC29A6-F84A-42DB-8052-186E8B0DE53C	2015-06-11 15:58:06.710	2015-06-11 15:58:06
20760	959	SAT	28	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	AF4BBEB5-E510-4E4D-8AF2-A22ACC1013CC	2015-06-11 15:58:06.680	2015-06-11 15:58:06
20761	959	SAT	29	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	A3CC12B8-6402-453E-89EA-7FE2FE89409D	2015-06-11 15:58:06.680	2015-06-11 15:58:06
20762	959	SAT	33	20	1	2011-05-01	None	0	517.0	3.0	1.0	NaN	NaN	None	None	0253D986-C39A-4C6E-BAA1-23926108EEAF	2015-06-11 15:58:06.680	2015-06-11 15:58:06

441 rows × 18 columns

All the students who have records for ACT or SAT or TSI

There is data for ACT only but there are no any students who have TSI or SAT marks.

df_course_trans_add_credit = pd.read_sql('SELECT * FROM edfi.CourseTranscriptEarnedAdditionalCredits', con=cnxn)

print(df_course_trans_add_credit.count())

AdditionalCreditTypeId           0
Credits                          0
CreateDate                       0
CourseAttemptResultTypeId        0
StudentUSI                       0
EducationOrganizationId          0
CourseEducationOrganizationId    0
SchoolYear                       0
TermDescriptorId                 0
CourseCode                       0
dtype: int64

    df_StudentAcademicRecordAcademicHonor = pd.read_sql('SELECT * FROM edfi.StudentAcademicRecordAcademicHonor', con=cnxn)

print(df_StudentAcademicRecordAcademicHonor.count())

AcademicHonorCategoryTypeId        0
StudentUSI                         0
SchoolYear                         0
TermDescriptorId                   0
EducationOrganizationId            0
HonorDescription                   0
HonorAwardDate                     0
HonorAwardExpiresDate              0
AchievementTitle                   0
AchievementCategoryDescriptorId    0
AchievementCategorySystem          0
IssuerName                         0
IssuerOriginURL                    0
Criteria                           0
CriteriaURL                        0
EvidenceStatement                  0
ImageURL                           0
CreateDate                         0
dtype: int64

analysis_sampleSandbox.md

Analysis of sample dataset

Source of dataset

What we are looking at?

1. Student records
2. School records
3. Assessment record
4. Student assessment records

Connecting with database

import pyodbc
import pandas as pd

conn_str = (
    r'Driver={SQL Server};'
    r'Server=(local);'
    r'Database=EdFi_Ods_Empty;'
    r'Trusted_Connection=yes;'
    )


try:
    cnxn = pyodbc.connect(conn_str)
except Exception as er:
    print(er)

df = pd.read_sql('SELECT * FROM edfi.Student', con=cnxn)

print(df)

     StudentUSI PersonalTitlePrefix  FirstName MiddleName LastSurname  \
0             1                 Mrs      Sarah       None     Stevens   
1             2                  Mr     Tyrone       None        Dyer   
2             3                  Ms       Lisa      Sybil       Woods   
3             4                 Mrs      Julie      Randi    Randolph   
4             5                 Mrs      Traci       None     Mathews   
5             6                  Mr       Dale       None     Jimenez   
6             7                  Mr     Jeremy  Alexander     Coleman   
7             8                  Dr    Vincent      Ruben      Orozco   
8             9                 Mrs      Julie       None       Beard   
9            10                 Mrs      April       None     Shelton   
10           11                  Mr       Rick    Jeffery        Owen   
11           12                  Mr      Peter       Wade       Pratt   
12           13                  Ms    Beverly    Nanette       Ortiz   
13           14                  Mr     Dennis    Gabriel  Mac Donald   
14           15                 Mrs      Janna    Marilyn     Jackson   
15           16                  Ms      Diana      Emily        Holt   
16           17                  Ms      Allie    Allison       Combs   
17           18                  Mr      Glenn        Max       Gross   
18           19                  Mr      Brent       None     Hartman   
19           20                 Mrs   Meredith    Felicia       Lloyd   
20           21                  Ms       Nora   Patricia     Blevins   
21           22                  Mr     Mathew       None     Ramirez   
22           23                  Mr  Frederick      Peter       Munoz   
23           24                  Mr      Byron      Keith   Wilkerson   
24           25                  Mr     Willie      Allan     Raymond   
25           26                  Mr    Russell      Jacob       Mayer   
26           27                  Mr     George       None       Velez   
27           28                  Ms     Fannie       None      Medina   
28           29                  Mr        Sam       Cory        Good   
29           30                  Mr     Robert       None       Mckee   
..          ...                 ...        ...        ...         ...   
932         933                  Mr      Allen       None        Bass   
933         934                 Mrs      Carly     Ginger      Phelps   
934         935                  Mr       Kyle       Troy        Owen   
935         936                  Dr      Alvin     Donald      Fields   
936         937                  Mr      Derek      Andre    Friedman   
937         938                 Mrs     Janine        Mae      Conrad   
938         939                  Ms      Ellen      Elena      Watson   
939         940                 Mrs    Leticia      Molly     Schultz   
940         941                 Mrs      Lilly       None       Costa   
941         942                 Mrs        Mia     Leanna        Levy   
942         943                  Ms    Marissa     Lauren     Summers   
943         944                 Mrs      Jaime     Cherie        Sosa   
944         945                  Mr    Charles       None    Franklin   
945         946                 Mrs    Beverly    Shirley        Wade   
946         947                  Ms    Kirsten        Eva      Davies   
947         948                  Mr       Kurt       None      Brewer   
948         949                  Ms    Adeline       Alma      Cannon   
949         950                  Mr   Mitchell       None      Travis   
950         951                  Mr       Rick      Steve       Kline   
951         952                  Mr     Harold    Roberto     Elliott   
952         953                  Dr      Andre    Clinton       Villa   
953         954                  Ms      Naomi       None         May   
954         955                  Mr      Julio       Alan     Montoya   
955         956                 Mrs      Becky       Amie        Todd   
956         957                 Mrs     Robbie     Virgie    Atkinson   
957         958                  Mr       Wade       None     Swanson   
958         959                  Ms     Kristi      Selma      Ritter   
959         960                  Ms    Cecelia       None   Blackwell   
960         961                 Mrs      Adela     Blanca      Branch   
961         962                  Mr     Sandro  Francisco      Miguel   

    GenerationCodeSuffix MaidenName  SexTypeId   BirthDate BirthCity  \
0                   None       None          1  2005-06-12      None   
1                   None       None          2  2003-11-13      None   
2                   None       None          1  1997-09-13      None   
3                   None       None          1  1996-07-22      None   
4                   None       None          1  1999-01-13      None   
5                   None       None          2  2005-08-15      None   
6                   None       None          2  2005-05-05      None   
7                   None       None          2  1995-04-01      None   
8                   None       None          1  2000-08-03      None   
9                   None       None          1  2004-08-17      None   
10                    Jr       None          2  1995-09-14      None   
11                  None       None          2  2004-04-13      None   
12                  None       None          1  2003-04-07      None   
13                    Jr       None          2  2003-05-24      None   
14                  None       None          1  2000-03-02      None   
15                  None       None          1  2005-09-29      None   
16                  None       None          1  1996-03-30      None   
17                  None       None          2  2001-12-19      None   
18                  None       None          2  1994-11-29      None   
19                  None       None          1  2000-04-26      None   
20                  None       None          1  2004-12-21      None   
21                  None       None          2  1999-12-15      None   
22                  None       None          2  2004-12-02      None   
23                  None       None          2  1998-09-24      None   
24                  None       None          2  1998-03-21      None   
25                  None       None          2  2003-08-14      None   
26                  None       None          2  2003-05-07      None   
27                  None       None          1  1997-11-30      None   
28                    Jr       None          2  1994-07-17      None   
29                  None       None          2  1997-08-29      None   
..                   ...        ...        ...         ...       ...   
932                 None       None          2  1996-05-07      None   
933                 None       None          1  2004-07-18      None   
934                 None       None          2  1996-12-29      None   
935                 None       None          2  1996-09-12      None   
936                 None       None          2  1996-02-23      None   
937                 None       None          1  1998-08-31      None   
938                 None       None          1  1999-05-25      None   
939                 None       None          1  1999-11-14      None   
940                 None       None          1  1995-07-12      None   
941                 None       None          1  2003-02-04      None   
942                 None       None          1  2003-11-15      None   
943                 None       None          1  1994-05-04      None   
944                 None       None          2  2003-03-12      None   
945                 None       None          1  1998-09-09      None   
946                 None       None          1  2002-07-06      None   
947                 None       None          2  1999-09-27      None   
948                 None       None          1  2001-01-10      None   
949                 None       None          2  2000-09-09      None   
950                 None       None          2  2003-09-09      None   
951                 None       None          2  2001-04-14      None   
952                 None       None          2  1998-06-09      None   
953                 None       None          1  2005-01-23      None   
954                 None       None          2  1997-08-15      None   
955                 None       None          1  1997-12-06      None   
956                 None       None          1  1999-12-17      None   
957                 None       None          2  1997-07-23      None   
958                 None       None          1  2000-05-20      None   
959                 None       None          1  2005-01-14      None   
960                 None       None          1  1997-09-09      None   
961                   Jr       None          2  2005-12-16      None   

              ...            LimitedEnglishProficiencyDescriptorId  \
0             ...                                              NaN   
1             ...                                              NaN   
2             ...                                              NaN   
3             ...                                              NaN   
4             ...                                              NaN   
5             ...                                              NaN   
6             ...                                              NaN   
7             ...                                              NaN   
8             ...                                              NaN   
9             ...                                              NaN   
10            ...                                              NaN   
11            ...                                              NaN   
12            ...                                              NaN   
13            ...                                              NaN   
14            ...                                              NaN   
15            ...                                              NaN   
16            ...                                              NaN   
17            ...                                              NaN   
18            ...                                              NaN   
19            ...                                              NaN   
20            ...                                              NaN   
21            ...                                              NaN   
22            ...                                              NaN   
23            ...                                              NaN   
24            ...                                              NaN   
25            ...                                              NaN   
26            ...                                              NaN   
27            ...                                              NaN   
28            ...                                              NaN   
29            ...                                              NaN   
..            ...                                              ...   
932           ...                                              NaN   
933           ...                                              NaN   
934           ...                                              NaN   
935           ...                                              NaN   
936           ...                                              NaN   
937           ...                                              NaN   
938           ...                                              NaN   
939           ...                                              NaN   
940           ...                                              NaN   
941           ...                                              NaN   
942           ...                                              NaN   
943           ...                                              NaN   
944           ...                                              NaN   
945           ...                                              NaN   
946           ...                                              NaN   
947           ...                                              NaN   
948           ...                                            608.0   
949           ...                                              NaN   
950           ...                                              NaN   
951           ...                                              NaN   
952           ...                                              NaN   
953           ...                                              NaN   
954           ...                                              NaN   
955           ...                                              NaN   
956           ...                                              NaN   
957           ...                                              NaN   
958           ...                                              NaN   
959           ...                                              NaN   
960           ...                                              NaN   
961           ...                                              NaN   

    DisplacementStatus LoginId BirthInternationalProvince  \
0                 None    None                       None   
1                 None    None                       None   
2                 None    None                       None   
3                 None    None                       None   
4                 None    None                       None   
5                 None    None                       None   
6                 None    None                       None   
7                 None    None                       None   
8                 None    None                       None   
9                 None    None                       None   
10                None    None                       None   
11                None    None                       None   
12                None    None                       None   
13                None    None                       None   
14                None    None                       None   
15                None    None                       None   
16                None    None                       None   
17                None    None                       None   
18                None    None                       None   
19                None    None                       None   
20                None    None                       None   
21                None    None                       None   
22                None    None                       None   
23                None    None                       None   
24                None    None                       None   
25                None    None                       None   
26                None    None                       None   
27                None    None                       None   
28                None    None                       None   
29                None    None                       None   
..                 ...     ...                        ...   
932               None    None                       None   
933               None    None                       None   
934               None    None                       None   
935               None    None                       None   
936               None    None                       None   
937               None    None                       None   
938               None    None                       None   
939               None    None                       None   
940               None    None                       None   
941               None    None                       None   
942               None    None                       None   
943               None    None                       None   
944               None    None                       None   
945               None    None                       None   
946               None    None                       None   
947               None    None                       None   
948               None    None                       None   
949               None    None                       None   
950               None    None                       None   
951               None    None                       None   
952               None    None                       None   
953               None    None                       None   
954               None    None                       None   
955               None    None                       None   
956               None    None                       None   
957               None    None                       None   
958               None    None                       None   
959               None    None                       None   
960               None    None                       None   
961               None    None                       None   

     CitizenshipStatusTypeId  StudentUniqueId  \
0                       None           555555   
1                       None           604821   
2                       None           604822   
3                       None           604823   
4                       None           604824   
5                       None           604825   
6                       None           604826   
7                       None           604827   
8                       None           604828   
9                       None           604829   
10                      None           604830   
11                      None           604831   
12                      None           604832   
13                      None           604833   
14                      None           604834   
15                      None           604835   
16                      None           604836   
17                      None           604837   
18                      None           604838   
19                      None           604839   
20                      None           604840   
21                      None           604841   
22                      None           604842   
23                      None           604843   
24                      None           604844   
25                      None           604845   
26                      None           604846   
27                      None           604847   
28                      None           604848   
29                      None           604849   
..                       ...              ...   
932                     None           605752   
933                     None           605753   
934                     None           605754   
935                     None           605755   
936                     None           605756   
937                     None           605757   
938                     None           605758   
939                     None           605759   
940                     None           605760   
941                     None           605761   
942                     None           605762   
943                     None           605763   
944                     None           605764   
945                     None           605765   
946                     None           605766   
947                     None           605767   
948                     None           605768   
949                     None           605769   
950                     None           605770   
951                     None           605771   
952                     None           605772   
953                     None           605773   
954                     None           605774   
955                     None           605775   
956                     None           605776   
957                     None           605777   
958                     None           605778   
959                     None           605779   
960                     None           605780   
961                     None           777777   

                                       Id        LastModifiedDate  \
0    32922A46-2D6D-4846-B529-49228EAACECF 2018-07-31 18:00:19.517   
1    EB22C693-5978-4A2F-A91F-EF36C5C1410C 2018-07-31 18:00:19.517   
2    8D6BCF09-0C93-493F-8DD7-50C1B795A783 2018-07-31 18:00:19.517   
3    1EF8E42F-00A8-4E6C-ACE2-04A700B83718 2018-07-31 18:00:19.517   
4    09B3E746-A34D-4305-868C-55B8C7941BB7 2018-07-31 18:00:19.517   
5    DCECB2D1-3E0A-41F2-9A3C-7BE3B16E0333 2018-07-31 18:00:19.517   
6    A3ADAACB-368F-47AB-9BA4-62747BEC8B18 2018-07-31 18:00:19.517   
7    CB84786B-2A08-4BF9-8E71-F47AF66560BF 2018-07-31 18:00:19.517   
8    33252637-8627-436C-AA61-80CF77388E95 2018-07-31 18:00:19.517   
9    EB812823-7376-4CD9-A161-F617B3371C38 2018-07-31 18:00:19.517   
10   E8F2F7D9-E3EF-4981-AF2A-6528AD15F0F2 2018-07-31 18:00:19.517   
11   F1C98DBE-787A-4D54-A631-520EC2C204D9 2018-07-31 18:00:19.517   
12   6F3792EF-6255-4C59-85A5-42C2F58074DA 2018-07-31 18:00:19.517   
13   EA2C1B66-1BE8-49D9-A128-632CFC32F604 2018-07-31 18:00:19.517   
14   A63E12EB-5422-444C-9FD9-037EF5DFB2C5 2018-07-31 18:00:19.517   
15   528CECF8-DD77-4BB2-9FE5-D9AE5B182ACD 2018-07-31 18:00:19.517   
16   29937F66-287F-4BC5-A64A-0E101883ADD8 2018-07-31 18:00:19.517   
17   9DAC9734-2294-4F82-BDAD-AD34A0F103E3 2018-07-31 18:00:19.517   
18   A5C2EE31-A169-46E4-A923-324F35382244 2018-07-31 18:00:19.517   
19   26050B87-26D7-4A95-8761-F3B74E426926 2018-07-31 18:00:19.517   
20   8A8DE76C-6F0D-40AD-8E55-A40D27218D59 2018-07-31 18:00:19.517   
21   0DCCA32F-4D67-4785-8371-D05CF47B8520 2018-07-31 18:00:19.517   
22   6A93D43B-9DCC-4D52-BBA2-3DD677FE762C 2018-07-31 18:00:19.517   
23   7C9DBC7A-3098-4EF8-A4E3-768971ED3A28 2018-07-31 18:00:19.517   
24   46E75CA5-30D0-4B08-9CC9-A7B33F2B2DCB 2018-07-31 18:00:19.517   
25   53BA06E0-39C2-4F4E-A145-E14CA2610B2F 2018-07-31 18:00:19.517   
26   149CE4D3-609B-44AB-95E2-4637A605C93C 2018-07-31 18:00:19.517   
27   FBD62265-0D6F-4684-B9EF-2EB6C3B509ED 2018-07-31 18:00:19.517   
28   F6BC81C1-BCD9-4040-92B0-6521468EFCCE 2018-07-31 18:00:19.517   
29   80B3993E-D32F-43F7-9860-A74DD9DCE273 2018-07-31 18:00:19.517   
..                                    ...                     ...   
932  854C5B7E-59A5-4F44-9441-CF444C4132C8 2018-07-31 18:00:19.517   
933  02359920-A3AC-4F4F-895F-07DACE683EB3 2018-07-31 18:00:19.517   
934  580CA12C-3F64-4C21-8E21-5BBC9C45A25E 2018-07-31 18:00:19.517   
935  35D56E77-2641-4177-A148-8F7E2F725173 2018-07-31 18:00:19.517   
936  3069CE4C-ED82-4D28-B25F-B02F9FA3ED7E 2018-07-31 18:00:19.517   
937  1E841EF2-DFA0-478B-8FAA-1CAA77EC2317 2018-07-31 18:00:19.517   
938  40729CF8-FE6B-4ABD-8AA2-BA6466316D0F 2018-07-31 18:00:19.517   
939  76C1398E-3B9F-4C6E-A07D-221E7DA8F47B 2018-07-31 18:00:19.517   
940  A54DA9FD-30A4-40BF-A977-9A3B97224242 2018-07-31 18:00:19.517   
941  528DD8C3-EBAA-4786-A5D4-71578C216CF4 2018-07-31 18:00:19.517   
942  A423680B-DA63-409A-8C77-5F24F7FE7B93 2018-07-31 18:00:19.517   
943  68297CAF-5369-444A-BB3C-3A1DD9EBF3FB 2018-07-31 18:00:19.517   
944  4FF6E8DE-77B1-4F97-A90D-28E776C5839B 2018-07-31 18:00:19.517   
945  43BFD414-3477-41E1-94EC-0A5A443A7453 2018-07-31 18:00:19.517   
946  FE95F1B8-88EF-4B52-9D39-4622FA479937 2018-07-31 18:00:19.517   
947  E51113DD-4E7D-4410-92CB-B463210EC9C1 2018-07-31 18:00:19.517   
948  87ABCCC2-3C34-4E7F-8F14-37CAE86CF268 2018-07-31 18:00:19.517   
949  E3A554A5-E8F2-4515-AC08-5A4AFFD4A1C5 2018-07-31 18:00:19.517   
950  5C340C45-2F3C-4646-8AAD-4929716765CA 2018-07-31 18:00:19.517   
951  F5868549-3C3C-47EF-A938-E4A1E922F2FB 2018-07-31 18:00:19.517   
952  5CF83F8B-8449-4C8E-969D-A5DADF649CFF 2018-07-31 18:00:19.517   
953  C435CC5C-D5A7-412C-8FF5-6543A71B7987 2018-07-31 18:00:19.517   
954  5D7F87D2-A6EE-4400-8565-B64D9BD37029 2018-07-31 18:00:19.517   
955  3CF856E2-1BC2-4CF0-A753-42BA4F4B92A4 2018-07-31 18:00:19.517   
956  A5A65133-F326-4870-9C85-236EB517E200 2018-07-31 18:00:19.517   
957  0365BDD8-187D-4FD9-BFE4-C1B453566FA9 2018-07-31 18:00:19.517   
958  0722C5A6-7EA0-42D7-93B6-0EC54535ACB3 2018-07-31 18:00:19.517   
959  28D97E67-5717-4968-9D82-4B73C5D48525 2018-07-31 18:00:19.517   
960  D3C2CA15-8806-4DDA-B55F-220450BB4830 2018-07-31 18:00:19.517   
961  F3710DAA-12C6-48A2-AC49-5446588D1B23 2018-07-31 18:00:19.517   

                 CreateDate BirthCountryDescriptorId  
0   2018-07-31 18:00:19.517                     None  
1   2018-07-31 18:00:19.517                     None  
2   2018-07-31 18:00:19.517                     None  
3   2018-07-31 18:00:19.517                     None  
4   2018-07-31 18:00:19.517                     None  
5   2018-07-31 18:00:19.517                     None  
6   2018-07-31 18:00:19.517                     None  
7   2018-07-31 18:00:19.517                     None  
8   2018-07-31 18:00:19.517                     None  
9   2018-07-31 18:00:19.517                     None  
10  2018-07-31 18:00:19.517                     None  
11  2018-07-31 18:00:19.517                     None  
12  2018-07-31 18:00:19.517                     None  
13  2018-07-31 18:00:19.517                     None  
14  2018-07-31 18:00:19.517                     None  
15  2018-07-31 18:00:19.517                     None  
16  2018-07-31 18:00:19.517                     None  
17  2018-07-31 18:00:19.517                     None  
18  2018-07-31 18:00:19.517                     None  
19  2018-07-31 18:00:19.517                     None  
20  2018-07-31 18:00:19.517                     None  
21  2018-07-31 18:00:19.517                     None  
22  2018-07-31 18:00:19.517                     None  
23  2018-07-31 18:00:19.517                     None  
24  2018-07-31 18:00:19.517                     None  
25  2018-07-31 18:00:19.517                     None  
26  2018-07-31 18:00:19.517                     None  
27  2018-07-31 18:00:19.517                     None  
28  2018-07-31 18:00:19.517                     None  
29  2018-07-31 18:00:19.517                     None  
..                      ...                      ...  
932 2018-07-31 18:00:19.517                     None  
933 2018-07-31 18:00:19.517                     None  
934 2018-07-31 18:00:19.517                     None  
935 2018-07-31 18:00:19.517                     None  
936 2018-07-31 18:00:19.517                     None  
937 2018-07-31 18:00:19.517                     None  
938 2018-07-31 18:00:19.517                     None  
939 2018-07-31 18:00:19.517                     None  
940 2018-07-31 18:00:19.517                     None  
941 2018-07-31 18:00:19.517                     None  
942 2018-07-31 18:00:19.517                     None  
943 2018-07-31 18:00:19.517                     None  
944 2018-07-31 18:00:19.517                     None  
945 2018-07-31 18:00:19.517                     None  
946 2018-07-31 18:00:19.517                     None  
947 2018-07-31 18:00:19.517                     None  
948 2018-07-31 18:00:19.517                     None  
949 2018-07-31 18:00:19.517                     None  
950 2018-07-31 18:00:19.517                     None  
951 2018-07-31 18:00:19.517                     None  
952 2018-07-31 18:00:19.517                     None  
953 2018-07-31 18:00:19.517                     None  
954 2018-07-31 18:00:19.517                     None  
955 2018-07-31 18:00:19.517                     None  
956 2018-07-31 18:00:19.517                     None  
957 2018-07-31 18:00:19.517                     None  
958 2018-07-31 18:00:19.517                     None  
959 2018-07-31 18:00:19.517                     None  
960 2018-07-31 18:00:19.517                     None  
961 2018-07-31 18:00:19.517                     None  

[962 rows x 28 columns]

(df.count())

StudentUSI                                   962
PersonalTitlePrefix                          962
FirstName                                    962
MiddleName                                   495
LastSurname                                  962
GenerationCodeSuffix                          16
MaidenName                                     0
SexTypeId                                    962
BirthDate                                    962
BirthCity                                      0
BirthStateAbbreviationTypeId                   0
DateEnteredUS                                  0
MultipleBirthStatus                            0
ProfileThumbnail                               0
HispanicLatinoEthnicity                      962
OldEthnicityTypeId                           950
EconomicDisadvantaged                         23
SchoolFoodServicesEligibilityDescriptorId     23
LimitedEnglishProficiencyDescriptorId         12
DisplacementStatus                             0
LoginId                                        0
BirthInternationalProvince                     0
CitizenshipStatusTypeId                        0
StudentUniqueId                              962
Id                                           962
LastModifiedDate                             962
CreateDate                                   962
BirthCountryDescriptorId                       0
dtype: int64

Total number of student general information: 962

(pd.Series({c: df[c].unique() for c in df}))

StudentUSI                                   [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14...
PersonalTitlePrefix                                                          [Mrs, Mr, Ms, Dr]
FirstName                                    [Sarah, Tyrone, Lisa, Julie, Traci, Dale, Jere...
MiddleName                                   [None, Sybil, Randi, Alexander, Ruben, Jeffery...
LastSurname                                  [Stevens, Dyer, Woods, Randolph, Mathews, Jime...
GenerationCodeSuffix                                                            [None, Jr, II]
MaidenName                                                                              [None]
SexTypeId                                                                               [1, 2]
BirthDate                                    [2005-06-12, 2003-11-13, 1997-09-13, 1996-07-2...
BirthCity                                                                               [None]
BirthStateAbbreviationTypeId                                                            [None]
DateEnteredUS                                                                           [None]
MultipleBirthStatus                                                                     [None]
ProfileThumbnail                                                                        [None]
HispanicLatinoEthnicity                                                          [False, True]
OldEthnicityTypeId                                              [1.0, 3.0, 4.0, 5.0, 2.0, nan]
EconomicDisadvantaged                                                      [True, None, False]
SchoolFoodServicesEligibilityDescriptorId                                  [668.0, nan, 669.0]
LimitedEnglishProficiencyDescriptorId                                             [nan, 608.0]
DisplacementStatus                                                                      [None]
LoginId                                                                                 [None]
BirthInternationalProvince                                                              [None]
CitizenshipStatusTypeId                                                                 [None]
StudentUniqueId                              [555555, 604821, 604822, 604823, 604824, 60482...
Id                                           [32922A46-2D6D-4846-B529-49228EAACECF, EB22C69...
LastModifiedDate                                               [2018-07-31T18:00:19.517000000]
CreateDate                                                     [2018-07-31T18:00:19.517000000]
BirthCountryDescriptorId                                                                [None]
dtype: object

Uniqueness of data

df2 = pd.read_sql('SELECT * FROM edfi.School', con=cnxn)

(df2.count())

SchoolId                                    3
LocalEducationAgencyId                      3
SchoolTypeId                                3
CharterStatusTypeId                         3
TitleIPartASchoolDesignationTypeId          3
MagnetSpecialProgramEmphasisSchoolTypeId    0
AdministrativeFundingControlDescriptorId    3
InternetAccessTypeId                        0
CharterApprovalAgencyTypeId                 0
CharterApprovalSchoolYear                   0
dtype: int64

Number of schools recorded in loaded sample

(pd.Series({c: df2[c].unique() for c in df2}))

SchoolId                                    [255901001, 255901044, 255901107]
LocalEducationAgencyId                                               [255901]
SchoolTypeId                                                              [2]
CharterStatusTypeId                                                       [4]
TitleIPartASchoolDesignationTypeId                                        [1]
MagnetSpecialProgramEmphasisSchoolTypeId                               [None]
AdministrativeFundingControlDescriptorId                                 [43]
InternetAccessTypeId                                                   [None]
CharterApprovalAgencyTypeId                                            [None]
CharterApprovalSchoolYear                                              [None]
dtype: object

Uniqueness of school records

(df2.nunique())

SchoolId                                    3
LocalEducationAgencyId                      1
SchoolTypeId                                1
CharterStatusTypeId                         1
TitleIPartASchoolDesignationTypeId          1
MagnetSpecialProgramEmphasisSchoolTypeId    0
AdministrativeFundingControlDescriptorId    1
InternetAccessTypeId                        0
CharterApprovalAgencyTypeId                 0
CharterApprovalSchoolYear                   0
dtype: int64

df_assessment = pd.read_sql('SELECT * FROM edfi.Assessment', con=cnxn)

(df_assessment.count())

AssessmentTitle                         76
AssessedGradeLevelDescriptorId          76
AssessmentCategoryDescriptorId          76
AcademicSubjectDescriptorId             76
LowestAssessedGradeLevelDescriptorId     0
AssessmentForm                           0
Version                                 76
RevisionDate                            76
MaxRawScore                             76
Nomenclature                             0
AssessmentPeriodDescriptorId             0
AssessmentFamilyTitle                    0
Namespace                                0
Id                                      76
LastModifiedDate                        76
CreateDate                              76
dtype: int64

Assessment types in the loaded set

(df_assessment.nunique())

AssessmentTitle                         68
AssessedGradeLevelDescriptorId          10
AssessmentCategoryDescriptorId           2
AcademicSubjectDescriptorId              7
LowestAssessedGradeLevelDescriptorId     0
AssessmentForm                           0
Version                                 68
RevisionDate                            22
MaxRawScore                              9
Nomenclature                             0
AssessmentPeriodDescriptorId             0
AssessmentFamilyTitle                    0
Namespace                                0
Id                                      76
LastModifiedDate                         1
CreateDate                               1
dtype: int64

(pd.Series({c: df_assessment[c].unique() for c in df_assessment}))

AssessmentTitle                         [2nd Grade ELA 2nd Six Weeks 2012-2013, 2nd Gr...
AssessedGradeLevelDescriptorId          [515, 519, 507, 505, 517, 516, 503, 520, 511, ...
AssessmentCategoryDescriptorId                                                   [66, 70]
AcademicSubjectDescriptorId                                      [17, 15, 1, 3, 4, 9, 16]
LowestAssessedGradeLevelDescriptorId                                               [None]
AssessmentForm                                                                     [None]
Version                                 [5221, 4874, 5317, 4913, 5114, 4854, 4863, 485...
RevisionDate                            [2012-10-23, 2012-09-26, 2012-10-26, 2012-10-2...
MaxRawScore                                          [10, 13, 15, 16, 19, 30, 25, 1, 750]
Nomenclature                                                                       [None]
AssessmentPeriodDescriptorId                                                       [None]
AssessmentFamilyTitle                                                              [None]
Namespace                                                                          [None]
Id                                      [19B9EEBE-043F-46F9-98E0-6D322BC24E40, 2D74E61...
LastModifiedDate                                          [2018-07-31T18:00:42.340000000]
CreateDate                                                [2018-07-31T18:00:42.340000000]
dtype: object

df_assessment[(df_assessment['AssessmentTitle'] == 'ACT') | (df_assessment['AssessmentTitle'] == 'SAT') | (df_assessment['AssessmentTitle'] == 'TSI')]

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	AssessmentTitle	AssessedGradeLevelDescriptorId	AssessmentCategoryDescriptorId	AcademicSubjectDescriptorId	LowestAssessedGradeLevelDescriptorId	AssessmentForm	Version	RevisionDate	MaxRawScore	Nomenclature	AssessmentPeriodDescriptorId	AssessmentFamilyTitle	Namespace	Id	LastModifiedDate	CreateDate
53	ACT	520	70	1	None	None	1	2005-03-12	30	None	None	None	None	6034F8CD-AC9F-4F48-B245-93EE64117605	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
54	ACT	520	70	3	None	None	1	2005-03-12	25	None	None	None	None	D39B5C2B-C727-4971-9B43-ADBBE09C4204	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
55	ACT	520	70	4	None	None	1	2005-03-12	25	None	None	None	None	BDB0D8D7-B29D-4366-A62A-E19AA66E66A3	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
56	ACT	520	70	9	None	None	1	2005-03-12	25	None	None	None	None	22611264-39C2-4064-9AEF-48B2618C677C	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
57	ACT	520	70	15	None	None	1	2005-03-12	25	None	None	None	None	8F1419ED-5C15-4515-B8CA-10C8777B687F	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
58	ACT	520	70	16	None	None	1	2005-03-12	25	None	None	None	None	05D04DD2-CF33-467C-ACFD-8512F37BD0B2	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
73	SAT	520	70	4	None	None	1	2005-03-12	750	None	None	None	None	83BD662E-65E5-46F0-8E7E-9C739AF97BB0	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
74	SAT	520	70	9	None	None	1	2005-03-12	750	None	None	None	None	8DB68398-A767-431E-8B6B-18F08B0DDE31	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340
75	SAT	520	70	15	None	None	1	2005-03-12	750	None	None	None	None	2E3083C9-4E66-4271-8073-CE2F98790352	2018-07-31 18:00:42.340	2018-07-31 18:00:42.340

There is no TSI defined in assessment table

df_student_assessment = pd.read_sql('SELECT * FROM edfi.StudentAssessment', con=cnxn)

(df_student_assessment.count())

StudentUSI                            4685
AssessmentTitle                       4685
AcademicSubjectDescriptorId           4685
AssessedGradeLevelDescriptorId        4685
Version                               4685
AdministrationDate                    4685
AdministrationEndDate                    0
SerialNumber                           441
AdministrationLanguageDescriptorId    4685
AdministrationEnvironmentTypeId       4685
RetestIndicatorTypeId                 4283
ReasonNotTestedTypeId                    0
WhenAssessedGradeLevelDescriptorId     402
EventCircumstanceTypeId                  0
EventDescription                         0
Id                                    4685
LastModifiedDate                      4685
CreateDate                            4685
dtype: int64

All students count which have any type of assessment given

(pd.Series({c: df_student_assessment[c].unique() for c in df_student_assessment}))

StudentUSI                            [2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17,...
AssessmentTitle                       [3rd grade math  2nd Six Weeks 2012-2013, 3rd ...
AcademicSubjectDescriptorId                                    [15, 17, 1, 3, 4, 9, 16]
AssessedGradeLevelDescriptorId        [519, 511, 518, 516, 520, 517, 515, 505, 503, ...
Version                               [5114, 4854, 4863, 4858, 5280, 4871, 5293, 476...
AdministrationDate                    [2012-10-29, 2012-09-28, 2010-12-01, 2010-09-2...
AdministrationEndDate                                                            [None]
SerialNumber                                                                  [None, 0]
AdministrationLanguageDescriptorId                                                [556]
AdministrationEnvironmentTypeId                                                     [3]
RetestIndicatorTypeId                                                        [1.0, nan]
ReasonNotTestedTypeId                                                            [None]
WhenAssessedGradeLevelDescriptorId                                         [nan, 504.0]
EventCircumstanceTypeId                                                          [None]
EventDescription                                                                 [None]
Id                                    [A659A664-523D-4081-80A7-EF3AE0C4BB19, 0A9A1A2...
LastModifiedDate                                        [2018-07-31T18:00:56.407000000]
CreateDate                                              [2018-07-31T18:00:56.407000000]
dtype: object

(pd.Series({c: df_student_assessment[c].unique() for c in df_student_assessment}))

StudentUSI                            [2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17,...
AssessmentTitle                       [3rd grade math  2nd Six Weeks 2012-2013, 3rd ...
AcademicSubjectDescriptorId                                    [15, 17, 1, 3, 4, 9, 16]
AssessedGradeLevelDescriptorId        [519, 511, 518, 516, 520, 517, 515, 505, 503, ...
Version                               [5114, 4854, 4863, 4858, 5280, 4871, 5293, 476...
AdministrationDate                    [2012-10-29, 2012-09-28, 2010-12-01, 2010-09-2...
AdministrationEndDate                                                            [None]
SerialNumber                                                                  [None, 0]
AdministrationLanguageDescriptorId                                                [556]
AdministrationEnvironmentTypeId                                                     [3]
RetestIndicatorTypeId                                                        [1.0, nan]
ReasonNotTestedTypeId                                                            [None]
WhenAssessedGradeLevelDescriptorId                                         [nan, 504.0]
EventCircumstanceTypeId                                                          [None]
EventDescription                                                                 [None]
Id                                    [A659A664-523D-4081-80A7-EF3AE0C4BB19, 0A9A1A2...
LastModifiedDate                                        [2018-07-31T18:00:56.407000000]
CreateDate                                              [2018-07-31T18:00:56.407000000]
dtype: object

df_student_assessment[(df_student_assessment['AssessmentTitle'] == 'ACT') | (df_student_assessment['AssessmentTitle'] == 'SAT') | (df_student_assessment['AssessmentTitle'] == 'TSI')]

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	StudentUSI	AssessmentTitle	AcademicSubjectDescriptorId	AssessedGradeLevelDescriptorId	Version	AdministrationDate	AdministrationEndDate	SerialNumber	AdministrationLanguageDescriptorId	AdministrationEnvironmentTypeId	RetestIndicatorTypeId	ReasonNotTestedTypeId	WhenAssessedGradeLevelDescriptorId	EventCircumstanceTypeId	EventDescription	Id	LastModifiedDate	CreateDate
24	8	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	D4432484-E845-4C92-A486-DD1D236BD5E8	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
25	8	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	11B2DCF3-8E4B-4630-98B9-FD6486A82014	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
26	8	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	6597AB9A-126B-4816-A3B8-68C8FDC68278	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
27	8	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	A4169D02-732E-414A-AA59-E49AAFE0F705	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
28	8	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	8BA9C757-C0D1-47D6-801C-F7E81BD496E5	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
29	8	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	63FEB9FD-3BFD-4AD6-9F6B-E13064472487	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
43	11	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	C41C2024-2CAA-46F0-81FC-F700F8B600D0	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
44	11	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	EA37FF09-D40B-4DA2-B0B4-DAC0A92A4BAD	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
45	11	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	272E4A63-EBAD-48D9-AF26-5E50F435485A	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
46	11	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	E218774F-3344-4129-8C57-4A2735151CB0	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
47	11	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	194C38A3-CC62-4323-835D-9670EA07A65F	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
48	11	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	9E7892CE-361B-4B00-9399-B860DFC9BFBD	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
316	64	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	59587CA6-28BE-4564-AEBD-ECC634B7700F	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
317	64	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	0074A1A3-89C9-4629-98C1-8EAF7F4835C4	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
318	64	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	BA69287C-6949-49D5-B5DA-3133932264D0	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
319	64	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	5542E25C-5A16-4F31-BD8C-59653143A74E	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
320	64	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	B724BE60-A8EF-482F-870C-123E9C825E0F	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
321	64	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	548F8A49-A915-4441-98AD-A0FE23CA6828	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
334	67	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	328006F2-519F-47C6-9D84-81ABAE739FC7	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
335	67	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	D5309EC5-4016-4FE6-9433-A3EB341A95A9	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
336	67	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	16BC679B-F375-4658-AE82-E5FBFD00FE1B	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
337	67	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	D0835EA2-A5E2-4289-B290-C8A891489C26	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
338	67	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	95D15841-0C74-4AB9-8671-E433733404A4	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
339	67	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	E0042E06-7E29-477D-BBD1-4C1BD0E9CBE0	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
368	77	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	BA501980-F603-400F-8525-70813FB0087F	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
369	77	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	B034A8BD-45C6-4DFB-848D-32419786413D	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
370	77	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	F80C01F2-300A-45C2-9396-CC9836C828EA	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
371	77	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	549FE1F8-DB86-4A13-AE44-304FAEAE493B	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
372	77	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	77324246-6B8C-478E-A6BC-875C5F8C680D	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
373	77	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	93627D44-E3BB-4708-A9B0-7AAFA2FB23A0	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
4342	897	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	DF9EB41F-955C-4435-9FD2-0AC0BEB34C83	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4343	897	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	36CE7A7B-6C61-4EBD-9EF3-52BFAFA091CB	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4344	897	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	E8270DAC-AA7A-4536-8567-57906B03D22C	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4345	897	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	E9CE0A2D-162F-4138-BF23-D108CCFF5DC4	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4346	897	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	9A48A7BC-DCF6-499B-A199-8CB619F612AC	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4347	897	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	641CDB69-5924-46C3-998A-1FE837728DA2	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4354	902	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	FC46DC73-207A-4B81-83F9-F41F286CEC91	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4355	902	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	61EA2514-7F58-4C57-AEC1-4431479CD39A	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4356	902	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	DD7CCF72-3407-42D8-93A4-20E53CDFE7C4	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4357	902	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	1AA461CC-B10F-4A9E-BAB9-4A26D646BE2D	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4358	902	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	A9DF1A40-5F1E-4647-A156-8CEF067E572C	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4359	902	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	72BACDC0-1671-410B-9EDD-FB5ACD3884D9	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4406	913	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	648B0582-A81A-4EFD-A6F6-98D6785A910C	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4407	913	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	2EE62115-F8B5-4433-8D72-6F5E46576EA9	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4408	913	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	BF7576E5-6422-44F0-933D-72F075BD6A33	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4409	913	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	27FB57CD-8503-4191-85D4-515EC2354349	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4410	913	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	9B7D6716-AE2F-4193-86CD-CF346D11E5B8	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4411	913	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	28809278-1926-4DA0-91BD-FBD578DB93DD	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4505	930	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	9FB88A2B-8D29-45F3-BCE1-5316B9781F67	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4506	930	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	4B908877-FBD9-41D0-8C84-6E38C08C44E2	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4507	930	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	A2FE18D9-BC09-47E2-8DA6-3584588934AE	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4508	930	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	EEF5F7DA-F65B-49A8-9873-F881FE76C27B	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4509	930	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	510C83AE-ABD7-4BC8-8CF4-4AB05BD07953	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4510	930	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	D813BD0C-5F13-45D6-903F-4654641D164C	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4511	931	ACT	1	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	9FC4646D-CF83-4AFF-8B15-C3B412E87946	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4512	931	ACT	3	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	1D3DE123-8624-4C1B-8568-EB724ECD2032	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4513	931	ACT	4	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	EE246BD8-84E3-452D-A71E-1E0503A586DE	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4514	931	ACT	9	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	94383D10-59DF-4EC3-97A1-C74887CD4AE6	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4515	931	ACT	15	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	1F9A3688-18A6-42D4-8546-7743E838DE87	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407
4516	931	ACT	16	520	1	2010-12-01	None	0	556	3	NaN	None	504.0	None	None	EE7258F9-B6E2-4AF8-AB74-30D217A9EDD2	2018-07-31 18:00:56.407	2018-07-31 18:00:56.407

441 rows × 18 columns

All the students who have records for ACT or SAT or TSI

There is data for ACT and SAT but there is no any students who have TSI marks.

df_course_trans_add_credit = pd.read_sql('SELECT * FROM edfi.CourseTranscriptEarnedAdditionalCredits', con=cnxn)

(df_course_trans_add_credit.count())

AdditionalCreditTypeId           0
Credits                          0
CreateDate                       0
CourseAttemptResultTypeId        0
StudentUSI                       0
EducationOrganizationId          0
CourseEducationOrganizationId    0
SchoolYear                       0
TermDescriptorId                 0
CourseCode                       0
dtype: int64

    df_StudentAcademicRecordAcademicHonor = pd.read_sql('SELECT * FROM edfi.StudentAcademicRecordAcademicHonor', con=cnxn)

(df_StudentAcademicRecordAcademicHonor.count())

AcademicHonorCategoryTypeId        0
StudentUSI                         0
SchoolYear                         0
TermDescriptorId                   0
EducationOrganizationId            0
HonorDescription                   0
HonorAwardDate                     0
HonorAwardExpiresDate              0
AchievementTitle                   0
AchievementCategoryDescriptorId    0
AchievementCategorySystem          0
IssuerName                         0
IssuerOriginURL                    0
Criteria                           0
CriteriaURL                        0
EvidenceStatement                  0
ImageURL                           0
CreateDate                         0
dtype: int64