If you’re new to Data Analysis… Welcome! Glad you have you :).
People learn data analysis skills in different ways. Maybe you’ve done some courses, tutorials, certifications or even a whole degree. You’re either working on creating a portfolio or you’ve received your very first dataset and real world questions to answer – congratulations!
At this point, it can be totally normal to think:
“Ok, how do I begin analysing this dataset? How exactly am I supposed to start? Help!”
Cue some mild panic and hint of existential dread 😨.
I’ve seen many a reddit-thread where people feel lost without the structure of learning material to guide them. It can be easy to feel like you’re not making any progress in your learning when you stumble at the very start of applying your knowledge in the real world.
When you first receive a dataset outside of a structured course, it can be really confusing about where to start because you could start anywhere, and you could head in any direction.
Don’t worry – you’ve got this!
Whatever training you’ve had up until now will move you forward in your analysis. What I’ll help you with today is not Step 1 of your analysis, but what I think of as Step 0 – Getting to know your dataset.
It’s tempting to dive straight into analyses because it feels like it’s the “real work” but rest-assured, getting to know your dataset IS real work and will pay off massively in steering your analyses to useful results. You’ll be able to provide others with guidance on what questions can or can’t be answered and you’ll have a better idea of how to interpret the results.
What I’ll present you with here is a checklist of ways in which you can get to know the dataset you’re working with. Take this with you in your first, fiftieth or hundred-and-fiftieth analysis – it’ll always work. The more you can check off, the better your understanding of the data and analyses will be. As you gain experience a lot of this will become second nature.
The basis of this approach is to give you a solid mental reference point of the data and will be your first mental model of what it represents. Once you’ve got this reference point, you’ll feel a lot more confident about what step 1 of your analysis should be. It’s like finding a landmark in an unfamiliar part of town and suddenly you have a better idea about where you are and what direction you should be going.
Use the table of contents below as a checklist and then read on for more detailed descriptions of each.
This advice is based on my own experience, but is by no means exhaustive. People who work with different datasets than I do will have different views, but I think this applies well to many situations.
I’d expect most datasets, if presented in a file, to be in a CSV (Comma Separated Variables) or Excel (XLS or XLSX) format.
If there’s some other weird or proprietary format that I don’t recognise, that would indicate I might have trouble opening the file, reading the contents correctly or finding help on issues and I may have to budget in extra time for converting the file, or finding a way of converting it to a more usable format.
File size (kB, MB, GB)
I’d expect smaller datasets to have smaller file sizes, and larger ones to have larger. Most datasets in files I’ve dealt with are in the 1- 50MB range. If I saw an excel file with over 100MB, I’d know there’s a lot going on in that workbook that could make working with it tricky. There’s probably a lot of formulas or analysis happening and I would see if I can separate out just the data I need into a new workbook or CSV.
Large excel files can be temperamental and have a habit of crashing systems or being very slow to load. An excel file can become corrupted at any time.
Anything geospatial, especially satellite imagery, can be very large. If you see file sizes in the GB range, its very likely you’ll need more specialised ways of working with it. It’s good to research how to handle such large files for your needs. For example with satellite imagery, you may need to work via cloud-based virtual machines and some sort of distributed storage and processing (I’m guessing). For CSVs, you may need particular plugins or packages that can access this data without crashing your system.
Number of rows and columns
So, how many rows and columns are we looking at? 500 rows? 5 000? 500 000? The order of magnitude will give me a sense of how much data has been collected and what might be possible to analyse in a few scenarios.
Example: If I was given a dataset of the income streams of all adults in the USA, but it only had 500 rows of data I’d know it’s likely a dataset of aggregations, and I won’t be able to dive into very granular analysis.
Example: If this dataset were the spending habits of several families over the course of a few years, and there were 500 000 rows, I’d immediately think that this is probably extremely granular detail and there’ll be a lot of angles to look at.
When it comes to columns (the variables), then a high number of them might tell me a few things. Firstly I expect there to be much fewer columns (variables) vs rows (observations). Let’s say 10, 20 or maybe even 50 columns. When it starts going higher, like 200 columns – that would give me cause for concern in that either the data isn’t structured well, or there’s metadata incorporated into the column names perhaps (harder to extract meaning). Of course there are many exceptions to this but they are more unusual than usual.
What data is contained in each column? Are they mostly numeric (decimals, whole numbers), text (free text, categories), dates and/times (formatting?), true/false (boolean).
Are the datatypes correct e.g. is a date column actually have the date in it formatted as a date (22-01-2019) or is it actually text (string) format of the date (in which case there could be issues and errors when that was converted)? Does the formatting match what you’d expect e.g. if there’s meant to be a column (variable) with numerical data, is it really a number (e.g. 6000) or a text representation of that (“6k”) – assuming it’s not the spreadsheet software’s own visual formatting that you’re looking at.
In short, if the data types match the data each column (variable) is meant to contain, you’re off to a good start. If not, tread carefully.
Quick scan of first 10 rows
Have a look at the data in the first 10 or 50 or 100 rows and scan over it. Does this data make any sense? Hopefully it will. Are numbers in the same order of magnitude as you’d expect? Are there any easy to spot correlations that make sense? For a dataset on personal wealth you might expect rows (observations) with high salaries to also have high home values.
Age of dataset and how often it’s updated
Datasets might be a live stream of information or as in many cases, it’s static and published on a specific date. Static datasets might be updated in a number of ways:
- Repeatedly updated and published as new datasets
- Single dataset gets updated i.e. there aren’t separate files published for each update
- Regular scheduled updates
- Scheduled updates that never end up happening (project is abandoned)
- Updates that happen but only for a specific period with a known or eventual end to the updates.
And then some datasets are never updated, they were always meant to be a one-time publication.
The age of the dataset will often matter, because you need to gauge how useful the dataset is based on whether it’s outdated for your needs.
If I want to estimate how many ice cream parlours could be supported by communities in a region, I would need relatively recent census data. Population estimates from 50 years ago would likely not be very useful for me today – for this particular analysis.
Knowing the age of the dataset and how often it gets updated go hand-in-hand when it comes to being able to provide advice on how reliable the analysis might be, whether you can repeat the analysis in future and what follow up questions can be answered.
Purpose of the dataset
When the data was collected, what was the initial purpose? The more aligned that original purpose is with your current needs, the more useful that dataset is likely to be.
Assume I want to help figure out the prevalence of stray dogs in a city and their effect on public health. A dataset (or datasets) that were collected specifically targeting the same questions, holding information like dog bite injuries in ER, prevalence of rabies, animal control callouts might all be more useful than related datasets of dog licence registration that only tell how many licenced pets there are in different areas.
Who uses it now?
This may or may not be tied to the original purpose of the dataset, but understanding who currently uses the dataset (if anyone) will be useful to know from a few angles, but primarily I like to know who I could ask questions of and what future developments might be coming. Perhaps there’s a sales team that uses the data extensively in dashboards and reports. If you know that they’re shifting strategy to measure targets that require new data to be captured in the database, maybe existing data points will be retired.
How was the data collected?
Is this survey data? Online collection or in person? Trained enumerators or volunteers? Device data from web analytics? Email tracking pixels? Telemetry data from automated systems? Was there data validation upon entry or capture? Has the data gone through any cleaning or preparation between the point of collection and now?
The answers to these questions may be hard to get and you’ll probably pick up bits of information about each dataset as you work with it and gain experience. Over time you’ll get a good overview of how data flows from the point of capture to being ready for analysis, and all the ways it might get lost, changed or how it must be interpreted slightly (or very) differently from what the header, code book or data dictionary specifies.
Take survey data for example. The same question being asked might be responded to very differently depending on factors like whether it was an in-person or online self-completed questionnaire, whether participants were incentivised to answer or not, whether they were asked in their own language or via translation, from a trained professional enumerator or from an untrained volunteer, in the presence of others or alone, anonymously or not.
For databases, where is the ERD?
The Entity Relationship Diagram (ERD) is key in understanding a database, the tables, fields and datatypes present and how they all relate to one another.
This goes well together with a Data Dictionary.
Is there a data dictionary? Does it look accurate?
A data dictionary will give more detail on the ERD and be easier to search and navigate as it’s in document form, but ideally the data dictionary and ERD go hand in hand. Many databases do not come with a database dictionary but creating one and keeping it updated can be one of your first really valuable additions to any team.
How descriptive are the headers?
If the headers have very descriptive names, like “device” , “address”, “owner” you’re going to have a much easier time figuring out what the data is saying than if you have “XB206_XT”. Non-obvious headings will take a while to figure out and hopefully there is some pattern to their naming convention, but this is not always the case.
Who is the custodian of the dataset? How are they maintaining it?
Whose responsibility is it to maintain the dataset? Who looks after it? What do they do to maintain consistency between updates? How strict are the protocols for ensuring headers retain consistent meanings (hopefully very strict!). These questions help you identify how much trust you can put in the analyses you produce. It’s not unheard of, and not even that uncommon, for datasets to not be properly governed, with definitions and input requirements changing without any control – leading to someone needing a lot of tacit knowledge passed down from one analyst to another to avoid non-obvious issues.
Do the headers match the data in them?
It can happen that somewhere in data capture or transformation from to the dataset you’re seeing that something has happened which assigned the wrong column name (variable header) to the wrong column (variable) , so it’s worth a quick check to see if “First Name” actually looks like it contains people names, and not maybe a street address for example.
I haven’t seen this happen often, but I have seen it.
How much of the data is missing?
For each variable, check how many of the rows have data for it. In most cases, you want there to be very little (or no) missing data. In other cases, you may expect that a lot of data is missing. This check is really critical because it will have a large impact on what it is you can actually analyse.
If a dataset of 20 000 train delays only has the train operator completed for 200 of those records, then it’s unlikely you can perform any meaningful analysis comparing train operator performance.
Can you see numbers like 999, 9999 in values?
Sometimes when numerical data is being captured, there’s a need to capture that the data is missing for one or more reasons, and then numbers like “999” or “9999” or something similar are used to denote that it’s missing for a specific reason – as opposed to an NA value which might mean it’s was just never collected, asked for or available. .
These uses of actual numbers as a form of additional data representation inside a variable that houses regular numeric data can skew your analysis results if you don’t filter them out and it’s not obvious that they’re there – you’ll have to look for them.
Sometimes you’ll have a code book or a data dictionary that goes along with the dataset which should hopefully highlight that these values exist and what they denote.
A similar occurrence in geographic coordinates is the use of 0 latitude and 0 longitude, referred to as “Null Island”.
How clean the data needs will change from one analysis to another, but you will encounter many datasets that are quite “dirty” and need a lot of cleaning before you can make them work. What do I mean by dirty data and what does cleaning it involve?
There are countless ways in which data can be dirty, but I’ll list some common ones that come to mind.
- Free text entry where a dropdown options should have been used. You’ll find countless spelling variations of “Mississippi” and you’ll need to transform them all to the correct spelling.
- Dates and times captured in different formats need alignment.
- Wrong data types captured e.g. numbers are stored as text and need to be converted.
- Values entered in the wrong order of magnitude e.g data is captured in metric tonnes rather than kilograms.
- Some values don’t make sense e.g. a person’s age is 300 years old and that has to be removed.
- Issues in recoding of special characters in text &quo& and must be removed or converted back to it’s original representation.
This is just a taste of some of the issues you’ll face. Personally I find sleuthing and cleaning quite enjoyable but this is often a key reason why just being able to jump into an analysis right away is not possible. You need to build in some allowance for data cleaning.
Distribution of values, counts, averages, min/max
This will be your first look at the range of data available. For each variable, look at the frequency of values, the distribution of maximum and minimum values and the interquartile ranges. You may even want to have a first look at the correlation of any variables that you think should be obviously correlated.
This is how I get a feel for the “size and shape” of this data and how much variation there is. In some cases you might expect lots of variation, or might expect very little – or have no expectation at all. That’s ok – you’re just familiarising yourself with the landscape.
Verify with a domain expert
Once you’ve got a feel for the dataset, it helps to verify some of your initial findings with a domain expert i.e. someone who is familiar with the content (or “business” side). If you were looking at facilities data, it would be good to check with the Facilities Manager if what you’re finding looks correct. For example, the Facilities Manager might know that there’s a lot of renovation work that’s happened but no one from the admin team have been recording the changes, so most of the data is likely right (building location, footprint size, utility providers etc) but there’ll be some data that’s probably wrong or out of date (value of fixtures and fittings, high risk materials, state of fire suppression systems to name a few).
Now you know your data!
Having just completed some or all of the checklist, you’ll be one of the few people on earth who understands this dataset as well as you do!
You’re in a good position to start analysing this data – either by answering questions you’ve been asked or by following the sparks of interest you’ve just had.
And did you notice something? You’ve actually already begun the analysis! Missing values, distributions, counts, averages – all are useful and valuable info. Even answers like “I don’t know / It’s hard to say” are useful outcomes of the exercise. They’ll inform discussions about the way forward.
Congrats! Now, back to some analysis :).
Looking to practice data cleaning on a real-life, messy dataset? Check out:
You might also find RScreencasts to be very helpful in your data analyst journey: