nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

A request for data reformatting/cleaning/recoding/etc challenges.

brady_brady

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...

NOTE: (Copied from a response below... I should have mentioned this at the outset)

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. Still, please submit ideas regardless!

Cheers,

Brady

Accepted answers

All comments

nick_shelton

Great topic Brady.

Can you show us how to import and clean a dataset (300 rows) that has header information repeating every 30 rows?

brady_brady

Hi Nick (and everyone else),

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

ian_jmp

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.

Data Table by Lot of Isat_P2A GSVT .6 .045 Avg @ E-TEST, Isat_N2A GSVT.3 .045 Avg @ E-TEST, Isat_P4G_G_PSVT_0.6 0.06 Avg mA um @ E-TEST.csv

Book4.xlsx

forBrady.jsl

Phil_Kay

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.

qualityinformationforpersonalwellbeing2018to2019.xlsx

nick_shelton

See attached example datasets

Wafer_Maps_After.jmp

Wafer_Maps_Before.jmp

brady_brady

Great--thank you, Nick. Need I anonymize this?

nick_shelton

No need, it's made up data.

Jordan_Hiller

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.

List of fatal bear attacks in North America2.jmp

flo_kussener

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

brady_brady

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 @jules, 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!