2

I have the following data frame in R:

> str(x2)
'data.frame':   262064 obs. of  15 variables:
 $ ykod : int  99 99 99 99 99 99 99 99 99 99 ...
 $ yad  : Factor w/ 38 levels "BAKUGAN","BARBIE",..: 2 2 2 2 2 2 2 2 2 2 ...
 $ per  : Factor w/ 3 levels "2 AYLIK","3 AYLIK",..: 3 3 3 3 3 3 3 3 3 3 ...
 $ donem: int  201106 201106 201106 201106 201106 201106 201106 201106 201106 201106 ...
 $ sayi : int  201106 201106 201106 201106 201106 201106 201106 201106 201106 201106 ...
 $ mkod : int  359 361 362 363 366 849 850 1505 1506 1525 ...
 $ mad  : Factor w/ 9529 levels "    Hilal Gida           ",..: 4473 3322 9360 7169 9359 9290 8903 6057 6055 6620 ...
 $ mtip : Factor w/ 27 levels "Abone Bürosu                                      ",..: 18 18 18 18 18 2 2 10 10 2 ...
 $ kanal: Factor w/ 2 levels "OB","SS": 2 2 2 2 2 2 2 1 1 2 ...
 $ bkod : int  110006 110006 110006 110006 110006 110006 110006 110006 110006 110006 ...
 $ bad  : Factor w/ 208 levels "4. Levent","500 Evler",..: 25 25 25 25 25 25 25 25 25 25 ...
 $ bolge: Factor w/ 12 levels "Adana Şehiriçi",..: 7 7 7 7 7 7 7 7 7 7 ...
 $ sevk : int  5 2 2 2 10 4 3 13 32 4 ...
 $ iade : int  0 2 1 2 4 3 2 0 8 4 ...
 $ satis: int  5 0 1 0 6 1 1 13 24 0 ...

Is it possible to rearrange this data frame like an Excel PivotTable? For example, I want to take ykod = 99 and create a new data frame with new rows and columns as follows:

                   donem=201106     donem=201107     donem=201108
Row#  mkod  mad   sevk iade satis  sevk iade satis  sevk iade satis
----  ----  ---   ---- ----- ----  ---- ---- ----   ---- ----- ----   
1     654   Abc    10    2    8     15   12   3       8    3    5
2     721   Def
.
. 
4345

Of course, the column names in the above example should be recreated, for example, as 201106_sevk, 201106_iade, 201106_satis, so and so forth.

Mehper C. Palavuzlar
  • 10,089
  • 23
  • 56
  • 69
  • I don't think this is deserved for data processing... as well as this is not used in proper sql queries. This is just for data presentation, but for further processing you will sooner or later find this inconvenient. I'd advice to process the data as is using filtering - like `x2[x2$donem==201106,c('sevk','iade','satis')]` rather than trying to make `donem=201106` a column. And then, when you need to present the results, export them to excel and make the pivot table there. – Tomas Oct 13 '11 at 07:57
  • this discussion http://stackoverflow.com/questions/6242106/excel-or-r-preparing-time-series-from-multiple-sources might be somewhat related to Tomas T.'s argument. Don't expect a solution there, but rather a grip on when to use R on when to use Excel. Maybe it helps you to find your individual preferences... – Matt Bannert Oct 13 '11 at 09:29

2 Answers2

1

Look at the reshape and plyr packages. The reshape package can do most (probably all) of the good parts of pivot tables in a more reproducable way, plus gives you more options on the summaries that you may want to compute and display. The plyr package is useful for any case where you want to take a data structure apart, do a computation or set of computations on the parts, then put the results back together.

Greg Snow
  • 48,497
  • 6
  • 83
  • 110
0

I don't think this is deserved for data processing... as well as this is not used in proper sql queries. This is usually deserved just for data presentation, but for further processing you will sooner or later find this inconvenient. I'd advice to process the data as is using filtering - like x2[x2$donem==201106,c('sevk','iade','satis')] rather than trying to make donem=201106 a column. And then, when you need to present the results, export them to excel and make the pivot table there.

But, if you still insist on it, it's little bit clumsy but you can try tapply (I'm sure the R guys will come with much better solutions):

pivot = tapply(df$value, list(df$row, df$column), identity)

You can then cbind() other row attributes and also change the column names like:

colnames(pivot) = paste("var_", colnames(pivot), sep = "")
Tomas
  • 57,621
  • 49
  • 238
  • 373