How do I access the corresponding groupby dataframe in a groupby object by the key? With the following groupby: rand = np.random.RandomState(1) df = pd.DataFrame({‘A’: [‘foo’, ‘bar’] * 3, ‘B’: rand.randn(6), ‘C’: rand.randint(0, 20, 6)}) gb = df.groupby([‘A’]) I can iterate through it to get the keys and groups: In [11]: for k, gp in … Read more
This question already has answers here: Fetch the row which has the Max value for a column (35 answers) Closed 4 years ago. I’m trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK … Read more
like when I do SELECT [Date] FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY [Date] how can I specify the group period ? MS SQL 2008 2nd Edit I’m trying SELECT MIN([Date]) AS RecT, AVG(Value) FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY (DATEPART(MINUTE, [Date]) / 10) ORDER BY RecT changed %10 to / 10. is it possible to make Date output without … Read more
Suppose I want to calculate the proportion of different values within each group. For example, using the mtcars data, how do I calculate the relative frequency of number of gears by am (automatic/manual) in one go with dplyr? library(dplyr) data(mtcars) mtcars <- tbl_df(mtcars) # count frequency mtcars %>% group_by(am, gear) %>% summarise(n = n()) # … Read more
I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with the same account number. UPDATE: Additionally, there should be a filter than only … Read more
I have a pandas DataFrame like following: df = pd.DataFrame({‘id’ : [1,1,1,2,2,3,3,3,3,4,4,5,6,6,6,7,7], ‘value’ : [“first”,”second”,”second”,”first”, “second”,”first”,”third”,”fourth”, “fifth”,”second”,”fifth”,”first”, “first”,”second”,”third”,”fourth”,”fifth”]}) I want to group this by [“id”,”value”] and get the first row of each group: id value 0 1 first 1 1 second 2 1 second 3 2 first 4 2 second 5 3 first 6 3 … Read more
I have a DataFrame with many missing values in columns which I wish to groupby: import pandas as pd import numpy as np df = pd.DataFrame({‘a’: [‘1’, ‘2’, ‘3’], ‘b’: [‘4’, np.NaN, ‘6’]}) In [4]: df.groupby(‘b’).groups Out[4]: {‘4’: [0], ‘6’: [2]} see that Pandas has dropped the rows with NaN target values. (I want to … Read more
Is it possible to join the results of 2 sql SELECT statements in one statement? I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of days from start to deadline. Age is also an INT number of days.) I want to … Read more
I want to group my dataframe by two columns and then sort the aggregated results within the groups. In [167]: df Out[167]: count job source 0 2 sales A 1 4 sales B 2 6 sales C 3 3 sales D 4 7 sales E 5 5 market A 6 3 market B 7 2 … Read more
Is it possible to GROUP BY more than one column in a MySQL SELECT query? For example: GROUP BY fV.tier_id AND ‘f.form_template_id’ 7 Answers 7
