13

I want to use pandas, to wrap an entire column. I have already set the width for the columns now I just need to wrap the entire column as they are all in 1 line.

I searched with varying answers not straight to the point. I don't need to edit the width of the columns, I just want to wrap the text in the cells which does the same thing as me highlighting a column n clicking on "Wrap Text". But I want to do it using a Python script hence I would like to use pandas to achieve that.

So basically the

before looks like this

And the

after looks like this

But I want to do this via pandas and not manually by clicking wrap text. And I want to apply the wrap to the whole column not just that one cell.

double-beep
  • 5,031
  • 17
  • 33
  • 41
Uraforclain
  • 325
  • 2
  • 8
  • 18

1 Answers1

20

You can use solution modifying example_pandas_column_formats:

import string

long_text = 'aa aa ss df fff ggh ttr tre ww rr tt ww errr t ttyyy eewww rr55t e'
data = {'a':[long_text, long_text, 'a'],'c':[long_text,long_text,long_text],
        'b':[1,2,3]}       
df = pd.DataFrame(data)

#choose columns of df for wrapping
cols_for_wrap = ['a','c']


writer = pd.ExcelWriter('aaa.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

#modifyng output by style - wrap
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
wrap_format = workbook.add_format({'text_wrap': True})

#dictionary for map position of selected columns to excel headers
d = dict(zip(range(26), list(string.ascii_uppercase)))
print (d)
{0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E', 5: 'F', 6: 'G', 7: 'H', 8: 'I', 
9: 'J', 10: 'K', 11: 'L', 12: 'M', 13: 'N', 14: 'O', 15: 'P', 16: 'Q', 
17: 'R', 18: 'S', 19: 'T', 20: 'U', 21: 'V', 22: 'W', 23: 'X', 24: 'Y', 25: 'Z'}

#get positions of columns
for col in df.columns.get_indexer(cols_for_wrap):
#map by dict to format like "A:A"     
    excel_header  =  d[col] + ':' + d[col]
    #None means not set with
    worksheet.set_column(excel_header, None, wrap_format)
    #for with = 20
    #worksheet.set_column(excel_header, 20, wrap_format)

writer.save()
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Woah haha im lost. lets say i have a excel file to read the columns and wrap based on columns. How would i change in that case. Your example is based on a string so i cant visualise how to do it. – Uraforclain Sep 04 '17 at 03:50
  • 1
    My example is based by wrap by columns of dataframe - `cols_for_wrap`. Do you need something else? Can you explain more? Or what is necessary explain more in my solution? – jezrael Sep 04 '17 at 04:15
  • i want to just open up the excel file and change the values in the entire columns C,D,E,F,G to be wrapped. – Uraforclain Sep 04 '17 at 05:54
  • i don't get the first part where u did, long_text= 'aa aa ss df fff ggh ttr tre ww rr tt ww errr t ttyyy eewww rr55t e ' that divider is 2 different ways to do it? – Uraforclain Sep 04 '17 at 05:55
  • Yes, then need instead `df = pd.DataFrame(data)` use `df = pd.read_excel('file')` if need pandas solution. – jezrael Sep 04 '17 at 05:55
  • ok then, writer = pd.ExcelWriter('aaa.xlsx', engine='xlsxwriter') is writing to another file? – Uraforclain Sep 04 '17 at 06:02
  • I think your input is dataframe and output is wrapped excel file. Is it True? – jezrael Sep 04 '17 at 06:03
  • yes, sure. So do you need input is excel file and output is wraped excel file? – jezrael Sep 04 '17 at 06:05
  • lets say my input excel file already has colours and formatting done. after i do the wrap and save to another file will those formatting be gone? – Uraforclain Sep 04 '17 at 06:08
  • I am worried you lost formating, because my solution working Excel file -> dataframe -> wraped excel file. And dataframe lost formating of excel file :( I think for your solution need another technology working with excel file directly and which not lost formatting. Maybe something macro of excel file (never do it, so I cant help you with this macro) – jezrael Sep 04 '17 at 06:12
  • awww ok then last question, df = pd.read_excel('file.xlsx') what if i want to read a sheet inside this workbook? – Uraforclain Sep 04 '17 at 06:16
  • how about write to a specific sheet? pd.ExcelWriter('aaa.xlsx', engine='xlsxwriter') – Uraforclain Sep 04 '17 at 06:19
  • then use `xlsx = pd.ExcelWriter('aaa.xlsx', engine='xlsxwriter') df = pd.read_excel(xlsx, 'Sheet1')` – jezrael Sep 04 '17 at 06:20
  • Yes, you can check http://pandas.pydata.org/pandas-docs/stable/io.html#io-excel-reader – jezrael Sep 04 '17 at 06:23
  • @Peter.k - It seems some wrong reassign, check variable names. – jezrael Feb 28 '19 at 12:58
  • `excel_header =f"{xlsxwriter.worksheet.xl_col_to_name(col)}:{xlsxwriter.worksheet.xl_col_to_name(col)}"` works for columns greater than Z(26) too :) – anky Feb 10 '21 at 15:47