I'm trying to solve a problem that exists for a sport. The sport is fragmented into many federations, and each federation is completely isolated from other federations. The sport is the same for each federation. The problem I'm trying to solve is to aggregate all of the data from each of the federations (about 10 of them) into a single data store.

This is a very important problem to solve because when you want to know "who is the best", you have to find every federation in existence, and cross reference their records against other federations records.

The data itself is the same across each federation except each federation has it's own format, and it is not even consistent within the federation, it changes over time. The data is also not stored well, mostly it is stored in xlsx spreadsheets that you can download, sometimes even PDF files.

My natural inclination is to write a parser (10 of them) and scrape the data and publish it to the single central data store. A problem here is that the way these sites store their data changes over time, so each federation may have had a completely different format for their spreadsheets/PDFs over the years. So for example the requirement could go from writing 10 custom parsers to 50 (5 for each federation). Not to mention any possible changes in the future. I don't see this as maintainable.

My only other idea is to parse the most recent data (that is in a consistent format) on each site, to get some initial data in my store, and then try promote my site and get the other federations to work with me to send me the data in a single format. Or I could allow users to publish data themselves (I would have to have a review process, so it would likely be no better than doing it all myself manually).

What I'm looking for is any other approaches I am not considering that might help me solve this problem?

I realize that this is a very open ended and opinion based question so I apologize if the question does not belong here.

  • 4
    You have two choices: 1. Convince the federations to all conform to the same standard, or 2. Adapt your parsers when the formats change. Option 2 is probably more palatable than you think it is. – Robert Harvey Sep 19 '17 at 19:39
  • @RobertHarvey #1 is not feasible at all. Regarding #2, I'll admit when I consider it I'm thinking only in terms of worst case scenarios (which is that I would be spending 95% of my time fixing scripts) – James Sep 19 '17 at 20:19
  • You may find Pandas to be very helpful if the names of the columns is consistent even if the order isn't as it can parse the data into named columns. pandas.pydata.org – Steve Barnes Sep 20 '17 at 5:18
  • @James #1 is not feasible...yet :-) once everybody will look at your website and download your best in class format, #1 will look great again ;-) – Christophe Sep 20 '17 at 6:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.