0

I have a code below that creates a summary table of missing values in each column of my data frame. I wish I could build a similar table to count unique values, but DataFrame does not have an unique() method, only each column independently.

def missing_values_table(df): 
    mis_val = df.isnull().sum()
    mis_val_percent = 100 * df.isnull().sum()/len(df)
    mis_val_table = pd.concat([mis_val, mis_val_percent], axis=1)
    mis_val_table_ren_columns = mis_val_table.rename(
    columns = {0 : 'Missing Values', 1 : '% of Total Values'})
    return mis_val_table_ren_columns

(source: https://stackoverflow.com/a/39734251/7044473)

How can I accomplish the same for unique values?

Will Razen
  • 307
  • 1
  • 11

3 Answers3

3

You can use function called 'nunique()' to get unique count of all columns

df = pd.DataFrame(np.random.randint(0, 3, (4, 3)))
print(df)
   0  1  2
0  2  0  2
1  1  2  1
2  1  2  2
3  1  1  2

count=df.nunique()
print(count)
0    2
1    3
2    2
dtype: int64
Mukta Sathe
  • 31
  • 4
1

You can create a series of unique value counts using the pd.unique function. For example:

>>> df = pd.DataFrame(np.random.randint(0, 3, (4, 3)))
>>> print(df)
   0  1  2
0  2  0  2
1  1  2  1
2  1  2  2
3  1  1  2

>>> pd.Series({col: len(pd.unique(df[col])) for col in df})
0    2
1    3
2    2
dtype: int64

If you actually want the number of times each value appears in each column, you can do a similar thing with pd.value_counts:

>>> pd.DataFrame({col: pd.value_counts(df[col]) for col in df}).fillna(0)
     0  1    2
0  0.0  1  0.0
1  3.0  1  1.0
2  1.0  2  3.0
jakevdp
  • 77,104
  • 11
  • 125
  • 160
1

This is not exactly what you asked for, but may be useful for your analysis.

def diversity_percentage(df, columns):
    """
    This function returns the number of different elements in each column as a percentage of the total elements in the group.
    A low value indicates there are many repeated elements.
    Example 1: a value of 0 indicates all values are the same.
    Example 2: a value of 100 indicates all values are different.
    """
    diversity = dict()

    for col in columns:
        diversity[col] = len(df[col].unique())

    diversity_series = pd.Series(diversity)
    return (100*diversity_series/len(df)).sort_values()

__ 

>>> diversity_percentage(df, selected_columns)
operationdate                0.002803
payment                      1.076414
description                 16.933901
customer_id                 17.536581
customer_name               48.895554
customer_email              62.129282
token                       68.290632
id                         100.000000
transactionid              100.000000
dtype: float64

However, you can always return diversity_series directly and will obtain just the count.

richar8086
  • 177
  • 4
  • 13