## Doodling With 3d Animated Charts in R

Doodling with some Gapminder data on child mortality and GDP per capita in PPP\$, I wondered whether a 3d plot of the data over the time would show different trajectories over time for different countries, perhaps showing different development pathways over time.

Here are a couple of quick sketches, generated using R (this is the first time I’ve tried to play with 3d plots…)

```library(xlsx)
#dir()
#names(getSheets(wb))

#Set up dataframes
gdp=read.xlsx("indicator gapminder gdp_per_capita_ppp.xlsx", sheetName = "Data")
mort=read.xlsx("indicator gapminder under5mortality.xlsx", sheetName = "Data")

#Tidy up the data a bit
library(reshape2)

gdpm=melt(gdp,id.vars = 'GDP.per.capita',variable.name='year')
gdpm\$year = as.integer(gsub('X', '', gdpm\$year))
gdpm=rename(gdpm, c("GDP.per.capita"="country", "value"="GDP.per.capita"))

mortm=melt(mort,id.vars = 'Under.five.mortality',variable.name='year')
mortm\$year = as.integer(gsub('X', '', mortm\$year))
mortm=rename(mortm, c("Under.five.mortality"="country", "value"="Under.five.mortality"))

#The following gives us a long dataset by country and year with cols for GDP and mortality
gdpmort=merge(gdpm,mortm,by=c('country','year'))

#Filter out some datasets by country
x.us=gdpmort[gdpmort['country']=='United States',]
x.cn=gdpmort[gdpmort['country']=='China',]```

Now let’s have a go at some charts. First, let’s try a static 3d line plot using the scatterplot3d package:

```library(scatterplot3d)

s3d = scatterplot3d(x.cn\$year,x.cn\$Under.five.mortality,x.cn\$GDP.per.capita,
color = "red", angle = -50, type='l', zlab = "GDP.per.capita",
ylab = "Under.five.mortality", xlab = "year")
s3d\$points3d(x.bg\$year,x.bg\$Under.five.mortality, x.bg\$GDP.per.capita,
col = "purple", type = "l")
s3d\$points3d(x.us\$year,x.us\$Under.five.mortality, x.us\$GDP.per.capita,
col = "blue", type = "l")```

Here’s what it looks like… (it’s worth fiddling with the angle setting to get different views):

A 3d bar chart provides a slightly different view:

```s3d = scatterplot3d(x.cn\$year,x.cn\$Under.five.mortality,x.cn\$GDP.per.capita,
color = "red", angle = -50, type='h', zlab = "GDP.per.capita",
ylab = "Under.five.mortality", xlab = "year",pch = " ")
s3d\$points3d(x.bg\$year,x.bg\$Under.five.mortality, x.bg\$GDP.per.capita,
col = "purple", type = "h",pch = " ")
s3d\$points3d(x.us\$year,x.us\$Under.five.mortality, x.us\$GDP.per.capita,
col = "blue", type = "h",pch = " ")```

As well as static 3d plots, we can generate interactive ones using the rgl library.

Here’s the code to generate an interactive 3d plot that you can twist and turn with a mouse:

```#Get the data from required countries - data cols are GDP and child mortality
x.several = gdpmort[gdpmort\$country %in% c('United States','China','Bangladesh'),]

library(rgl)
plot3d(x.several\$year, x.several\$Under.five.mortality,  log10(x.several\$GDP.per.capita),
col=as.integer(x.several\$country), size=3)
```

We can also set the 3d chart spinning….

`play3d(spin3d(axis = c(0, 0, 1)))`

We can also grab frames from the spinning animation and save them as individual png files. If you have Imagemagick installed, there’s a function that will generate the image files and weave them into an animated gif automatically.

It’s easy enough to install on a Mac if you have the Homebrew package manager installed. On the command line:

brew install imagemagick

Then we can generate a movie:

```movie3d(spin3d(axis = c(0, 0, 1)), duration = 10,
dir = getwd())```

Here’s what it looks like:

Handy…:-)

Following on from Data Scraping Wikipedia With Google Spreadsheets, here’s a quick post showing how you can use another handy Google spreadsheet formula:

