Insert a single cell of string above header row in python pandas-CodePudding

I have my dataframe ready to be written to an excel file but I need to add a single cell of string above it. How do I do that?

CodePudding user response：

import pandas as pd
df = pd.DataFrame({'label':['first','second','first','first','second','second'],
               'first_text':['how is your day','the weather is nice','i am feeling well','i go to school','this is good','that is new'],
               'second_text':['today is warm','this is cute','i am feeling sick','i go to work','math is hard','you are old'],
               'third_text':['i am a student','the weather is cold','she is cute','ii am at home','this is bad','this is trendy']})

df.loc[-1] = df.columns.values
df.sort_index(inplace=True)
df.reset_index(drop=True, inplace=True)
df.rename(columns=
    {"label": "Register", 'first_text':'', 'second_text':'', 'third_text':''}, 
    inplace=True)

Try this MRE, so you can change your data as well.

CodePudding user response：

You can save the dataframe starting from the second row and then use other tools to write the first cell of your excel file.

Note that writing from pandas to excel overwrites its data, so we have to follow this order (but there are also methods how to write to an existing excel file without overwriting data).

1. Save the dataframe, specifying startrow=1:

df.to_excel("filename.xlsx", startrow=1, index=False)

2. Write a cell value.

For example, using openpyxl (from a GeeksforGeeks tutorial):

from openpyxl import load_workbook

# load excel file
workbook = load_workbook(filename="filename.xlsx")
 
# open workbook
sheet = workbook.active
 
# modify the desired cell
sheet["A1"] = "A60983A Register"
 
# save the file
workbook.save(filename="filename.xlsx")