The Pivot Table

The business of expressing data in a spatial manner is a very fascinating one. My pastel-colored world is definitely an eye-catching venture that can help you make sense of an otherwise bland set of data of infinite rows. (This particular one had almost 2,000 rows in them!)

land use sample map

But before anything can look as pastel-color perfect, you need to first ensure that you have the right type of data. Usually the editing and cleaning up and analysis of data begins in Microsoft Excel or any spreadsheet that you are comfortable with. I tend to prefer Excel and a new favorite feature I have of this program is the Pivot Table function.

Pivot Table, as the name implies, involves manipulating a given table to make it appear in a different way so that you can have more room for new emphasis. A simple table can look like this:

given table

(Sorry for the apples and oranges sample. It’s just for illustration purposes only.)

Suppose that I want to line up the columns in a different way. I can use the pivot table option found on the Insert menu toolbar.

step 1

 

The program will prompt me to highlight the region of interest and then choose whether I want the new table in the same page or in another worksheet: step 2

After that has been settled, a new panel on the right sidebar appears where I can manipulate the fields by dragging and dropping them on the areas below:

step 3

For this particular set, I decided to use this:

step 4 - drag and drop

And voila. There’s my new perspective of the previous table I had for a given.

There are options to tweak this appearance by accessing the field value settings (right click on lower left area to access this menu). I can choose to just display counts, averages, maximum or minimum values, and other things:

step 4a - value field settings

 

Clicking the design tab on top allows me to tweak the appearance a little and decide whether I want to display subtotals, depending on the type of data. For the purposes of fruit tallies, I settled for the simple tabular format:

step 4b - report layout

The pivot table can allow me to make many things out of an otherwise ordinary table.If this function did not exist, I’d probably be able to sort 5,000 rows of data in a whole day. This exercise took only the whole of 20 minutes. Go figure. 😉

I have to thank my boss Engr. Gina Milarion for sitting with me on this one as I play with real-life apples and oranges. 🙂 I keep on using MS Excel daily and I realize the magic tricks it has under its sleeve. As it turns out, it’s not as boring or generic as I initially thought. 🙂

 

 

9 thoughts on “The Pivot Table

  1. Hi,

    I would like to say that you have done a great deal of map making. I have also been making maps and upload it to Wikipedia, always in the search for accurate boundaries or observing “status quo” in disputed areas. I wonder if you have large format images of your maps. I also see that you have in your excel sample the land area of the barangays. One time I was able to download this from the PHILVOLCS website, but since my pc crashed I lost the file and couldn’t locate it any longer, I was wondering if I could obtain the data from you? Thanks

    1. Hi Roel, thank you for visiting Helena. 🙂 Oh no, sayang ang PHIVOLCS data!

      To be honest, the data I can share would be yung Open Source datasets from PhilGis.org. Kasi the others have owners from private sector, and I have limited sharing ability for them because I don’t own them…

      If it helps, I just used NSO Statistics for land area and superimposed them with PhilGIS barangay datasets. However, it might be good to georeference or overlay the resulting shapefile into QGIS or ArcGIS from satellite maps kasi barangays change in some areas. In the particular area I studied recently, yung isang barangay sa Cavite nung 1990, na-split na into 5 barangays by year 2010. Population data went all bonkers and I had to basically maneuver sa Excel to clean it up and then find the right shapes. I did some shape editing but it’s not an exact science. Mas ok siya for land use classifications or flood hazard basemapping but for precise survey-grade ones, baka mahirap.

      Also, regarding boundary disputes, that is also one politically motivated issue that causes a challenge sa isang cartographer. 🙂 Happy map making to us, then!

      1. Oh thanks for the reply and I totally understand the limitations that you have. I’ve actually been reaching out to some LGUs and even news reports whenever available to observe a status quo in determining boundaries. I do get responses sometimes. But this is very useful thanks for the information.

    1. hahahaha Hi Nonoy! Nope, yung preliminary step lang of cleaning data ang nagagawa sa Excel. I saw a map done in Excel na raster-like pero I haven’t tried, old school yun. I used a software for generating the map image itself. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *