xzp2050 xzp2050 - 1 year ago 141
Python Question

merge pandas pivot tables

I have a dataframe like this:

Application|Category|Feature|Scenario|Result|Exec_Time
A1|C1|F1|scenario1|PASS|2.3
A1|C1|F1|scenario2|FAIL|20.3
A2|C1|F3|scenario3|PASS|12.3
......


The outcome i am looking for will be a pivot with count of results by Feature and also the sum of exec times. Like this

Application|Category|Feature|Count of PASS|Count of FAIL|SumExec_Time
A1|C1|F1|200|12|45.62
A1|C1|F2|90|0|15.11
A1|C2|F3|97|2|33.11*


I got individual dataframes to get the pivots of result counts and the sum of execution time by feature but I am not able to merge those dataframes to get my final expected outcome.

dfr = pd.pivot_table(df,index=["Application","Category","Feature"],
values=["Final_Result"],aggfunc=[len])

dft = pd.pivot_table(df,index=["Application","Category","Feature"],
values=["Exec_time_mins"],aggfunc=[np.sum])

Answer Source
#Count total records, number of FAILs and total time.
df2 = df.groupby(by=['Application','Category','Feature']).agg({'Result':[len,lambda x: len(x[x=='FAIL'])],'Exec_Time':sum})

#rename columns
df2.columns=['Count of PASS','Count of FAIL','SumExec_Time']

#calculate number of pass
df2['Count of PASS']-=df2['Count of FAIL']

#reset index
df2.reset_index(inplace=True)

df2
Out[1197]: 
  Application Category Feature  Count of PASS  Count of FAIL  SumExec_Time
0          A1       C1      F1              1              1          22.6
1          A2       C1      F3              1              0          12.3
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download