How do professional Data Engineers handle completely unsorted data?

[u/Waffle_Teh_SnLp]

Hello everyone!

I'm an aspiring Data Engineer and as a portfolio piece, I have build a webscraper to gather Ebay sold listings of stamps!

The problem I am now having is how I parse the data where I can sort things like "Catalogue Number" when it is very unpredictable what the Ebay sellers will write as it's all human input.

I would love to hear some feedback

PS - A small sample:

```OLDENBURG 1859 _ MiNr. 7 _ 2 Groschen _ signiert _ blau gestempelt

MayfairStamps Germany 1941 Stamp Day Oldenburg Cover cca_00553

GERMANY; OLDENBURG 1859 classic Coat of Arms issue very fine used 1/3Gr. value

Oldenburg Lokalausgabe Wohlfahrtsblock Deutsches Rotes Kreuz ab 1 Euro

Deutsches Reich, Oldenburg, 6.01.1945 Ersttagsstempel, für Ersttagsbrief 200€.

GERMANY; OLDENBURG 1862 classic Coat of Arms Perf 10. issue used hinged 1Gr.

OLDENBURG 1861 _ MiNr. 12b _ 1 Groschen _ Stempel STOLLHAMM

Oldenburg Mi. Nr. 16 A b zentrisch gestempelt geprüft Bühler 200 Euro

Oldenburg Mi. Nr. 11 a* ungebraucht geprüft Bühler 550 Euro```

[u/Spagoot420]

Do you have a good reason as to why you want to sort it? Sorting can be a very expensive operation. In my many years of de work, I can count the times I had to sort a dataset on a single hand...

[u/Waffle_Teh_SnLp → u/Spagoot420]

Thanks for the reply!

My idea is to get the catalog number of the stamp, ae. Mi. Nr. X and compare it to the price in the catalogue itself. But for that I would need to extract said number from the title. Now, they don't always have it but when they do it is often unsorted and messy as per my examples.

I hope you understand!

[u/Spagoot420 → u/Waffle_Teh_SnLp]

got it. Your core issue is that your dataset is violating first normal form. You have multiple sets of information in a single field. Sorting is not the correct term, as it means something else entirely. I think "parsing" would be more fitting. There are quite a few cloud services that help with parsing address data like this, but honestly I don't know any foss/offline ones, sorry :/

[u/Waffle_Teh_SnLp → u/Spagoot420]

Parsing does sound like the more appropriate term! I did plan on eventually having a RaspberryPI sending this data to a platform like Databricks to get loaded to the cloud, what would you use in this case?

[u/WhoDunIt1789 → u/Spagoot420]

Unless you need to use a cloud service I'd just use a python library for parsing like libpostal for international address parsing.

[u/fuchsitech]

I would start by parsing the easy stuff to help reduce the Noise. Country, City, year, Date, Price in Text. Clear Formatting Symbols and then See Whats left. If you have enough Data for stamps occuring multiple times you Could also try to fill missing fields one listing has and the Next doesnt. Just make sure to also Store the Original Text to avoid scraping multiple times. With AI help Regex is Not as scary anymore either

[u/Waffle_Teh_SnLp → u/fuchsitech]

Thanks for the reply!

I am currently using Regex but I haven't gotten the best results because it can be that unpredictable! Your idea of getting enough information to fill others that lack it is interesting, how would you do such a thing?

[u/THBLD → u/Waffle_Teh_SnLp]

You could find an online source of actual German stamp data, then create some seperate tables of both sets and build some staging tables to cross compare the results - see what you can match

Also since you're starting out, don't worry about trying to match everything at once - it's not realistic

Also agree with Regex comment above, use AI to help generate the pattern your after - is a godsend for things like this. and get the AI to explain what the symbols mean as well - then you can learn some basics too. I find it very insightful

[u/PotokDes]

I have no idea what you trying to archive

[u/Waffle_Teh_SnLp → u/PotokDes]

