Examples for visualization calculations

calc

Some of the projects have really transformative methods of turning one set of data (i.e. borrow direct data) into another set of seemingly unrelated data (environmental impact of borrow direct). It is often a good idea to show these calculations in your visualization, because the innovation is part of the charm of the visualization.

So how exactly do you put mathematical calculations in visualizations without seeming too wordy and complex? Here are some examples from Jessica Xu, an alum of the class and math visualization extraordinaire from 2014.

Type by Type

A measure of comparison

What if I made it myself

 

Basic Excel Tutorial

Because the project 3 data sets are so huge, you’re probably going to need a few more tricks up your sleeve than just counting things manually.

You can accomplish most data analysis you need by using pivot tables. They compute fairly fast and allow you to mix and match any columns together for comparison. Click this link to read about how to use them.

If you really want to zoom in and analyze specific parts of the data, then you might need to use either Excel functions or VBA macros. I recommend using Excel functions over VBA, which is slightly more complicated and require some serious programming. Here are some simple Excel functions to start you off.

Counting unique values in a column

  • paste this formula into a cell
    • =SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),””), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),””))>0,1))
  • change all occurrences of “A2:A10” into whatever range you want. You can also use “A:A” if you want to calculate for the entire column.
  • select the cell containing the formula, press f2, then press ctrl+shift+enter
  • the value might show up as 0 initially, but will show the right value once it finishes the calculation.

Showing all unique values in a column

  • On the top bar of the excel window, click the Data tab
  • under the Sort & Filter section, click the advanced button and an “advanced filter” window will pop up
    • for action, select “copy to another location”
    • for List and Criteria range, select the column you want filtered
    • for copy to, select an empty column you want the results displayed
    • check “unique records only”
    • click okay

Counting frequency of a certain value in a column

To count word frequency in a single column

  • past this formula into a cell
    • = COUNTIF(A2:A10, “word”)
  • replace “A2:A10” with your own cell range, or just “A:A” for the entire A column
  • replace “word” with your own word. use quotation marks!
  • or if you’re dealing with values, replace “word” with formulas like “< 2000”

To count word frequency across multiple columns (e.g. given a name and home state column, how many John’s are from NY?)

  • past this formula into a cell
    • = COUNTIF (A2:A10, “John”, B2:B10, “NY”)
  • replace the cell ranges with your own cell range
  • replace “John” and “NY” with your own words or values
  • you can string along as many criteria’s as you want as such: = COUNTIF(range, text/value, range, text/value, range, text/value…)

Check out this guide and this guide for more info.

Note: Excel doesn’t care about cares when matching words, so “John” and “john” will both be counted. Excel only tries to find exact matches, so “Johnathan” will not match with “John”. If you want to match all words that contain the words “John” in some fashion, you are going to want to use regular expressions. This site has some simple tutorials on how to use them.

Other Tutorials

Simple tutorial on excel formulas and functions if you have no experience at all

List of all Excel functions