Forum Discussion
Solved
Looking for trends in unrelated related data
I don't know if this is in the exact right spot, but here goes.
Let's say that I'm a biologist analyzing litters of puppies:
- I've got six litters I want to analyze.
- Each of those litters could have anywhere from five to ten puppies.
- Each one of those puppies should be identical (for the sake of the thought experiment), but there's variation between each one.
- Each of those puppies has roughly 30 different areas of the body that I want to analyze.
- These 30 areas are the exact same spots on all of the puppies across the six litters, and, functionally speaking should be identical to each other of the chosen areas on the same puppy
- For each one of those areas, I'm investigating aspects of up to each of the following features: skin, fur, and muscle
- Each one of those features has a given set of characteristics
- Fur: length, density, color, pattern
- Skin: color, pattern, elasticity (okay, I know that sounds weird, but I'm grasping at straws for this analogy)
- Muscle: strength, volume, stamina (lots and lots of straws being grasped)
- Combining the above results in hundreds of trios of data that I'm trying to analyze: Area - Feature - Characteristic
- Each trio has its own unique set of allowable values:
- Area 2 fur length can be between 1 and 2 inches, whereas area 18 fur length can be any length under 4 inches
- Area 7 skin color can be pink, white, or black, but area 20 skin color can only be brown
- The Area 12 muscles need to be strong enough to lift 4 lbs, but there is no specification for the strength of the Area 2 muscles
- Each unique trio of data points has the same allowable range as that same trio of data points across all puppies across all litters
- Area 6 fur density from puppy 2, litter 1 has the same set of allowable values as Area 6 fur density for puppy 9, litter 10, as does area 12 fur density from puppy 2, litter 1.
- Despite the fact that the range of allowable values for each trio of data is unique, each area is affected by the same things that affect all other areas; the same is true for Features and Characteristics:
- Whether or not the puppies are indoor or outdoor dogs affects all fur length
- The puppy's pedigree affects all puppies' skin color and elasticity
- How much exercise any one puppy gets affects the strength of all of its muscles
- Additionally, one trio of data may lend clues to another trio of data
- Area 10 skin pattern often has an affect on area 10 fur pattern
Given all of that: I have thousands of data points that I'm trying to analyze and draw conclusions from, and I'm looking for the best way(s) to do so. Pivot Table seems like it'd be helpful, but, as I move the data around to better understand it, I can't make conditional formatting follow individual cells around that would highlight cells showing bad values based on that data point's area, feature, and characteristic. I'm also poking around with Power Query, or whatever it's called now, but I haven't been able to make anything useful.
Suggestions would be incredibly helpful; otherwise I just have to look at all of this data manually.
What constitutes "wrong" fur color?
In your sample above, the Fur color is a numerical value. If you want to mark this as either right or wrong, then that is already one piece of analysis that you need to undertake in a separate step.
For example, you could create a column in the data that shows "fur color status", for which the value can be wrong or right or whatever. To arrive at the value, you may want to employ a formula that evaluates some of the other properties of that puppy. Maybe skin colors below 10 should have fur colors over 100 and if the fur color is not over 100, it is classified as wrong. Or something like that. An IF function should do the trick. With that function in place, you can then classify the data by the fur color status.
- Do Puppies from Litter 2 have a higher-than-average rate of wrong fur color?
Create a pivot table that shows the average rate for each fur color status in the columns, the litter numbers in the rows.
fur color status Litter correct wrong 1 80% 20% 2 75% 25% 3 95% 5% 4 19% 89% overall 67% 35% - Do all Puppy 4s have a higher-than-average rate of wrong fur color?
use a similar table, but with puppy numbers in the rows.
- Do all Site 21s have a higher-than-average rate of wrong fur color?
use a similar table, but with site numbers in the rows.
- If a Puppy's fur color is wrong, does that mean there's a greater-than-average likelihood that their fur length is wrong?
- If a Puppy's fur color is wrong, does that mean there's a greater-than-average likelihood that their eye color is wrong?
The first three bullets were simple calculations. These last two are about probability and go deeper into the realm of statistical analysis. Again, you will first need to build the structures (helper columns) required to classify by fur length status and eye color status.
If you load the data into the Power Pivot data model, you can add the helper columns there. You can then also use the powerful statistical DAX functions to create measures and then surface the results in pivot tables.
I'm not sure whether your question is more about understanding how Excel works, or what math to use to calculate a value with several variables, or if you need help with the whole concept of statistical analysis.
Neither of these can be explained in a single forum question, because they each have their own learning curves.
Hello jamesson_kaupanger ,
Power Query will help if you need to clean the data or merge different data sources into one.
For analysis you may want to use Power Pivot. Load the data into the Power Pivot Data Model. Then you can create all kinds of measures for all the different qualities and properties. Total (sum), count and average are just a fraction of what Power Pivot can calculate. With the Power Pivot measures, you can then build pivot tables and charts to visualise the information.
Have a look for Power Pivot articles and tutorials. It was made for the stuff you describe.
Thanks for responding.
I'm not looking to clean the data up; I trust the results themselves, and I'm trying to analyze them as they are.
I was thinking that Pivot Tables (which I'm assuming is related to Power Pivot, right?) might be the best way to go; it's just obnoxious that I can't seem to make the formatting sticky enough.
jamesson_kaupanger Pivot tables existed long before Power Pivot came along. Power Pivot has a new set of functions that can be used to analyze data and it goes way beyond what can be done with traditional pivot tables alone. You will still build pivot tables off the Power Pivot data model, but the measures that can be created with Power Pivot allow for a lot more and a lot differentiated analysis.
Load the data into the PowerPivot data model and create helper columns to help tag and classify data. Then these helper columns can be used in either measures or in filters and slicers of the pivot table you create from the data model.
Power Query can help you consolidate different data sources and shape data to the perfect form before it is loaded into the data model. Power Query on its own is not an analysis tool.
