CODEWITHSUNDEEP
r
Thomas Browne
Thomas Browne

Reputation: 24888

Turning field values into column names in an R data frame

Hello I have the following dataframe returned using the RBloomberg library:

> tt <- bdh(conn, secs, "last price", "20110501")
> tt
          ticker       date last price
1  EURUSD Curncy 2011-05-01         NA
2  EURUSD Curncy 2011-05-02     1.4830
3  EURUSD Curncy 2011-05-03     1.4825
4  EURUSD Curncy 2011-05-04     1.4827
5  EURUSD Curncy 2011-05-05     1.4539
6  EURUSD Curncy 2011-05-06     1.4316
7  EURUSD Curncy 2011-05-07         NA
8  EURUSD Curncy 2011-05-08         NA
9  USDZAR Curncy 2011-05-01         NA
10 USDZAR Curncy 2011-05-02     6.6090
11 USDZAR Curncy 2011-05-03     6.6394
12 USDZAR Curncy 2011-05-04     6.6837
13 USDZAR Curncy 2011-05-05     6.7250
14 USDZAR Curncy 2011-05-06     6.7051
15 USDZAR Curncy 2011-05-07         NA
16 USDZAR Curncy 2011-05-08         NA
17 USDTRY Curncy 2011-05-01         NA
18 USDTRY Curncy 2011-05-02     1.5218
19 USDTRY Curncy 2011-05-03     1.5336
20 USDTRY Curncy 2011-05-04     1.5471
21 USDTRY Curncy 2011-05-05     1.5488
22 USDTRY Curncy 2011-05-06     1.5445
23 USDTRY Curncy 2011-05-07         NA
24 USDTRY Curncy 2011-05-08         NA
25 USDBRL Curncy 2011-05-01         NA
26 USDBRL Curncy 2011-05-02     1.5893
27 USDBRL Curncy 2011-05-03     1.5876
28 USDBRL Curncy 2011-05-04     1.6182
29 USDBRL Curncy 2011-05-05     1.6220
30 USDBRL Curncy 2011-05-06     1.6149
31 USDBRL Curncy 2011-05-07         NA
32 USDBRL Curncy 2011-05-08         NA
33 USDINR Curncy 2011-05-01         NA
34 USDINR Curncy 2011-05-02    44.3350
35 USDINR Curncy 2011-05-03    44.5150
36 USDINR Curncy 2011-05-04    44.4675
37 USDINR Curncy 2011-05-05    44.7625
38 USDINR Curncy 2011-05-06    44.7950
39 USDINR Curncy 2011-05-07         NA
40 USDINR Curncy 2011-05-08         NA

How do I turn it into something that looks more like this (ie the ticker column values have become the column names)?

Date    EURUSD Curncy   USDZAR Curncy   USDTRY Curncy   USDBRL Curncy   USDINR Curncy
2011-05-01          NA          NA          NA          NA          NA
2011-05-02      1.4830      6.6090      1.5218      1.5893     44.3350
2011-05-03      1.4825      6.6394      1.5336      1.5876     44.5150
2011-05-04      1.4827      6.6837      1.5471      1.6182     44.4675
2011-05-05      1.4539      6.7250      1.5488      1.6220     44.7625
2011-05-06      1.4316      6.7051      1.5445      1.6149     44.7950
2011-05-07          NA          NA          NA          NA          NA
2011-05-08          NA          NA          NA          NA          NA

Upvotes: 8

Views: 12126

Answers (2)

Andrie
Andrie

Reputation: 179398

Have a look at the dcast function in package reshape2:

library(reshape2)
dcast(tt, date ~ ticker)


        date EURUSD USDBRL  USDINR USDTRY USDZAR
1 2011-05-01     NA     NA      NA     NA     NA
2 2011-05-02 1.4830 1.5893 44.3350 1.5218 6.6090
3 2011-05-03 1.4825 1.5876 44.5150 1.5336 6.6394
4 2011-05-04 1.4827 1.6182 44.4675 1.5471 6.6837
5 2011-05-05 1.4539 1.6220 44.7625 1.5488 6.7250
6 2011-05-06 1.4316 1.6149 44.7950 1.5445 6.7051
7 2011-05-07     NA     NA      NA     NA     NA
8 2011-05-08     NA     NA      NA     NA     NA

Upvotes: 15

Thilo
Thilo

Reputation: 9157

What comes to my mind is the use of tapply:

tapply(tt$price, list(date=tt$date, ticker=tt$ticker), mean)

As Mean for one element is the element itself, this should be the same. You only may get in trouble once you have more than one value for one day and ticker. (But, then, your requirement is not well defined)

Upvotes: 0

Related Questions