Hướng dẫn format header excel python
Ngày đăng:
26/09/2022
Trả lời:
0
Lượt xem:
138
An example of converting a Pandas dataframe to an Excel file with a user defined header format using Pandas and XlsxWriter. ############################################################################## # # An example of converting a Pandas dataframe to an xlsx file # with a user defined header format. # # SPDX-License-Identifier: BSD-2-Clause # Copyright 2013-2022, John McNamara, # import pandas as pd # Create a Pandas dataframe from some data. data = [10, 20, 30, 40, 50, 60] df = pd.DataFrame({'Heading': data, 'Longer heading that should be wrapped' : data}) # Create a Pandas Excel writer using XlsxWriter as the engine. writer = pd.ExcelWriter("pandas_header_format.xlsx", engine='xlsxwriter') # Convert the dataframe to an XlsxWriter Excel object. Note that we turn off # the default header and skip one row to allow us to insert a user defined # header. df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False) # Get the xlsxwriter workbook and worksheet objects. workbook = writer.book worksheet = writer.sheets['Sheet1'] # Add a header format. header_format = workbook.add_format({ 'bold': True, 'text_wrap': True, 'valign': 'top', 'fg_color': '#D7E4BC', 'border': 1}) # Write the column headers with the defined format. for col_num, value in enumerate(df.columns.values): worksheet.write(0, col_num + 1, value, header_format) # Close the Pandas Excel writer and output the Excel file. writer.save() I have an excel sheet with multiple header like:
Now here you can see that first two columns do not have headers they are blank but other columns have headers like Header1, Header2 and Header3. So I want to read this sheet and merge it with other sheet with similar structure. I want to merge it on first column 'ColX'. Right now I am doing this:
But I don't know its neither reading columns correctly and I think will not return the results in the way I want. So, I want the resulting frame should be like:
Any suggestions please. Thanks. |