top of page

Patterns of Lisbon Education System

Data analysis to the Lisbon education system, public vs private schools, time analysis to the number of students over specific time periods, number of students by public school and class, location of schools and other relevant details.

Business question: What is the overall number of students distributed by schools, education levels and activities in Lisbon public schools?

Tools used: Microsoft Power BI.

Image_03.JPG
Image_05.JPG
Image_06.JPG
Image_07.JPG
Image_14.JPG
Key Insights

  • Lisbon City has the lowest percentage of students in the public sector, 55%, when compared with Lisbon Metropolitan Area, 75%, and Portugal, 80% (year 2020/2021).

  • In Lisbon City, pre-primary level has the lowest percentage of students in the public sector, with only 33% (year 2020/2021).

  • In Lisbon City, when combined public and private sector, the number of students from 2011 to 2020 dropped by 3.3%, in the public sector alone dropped by 15.3% and in the private sector alone, increased by 6.7%.

  • This seems to indicate that in Lisbon City the private sector has grown consistently over the years, even if the majority of students are still in the public sector.

  • In 2020, the public sector accommodated 159 schools for 117.749 students, while the private sector accommodated 231 schools for 50.637 students, this seems to indicate that there is higher student density in the public sector (schools with more students).

  • Lisbon Municipality runs a total of 137 schools, from pre-primary to secondary.

  • There are big differences between the schools with the highest and lowest number of students, for example:

  • In primary, 2º cycle, 430 to 7 students per school

  • In primary, 3º cycle, 602 to 12 students per school

  • In secondary, 1.285 to 53 students per school

  • There is room for a better distribution amongst students per schools, the same happens between students per class (24 max and 10 min).

1. Data Source

Data from two different sources:
 

  • Provided by the Education Department of Lisbon Municipality (3 excel files):

    • Kindergarten - Number of schools, classrooms and children;

    • All levels of education - Number of schools, classrooms , students by classroom, students by year;

    • Extracurricular activites (sports, arts and sciences) by school and organizations.

  • Public data on the website of DGEEC (2 excel files):

    • Number of students by level of education, public vs private, of Lisbon City, Lisbon Metropolitan Area and Portugal

    • Number of students, and schools, public vs private, of Lisbon City, from 2011 to 2020.

2. Data Exploration and Visualization

In Microsoft Power BI works has divided in 3 main phases:

 

    1. Data Preparation

  • In Power Query transform the tables and applied steps as use first row as headers, remove last rows, change column data type and column names, remove and reorder columns, add index column, replace values, amongst others.

​

    2. Creating a Schema

  • Creating relationships between the tables connecting based on common features / columns;

  • All the relationships are one-to-many.

  • Example: Table 'T_Evolution_Nu_Students' and table 'T_Calendar_Year' related through field 'Year'.

​

    3. Data Transformation and Visualization

  • Creating calculated measures in DAX, such as differences, percentages, sum with filters, calculate with distinct count and cointains string, calculate with count rows and cointains string.

  • Pie chart to get the distribuition by sector (public vs private).

  • Area chart for time series analysis, evolution of the number of students and number of schools, from 2011 to 2020 (public vs private).

  • Card for KPI, examples are total number of schools, total number of students, school with the highest and lowest number of students overall and by education level, percentage of schools with extracurricular activities.

  • Matrix for the number of students by education level and the number of students by school, extracurricular activities by school and organizations.

  • Horizontal bar chart to get the distribution of  number of schools by education level, number of students by school,

  • Vertical bar chart to get the top 5 schools with the highest and lowest number of students, by education level and the highest and lowest average number of students by classroom.

  • Azure Map with the location of schools and a filter for further selection.

  • Each vizualization has its specific tooltip.

​

Other projects

bottom of page