Sunday, October 30, 2011

Easier the Second Time


I got this from Billy Mitchell:

Graph #1

I threw some trends on Billy's graph, and Jazzbumpa threw them back at me.

I want to do more with trends, using this same graph as a sample. So now I need the numbers. I found this graph at the BLS link Billy provides:

Graph #2

BLS provides the data in an Excel file. Pretty nice: It goes back to 1939. Monthly numbers. Funny thing, though: Each year is on its own row, with one column for each month. Just like the Consumer Price Index file I got the other day.

So it was another opportunity to practice my transposing skills, to get all those 12-cell rows turned and moved and properly aligned with all the data in one column so I can more easily use it for graphs and calculations.

I did it this time in Excel, rather than Open Office Calc. I did the same thing as before, inserting columns at the left and doing my work there.


1. In Excel I typed Jan 1939 and when I hit ENTER, it got translated into Jan-39. So Excel recognized it as a date. After I entered the next two months of 1939, I grabbed all three cells and dragged 'em down, and Excel filled in the whole column for me with all the dates I needed.

Perhaps in Open Office if I explicitly formatted the cell as a date, it would have filled in all the dates for me when I tried dragging the group of three cells (see steps 8 and 9 of yesterday's early post).

//

I notice it is much easier to keep track of what I'm doing with this source data, because this time the file does not have the blank line every fifth year.

//

I also noticed the rhythm of the process. First, select a dozen cells and press CTRL-C. Then:

Click a temporary destination and press CTRL-V and then CTRL-X
Click a permanent destination and press CTRL-V and then CTRL-C.

Click a temporary destination and press CTRL-V and then CTRL-X
Click a permanent destination and press CTRL-V and then CTRL-C.

Click a temporary destination and press CTRL-V and then CTRL-X
Click a permanent destination and press CTRL-V and then CTRL-C.

and like that to the end. Except at the end, you don't need the last CTRL-C, because there are no more years to copy.

But it's symmetrical: The CTRL-C that's missing from the end of the sequence appears at the start!

//

Anyway, the whole rearranging-the-data thing took less than half an hour. And that's for all the years since 1939. It goes quick.

//

Spending that much time with the numbers, it occurred to me how unusual it is that the numbers don't keep rising. Economic numbers always go up. But not these numbers.

2 comments:

Jazzbumpa said...

No, no, no. That was only a list of helpful suggestions. When I throw something back at you, it will go SPLAT!

Cheers!
JzB

Jazzbumpa said...

BTW, FRED has this data ready to go.

http://research.stlouisfed.org/fred2/series/MANEMP/downloaddata?cid=32311

Cheers,
JzB