This function will pull in live – and historical – price data for a stock.

Although I noticed this formula yesterday as I was exploring the “importHTML” formula described in the Wikipedia crawling post, I didn’t have time to have a play with it; but after a quick crib of HOWTO – track stocks in Google Spreadsheets, it struck me that here was something I could have fun with in a motion chart (you know, one of those Hans Rosling Gapminder charts….;-)

NB For the “official” documentation, try here: Google Docs Help – Functions: GoogleFinance)

# Stock quotes and other data may be delayed up to 20 minutes. Information is provided “as is” and solely for informational purposes, not for trading purposes or advice. For more information, please read our Stock Quotes Disclaimer.
# You can enter 250 GoogleFinance functions in a single spreadsheet; two functions in the same cell count as two.

So – let’s have some fun…

Fire up a new Google spreadsheet from http://docs.google.com, give the spreadsheet a name, and save it, and then create a new sheet within the spreadsheet (click on the “Add new sheet” button at the bottom of the page). Select the new sheet (called “Sheet2” probably), and in cell A1 add the following:

In case you didn’t know, AAPL is the stock ticker for Apple. (You can find stock ticker symbols for other companies on many finance sites.)

The formula will pull in the historical price data for Apple at weekly intervals from the start of 2008 to October 10th. (“all” in the formula means that all historical data will be pulled in on each sample date: opening price, closing price, high and low price, and volume.)

(If this was the live – rather than historical – data, it would be updated regularly, automatically…)

I’m not sure a bar chart or scatter chart are quite right for historical stock pricing… so how about a line chart:

Et voila:

If you want to embed this image, you can:

If I was using live pricing data, I think the image would update with the data…?

Now create a few more sheets in your spreadsheet, and into cell A1 of this new sheet (sheet3) paste the following:

This will pull in the historical price data for IBM.

Create two or three more new sheets, and in cell A1 of each pull in some more stock data (e.g. MSFT for Microsoft, YHOO for Yahoo, and GOOG…)

Now click on Sheet1, which should be empty. Fill in the following title cells by hand across cells A1 to G1:

Now for some magic…

That key value – the value between “key=” and “&hl=en_GB#” is important – to all intents and purposes it’s the name of the spreadsheet. Generally, the key will be the characters between “key=” and an “&” or the end of the URL; the “&” means “and here’s another variable” – it’s not part of the key.

In cell B2, enter the following:

=ImportRange(“YOURKEY”, “Sheet2!A2:F42”)

=ImportRange(“p1rHUqg4g423seyxs3O31LA”, “Sheet2!A2:F42”)

What ImportRange does is pull in a range of cell values from another spreadsheet. In this case, I’m pulling in the AAPL historical price data from Sheet2 (but using a different spreadsheet key, I could pull in data from a different spreadsheet altogether, if I’ve made that spreadsheet public).

In cell A2, enter the ticker symbol AAPL; highlight cell A2, click on the square in the bottom right hand corner and drag it down the column – when you release the mouse, the AAPL stock ticker should be added to all the cells above. Label each row from the imported data, and then in the next row, B column, import the data from Sheet 3:

These rows will need labeling “IBM”.

Import some more data if you like and then… let’s create a motion chart (info about motion charts.

Highlight all the cells in sheet1 (all the imported data from the other sheets) and then from the Insert menu select Gadget; from the Gadget panel that pops up, we want a motion chart:

Configure the chart, and have a play [DEMO]:

Enjoy (hit the play button… :-)

PS And remember, you could always export the data from the spreadsheet – though there are probably better API powered ways of getting hold of that data…

PPS and before the post-hegemonic backlash begins (the .org link is broken btw? or is that the point?;-) this post isn’t intended to show how to use the Google formula or the Motion Chart well or even appropriately, it’s just to show how to use it to get something done in a hacky mashery way, with no heed to best practice… the post should be viewed as a quick corridor conversation that demonstrates the tech in a casual way, at the end of a long day…

PS for a version of this post in French, see here: Créer un graphique de mouvement à partir de Google Docs.