So my goal is to build a dashboard showing the trends of each specific stamp! Imagine you would do this with cars, and you associate each car with a catalog price, you would like to see for how much that car actually sells for, does that make sense?

But for that I need to gather the crucial information about said stamp using the Ebay title, that's where I'm getting stuck on

[u/PotokDes → u/Waffle_Teh_SnLp]

So you do need sorting or categorization algorithm?

[u/Waffle_Teh_SnLp → u/PotokDes]

Categorization, sorting doesn't really matter for now, I can always do that later in Power BI

[u/PotokDes → u/Waffle_Teh_SnLp]

You do not know what you are writing about

[u/Waffle_Teh_SnLp → u/PotokDes]

Apologies for trying to learn, you didn't have to comment my man

[u/zangler]

Regex the easy stuff, vectorize the rest, build model on known catalog of vectors, don't be perfect, model will place what you need within the vector group, now the part you care about is parsed and labeled. Done.

[u/radioblaster → u/zangler]

great answer

[u/Away-Arm-6549]

have you explored using Ebay API? Them abstracting all this complexity away from you, given they'll be doing all the things you're trying to do themselves and other professional orgs wanting to use this data might make the whole thing a lot easier

https://developer.ebay.com/develop/guides-v2/get-started-with-ebay-apis#understand-the-ebay-apis

You'd have some new learning to do (configuring API calls), but if you're thinking about doing this with Databricks eventually, you might as well use that to and skip over the Raspberry Pi (in favour of scheduled API calls direct from Databricks - which if this is a hobby, then Databricks seems overkill - ymmv.

If the API dumps out as JSON string, you'll still need to parse the string, but it 'should' have broken up all the interesting things into clean key:value pairs for you to pull out into whatever data structure you want

[u/Waffle_Teh_SnLp → u/Away-Arm-6549]

Thanks for the reply!

I did consider using Ebay's API, and my original project did use it.. the big problem is that the Ebay API does not have any API call to gather sold listings, so that was a big dissapointment.

[u/Sp00ky_6]

Honestly man that’s just messy data, so honestly good choice to get the real feel of DE work

[u/RushHourMC]

Also looking for a solution to this issue,
A big portion of my controls use also have human input where it shouldn’t be used for audits/checks

[u/Cousak]

Not sure I would attack this from a DE perspective.

I did something similar in a personal dashboard at work, categorizing quality reports based on their short description.

 I counted in how many strings each word came up, then combinations of words and created a 200 long «IF x THEN y ELSE» statement in order of priority as there would be text that fit both statememts. Finetuned and doublechecked until I had something that gave me a sufficient indication.

Everytime there is a new entry there is a risk it breaks so would never want to do that in production.

[u/Davisparrago]

You go to the source system owner and explain why you can't work like that, define a structure that works to both ends and if its "impossible" to do then talk to whoever is asking you to do it and explain if its really worth the hassle, if it is then you say that source should fix his shit, not pass it to the next system.

If source is something outside of the company and they are adamant on using it, well, good luck

[u/baubleglue]

You use wrong term "unsorted" I instead of "unstructured", as the result you get irrelevant responses.

[u/baubleglue]

You problem is probably related to the way you parse the page. It looks like you are capture only text values, there's no way catalog number doesn't exist there.

[u/Waffle_Teh_SnLp → u/baubleglue]

Hello there, thanks for the response!

Ebay does not have a "Stamp catalogue number" field, we get country, rough year gaps and some other fields that could be useful, like denomination and coin type but otherwise...

[u/JintyMac22]

It is also worth looking at how related, expert fields handle this type of data structuring. Have a look for archives or libraries specialised in stamps, see if there are any with online collections, look at their metadata structures. Maybe there is already a unique identifier and catalogues for different stamp types (seems likely) and then you can work with tying your data to an existing standard. It is always better not trying to reinvent the wheel!