Option 1 def first_last(df): return df.ix[[0, -1]] df.groupby(level=0, group_keys=False).apply(first_last) Option 2 – only works if index is unique idx = df.index.to_series().groupby(level=0).agg([‘first’, ‘last’]).stack() df.loc[idx] Option 3 – per notes below, this only makes sense when there are no NAs I also abused the agg function. The code below works, but is far uglier. df.reset_index(1).groupby(level=0).agg([‘first’, ‘last’]).stack() \ … Read more
In order to avoid such error you could use CASE + ISNUMERIC to handle scenarios when you cannot convert to int. Change CONVERT(INT, CONVERT(VARCHAR(12), a.value)) To CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value) ELSE 0 END) Basically this is saying if you cannot convert me to int assign value of 0 (in my … Read more
You could group by both the bins and username, compute the group sizes and then use unstack(): >>> groups = df.groupby([‘username’, pd.cut(df.views, bins)]) >>> groups.size().unstack() views (1, 10] (10, 25] (25, 50] (50, 100] username jane 1 1 1 1 john 1 1 1 1
One way would be to use a nested query: SELECT count(*) FROM ( SELECT COUNT(Genre) AS count FROM movies GROUP BY ID HAVING (count = 4) ) AS x The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.
In mysql, use SELECT id, GROUP_CONCAT(data) FROM yourtable GROUP BY id or use your custom separator: SELECT id, GROUP_CONCAT(data SEPARATOR ‘, ‘) FROM yourtable GROUP BY id see GROUP_CONCAT.
The rough equivalent is .reset_index(), but it may not be helpful to think of it as the “opposite” of groupby(). You are splitting a string in to pieces, and maintaining each piece’s association with ‘family’. This old answer of mine does the job. Just set ‘family’ as the index column first, refer to the link … Read more
Found answer on MSDN: Yes. The IGrouping<TKey, TElement> objects are yielded in an order based on the order of the elements in source that produced the first key of each IGrouping<TKey, TElement>. Elements in a grouping are yielded in the order they appear in source.
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
if you’re using SQL Server, dateadd(DAY,0, datediff(day,0, created)) will return the day created for example, if the sale created on ‘2009-11-02 06:12:55.000’, dateadd(DAY,0, datediff(day,0, created)) return ‘2009-11-02 00:00:00.000’ select sum(amount) as total, dateadd(DAY,0, datediff(day,0, created)) as created from sales group by dateadd(DAY,0, datediff(day,0, created))
Here’s an automated layout with lots of groups (of random fake data) and playing around with grouped.get_group(key) will show you how to do more elegant plots. import pandas as pd from numpy.random import randint import matplotlib.pyplot as plt df = pd.DataFrame(randint(0,10,(200,6)),columns=list(‘abcdef’)) grouped = df.groupby(‘a’) rowlength = grouped.ngroups/2 # fix up if odd number of groups … Read more