CODEWITHSUNDEEP
pythonpandasdataframe
harp1814
harp1814

Reputation: 1658

Pandas: add percentage column

There is pandas DataFrame as:

print(df)

call_id   calling_number   call_status
1          123             BUSY
2          456             BUSY
3          789             BUSY
4          123             NO_ANSWERED
5          456             NO_ANSWERED
6          789             NO_ANSWERED

In this case, records with different call_status, (say "ERROR" or something else, what i can't predict), values may appear in the dataframe. I need to add a new column on the fly for such a value. I have applied the pivot_table() function and I get the result I want:

df1 = df.pivot_table(df,index='calling_number',columns='status_code', aggfunc = 'count').fillna(0).astype('int64')

calling_number    ANSWERED  BUSY   NO_ANSWER  
123               0          1      1
456               0          1      1
789               0          1      1

Now I need to add one more column that would contain the percentage of answered calls with the given calling_number, calculated as the ratio of ANSWERED to the total. Source dataframe 'df' may not contain entries with call_status = 'ANSWERED', so in that case the percentage column should naturally has zero value.

Expected result is :

calling_number    ANSWERED  BUSY   NO_ANSWER  ANS_PERC(%)
    123               0          1      1      0
    456               0          1      1      0
    789               0          1      1      0

Upvotes: 1

Views: 840

Answers (2)

jezrael
jezrael

Reputation: 862791

Use crosstab:

df1 = pd.crosstab(df['calling_number'], df['status_code'])

Or if need exclude NaNs by count function use pivot_table with added parameter fill_value=0:

df1 = df.pivot_table(df,
               index='calling_number',
               columns='status_code', 
               aggfunc = 'count', 
               fill_value=0)

Then for ratio divide summed values per rows:

df1 = df1.div(df1.sum(axis=1), axis=0)
print (df1)
                ANSWERED      BUSY  NO_ANSWER
calling_number                               
123             0.333333  0.333333   0.333333
456             0.333333  0.333333   0.333333
789             0.333333  0.333333   0.333333

EDIT: For add possible non exist some categories use DataFrame.reindex:

df1 = (pd.crosstab(df['calling_number'], df['call_status'])
         .reindex(columns=['ANSWERED','BUSY','NO_ANSWERED'], fill_value=0))

df1['ANS_PERC(%)'] = df1['ANSWERED'].div(df1['ANSWERED'].sum()).fillna(0)
print (df1)
call_status     ANSWERED  BUSY  NO_ANSWERED  ANS_PERC(%)
calling_number                                          
123                    0     1            1          0.0
456                    0     1            1          0.0
789                    0     1            1          0.0

If need total per rows:

df1['ANS_PERC(%)'] = df1['ANSWERED'].div(df1.sum(axis=1))
print (df1)
call_status     ANSWERED  BUSY  NO_ANSWERED  ANS_PERC(%)
calling_number                                          
123                    0     1            1          0.0
456                    0     1            1          0.0
789                    0     1            1          0.0

EDIT1:

Soluton with replace some wrong values to ERROR:

print (df)
   call_id  calling_number  call_status
0        1             123          ttt
1        2             456         BUSY
2        3             789         BUSY
3        4             123  NO_ANSWERED
4        5             456  NO_ANSWERED
5        6             789  NO_ANSWERED

L = ['ANSWERED', 'BUSY', 'NO_ANSWERED']
df['call_status'] = df['call_status'].where(df['call_status'].isin(L), 'ERROR')
print (df)
0        1             123        ERROR
1        2             456         BUSY
2        3             789         BUSY
3        4             123  NO_ANSWERED
4        5             456  NO_ANSWERED
5        6             789  NO_ANSWERED
df1 = (pd.crosstab(df['calling_number'], df['call_status'])
         .reindex(columns=L + ['ERROR'], fill_value=0))

df1['ANS_PERC(%)'] = df1['ANSWERED'].div(df1.sum(axis=1))
print (df1)
call_status     ANSWERED  BUSY  NO_ANSWERED  ERROR  ANS_PERC(%)
calling_number                                                 
123                    0     0            1      1          0.0
456                    0     1            1      0          0.0
789                    0     1            1      0          0.0

Upvotes: 1

supercooler8
supercooler8

Reputation: 503

I like the cross_tab idea but I am a fan of column manipulation so that it's easy to refer back to:

    # define a function to capture all the other call_statuses into one bucket 
def tester(x):
    if x not in ['ANSWERED', 'BUSY', 'NO_ANSWERED']:
        return 'OTHER' 
    else:
        return x
    
#capture the simplified status in a new column
df['refined_status'] = df['call_status'].apply(tester)


#Do the pivot (or cross tab) to capture the sums:
df1= df.pivot_table(values="call_id", index = 'calling_number', columns='refined_status', aggfunc='count')

#Apply a division to get the percentages:
df1["TOTAL"] = df1[['ANSWERED', 'BUSY', 'NO_ANSWERED', 'OTHER']].sum(axis=1)
df1["ANS_PERC"] = df1["ANSWERED"]/df1.TOTAL * 100

print(df1)

Upvotes: 0

Related Questions