Best way to retrieve many aggregate counts from a dataframe?

pandas count specific value in column
pandas groupby aggregate multiple columns
pandas aggregate count
pandas groupby value counts
pandas groupby sum multiple columns
pandas groupby count unique
pandas aggregate custom function multiple columns
pandas groupby multiple columns

I have a dataframe that I need to retrieve many metrics from. Dataframe columns are the following:

Consumer_ID|Client|Campaign|Date

I am trying to get the unique count of the consumer_ID column for various combinations of the Client, Campaign, and Date columns. So far I have come up with two solutions:

  1. Groupby statements with count as the agg function for every combination of client, campaign, and date.
  2. Writing for loops and filtering on every combination of the client, campaign and date columns and then using the nunique() function to get the final count.

My question: is there a cleaner more Pythonic way of getting the unique count of one column for all available combinations of other columns?

Example (annoying) solution using groupbys: Yes understood, but is there a more pythonic way to get every combination of the groupby columns? For example, right now to get all combinations I'd have to write:

df.groupby(['Client']).Consumer_ID.nunique()
df.groupby(['Client', 'Campaign']).Consumer_ID.nunique()
df.groupby(['Client', 'Campaign', 'Date']).Consumer_ID.nunique()
df.groupby(['Client', 'Date'].Consumer_ID.nunique()

If I understand correctly:

df.groupby(df.columns.drop(Consumer_ID).tolist(), as_index=False).nunique()

Pandas Tutorial 2: Aggregation and Grouping, Check out that post if you want to get up to speed with the basics of Pandas. Exploring your Pandas DataFrame with counts and value_counts . The easiest and most common way to use groupby is by passing one or more� Best way to retrieve many aggregate counts from a dataframe? Hot Network Questions The length extension attack and security on length shortening of a hashed message by one byte

I believe what you're looking for is:

df.groupby(['Client', 'Campaign', 'Date']).Consumer_ID.nunique()

How to Use Pandas GroupBy, Counts and Value Counts, Aggregation and grouping of Dataframes is accomplished in Python Pandas using "groupby()" and "agg()" functions. Apply max, min How many seconds of phone calls are recorded in total? 'network_type': "count", # get the count of networks Using ravel, and a string join, we can create better names for the columns:. If 0 or ‘index’ counts are generated for each column. If 1 or ‘columns’ counts are generated for each row. level int or str, optional. If the axis is a MultiIndex (hierarchical), count along a particular level, collapsing into a DataFrame. A str specifies the level name. numeric_only bool, default False. Include only float, int or

You can use a pivot table, as below:

import pandas as pd pd.pivot_table(df, index=['Client', 'Campaign', 'Date'], values = 'Consumer_ID', aggfunc=pd.Series.nunique)

Pandas Groupby: Summarising, Aggregating, and Grouping data in , agg is called with several functions. Return scalar, Series or DataFrame. The aggregation operations are always performed over an axis, either the: index ( default)� Counting the number of the animals is as easy as applying a count function on the zoo dataframe: zoo.count() Oh, hey, what are all these lines? Actually, the .count() function counts the number of values in each column. In the case of the zoo dataset, there were 3 columns, and each of them had 22 values in it.

Answered my own question. I used itertools combinations to create all possible combinations of columns that are then used to complete all groupby aggregations. Example code below:

from itertools import combinations
cols = df.columns
combinations = [j for i in range(len(cols)) for j in combinations(cols, i+1)]

I can then use the different combinations of columns within the "combinations" list to complete all groupby aggregations without having to write the groupby statement multiple times.

Thanks!

pandas.DataFrame.aggregate — pandas 1.1.0 documentation, We will use dataframe count() function to count the number of Non Null column and use count function to aggregate the data and find out the count but what if you want to get the unique value count for multiple columns? Unpivot a DataFrame from wide to long format, optionally leaving identifiers set. memory_usage ([index, deep]) Return the memory usage of each column in bytes. merge (right[, how, on, left_on, right_on, …]) Merge DataFrame or named Series objects with a database-style join. min ([axis, skipna, level, numeric_only])

How to use Pandas Count and Value_Counts, COUNT() is an example of an aggregate function, which takes many values and returns one. Animal column, while using COUNT() to find out how many ID's we have in each group. Now that our query is ready, let's run it and store the results in a pandas DataFrame: A couple hints to make your queries even better:. Too many things to remember? If you think that AGGREGATE is complicated because there are too many things to remember, don’t worry. When you start typing the function into a cell or in the formula box, a drop-down will appear with the various options. The function_num drop-down list. The options drop-down list

Group By, Having & Count, For instance; many functions are more concisely written in Python. By default, SQL aggregate functions like count() apply to all of the rows in a dataset and The groupby() method can be called directly on a pandas Dataframe object. You can see how this has the potential to get messy if you are computing a large � Listing 1 uses a subset of the mtcars dataset in order to conserve space on the page. You’ll see a more flexible way of transposing data when we look at the reshape package later in this article. Aggregating data. It’s relatively easy to collapse data in R using one or more by variables and a defined function. The format is

“Group By” in SQL and Python: a Comparison, Nested inside this list is a DataFrame containing the results generated by the SQL But how many times does a particular value appear in the same column? Simply counting is often the fastest way to get information about a dataset in aggregate the number of times each value occurred using the .value_counts() method. Groupby can return a dataframe, a series, or a groupby object depending upon how it is used, and the output type issue leads to numerous problems when coders try to combine groupby with other pandas functions. One especially confounding issue occurs if you want to make a dataframe from a groupby object or series.

Comments
  • Could you please post sample input and output data sets (5-7 rows in CSV/dict/JSON/Python code format as text, so one could use it when coding) and describe what do you want to do with the input data in order to get output data set? How to create a Minimal, Complete, and Verifiable example
  • @MaxU Apologies, I can see how it's confusing. Solved my own question and answered it below if you're interested!
  • I will try this, I'm not sure it is grouping by every single combination of columns. Edit to explain this contained in the question above.
  • I've a bit different case but I tried to adopt your solution and now it works just fine for me, thanks.
  • Yes understood, but is there a more pythonic way to get every combination of the groupby columns? For example, right now to get all combinations I'd have to write: df.groupby(['Client']).Consumer_ID.nunique() df.groupby(['Client', 'Campaign']).Consumer_ID.nunique() df.groupby(['Client', 'Campaign', 'Date']).Consumer_ID.nunique() df.groupby(['Client', 'Date'].Consumer_ID.nunique()
  • What kind of Data structure would you store this in? Perhaps you can provide a sample output? My initial thoughts are that you aren't going to do a lot better than 4 lines. If your challenge is scaling this up to n columns, then you could write the groupby inside a combinatorial loop but I'd want to see what you want your output to look like.
  • Apologies, I can see how my question was confusing. I figured out the answer and posted above if interested!