Consolidating Result Summary in new sheet in same excel from different sheets on a existing excel file using python

Question

After each run i am getting a new csv file with test results and I am able to consolidate all excel files into a excel file with each run as a sheet name .

For this i am using xlwt

Code for someone else reference for adding different excel files into a consolidated excel file with :

book = xlwt.Workbook()
    for file in os.listdir(path):
        if file.endswith('csv'):
            sheet = book.add_sheet(file[:-4])
            with open(path + file) as filname:
                reader = csv.reader(filname)
                i = 0
                for row in reader:
                    for j, each in enumerate(row):
                        sheet.write(i, j, each)
                    i += 1

    book.save("consolidate_result.xls")

Now i have a scenario in which i have to provide the summary of different test run in a new summary sheet of a Excel.

Here is my sample Excel file which contain multiple sheets with these data format with firstcolumn as testname second column as test status and third column as time value of that test:

Sheet 1 with Name Run 1

Test Name   Test Status     Time Value
Test 1      PASS            00:06:43
Test 2      Fail            00:06:24
Test 3      PASS            00:06:10
Test 4      PASS            00:05:25
Test 5      Fail            00:05:07
Test 6      PASS            00:02:45

Sheet 2 with Name Run 2

Test Name   Test Status     Time Value
Test 1      PASS            00:05:43
Test 2      Fail            00:04:24
Test 3      PASS            00:05:10
Test 4      PASS            00:06:25
Test 5      PASS            00:03:07
Test 6      PASS            00:04:45

Sheet 3 with Name Run 3

Test Name   Test Status     Time Value
Test 1      PASS            00:06:40
Test 2      PASS            00:06:52
Test 3      PASS            00:05:50
Test 4      PASS            00:05:35
Test 5      PASS            00:06:17
Test 6      PASS            00:03:55

What i want to achieve is get a new sheet with some name say Status or consolidation results in the existing excel file with this format

Test Name   Test-Status        Run 1        Run 2       Run 3
Test 1      Pass               00:06:43     00:05:38    00:06:43
Test 2      Fail               00:06:24    00:05:56     00:06:24
Test 3      Pass               00:06:10    00:06:43     00:06:10
Test 4      Pass               00:05:25    00:05:32     00:05:25
Test 5      Fail               00:05:07    00:05:22     00:05:07
Test 6      Pass               00:02:45    00:07:26     00:02:45

I have tried to add the result into a List by reading the excel file using pd.ExcelFile(filename) and then iterate over the sheets and add the data into the result list

df = pd.read_excel(fname, None)
result=[]
for x in range(len(df.keys())):
    dfx=pd.read_excel(xls, xls.sheet_names[x])
    result.append(dfx)

can someone please help me in consolidating the results into a new sheet as when i am using writer = pd.ExcelWriter(fname, engine='openpyxl') and df.to_excel(writer, sheet_name='Summary') it overwrites the excel and add a blank sheet with name Summary. Thanks in advance

score 0 · Accepted Answer · answered Aug 20 '18 at 06:59

I suggest use sheet_name=None parameter for create Ordered Dictionary of DataFrames by all sheets:

path = "file.xlsx"

df = pd.read_excel(path, sheet_name=None)
print (df)
OrderedDict([('Run 1',   Test Name Test Status Time Value
0    Test 1        PASS   00:06:43
1    Test 2        Fail   00:06:24
2    Test 3        PASS   00:06:10
3    Test 4        PASS   00:05:25
4    Test 5        Fail   00:05:07
5    Test 6        PASS   00:02:45), ('Run 2',   Test Name Test Status Time Value
0    Test 1        PASS   00:05:43
1    Test 2        Fail   00:04:24
2    Test 3        PASS   00:05:10
3    Test 4        PASS   00:06:25
4    Test 5        PASS   00:03:07
5    Test 6        PASS   00:04:45), ('Run 3',   Test Name Test Status Time Value
0    Test 1        PASS   00:06:40
1    Test 2        PASS   00:06:52
2    Test 3        PASS   00:05:50
3    Test 4        PASS   00:05:35
4    Test 5        PASS   00:06:17
5    Test 6        PASS   00:03:55)])

Then loop and concat together with align by columns Test Name and Test Status, so set_index is necessary. Also NaNs are added for not matched values:

d = {k:v.set_index(['Test Name','Test Status'])['Time Value'] for k, v in df.items()}
result= pd.concat(d, axis=1).reset_index()
print (result)
  Test Name Test Status     Run 1     Run 2     Run 3
0    Test 1        PASS  00:06:43  00:05:43  00:06:40
1    Test 2        Fail  00:06:24  00:04:24       NaN
2    Test 2        PASS       NaN       NaN  00:06:52
3    Test 3        PASS  00:06:10  00:05:10  00:05:50
4    Test 4        PASS  00:05:25  00:06:25  00:05:35
5    Test 5        Fail  00:05:07       NaN       NaN
6    Test 5        PASS       NaN  00:03:07  00:06:17
7    Test 6        PASS  00:02:45  00:04:45  00:03:55

Last append to existing file in new sheet:

#https://stackoverflow.com/a/42375263
from openpyxl import load_workbook

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

result.to_excel(writer, sheet_name = 'Status', index=False)

writer.save()
writer.close()

Consolidating Result Summary in new sheet in same excel from different sheets on a existing excel file using python

1 Answers1