Table Transformation

On Wed, Mar 4, 2009 at 11:58 AM, Christian Pilger

Dear R-experts,

recently, I started to discover the world of R. I came across a problem,
that I was unable to solve by myself (including searches in R-help, etc.)

I have a flat table similar to

key1 ? ?key2 ? ?value1

abcd_1 ?BP ? ? ?10
abcd_1 ?BSMP ? ?1A
abcd_1 ?PD ? ? ?25
abcd_2 ?BP ? ? ?20
abcd_3 ?BP ? ? ?80
abcd_4 ?IA ? ? ?30
abcd_4 ?PD ? ? ?70
abcd_4 ?PS ? ? ?N

I wish to transform this table to obtain the following result:

? ? ? ?key2
key1 ? ?BP ? ? ?BSMP ? ?IA ? ? ?PD ? ? ?PS
abcd_1 ?"10" ? ?"1A" ? ?"" ? ? ?"25" ? ?""
abcd_2 ?"20" ? ?"" ? ? ?"" ? ? ?"" ? ? ?""
abcd_3 ?"80" ? ?"" ? ? ?"" ? ? ?"" ? ? ?""
abcd_4 ?"" ? ? ?"" ? ? ?"30" ? ?"70" ? ?"N"

I think we would say that a dataframe of the first type is in the
"long" format, while the other one you want is in the "wide" format.
I've done changes like that with the "reshape" function that is in the
stats package.

This example you propose is like making one column for each "country"
where key 1 is like the "year" in which the observation is made.
Right?

You don't have an easily cut-and-pasteable code example, so I've
generated a little working example. Here, x1 is key 1 and x2 is key 2.

x1 <- gl(4,5, labels=c("c1","c2","c3","c4"))
x1

[1] c1 c1 c1 c1 c1 c2 c2 c2 c2 c2 c3 c3 c3 c3 c3 c4 c4 c4 c4 c4
Levels: c1 c2 c3 c4

x2 <- rep(1:5,4)
df <- data.frame(x1, x2, y=rnorm(20))
df

x1 x2           y
1  c1  1  0.02095747
2  c1  2  0.05926233
3  c1  3 -0.07561916
4  c1  4 -1.06272710
5  c1  5 -1.89202032
6  c2  1 -0.04549782
7  c2  2 -0.68333187
8  c2  3 -0.99151410
9  c2  4 -0.29070280
10 c2  5 -0.97655024
11 c3  1  0.33411223
12 c3  2 -0.24907340
13 c3  3 -0.25469819
14 c3  4  1.23956157
15 c3  5 -1.38162430
16 c4  1  0.50343661
17 c4  2 -0.58126964
18 c4  3  0.24256348
19 c4  4 -0.39398578
20 c4  5  0.01664450

reshape(df, direction="wide", timevar="x2", idvar="x1")

x1         y.1         y.2         y.3        y.4        y.5
1  c1  0.02095747  0.05926233 -0.07561916 -1.0627271 -1.8920203
6  c2 -0.04549782 -0.68333187 -0.99151410 -0.2907028 -0.9765502
11 c3  0.33411223 -0.24907340 -0.25469819  1.2395616 -1.3816243
16 c4  0.50343661 -0.58126964  0.24256348 -0.3939858  0.0166445

Your case will have many missings, but I think the idea is the same.

HTH

Paul E. Johnson
Professor, Political Science
1541 Lilac Lane, Room 504
University of Kansas