How do you create pretty pictures in Excel from raw, fairly unorganised data? Well... this is where the magic happens.
In order to make pretty charts, you have to do generally a bit more than just throw some data in Excel and press "chart". Generally my process is something like this:
- formulate the question
Often somebody makes a statement on the forum that seems excessive to me, or that I've seen repeated without proof, etc. I'll wonder: is that really true? For instance: are rookie TEs that are drafted high, better candidates for good season?
- formulate that question in terms of a query on pro football reference (or other data)
Of course you cannot easily quantify every question. But you have to make this more specific though, in terms where you can find data to prove or disprove it. So in this case, let's get all rookie TEs with their season fantasy points for the period 2000-2018, as we do this to match https://www.fantasyindex.com/2018/07/10/factoid/rookie-tight-ends
- get the data and copy it in chunks of 100 to an Excel tab
Pro Football Reference has a number of amazing query tools to provide season, game and career data. Unfortunately they generally present this in blocks of 100, so if you want a bit more data (and in statistics you ALWAYS want more data), then that's a lot of copy paste. I've asked, and there are no more advanced tools to do that.
- now I force the data into a format I can use, eg:
* introduce a "filter" in the data to get rid of unwanted title rows and other garbage
* often I will create a "pivot table" so I can group data by age or year or something that is relevant. Eg if I would want to plot the data above by year (which in this case doesn't make much sense as I only have one year for every player), I would probably put player in the rows, year in the columns, and FanPoints in the values.
* Copy the data from the pivot table to a new sheet
This step is necessary as you cannot easily make charts directly from a pivot table, especially scatter graphs. You can make a simple bar chart, but if you have an X-Y chart, this does not translate into a ready chart.
Still with me? We now finally get to the charts, but the previous steps are essential to the process, so I felt I needed to explain them.
- now I can start creating charts, generally from a "scatter graph". I select 2 columns in the data with numerical data, and click "insert scatter chart"
Now this chart you can easily format into other charts, including graphs that combine bars and lines, or other methods. Just play with the options until the thing you wanted to know becomes clearer. For instance, maybe I want the years to be bars, but the average a line; that would then look like this:
If you have data that's very different in range, eg one is yards and the other is percentages, you can also use "secondary axis" to make both visible.
There are some more nice tricks in Excel and charts, eg the use of data labels/call-outs, but this should be a nice start.
In order to make pretty charts, you have to do generally a bit more than just throw some data in Excel and press "chart". Generally my process is something like this:
- formulate the question
Often somebody makes a statement on the forum that seems excessive to me, or that I've seen repeated without proof, etc. I'll wonder: is that really true? For instance: are rookie TEs that are drafted high, better candidates for good season?
- formulate that question in terms of a query on pro football reference (or other data)
Of course you cannot easily quantify every question. But you have to make this more specific though, in terms where you can find data to prove or disprove it. So in this case, let's get all rookie TEs with their season fantasy points for the period 2000-2018, as we do this to match https://www.fantasyindex.com/2018/07/10/factoid/rookie-tight-ends
- get the data and copy it in chunks of 100 to an Excel tab
Pro Football Reference has a number of amazing query tools to provide season, game and career data. Unfortunately they generally present this in blocks of 100, so if you want a bit more data (and in statistics you ALWAYS want more data), then that's a lot of copy paste. I've asked, and there are no more advanced tools to do that.
- now I force the data into a format I can use, eg:
* introduce a "filter" in the data to get rid of unwanted title rows and other garbage
* often I will create a "pivot table" so I can group data by age or year or something that is relevant. Eg if I would want to plot the data above by year (which in this case doesn't make much sense as I only have one year for every player), I would probably put player in the rows, year in the columns, and FanPoints in the values.
* Copy the data from the pivot table to a new sheet
This step is necessary as you cannot easily make charts directly from a pivot table, especially scatter graphs. You can make a simple bar chart, but if you have an X-Y chart, this does not translate into a ready chart.
Still with me? We now finally get to the charts, but the previous steps are essential to the process, so I felt I needed to explain them.
- now I can start creating charts, generally from a "scatter graph". I select 2 columns in the data with numerical data, and click "insert scatter chart"
Now this chart you can easily format into other charts, including graphs that combine bars and lines, or other methods. Just play with the options until the thing you wanted to know becomes clearer. For instance, maybe I want the years to be bars, but the average a line; that would then look like this:
If you have data that's very different in range, eg one is yards and the other is percentages, you can also use "secondary axis" to make both visible.
There are some more nice tricks in Excel and charts, eg the use of data labels/call-outs, but this should be a nice start.
Comments
Post a Comment