How to compute occurrencies of specific value and its percentage for each column based on condition-CodePudding

I have the following dataframe df, in which I highlighted in green the cells with values of interest: enter image description here and I would like to obtain for each columns (therefore by considering the whole dataframe) the following statistics: the occurrence of a value less or equal to 0.5 (green cells in the dataframe) -Nan values are not to be included- and its percentage in the considered columns in order to use say 50% as benchmark.

For the point asked I tried with value_count like (df['A'].value_counts()/df['A'].count())*100, but this returns the partial result not the way I would and only for specific columns; I was also thinking about using filter or lamba function like df.loc[lambda x: x <= 0.5] but cleary that is not the result I wanted.

The goal/output will be a dataframe as shown below in which are displayed just the columns that "beat" the benchmark (recall: at least (half) 50% of their values <= 0.5).

enter image description here e.g. in column A the count would be 2 and the percentage: 2/3 * 100 = 66%, while in column B the count would be 4 and the percentage: 4/8 * 100 = 50%. (The same goes for columns X, Y and Z). On the other hand in column C where 2/8 *100 = 25% won't beat the benchmark and therefore not considered in the output.

Is there a suitable way to achieve this IYHO? Apologies in advance if this was a kinda duplicated question but I found no other questions able to help me out, and thx to any saviour.

CodePudding user response：

I believe I have understood your ask in the below code... It would be good if you could provide an expected output in your question so that it is easier to follow.

Anyways the first part of the code below is just set up so can be ignored as you already have your data set up. Basically I have created a quick function for you that will return the percentage of values that are under a threshold that you can define. This function is called in a loop of all the columns within your dataframe and if this percentage is more than the output threshold (again you can define it) it will keep it for actually outputting.

import pandas as pd
import numpy as np
import random
import datetime

### SET UP ###

base = datetime.datetime.today()
date_list = [base - datetime.timedelta(days=x) for x in range(10)]

def rand_num_list(length):
    peak = [round(random.uniform(0,1),1) for i in range(length)]   [0] * (10-length)
    random.shuffle(peak)
    return peak


df = pd.DataFrame(
    {
        'A':rand_num_list(3),
        'B':rand_num_list(5),
        'C':rand_num_list(7),
        'D':rand_num_list(2),
        'E':rand_num_list(6),
        'F':rand_num_list(4)
    },
    index=date_list
)

df = df.replace({0:np.nan})

##############

print(df)

def less_than_threshold(thresh_df, thresh_col, threshold):
    if len(thresh_df[thresh_col].dropna()) == 0:
        return 0

    return len(thresh_df.loc[thresh_df[thresh_col]<=threshold]) / len(thresh_df[thresh_col].dropna())

output_dict = {'cols':[]}
col_threshold = 0.5
output_threshold = 0.5
for col in df.columns:
    if less_than_threshold(df, col, col_threshold) >= output_threshold:
        output_dict['cols'].append(col)
    
df_output = df.loc[:,output_dict.get('cols')]

print(df_output)

Hope this achieves your goal!