Pandas count distinct equivalent

Question

I am using pandas as a db substitute as I have multiple databases  oracle  mssql  etc  and I am unable to make a sequence of commands to a SQL equivalent   I have a table loaded in a DataFrame with some columns   YEARMONTH  CLIENTCODE  SIZE       etc etc   In SQL  to count the amount of different clients per year would be   SELECT count distinct CLIENTCODE  FROM table GROUP BY YEARMONTH    And the result would be   201301    5000 201302    13245   How can I do that in pandas

User · Accepted Answer

I believe this is what you want   table groupby  YEARMONTH   CLIENTCODE nunique     Example   In  2   table Out 2       CLIENTCODE  YEARMONTH 0           1     201301 1           1     201301 2           2     201301 3           1     201302 4           2     201302 5           2     201302 6           3     201302  In  3   table groupby  YEARMONTH   CLIENTCODE nunique   Out 3    YEARMONTH 201301       2 201302       3

User · Answer

Distinct of column along with aggregations on other columns  To get the distinct number of values for any column  CLIENTCODE in your case   we can use nunique  We can pass the input as a dictionary in agg function  along with aggregations on other columns   grp df   df groupby  YEARMONTH   agg   CLIENTCODE     nunique                                           other col 1     sum    count        to flatten the multi-level columns grp df columns        join col  strip   for col in grp df columns values     if you wish to reset the index grp df reset index inplace True

User · Answer

I am also using nunique but it will be very helpful if you have to use an aggregate function like  min    max    count  or  mean  etc    df groupby  YEARMONTH    CLIENTCODE   transform  nunique    count distinct  df groupby  YEARMONTH    CLIENTCODE   transform  min        min df groupby  YEARMONTH    CLIENTCODE   transform  max        max df groupby  YEARMONTH    CLIENTCODE   transform  mean       average df groupby  YEARMONTH    CLIENTCODE   transform  count      count

User · Answer

Interestingly enough  very often len unique    is a few times  3x-15x  faster than nunique

User · Answer

Here is another method  much simple  lets say your dataframe name is daat and column name is YEARMONTH  daat YEARMONTH value counts

User · Answer

Using crosstab  this will return more information than groupby nunique   pd crosstab df YEARMONTH df CLIENTCODE  Out 196    CLIENTCODE  1  2  3 YEARMONTH           201301      2  1  0 201302      1  2  1   After a little bit modify  yield the result   pd crosstab df YEARMONTH df CLIENTCODE  ne 0  sum 1  Out 197    YEARMONTH 201301    2 201302    3 dtype  int64

User · Answer

Here an approach to have count distinct over multiple columns  Let s have some data   data     CLIENT CODE   1 1 2 1 2 2 3            YEAR MONTH   201301 201301 201301 201302 201302 201302 201302            PRODUCT CODE    100 150 220 400 50 80 100           table   pd DataFrame data  table  CLIENT CODE YEAR MONTH  PRODUCT CODE 0   1       201301      100 1   1       201301      150 2   2       201301      220 3   1       201302      400 4   2       201302      50 5   2       201302      80 6   3       201302      100   Now  list the columns of interest and use groupby in a slightly modified syntax   columns     YEAR MONTH    PRODUCT CODE   table columns  groupby table  CLIENT CODE    nunique     We obtain   YEAR MONTH  PRODUCT CODE CLIENT CODE         1           2            3 2           2            3 3           1            1

User · Answer

With new pandas version  it is easy to get as dataframe  unique count   pd groupby   YEARMONTH    as index False  agg uniq CLIENTCODE    CLIENTCODE  pd Series count

[python] Pandas count(distinct) equivalent

Examples related to python

Examples related to pandas

Examples related to count

Examples related to group-by

Examples related to distinct