1

I am trying to read an excel data where few cell values are having line break, but when I print the dataframe it print the line break as \n. I do not want to print \n for line break instead print the actual value shown in excel cell.

data=pd.read_excel(fileName, sheetname='Sheet1', parse_cols='A:E')
df=data.loc[controllers.Is_run == 'Y'] #filtering the data using flag
print df["Column2"]

Please note when I tried to use df.style it throws below error 

AttributeError: 'DataFrame' object has no attribute 'style'

Sample Excel : Please ignore the table formatting

+-------+------+------+
| Col1  | Col2 | Col3 |
+-------+------+------+
| Prod1 | "ABC |  100 |
           XYZ"         
| Prod2 | Test |  200 |
+-------+------+------+

Column2 of Excel is having value like :

ABC
XYZ

Expected - similar as above

Actual using my code - ABC\nXYZ

Amit Singh
  • 63
  • 2
  • 12
  • Can you show us few line of the xls sheet , that will help? – Karn Kumar Mar 25 '19 at 15:21
  • 1
    Well as `'\n'` is the ascii (or unicode) code point for a new line, I would say that everything if fine. Just try to *print* it: `print(df.Column2.name)` – Serge Ballesta Mar 25 '19 at 15:32
  • @SergeBallesta print(df.Column2.name) just print the Column2 (which is the header) and not the actual cell value – Amit Singh Mar 25 '19 at 15:43
  • @ukemi sorry but its not duplicating the earlier question. here I am reading excel sheet and trying to print the dataframe. so If is there any method or properties of dataframe that can ignore the extended characters and print the data exactly like excel cell – Amit Singh Mar 25 '19 at 15:50
  • 1
    @AmitSingh https://stackoverflow.com/questions/34322448/pretty-printing-newlines-inside-a-string-in-a-pandas-dataframe – iacob Mar 25 '19 at 15:52
  • So you can print it as `df.loc[(df.Col1 == 'Prod1'), 'Prod2'].values[0]` ... – Serge Ballesta Mar 25 '19 at 16:10
  • 1
    @AmitSingh: It is the same question. Where the data frame comes from does not matter. What matters is that you have a dataframe with a cell spanning multiple lines (2 in your case). Pandas can handle it without any problem and if you print the content of the cell (outside pandas), you will see the newline. The default display of pandas shows the encoded value of `\n` and I know no way to have a different display, but if you write the dataframe back into an Excel sheet or a csv file or ... you will get the newline back. – Serge Ballesta Mar 25 '19 at 16:19
  • @ukemi Thanks, it helps. – Amit Singh Mar 26 '19 at 09:22

0 Answers0