How to create this dataframe using Pandas?-CodePudding

Please see this -

I want to create a table similar to this using pandas in python? Can anyone guide? Unable to understand how to add "Total Marks" text corresponding to two columns ("Student id" & "Course id").

Thanks

CodePudding user response：

As far as I'm aware, pandas doesn't really support merged cells/cells spanning multiple rows in the same way as Excel/HTML do. A pandas-esque way to include the total row for a single column would be something like the following:

import pandas as pd

columns = ['Student ID', 'Course ID', 'Marks']
data = [(103, 201, 67), (103, 203, 67), (103, 204, 89)]
df = pd.DataFrame(data, columns=columns)
df.at['Total marks', 'Marks'] = df['Marks'].sum()

print(df)

(per this answer)

This gives you:

                Student ID  Course ID  Marks
0                    103.0      201.0   67.0
1                    103.0      203.0   67.0
2                    103.0      204.0   89.0
Total marks            NaN        NaN  223.0

For HTML output like the image you provided, it looks like you would have to edit the HTML after exporting from pandas (this thread talks about columns but I believe the same applies for merging cells in rows).

CodePudding user response：

Assuming you wish to export your dataframe (df) with merged cells into excel, then try this code;

writer = pd.ExcelWriter(r"C:\Users\pandas_excel_export.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Edit below code if required (coloring, bold etc.)
merge_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'fg_color': 'yellow'})
worksheet.merge_range('B5:C5', 'Total Marks', merge_format)
writer.save()

Hope this Helps...