2

In each area, performance of various Rockbands are measured. We need to reshape such that it is convenient to see top 3 brands and their score for each area. Given input file would be like Input File Look like

Output File look like

Community
  • 1
  • 1
Navin Manaswi
  • 964
  • 7
  • 19
  • Add a ranking variable (or just sort), drop anything lower than your specified ranking, and then do a basic long to wide reshape. Without reproducible data, you're asking for quite a lot of effort on the part of people who may be interested in answering the question. – A5C1D2H2I1M1N2O1R2T1 Nov 09 '15 at 06:52
  • This type of operation is called to "pivot" or "unpivot" a table. (After choosing the top 3) With this term, I'm sure you will find something on CRAN. – Has QUIT--Anony-Mousse Nov 09 '15 at 07:18
  • Do not post your data as an image, please learn how to give a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610) – Jaap Nov 09 '15 at 07:30

1 Answers1

0

We could try with data.table. Convert the 'data.frame' to 'data.table' (setDT(df1)), grouped by 'Area', we order the 'PopularityScore' and subset the first three rows (head(.SD,3)), we create a sequence column ('ind') grouped by 'Area', then reshape to wide format using dcast.

library(data.table)#v1.9.6+
 DT <- setDT(df1)[order(-PopularityScore), head(.SD,3) ,.(Area)][, ind:= 1:.N, Area]
dcast(DT, Area~ind, value.var=c('RockBand', 'PopularityScore'))
#           Area  RockBand_1 RockBand_2 RockBand_3 PopularityScore_1
#1: floridaChrist       Rocus  Metajones         U3             87012
#2:         sesam FlowerModes   Scouting         U3             51326
#   PopularityScore_2 PopularityScore_3
#1:             43136             17969
#2:             36622             30839

data

df1 <- data.frame(Area = rep(c('sesam', 'floridaChrist'), c(8,6)), 
      RockBand= c('FlowerModes', 'Scouting', 'U3', 'MetaJones', 'Rocus', 
     'Britpi', 'Californi', 
     'SystemOfDown', 'Rocus', 'Metajones', 'U3',
   'FlowerModes', 'Scouting', 'Britpi'), PopularityScore=c(51326, 36622, 
   30839, 543, 197, 115,70, 3294, 87012, 43136, 17969, 6425, 4383, 3955))
akrun
  • 874,273
  • 37
  • 540
  • 662