A request for data reformatting/cleaning/recoding/etc challenges.Hi SEs,
I've signed myself up to deliver a 20-minute JMP On-Air segment called "Ask the data doctor". The idea is that I'll pick from a collection of data-prep questions that have been submitted over time, and show one or more ways to accomplish the task(s) in JMP. The problems don't have to be diabolical, but ideally should represent cleanup tasks that a non-expert (or even expert, I guess.. they need our love, too) user might find perplexing.
We need to prime the pump, though... currently I've some ideas, but I'm sure you've many more.
Will you please reply with any data prep questions you think customers (or yourselves) might find difficult or tedious... I'll put them in the "pile" and hopefully we'll be off and running with something I can use for the first segment.
I've left this open-ended, because who knows what will come in--that is part of the fun. I can't promise I'll solve your problem on-air (or ever;)), but who knows...
Thank you,
Brady
Re: A request for data reformatting/cleaning/recoding/etc challenges.Great topic Brady.
Can you show us how to import and clean a dataset (300 rows) that has header information repeating every 30 rows?
Re: A request for data reformatting/cleaning/recoding/etc challenges.Hi Nick (and everyone else),
If you have an example dataset, (even better: BEFORE and AFTER versions) that would be awesome--please post it so a) we will all have it, b) I won't have to come up with one and c) we can avoid miscommunication about what actually was the goal of the clean-up task.
If you don't, I can come up with something, but to be honest, I'm gong to try to minimize pre-work for myself here, so I may end up leaning towards ideas where I've got a sample dataset.
Cheers,
Brady
Re: A request for data reformatting/cleaning/recoding/etc challenges.Here's a somewhat old (but real) example that, from memory at least, has quite a bit of data wrangling. I imagine you would have to break it into parts, but it's a nice case from one of our core markets. These are the kind of hoops one has to jump through if you don't follow the guidance offered by Phil Ramsay in his recent Discovery Munich presentation . . .
If you do use it, we should anonymise the data of course. Speaking of which, this should NOT be used for demo purposes as it is.
Re: A request for data reformatting/cleaning/recoding/etc challenges.Attached is an example not from our core market: well-being data for UK regions.
It is from the UK Office for National Statistics so free to use.
It is not really nasty. But people respond well to the end result visualisation.
This is the end result that you are after:
You can get the final JMP table along with map shape files from the JMP Public post. For the purposes of my story I excluded 2018 and 2019 but you wouldn't need to do that.
Re: A request for data reformatting/cleaning/recoding/etc challenges.See attached example datasets
Re: A request for data reformatting/cleaning/recoding/etc challenges.Great--thank you, Nick. Need I anonymize this?
Re: A request for data reformatting/cleaning/recoding/etc challenges.No need, it's made up data.Re: A request for data reformatting/cleaning/recoding/etc challenges.Messy Tabular data from Wikipedia. Sometimes "Internet Open" works well, other times not so much. Here's an example inspired by my argumentative daughter who didn't want to carry bear spray on a backpacking trip last summer:
https://en.wikipedia.org/wiki/List_of_fatal_bear_attacks_in_North_America
Massaged JMP data table attached.
Re: A request for data reformatting/cleaning/recoding/etc challenges.Hi Brady,
Sometime people have issues with date in XLS file. Depending the language, they do not easily manage them (for example, French date are usually with the day before the month). if you have some guildelines on this subject, it would be great!
Thank you,
Florence
Re: A request for data reformatting/cleaning/recoding/etc challenges.Hi Florence (and everyone!!!!), will you please send me some examples of nasty date formats you see in your areas of the world. Based on discussions with @julian, we have decided to make the first show about this topic. We'll introduce the date-formatting addin, which does cover many (but unfortunately not all) cases.
I'd love to have as many examples as possible, so that I can find some nasty ones to show. Of course, I'll show a couple of the more typical cases, as well.
Also, it will also be helpful to discuss formats the addin cannot resolve, and why.
Thank you!
