Today’s post was written by Igor Peev, senior program manager on the Power Map for Excel team.
On February 19th, I received an email from Vice President Joe Biden. I have never had the pleasure to meet our Vice President and I was a little surprised. The email looked legit and not like a phishing scam. The subject of the email was “Good Job.” I figured the email was sent to millions of people and I was one of them, but because I like compliments, I opened it.
The first sentence in the email read, “This time of year, I like to break out a little saying: Don’t tell me what you value, show me your budget and I’ll tell you what you value.” I searched the web for “White House budget dataset,” and found just the thing I was looking for. The dataset containing the information about the White House 2016 budget announcement was posted as a budauth.CSV file on GitHub.
The site stated, “Each year, after the President’s State of the Union address, the Office of Management and Budget releases the Administration’s Budget, offering proposals on key priorities and newly announced initiatives. This year we are releasing all of the data included in the President’s Fiscal Year 2016 Budget in a machine-readable format here on GitHub. The Budget process should be a reflection of our values as a country, and we think it’s important that members of the public have as many tools at their disposal as possible to see what is in the President’s proposals. And, if they’re motivated to create their own visualizations or products from the data, they should have that chance as well.”
Reading this was very exciting because I did not expect it to be that easy. And what a great way to invite the public to an open dialog about spending—here is the data showing how money is spent by different agencies over a number of years. This was an open invitation to explore the data and to visualize it.
Using Power Map to explore the U.S. federal budget
I started thinking how this may work in Power Map and about the different ways the information can be presented. I decided to experiment and my first idea was to work with that data in the format and layout in which the government delivered the information. The data in the budauth.csv file published on GitHub was in cross-tabular format and I assumed this choice was intentional.
First, I took the raw data and built a 3D data visualization tour of it ‘as is.’ The financing of the U.S. government is actually a lot more complicated and to build a realistic picture one needs to do a lot more work in adding and adjusting the raw data. The data in budauth.csv was structured so that a human can consume it even without manipulating it. In its raw form there are some 4443 rows and 57 columns in the budauth.csv, so not necessarily an easy task, but easy to navigate. My initial tour can be used as a foundation to present a view of the data adjusted for inflation and reflects how the positive and negative numbers must be interpreted.
From there I realized that if I wanted to find something specific I could. For example, if I wanted to browse spending for Social Security, I could just filter to the agency name and scroll right to see the years.
I imagined how this would look in 3D and got to work on un-pivoting the big table. My friend Chad Rothschiller, a senior program manager who worked many years on the Excel team lent his expertize. In short time we had a solid set of data that could be visualized in Power Map as long as it had X and Y coordinates.
I used the original format using sequential numbers for the rows, which are in the ‘ID’ column. I then made a calculated column converting years into numbers. I decided to use the =DAYS360(DATE(2020,1,1),[@YearAsDate] function because this way you can reuse this example to make visualizations in Power Map for events that happen on a day-to-day basis rather than a yearly basis. In this case, there are only 44 distinct vales for Y mapping to the 44 years in the columns.
Next, I set out to make a couple of images that I used as Custom Maps. One was a rough representation of the original layout of the data and the second one I made after I made a version of the data pivoted by Agency name and then by year.
This second image below allowed for the final scenes in the video where all spending by a particular agency is aggregated in a single column in a particular year. The details on how the data was un-pivoted are in the workbook. There are worksheets in the workbook that show how to make the images used as the Custom Maps as well. The best part about this visualization of the budget is interacting with it. You will really get a lot more out of it if you allow for some time and just filter the different scenes.
For example, if you filter out the Value field, which represents spending or deficit to show positive values below $20 billion dollars, you will be able to explore the agencies that are not as big and not visible in the initial scenes. Also, I tried finding outliers using the Data Card feature to customize further if needed and then searched with Bing to find more context about them. That’s how I was able to identify the Savings and Loan crisis in the 1990s and the Hurricane Katrina spending in 2005 in the budget of Homeland Security.
The value of 3D here is really the interaction and the very quick summary of a significant and complicated dataset.
I also explored other ways to visualize the data, such as this 2D version, done using the Bubble charts and simply looking directly down:
It is easy to explore and discover insights and then it is easy to make a video showing your findings. Please feel free to explore, create and post more tours as videos on your favorite video sharing site and tweet them back at us @MSPowerMap. You can download the workbook and get started here.
Power Map for Excel is available with any Office 365 subscription that includes Office desktop apps. To use Power Map, open Excel and go to Insert > Map.