Group by with null

I answered this already, but some reason the answer was converted to a comment. Nevertheless, this is the most efficient solution:. Not being able to include and propagate NaNs in groups is quite aggravating. Citing R is not convincing, as this behavior is not consistent with a lot of other things.

Anyway, the dummy hack is also pretty bad. When these differ, you can set the value back to None for the result of the aggregation function for that group. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years, 4 months ago. Active 4 months ago. Viewed k times. I want to include these rows! Any suggestions? Should I write a function for this or is there a simple solution?

Improve this question. PhillipCloud I've edited this question to include just the question, which is actual quite good, relating to open pandas enhancement of Jeff's. There not being able to include and propagate NaNs in groups is quite aggravating. Can you summarize what you are specifically trying to achieve?

With pandas 1. More info — cs There are a handful of open issues mentioning this on their github and not a lot of momentum on fixing it yet unfortunately. Add a comment. Active Oldest Votes. Improve this answer. Brendan 1, 1 1 gold badge 14 14 silver badges 21 21 bronze badges. Hopefully this answer makes a gradual march up to the top. It's the correct approach. It doesn't work to me. Cignitor please run print pd. This fails with MultiIndex grouping unfortunately.

The most straightforward workaround I've seen so far, albeit ugly, appears to be replacing the NaN value before grouping. Both CUBE and the grouping set produce a grand total row and duplicate grouping sets are not eliminated. This example uses the backwards compatible syntax. It generates 2 13 grouping sets and will fail. This limit originates from the limit of 8, bytes on the intermediate worktable that is needed to hold intermediate query results. ColumnN , which are semantically equivalent.

ColumnN , which are not semantically equivalent. Grouping sets are not allowed inside grouping sets. An, C1, C2, ALL is the default and is implicit. It is also only allowed in the backwards compatible syntax. This example uses AdventureWorks. The following example retrieves the number of employees for each City from the Address table joined to the EmployeeAddress table.

The following example finds the total amount for all sales on each day. One row containing the sum of all sales is returned for each day. If, within each OrderDateKey group, there are subgroups that can be differentiated by DueDateKey , a new grouping will be defined for the result set.

Only those groups with order dates in or later will be included in the results. Skip to main content. This browser is no longer supported. Show 1 more comment. Active Oldest Votes. Improve this answer. Thank you this worked! Thanks for introducing the UUID function to me. If you use primary key, it may have the same value as the ancestor value and cause errors — Remade. Add a comment. If you have a unique column X then this is easy. Lightness Races in Orbit Lightness Races in Orbit k 70 70 gold badges silver badges bronze badges.

Hett Hett 2, 2 2 gold badges 29 29 silver badges 45 45 bronze badges. Simple, and straight to the point, this answer should be ranked up : — jmserra. This is quite an edge case but be aware of it. I like the concise nature of this response — Luke Madhanga. Stepan Stepan 1, 2 2 gold badges 13 13 silver badges 11 11 bronze badges. Sreejith N Sreejith N 5 5 5 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.