0

I am attempting to create a pivot table in R.

I have code like so:

 Grps <- c("MoveNumber","TripOrderNumber","BillToParentAssignment","AccountOwner","MonthYear",
           "Shipper_Latitude","Shipper_Longitude","Shipper.CompanyAddress",
          "ShipCity_New","ShipperState","ShipperZip","ShipCntry1","ShipCntry2",
           "Consignee_Latitude","Consignee_Longitude","Consignee.CompanyAddress",
           "ConsCity_New","ConsigneeState","ConsigneeZip","ConsCntry1","ConsCntry2",
           "InvoiceCurrency","CommodityDesc","One.TrailerAxles","Two.TrailerAxles","Trailer_tsID",
           "TariffNumber","DateBilled","IndexEndDate")

Start2 <- Start2 %>% group_by_at(Grps) %>%
   summarise(
     TM = sum(Travel.Miles),
     LM = sum(LoadedMiles),
     EM = sum(EmptyMiles),
     LH = sum(LinehaulRev),
     FSC = sum(FuelRev),
     Terminal = mode(Terminal),
     AccessRev = sum(AccessorialRev),
     StopCount = sum(StopCount),
     Pickups = sum(Pickups),
     Deliveries = sum(Delivery)
   )

The pivot works fine except for Terminal. What I am looking for is the most frequently appearing value in a column of characters. Instead, I get "character" in every row.

Would be grateful for a solution.

jyr
  • 690
  • 6
  • 20
dkent
  • 151
  • 1
  • 7
  • R doesn't have a built-in function to calculate the mode. The [`mode`](https://stat.ethz.ch/R-manual/R-devel/library/base/html/mode.html) function you're using refers to the storage mode of an object. See [this SO question about mode](https://stackoverflow.com/questions/2547402/is-there-a-built-in-function-for-finding-the-mode) – bouncyball Feb 04 '20 at 21:22

1 Answers1

0

You can define your own 'mode' function, something like this:

modeCustom <- function(vals) {
  uniqvals <- unique(vals)
  uniqvals[which.max(tabulate(match(vals, uniqvals)))]
}

Then just use 'modeCustom' instead of 'mode' inside your 'summarise' statement.

MH765
  • 390
  • 3
  • 11