I’m working in travel industry as a software architect / project lead on the precisely kind of project you describe – in our region we work with suppliers directly, but for outgoing we connect to several aggregators. To answer your question… some data you have, some you get in various ways, and some you have … Read more
Method #1: use idxmin() to get the indices of the elements of minimum diff, and then select those: >>> df.loc[df.groupby(“item”)[“diff”].idxmin()] item diff otherstuff 1 1 1 2 6 2 -6 2 7 3 0 0 [3 rows x 3 columns] Method #2: sort by diff, and then take the first element in each item group: … Read more
Perhaps not super efficient, but one way would be to create a function yourself: def percentile(n): def percentile_(x): return np.percentile(x, n) percentile_.__name__ = ‘percentile_%s’ % n return percentile_ Then include this in your agg: In [11]: column.agg([np.sum, np.mean, np.std, np.median, np.var, np.min, np.max, percentile(50), percentile(95)]) Out[11]: sum mean std median var amin amax percentile_50 percentile_95 … Read more
Here are some options using toString, a function that concatenates a vector of strings using comma and space to separate components. If you don’t want commas, you can use paste() with the collapse argument instead. data.table # alternative using data.table library(data.table) as.data.table(data)[, toString(C), by = list(A, B)] aggregate This uses no packages: # alternative using … Read more
MySQL SELECT FieldA , GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ‘,’) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA; Oracle & DB2 SELECT FieldA , LISTAGG(FieldB, ‘,’) WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA; PostgreSQL SELECT FieldA , STRING_AGG(FieldB, ‘,’ ORDER BY FieldB) AS FieldBs FROM … Read more
You can do it all in one step and get proper labeling: > aggregate(. ~ id1+id2, data = x, FUN = function(x) c(mn = mean(x), n = length(x) ) ) # id1 id2 val1.mn val1.n val2.mn val2.n # 1 a x 1.5 2.0 6.5 2.0 # 2 b x 2.0 2.0 8.0 2.0 # 3 … Read more
Slightly more elegant: library(data.table) DT[ , .SD[which.min(Employees)], by = State] State Company Employees 1: AK D 24 2: RI E 19 Slighly less elegant than using .SD, but a bit faster (for data with many groups): DT[DT[ , .I[which.min(Employees)], by = State]$V1] Also, just replace the expression which.min(Employees) with Employees == min(Employees), if your data … Read more
You can select the columns of a groupby: In [11]: df.groupby([‘Country’, ‘Item_Code’])[[“Y1961”, “Y1962”, “Y1963”]].sum() Out[11]: Y1961 Y1962 Y1963 Country Item_Code Afghanistan 15 10 20 30 25 10 20 30 Angola 15 30 40 50 25 30 40 50 Note that the list passed must be a subset of the columns otherwise you’ll see a KeyError.
How about either of: >>> df date duration user_id 0 2013-04-01 30 0001 1 2013-04-01 15 0001 2 2013-04-01 20 0002 3 2013-04-02 15 0002 4 2013-04-02 30 0002 >>> df.groupby(“date”).agg({“duration”: np.sum, “user_id”: pd.Series.nunique}) duration user_id date 2013-04-01 65 2 2013-04-02 45 1 >>> df.groupby(“date”).agg({“duration”: np.sum, “user_id”: lambda x: x.nunique()}) duration user_id date 2013-04-01 65 … Read more
Current best practice (tidyverse) is: require(dplyr) df1 %>% count(Year, Month)