mhaz
commented
3 years ago For now I am just extracting the data from the html tables. The data is poorly formatted so the process is ad-hoc. This is the reason why I first want to go year by year, "filiere" by "filiere". Although there will first be multiple files, I expect to eventually summarize them all in one place -- this is the reason why for example https://github.com/mhaz/SCEI-statistics/blob/master/src/stats_generales/2020/2020_tsi.tsv has a column for the year and the "filiere" -- once I get everything in an easy to read format, for now tsv.
Since the data has inconsistencies, I have not yet decided on what the final dataset will look like, for instance:
- From 2004 (included) up to 2017 (included), the rank of the last candidate called is available. From 2018 onward, median and mean rank of accepted candidates are instead made available. In 2002 and 2003, no such information.
- For Ecoles such as Polytechnique, ENS, Ecole Centrales, we have data throughout the integration process. Others like Concours Communs share data for enrolled students on a per Ecole basis, with inscriptions and admissibles only available for the Concours.
- If I recall, the data changes from student count to student percentage over the years.
As you suggest this data set is small enough to be loaded entirely in memory. For the first step, I think I'll go with just pandas.
I am interested with your idea of gathering the data in an SQL database. As mentioned above, data from SCEI isn't consistent, so I don't have a a clear idea on what the database schema would look like.
If you just want a sql format and don't care if it's "flat", then I guess it's only a matter of concatenating all the files, and using .import on a sqlite prompt?
If you have something interesting you want to add, don't hesitate to make a pull request.
Naereen
Hi again, Also, I am very interested in these datasets, I would like to have SQLite files (or just one file) containing the data.
When doing data exploration, on such "small" databases, I like to do everything using SQL from Jupyter notebooks. It can be done from a Python notebook, with ipython-sql, or with pandas and Pandas-SQL, to have a really neat interface (any result of a SQL query is a pandas dataframe which is nicely displayed, which can be plotted easily etc).
It seems more efficient to me than using a specific Jupyter kernel, like xeus-sqlite or xeus-sql.
Tell me if you're interested and able to do this yourself, or I'll come back in a couple of months and do it myself. Thanks, regards from Rennes in France, @Naereen