Menu
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
229
To convert a column values to column names, we can use dcast function of reshape2 package. For example, if we have a data frame called df that contains two columns say x and y, where x is categorical and y is numerical. Now if we want to convert the categories in x as column names then it can be done as dcast(df,y~x).
To access a specific column in a dataframe by name, you use the $ operator in the form df$name where df is the name of the dataframe, and name is the name of the column you are interested in. This operation will then return the column you want as a vector.
To interchange rows with columns, you can use the t() function. For example, if you have the matrix (or dataframe) mat you can transpose it by typing t(mat) . This will, as previously hinted, result in a new matrix that is obtained by exchanging the rows and columns.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
