Python: Add Line breaks into Excel cells while exporting the DataFrame

Question

I have the following df, where there are new line characters \n in column Data, as shown below.

import pandas as pd
import xlsxwriter
df = pd.DataFrame({'ID': ['111', '222', '222'],
                   'Data': ['Population\nDensity','Population\nDensity','Population\nDensity\nArea']})
print(df)

    ID  Data
0   111 Population\nDensity
1   222 Population\nDensity
2   222 Population\nDensity\nArea

While exporting this df to Excel, I want line breaks at \n. It should look like:

I sought some help from here, using xlsxwriter but didn't work out.

Answer 1

If you use xlsxwriter as the Excel writing engine you can add a text wrap format to the column like this:

import pandas as pd

df = pd.DataFrame({'ID': ['111', '222', '222'],
                   'Data': ['Population\nDensity',
                            'Population\nDensity',
                            'Population\nDensity\nArea']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a text wrap format.
text_wrap_format = workbook.add_format({'text_wrap': True})

# Add the format to column 2 (zero-indexed) and adjust the width.
worksheet.set_column(1, 1, 15, text_wrap_format)

# Close the Pandas Excel writer and output the Excel file.
writer.close()

Output: