Home > Net >  Pandas fill dataframe with count of values within a range from another dataframe
Pandas fill dataframe with count of values within a range from another dataframe

Time:06-21

I currently have two dataframes, df_ages and df_count:

In  [1]: df_ages
Out [1]: 
        Enrolled         Age
    1          Y          44    
    2          Y          35    
    3          N          37        
    4          Y          55    
    5          N          26    
    6          Y          19    
    7          N          18    
    8          N          49        
    9          Y          26    
    10         Y          25
    11         Y          25    
    12         Y          32    
    13         Y          25        
    14         N          50    
    15         N          58        

In  [2]: df_count
Out [2]: 
             Min         Max    counts     percentage
    1         18          25        
    2         26          35        
    3         36          45        
    4         46          55        
    5         56          65        

I am looking for code to populate df_count [count] column with the sum of people who fit within the min and max age range in the previous columns.

The [percentage] column should be the percentage of number of entries.

The desired resulting output is shown below:

In  [2]: df_count
Out [2]: 
             Min         Max    counts    percentage
    1         18          25         5          33.3
    2         26          35         4          26.7
    3         36          45         2          13.3
    4         46          55         3          20.0
    5         56          65         1           6.7

CodePudding user response:

You can try apply on rows with Series.between

df_count['counts'] = df_count.apply(lambda row: df_ages['Age'].between(row['Min'], row['Max']).sum(), axis=1)
df_count['percentage'] = df_count['counts'].div(len(df_ages)).mul(100).round(1)
print(df_count)

   Min  Max  counts  percentage
0   18   25       5        33.3
1   26   35       4        26.7
2   36   45       2        13.3
3   46   55       3        20.0
4   56   65       1         6.7
  • Related