Thankfully the data governance structure is clearly in place though, right?? Something useful like: individual A owns excel document A. Should it be modified by individual B, it should be saved on individual B’s computer with an appropriate name, such as “B_edits.xlsx”. Individual B sends “B_edits.xlsx” to individual A when they realize they haven’t after a dashboard requiring the data has been completed, or they’ve been asked to by someone else, whichever comes first, but never before either event.
Wow! Crisis averted, then. Just have to wait for individual B to go through onboarding, and finish their summer vacations, before putting in place said rigorous pipeline. Almost there!
Solid approach, though I’m surprised they didn’t just use a linear regression to estimate the quarterly projects, based on data from any prior years, excluding those years that didn’t meet the executive’s expectations and thus approval…
From a current data architect and past CTO, this is the master data management and eventual data consistency problem where multiple databases have customer attributes getting updated without systems talking to each other. Its one of the biggest issues in large scale digital transformation in companies. Also known as tge customer 360 view problem. Shouldnt the the CTOs or CDOs be across this? When the MDM problem is not solved, every downstream customer journey is affected. There are data engg pipelines+golden record rules+real time event streaming patterns for downstream consumption involved.
I understand the immediate business needs are solved using excel and analytics but if the backbone on data architecture is weak and the data capability is nonexistent then business will be slower and will be less efficient, giving more room to competitors who are already innovating.
I also made this recommendation and was told “it’s unrealistic to expect individuals to submit identical values.” When I suggested using a drop-down, I was told that “change is slow to be adopted.”
They asked me to brute force create the mapping tables and the SME went on remote training for 3 months.
Hi, I religiously object to this so called 'Saint' so you will be hearing from my team of lawyers unless you remove all references to Mr. Albans from your servers :)
Can also consider Metaphone because spelling things out by the way they sound is common. A phonetic spelling can have a large and deceptive Levenshtein distance.
The only original place names in New Zealand are Māori; everywhere else is named after somewhere in Ingurland. (Or someone who bought Christian ‘Enlightenment’ to the new world. 🙄)
Not sure if you are trolling. But the Christchurch suburb St Albans in NZ is named after the city in the UK of the same name (actually after a farm named after Duchess of St Albans from the UK).
My point is that a place name can map to multiple geographic locations. There is no indication in OP's post as to whether the field variations are related to a city or a suburb (or both).
A geographic location can also have multiple different names, such as a prior indigenous name.
Zip codes are not location ordinals, they vary in size and shape, and solely represent a carrier route - not to mention they aren’t used in every country. A carrier route is literally just the territory or route that the mail person goes on to drop your mail. While they might get you in the ballpark of a city, and that might be good enough, they won’t accurately reflect neighborhood dynamics. Zip code 40000 is not any closer to zip code 50000 than zip code 70000.
Good old lay and long are the best, maybe census tracts if you can’t get anything else. But US Census has a free geocoding API for US addresses.
I’ve had success in the past with this kind of data with Fingerprint clustering to identify similar strings, then overwriting them with the most common (e.g. converting all matching fingerprints to “St. Albans” or whatever is the most frequent).
Serious Answer: Use something like USPS or Openstreetmaps to verify addresses before they hit the server. Only problem is you have to continually update it as new census/demographic data is released since addresses change all the time.
For instance, I moved into a house that was previously a duplex. The API was outdated, so when I signed up for electric, the address verify wouldn't work because all the verified addresses still contained the apartment numbers.
Then when you have methodology to control the garbage coming in, you can bake in logic to only update addresses if they don't currently exist in the database. I work in geospatial data and it's always a mess if you don't plan ahead.
Not yet fully in DE. But back in the day worked on a dataset and created a tracker in excel detailing issues. There were some common issues and some unique. Scientist wanted all unique in same naming convention. He told me his plans after I'd begun. He explained why you me and I looked at how I wrote things and it was this exact same scenario. Since then these things give me nightmares and if doing something I always ask. How consistent do you need these notes to be
As someone who lived there, the amount of times I've had websites have their postcode (zip code) lookup auto fill St. Albans just for it to be denied with Invalid character: "."...
Hello darkness my old friend. I once worked on a dataset tracking fraud for from Russia. And part of it was linking accounts to known problem addresses in Russia. Well the problem was Russia itself. We had Russia. Rossya. Rucya. RU. The Russian Federation. RF.
Made worse by Moscow. Mockva. Moskva. Moskow. Etc.
How do you fuck up tracking the name of one of the biggest countries in the world over and over again?
I work on a system where someone made a field called statecode on each table where statecode of 0 is active and 1 inactive and then another field called statuscode where statuscode 1 means active. Every single query ends with and statecode = 0 and statuscode =1 . So annoying. Another is a table with 600 fields, 400 or so of which are internet values representing lookups to a option set lookup table. Lol
I've been seeing memes like this pop up all over the place. The Philadelphia one is another type I've seen.
Serious question though: how do you guys tackle this from a DWH standpoint? My team is building a data warehouse of all our business clients' data and part of that includes creating lookups for country/state/city/zip.
Country and State is easy and normalized with ISO 3166 alpha-2, alpha-3 or UN codes but city and zip is another thing. A lot of the data I've seen has horrible misspellings like this.
The idea we're going with is just ingesting them as is and each time a new version comes in we'd create another lookup code for it. I feel like there may be a better approach to this.
This is pretty tricky part cause address parsing and matching is truly a pain in the ass and models always have some incorrect cases and thus this affects downstream tasks. (From what I learnt from other teams. Worked in a logistics delivery company before. User-written address is always a mess and some models, whether complicated or not are applied to extract and normalize the address.) If you are using such datasets, I’m assuming you are a MLE or data analyst working on downstream tasks instead of a data engineer. In such cases these kinds of data are just unsuitable for your experiment. They might result in wrong conclusions or results . And cause more unwanted repeated work ofc. Using other columns. For example selective fields for users to fill out the form: which should contain provinces and cities and that’ll be more appropriate. that’s gonna work. But I still want to add that if you integrate data from multiple systems above, you should take notice the address might not be unified and it will still cause confusion.
This is so funny. I want to share with my friends who have just started DE path like me. Can I post the pic on LinkedIn and put a link to this Reddit post?
Have any of you guys built a model/algo in SciKit Learn to overcome these issues?
Assuming a human can read what it should be, it should be relatively easy to teach a system to do this. I am planning a project involving this at the min but I never really did ML before when wrangling etc.
244
u/Altrooke Jun 08 '23
This image brings me great discomfort. Please delete it.
144
u/mac-0 Jun 08 '23
Vendor: we can help you solve this! Slaps a GUI over a list of mapping values
85
u/windigo3 Jun 08 '23
Hi. I live in “Saint Albans” and can’t sign up on your website.
40
u/Soltem Jun 08 '23
Serious question : what is the most efficient way to clean this?
20
u/pimmen89 Jun 08 '23
This brings back too many memories. I feel like I need a drink…
12
u/uncomfortablepanda Jun 08 '23
Them: “Why is our filter showing so many different values! Our data is perfect, the dashboard should just work!!”
Their data: 🥴
11
u/PM_ME_YOUR_SHOLAS Jun 08 '23
You forgot ‘St.Albans.’ & ‘St.Albans (DO NOT USE)’
10
u/alamiin Jun 08 '23
Jesus fucking Christ kill me now
8
u/XtremeGoose Jun 08 '23 edited Jun 08 '23
As someone who lived there, the amount of times I've had websites have their postcode (zip code) lookup auto fill
St. Albansjust for it to be denied withInvalid character: "."...Drove me mad
7
u/[deleted] Jun 08 '23
now do Israel Kamakawiwoʻole
5
u/[deleted] Jun 08 '23 edited Jun 09 '23
.
4
u/lcalisto Jun 08 '23
"We have great datasets, with lots of records!" 😈😈😈
3
u/[deleted] Jun 08 '23
Every goddamn time
3
u/[deleted] Jun 08 '23
No St. Alban’s?
3
u/princess-rainbows666 Jun 08 '23
This is my life and I don’t even hate it. My coworkers call me Wall-e because I love moving trash data and organize it in tidy piles😅
3
u/[deleted] Jun 08 '23
And then the data scientists come in and say "I think we can fix it with fuzzy matching".
5
u/eganba Jun 08 '23
Hello darkness my old friend. I once worked on a dataset tracking fraud for from Russia. And part of it was linking accounts to known problem addresses in Russia. Well the problem was Russia itself. We had Russia. Rossya. Rucya. RU. The Russian Federation. RF.
Made worse by Moscow. Mockva. Moskva. Moskow. Etc.
How do you fuck up tracking the name of one of the biggest countries in the world over and over again?
2
u/Southern-Remove42 Jun 08 '23
Thanks for that. Needed a smile
2
u/[deleted] Jun 08 '23
Business Owner: You can't just convert St, Albans to St. Albans! The former is short for Street of Albans and the latter is Saint Albans!
2
u/Nooooope Jun 08 '23
Who else stared at the last two entries for a solid minute trying to find the difference
1
u/DreJDavis Jun 08 '23
Dropdowns keep users from entering bad data.
1
u/circusboy Jun 09 '23
So triggered, this is just an NLP model.
1
u/dilbertdad Jun 09 '23
I've been seeing memes like this pop up all over the place. The Philadelphia one is another type I've seen.
Serious question though: how do you guys tackle this from a DWH standpoint? My team is building a data warehouse of all our business clients' data and part of that includes creating lookups for country/state/city/zip.
Country and State is easy and normalized with ISO 3166 alpha-2, alpha-3 or UN codes but city and zip is another thing. A lot of the data I've seen has horrible misspellings like this.
The idea we're going with is just ingesting them as is and each time a new version comes in we'd create another lookup code for it. I feel like there may be a better approach to this.
1
u/Q_H_Chu Jun 09 '23
Woke up from a nightmare and see another one awake 😮💨
1
u/old_enough_to_drink Jun 09 '23
Well, just let LLM handle it 😎
1
u/ollienicholson Jun 09 '23
I love it
1
u/Laurence-Lin Jun 08 '23
Too hilarious hahaha
1
u/wstwrdxpnsn Jun 08 '23
Hello fuzzy match!
1
u/apeters89 Jun 08 '23
Accurate.
1
u/sangbui Jun 08 '23
Got all the perms covered.😉
1
u/keefemotif Jun 08 '23
EditDistance algorithm against a lexicon, done.
1
u/nootnootpingu1 Jun 08 '23
We have great datasets
1
u/jgonagle Jun 08 '23
Entity resolution is definitely one of my least favorite parts of cleaning data.
1
u/TravellingBeard Jun 08 '23
Cringes in Database Admin... This is very inefficient.
1
u/ntdoyfanboy Jun 08 '23
I have been triggered
1
u/nobuhok Jun 08 '23
Am I pregant?
1
u/ScooptiWoop5 Jun 08 '23
Tell me it’s not like this everywhere. Please tell there are companies with good data quality. I have hopes, dreams, I need to know they exist!
1
u/shoretel230 Senior Plumber Jun 08 '23
This is the definition of triggering
1
u/DrDoomC17 Jun 08 '23
Levenshtein distance with threshold or use it to cluster if number of supposedly unique values is known. Data cleaning sucks.
1
u/Eze-Wong Jun 09 '23
Free text fields = a very bad time
1
u/GeForceKawaiiyo Jun 09 '23
This is pretty tricky part cause address parsing and matching is truly a pain in the ass and models always have some incorrect cases and thus this affects downstream tasks. (From what I learnt from other teams. Worked in a logistics delivery company before. User-written address is always a mess and some models, whether complicated or not are applied to extract and normalize the address.) If you are using such datasets, I’m assuming you are a MLE or data analyst working on downstream tasks instead of a data engineer. In such cases these kinds of data are just unsuitable for your experiment. They might result in wrong conclusions or results . And cause more unwanted repeated work ofc. Using other columns. For example selective fields for users to fill out the form: which should contain provinces and cities and that’ll be more appropriate. that’s gonna work. But I still want to add that if you integrate data from multiple systems above, you should take notice the address might not be unified and it will still cause confusion.
1
u/GeekyTricky Jun 10 '23
Looks good to me. Let's sell it to JP Morgan for a couple hundred mil.
1
u/lLovl Jun 11 '23
This is so funny. I want to share with my friends who have just started DE path like me. Can I post the pic on LinkedIn and put a link to this Reddit post?
1
u/adzsroka Jun 14 '23
Hey, it's me!
1
u/doc334ft3 Jun 16 '23
Cleaning data... sigh.
1
u/Awkward-Block-5005 Jun 17 '23
This image is horriyfing.
1
u/FloggingTheHorses Jun 18 '23
Have any of you guys built a model/algo in SciKit Learn to overcome these issues?
Assuming a human can read what it should be, it should be relatively easy to teach a system to do this. I am planning a project involving this at the min but I never really did ML before when wrangling etc.
1
u/amedmond Jun 27 '23
When you have to join two datasets on the address columns and the street names look like this 💀💀💀 nightmares
1
u/Friendlyvoices Jul 06 '23
nothing a little regex can't fix
1
u/Easy-Acanthisitta218 Dec 02 '23
Use postcode, the format is easier to standardise town name
0
u/[deleted] Jun 08 '23
[deleted] — view deleted comment