De-pivot to pivot

If you’ve ever wanted to plot some data that isn’t pivoted in quite the right way, but you can’t figure out how to transform it…

Hadley Wickham (who writes of lot of great R libraries) wrote this article called Tidy Data. It’s the source of many great 5 dollar words. Even if you don’t use R, it’s worth a read.

One I particularly like is what you do when you have data that isn’t pivoted in the right way, but you need to de-pivot it in order to pivot again.

This data:

Year

Joiners

Leavers

Transfer in

Transfer out

2012

10

4

0

6

2013

15

7

1

6

2014

6

6

5

3

2015

10

8

5

4

 

Is already in a summary form. If I were working in SQL I might do:

Select year, Joiners, ‘joiners’ as type

    union

Select year, Leavers, ‘leavers’ as type

    union

Select year, TransferIn, ‘TransferIn’ as type

    union

Select year, TransferOut, ‘TransferOut’ as type

 

Wickham’s word for this is “melt” and he has a function for it in the reshape package.

In R (I save my data as CSV, yuck!):

before <- read.csv(“C:\\temp\\head-before.csv”)

melt(before, id=c(Year”))

 

and the result:

Year variable value

1 2012 Joiners 10.0

2 2013 Joiners 15.0

3 2014 Joiners 6.0

4 2015 Joiners 10.0

5 2012 Leavers 4.0

6 2013 Leavers 7.0

7 2014 Leavers 6.0

8 2015 Leavers 8.0

.

.

 

Damn hard to do in Excel.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s