Analysis of a Shopping List

I know some people would look at the file you can download to go with this page and say, who needs to analyse someone else’s shopping list? Well, that’s the wrong question! The reaction should be, let’s see what we can do with a shopping list!

What the attached file does is to give you the things I bought at my local supermarket on 11 different occasions. I have just left out some of the data to keep the list from being overwhelming. The file is then structured as follows but you are free to do with it what you wish once you have downloaded it.

Tab 1: original data

The data in this sheet are the actual data collected from the till receipts relating to 11 trips to my local supermarket or grocery delivery received over a number of weeks in 2012.

I like everyone who works with me to know that I like to gather and use real data as much as possible: these are all actual data. The data  reveal a lot about my shopping and eating habits but I have added nothing and taken nothing away from what I actually did on those trips. You might even be able to draw conclusions about my lifestyle from this list!

Your task is the analyse the  data you can see here in the way you think best. Consider the following, however:

Classification of data … since this file will be used around the world, I have provided my classification of every item to help you. Plus,

  • Excel Table
  • Graphs/Charts
  • Pivot Table(s)
  • Pivot Chart(s): I have not included any of these
  • Descriptive Statistics
  • Anything else you can think of as appropriate

Tab 2: Classification and Excel Table 

I classified the data and then converted the original list into an Excel Table:

Classification of data … since this file will be used around the world, I have provided my classification of every item to help you. Are you happy with the one level of classification I have provided? Do you think one or two more levels might help in any way? For example, I could have further classified dairy as, say,

  • processed cheese
  • British cheese
  • foreign cheese
  • yoghurt
  • butter
  • milk

How would you sub classify the vegetables? the biscuits? and so on?

Excel Table

Now that you have prepared your Excel Table, what can we do with it?

  • sort … analyse the data to quantify differences between shopping trips, such as with the items I bought, the changes in costs …
  • filter
  • count … there are 238 items in the list
  • totals … I spent £355.88 on these items
  • averages … the average cost per item is £1.50
  • standard deviation of all items is £0.93
  • the minimum amount spent on any item is £0.66
  • the maximum amount spent on any item is £6.60

Tab 3: Sub Classification

Sub Classification of data … since this file will be used around the world, I have provided my sub classification of every item to help you

I have further classified dairy as, for example,

  • processed cheese
  • British cheese
  • foreign cheese
  • yoghurt
  • butter
  • milk
  • eggs
  • and so on

Please note I have marked some items in column G with ***: in these cases I have changed the classification since the initial, original, data sheet

Now that there is another level of classification, you can further analyse my shopping habits by trip, again, by sub classification and so on.

Tab 4: Data Analysis

On tab 4 I have given you some ideas of how to analyse such data:

  • class intervals: amount spent per item …
  • frequency distribution
  • cumulative frequency distribution
  • descriptive statistics
  • histogram

Tab 5: Pivot Table

The analysis of such data is made relatively simple by using a pivot table. The classification and sub classification steps are vital here too: so there are related learning points here for you!

Conclusions

Overall, a simple shopping list becomes a significant piece of work for anyone starting out in data analysis and this page and the file you can download will help you with that. Take a look at what I did and how I did it. Tell me if you would have done things differently: improvements are always welcome. I am more than willing to learn from you as you are to learn from and share with me.

File to download: shopping_data_detailed_analysis

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: