1

I have a little app that lists customers, items and sales. I want to generate a lookup list from the table but get two columns if I try to order the list using keyby. It is probably VERY simple but I'm confused. Code below....

library(data.table)

company=c("A","S","W","L","T","T","W","A","T","W")
item=c("Thingy","Thingy","Widget","Thingy","Grommit","Thingy","Grommit","Thingy","Widget","Thingy")
sales=c(120,140,160,180,200,120,140,160,180,200)
salesdt<-data.table(company,item,sales)

server <- function(input, output) {

  output$theCustomersList <- renderUI({
    list(
      selectInput("customer", "Choose a customer:",
                   choices = salesdt[,unique(company), keyby=company]
                   ,selectize=FALSE
                   ,selected="A"
                   )

    )
  })

  output$result <- renderTable(
                    salesdt[company%in%c(input$customer),
                    .(valuesold=sum(sales)), item
                    ]
                  )

}

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      uiOutput("theCustomersList")
    ),


    mainPanel(tableOutput('result'))
  )
)

shinyApp(ui = ui, server = server)

To clarify.... my question is how do I change 

salesdt[,unique(company), keyby=company]

to get get one column but ordered.

Martyn
  • 55
  • 6
  • I'm not sure what your problem exactly is. Perhaps use only `choices = unique(company)`? – Roman Apr 29 '16 at 11:47
  • The company is referenced in the data.table ..... In the real world there are 40k rows in the CSV I am importing and I need the unique list from there. – Martyn Apr 29 '16 at 11:59
  • You're asking why you get two columns, `salesdt[,unique(company), keyby=company]` is producing 2 columns. So you have to change this line. – Roman Apr 29 '16 at 12:10
  • Yes I understand.... Sorry if I didn't make that clear. My question is how do I change that to get one column but ordered... if I leave out keyby I get one column but it is not unordered. – Martyn Apr 29 '16 at 14:35

2 Answers2

2

The OP didn't specify in which particular order he wants the company names to show up in the select. The answers given by Symbolix are perfect if you want the company names ordered alphabetically (no need to use keyby = for this purpose).

You can also order the company names by the number of rows (most important first)

salesdt[, .N, by = company][order(-N), company]
[1] "W" "T" "A" "S" "L"

or by total sales volume (again, most important first)

salesdt[, sum(sales), by = company][order(-V1), company]
[1] "W" "T" "A" "L" "S"

In both cases, this is done in two steps: (1) Compute the metric (either count or sum) per company using by =, (2) order the result as desired but return only the company names.

Uwe
  • 41,420
  • 11
  • 90
  • 134
1

Replace

choices = salesdt[,unique(company), keyby=company]

With

choices = sort(salesdt[,unique(company)])

Or

choices = sort(unique(salesdt$company))

Or 

salesdt[order(company), unique(company)]
SymbolixAU
  • 25,502
  • 4
  • 67
  • 139
  • Thanks..... three was the answer I was looking for in this case – Martyn May 01 '16 at 07:09
  • Inspired by your answers, I wondered what the fastest of the three alternatives would be with a larger `data.table`. You might find the results interesting http://stackoverflow.com/questions/36953026/what-is-the-fastest-way-to-get-a-vector-of-sorted-unique-values-from-a-data-tabl – Uwe May 01 '16 at 07:20
  • @Martyn - take a look at Uwe's benchmarks at his linked question - might be of use if speed is a concern. – SymbolixAU May 01 '16 at 07:38
  • @Martyn A comment suggested to use `salesdt[,logical(1), keyby=company]$company`. So I repeated my benchmark and it turned out that this is the fastest solution. – Uwe May 01 '16 at 14:14
  • Thanks all.... brilliant responses – Martyn May 09 '16 at 08:54