can we add one more column "filestatus" with add logic on status column using pandas

user_11252981 0
Viewed 37

Here is my python code:

In the below output samplefile1 has passed in all the 3 rows so newcolumn {"filestatus" : "passed"} for "InputFile":"samplefile1"

For example it has one pass and one fail so newcolumn {"filestatus":"failed"} for all the {"inputfile":"example"}

import json

df = pd.DataFrame([
            ['samlefile1','user1@gmail.com', 'xyz' ,'pass'],
            ['samlefile1','user5@gmail.com', 'xyz' ,'pass'],
            ['samlefile1','user6@gmail.com', 'xyz' ,'pass'],
            ['testfile','user2@gmail.com', 'abc' ,'pass'],
            ['example','user3@gmail.com', 'xyz' ,'pass'],
            ['example','user3@gmail.com', 'xyz' ,'fail']],columns = ['InputFile','UserId', 'UserGroup' ,'status']
    )
#Count no of status per filename
Input_status_count = df.groupby(['InputFile']).agg(success_count=('status', lambda x: x[x == 'pass'].count()),
                                                           fail_count=('status', lambda x: x[x == 'fail'].count()))
        
#Merge the 2 files on 'InputFile' column
FinalDF = pd.merge(df, Input_status_count,on = "InputFile" )
JSON_String = FinalDF.to_json(orient='records')
JSON_String


output:
[    
{"InputFile":"samlefile1","UserId":"user1@gmail.com","UserGroup":"xyz","status":"pass","success_count":3,"fail_count":0, "filestatus":"passed"},
    {"InputFile":"samlefile1","UserId":"user5@gmail.com","UserGroup":"xyz","status":"pass","success_count":3,"fail_count":0, "filestaus":"passed"},
    {"InputFile":"samlefile1","UserId":"user6@gmail.com","UserGroup":"xyz","status":"pass","success_count":3,"fail_count":0, "filestatus":"passed"},
    {"InputFile":"testfile","UserId":"user2@gmail.com","UserGroup":"abc","status":"","success_count":1,"fail_count":0, "filestatus":"not ran"},
    {"InputFile":"example","UserId":"user3@gmail.com","UserGroup":"xyz","status":"pass","success_count":1,"fail_count":1,"filestatus":"failed"},
    {"InputFile":"example","UserId":"user3@gmail.com","UserGroup":"xyz","status":"fail","success_count":1,"fail_count":1, "filestatus": "failed"}
]
pandas python
2 Answers

Yes you can do it like this:

import numpy as np

#Count no of status per filename
Input_status_count = df.groupby(['InputFile']).agg([('filestatus', lambda x: 'passed' if x[x['status'] == 'fail'].count() == 0 else 'failed')])

Lambda function basically checks if we get a zero row count with status as fail, then the filestatus will be passed otherwise failed.

user_53822720

You can tabulate the pass and fail using pd.crosstab (instead of the complicated agg) and the status column is passed when there is no fail:

Input_status_count = pd.crosstab(df['InputFile'],df['status']).reset_index()
Input_status_count['filestatus'] = ["passed" if i==0 else "failed" for i in Input_status_count['fail']]

Input_status_count

status   InputFile  fail  pass filestatus
0          example     1     1     failed
1       samlefile1     0     3     passed
2         testfile     0     1     passed
user_122584590
Related