Rilcon42 Rilcon42 - 2 months ago 15
R Question

Combine dataframe based lowest and highest values of a subset

I am trying to combine a dataframe to get the item name, cheapest buy region, and highest sell region from one dataframe. I attempted to do this using merge, in one step, but kept getting errors. Anyone have another suggestion?

GOAL

item.name id buy.price buy.region sell.price sell.region
Isogen 37 82.02 Amarr 434.37 Jita
Nocxium 38 395.00 Amarr 449.27 Jita
....


DATA

> eve.data[150:160,]
buy.sell item.id region price date item.name
76 s 37 Amarr 99.94956 2016-10-05 22:30:42 Isogen
1077 b 37 Jita 83.08134 2016-10-05 23:05:27 Isogen
1078 b 37 Amarr 82.02000 2016-10-05 22:30:42 Isogen
77 s 38 Jita 434.37451 2016-10-05 23:05:27 Nocxium
78 s 38 Amarr 449.24791 2016-10-05 22:30:42 Nocxium
1079 b 38 Jita 421.00000 2016-10-05 23:05:27 Nocxium
1080 b 38 Amarr 395.00000 2016-10-05 22:30:42 Nocxium
79 s 39 Jita 1036.06204 2016-10-05 23:05:27 Zydrine
80 s 39 Amarr 1389.18975 2016-10-05 22:30:42 Zydrine
1081 b 39 Jita 1009.97722 2016-10-05 23:05:27 Zydrine
1082 b 39 Amarr 1063.52062 2016-10-05 22:30:42 Zydrine


MY CODE:

x<-paste0(0:500,collapse=",")
eve.url<-paste0("http://eve-marketdata.com/api/item_prices2.txt?char_name=demo&type_ids=",x,"&region_ids=10000002,10000033&buysell=a")
eve.data<-read.table(url(eve.url),sep="\t",col.names=c("buy.sell","item.id","region","price","date"),stringsAsFactors=F)
eve.data$region[eve.data$region==10000002]<-"Jita"
eve.data$region[eve.data$region==10000033]<-"Amarr"
eve.data$item.name <- item.ids[match(eve.data$item.id, item.ids$typeID),2]
#doesnt work
group_by(eve.data$buy.sell)%>%mutate(low.buy.price=XXX,low.buy.region=XXX, high.sell.price=XXX,high.sell.region=XXX)

Answer

It seems I couldn't get the same data from web. many price is 0 in the table I downloaded.

however, the following code could get all the information in one step:

x <- eve.data %>% group_by(item.id) %>%
    summarise(
        low.buy.price = min(price[buy.sell == 'b']),
        low.buy.region = paste(region[buy.sell == 'b' & price == low.buy.price], collapse = ','),
        high.sell.price = max(price[buy.sell == 's']),
        high.sell.region = paste(region[buy.sell == 's' & price == high.sell.price], collapse = ',')
    )