6

How to export result in excel file? I tried below script but its not giving meproper output. In the case where there is no dependent label present in predicted column and the class which is present in test data set it is not showing it in the output.

Is there any other way to achieve this. I want to present model result in the excel format.

import pandas as pd
expected = y_test
y_actu = pd.Series(expected, name='Actual')
y_pred = pd.Series(predicted, name='Predicted')
df_confusion = pd.crosstab(y_actu, y_pred,y_test.unique())

df_confusion


df_confusion.to_csv('SVM_Confusion_Matrix.csv')

from pandas import ExcelWriter
writer = ExcelWriter('SVM_Confusion_Matrix.xlsx')
df_confusion.to_excel(writer,'Sheet1')
writer.save()
niranjan
  • 269
  • 3
  • 5
  • 13
  • This is a very detailed post with some great answers on the topic: http://stackoverflow.com/questions/13437727/python-write-to-excel-spreadsheet – winternet Aug 18 '16 at 07:15

2 Answers2

0

You can use following codes for classification report:

  1. classification_report = classification_report(y_actu, y_pred, output_dict=True)

  2. df = pandas.DataFrame(classification_report ).transpose()

  3. df.to_excel('classification_report.xlsx')

Asad Asadzade
  • 36
  • 2
0

The function will create a data frame of confusion matrix with embedded Recall and precision scores. The data frame can then be easily exported to excel. Works with any number of categories

def confusion_max_df(actual, prediction, labels):
    """
    Input: A list of actual values, prediction values and labels
    returns: A data frame of confusion matrix embedded with precision and recall
    """
    
    # confusion matrix from sklearn.metrix library
    cnf_matrix = confusion_matrix(actual, prediction,labels=labels)
    
    # calculatimg recall and precision at category label
    tp_and_fn = cnf_matrix.sum(1)
    tp_and_fp = cnf_matrix.sum(0)
    tp = cnf_matrix.diagonal()
    precision = [str(round(num, 2)*100) +'%' for num in list(tp / tp_and_fp)]
    recall = [str(round(num, 2)*100)+'%' for num in list(tp / tp_and_fn)]
    
    # creating dataframe for exporting to excel
    cnf_matrix_df = pd.DataFrame(cnf_matrix, columns=labels)
    cnf_matrix_df = cnf_matrix_df.add_prefix('Predicted - ')
    actual_list = ['Actual - ' + str(x)  for x in labels]
    cnf_matrix_df['Confusion matrix'] = actual_list
    cnf_matrix_df = cnf_matrix_df.set_index('Confusion matrix')
    cnf_matrix_df['Recall'] = recall
    
    # adding a row in the dataframe for precision scores
    precision_row = ['Precision']
    precision_row.extend(precision)
    precision_row.append('')
    
    cnf_matrix_df.loc['Precision'] = precision_row[1:]
    
    return cnf_matrix_df

confusion_max_df(['Cat A','Cat A','Cat B','Cat B','Cat A','Cat B'],['Cat A','Cat A','Cat B','Cat B','Cat A','Cat A'],['Cat A','Cat B'])
gunjit bedi
  • 62
  • 9