Specify S4Y data workflow

[FranckCo]

Specify the data location, formats, ETL, etc.

[francescadag]

The Italian data to be used for the pilot belong to the MIUR (The Ministry for Education) catalogue, consider both public and private schools, are the following:

• Registry information on schools;
• Students by course year and age group;
• Second grade secondary school students by address;
• Number of children by age group.
  The datasets have been uploaded to the FTP area of the project and are located in the specific location. At the moment the datasets referring to the year 2019 have been loaded, but all the school years could be considered and added in order to allow the user to also make an analysis of the data over time.
  The datasets considered are in RDF format.

The reference dataset for the list and location of schools of all levels is available at the following link and is also considered for the GF pilot. The coordinates of the schools have been added to this dataset and the output file is present in the FTP area of the project.

In addition, we will extract aggregated data about French and Italian student population from the European Data Portal datasets: a selection of datasets available on the European Data Portal, published by Publications Office of the European Union.
The Education and training statistics indicators provide information on the participation of individuals in education and training activities, education financing and teaching staff as well as on outcomes of education, view the link.
In this expirence, we concentrate on: Participation in Education and Training.
The description of data and metadata is available in the Deliverable 2.1, paragraph 3.1.2.

[pafrance]

Data structure for Italian School datasets provide the number of students given the following dimensions:

• school id
• localization
• type of institution
• scholastic year
• education level

We need the similar data structure for french data, if available.

[ThomasPO]

Let's consider the following structure:

Field name	Description	Data type
school_id	Unique school identifier	string
name	Official name of the school	string
lambert_x	Latitude of the school	Float
lambert_y	Longitude of the school	Float
institution_type	Type of institution (Public or Private)	Code list
school_level	Level of school (Nursery, Primary, Secondary)	Code list
scholastic_year	Scholastic year	Year (from 2019 to 2021)
course_year	0 for Nursery school, 1 for first year of Primary school, 2 for second year of Primary school and so on until the last year of upper secondary shool	Code list
students_number	Number of students (measure)	integer

How to get French data compliant with this structure?

From the school registry dataset (called "Address and geolocalization of primary and secondary educational institutions"), we can get:

• school id
• name
• lambert_x
• lambert_y
• institution_type
• school_level (has to be recoded from a French classification)

To note:

• that some schools are open, to be opened or closed.
• Geographic coordinates are expressed using the Lambert 93 coordinate system.

Then, we can get number of students for the three last scholastic year (2019, 2020 and 2021) from 4 datasets :

• Student enrollment by grade and number of classes per school (only primary and nursery school)
• Number of students by grade, gender, most frequent modern languages 1 and 2, by lower secondary school
• Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (school of general and technological education)
• Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (vocational school)

course_year dimension has to be coded from each measure "number of students by grade" (its exists 1 measure “Number of students” per grade).

[pafrance]

Here's a sketch with data work flow we are implementing

Data workflow needs the following steps to be implemented:

1. Define one single Ontology as conceptual data model of common domain of S4Y
2. Data Harmonization
   2.1. Prepare a common data federated archive to harmonize data formats
   2.2. Data transformation: i.e. Transform Lambert coordinates to common EU standards WGF84 and then to LAU using the same services developed for the other pilots
3. Map Ontology to data sources
4. Use a SparQL query to integrate data
5. Export integrated dataset in json format for context broker ingestion

[francescadag]

Procedure to extrapolate the French datasets listed by @ThomasPO
The platform provides APIs for the datasets extrapolation, whose parameters can be suitably configured to extrapolate only the fields of interest.
API used:
https://data.education.gouv.fr/api/v2/catalog/datasets/{dataset_id}/exports/{format}

From the school registry dataset (called "Address and geolocalization of primary and secondary educational institutions"), have been extracted:
School id, Name, Lambert_x, Lambert_y, Institution_type, School_level

There is also the Status_code field which in the dataset has the following value: 1 = open; 2 = to be closed; 3 = to open. Only open schools were considered and therefore with Status_code equal to 1.
API to get the dataset in CSV.
API to get the dataset in RDF.

Datasets from which the number of students for the three last scholastic year (2019, 2020 and 2021) have been extracted with such fields:
Scholastic_year, School_id, Institution_type, Number_of_classes, Number_of_students

1. Student enrollment by grade and number of classes per school (only primary and nursery school
   API to get the dataset in CSV.
   API to get the dataset in RDF.

2. Number of students by grade, gender, most frequent modern languages 1 and 2, by lower secondary school
   API to get the dataset in CSV.
   API to get the dataset in RDF.

3. Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (school of general and technological education
   API to get the dataset in CSV.
   API to get the dataset in RDF.

4. Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (vocational school
   API to get the dataset in CSV.
   API to get the dataset in RDF.

These datasets contain many other fields that may be of interest. If so, they can then be added to the API for extraction.
They have been added in the FTP server in RDF format.

[pafrance]

We checked French data models and we need a set of other variables as follows:

1. Student's sex.
2. Age class. (0-5 yrs, 5-10 yrs etc)
3. Student's course of study:
   This is a complex hierarchy to be defined together based on some sort of standard.
4. Coordinates should be converted in EU standard
5. Add NUTS3 and LAU

[francescadag]

I report the following requests for @ThomasPO from a Michele's mail regarding the French data model:
1 - Which is the mapping to recode school level from French classification ? Have we to decode from the french field "Code_nature". Otherwise have you a mapping to recode from french classification to ISCED ?
2 - We have founded in french school dataset also "Position" field in WGS84 international format. Are these better than Lambert coordinates ?
3 - Is french field "Code_commune" the same of LAU code or it must be recoded ? and how?
4 - From column "Dénomination principale" can we derive the type of Study area (kind of Lycee, Technological, ....)

[francescadag]

Upon request, I get the complete datasets concerning the Number of students for the three last scholastic year (2019, 2020 and 2021):

1. Student enrollment by grade and number of classes per school (only primary and nursery school
   API to get the dataset in CSV.
   API to get the dataset in RDF.
2. Number of students by grade, gender, most frequent modern languages 1 and 2, by lower secondary school
   API to get the dataset in CSV.
   API to get the dataset in RDF.
3. Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (school of general and technological education
   API to get the dataset in CSV.
   API to get the dataset in RDF.
4. Number of students by grade, gender, most frequent modern languages 1 and 2, by upper secondary school (vocational school
   API to get the dataset in CSV.
   API to get the dataset in RDF.
   The datasets loaded in the previous comment contained only some columns of these starting datasets.
   They have been added in the FTP server here in CSV and RDF format.

[ThomasPO]

@francescadag
when you ask for "Student's sex" and "Age class. (0-5 yrs, 5-10 yrs etc)" variables, do you mean these variables for each school?
I can only find "sex" variable for each upper secondary school.

[francescadag]

Hi Tomas, does your request relate to this comment? I therefore think is addressed to @pafrance .

[ThomasPO]

Yes, exactly. It's addressed to @pafrance