1

I am using pandas.read_excel(file) to read the file, but instead of getting number with currency symbol its giving numbers only not with currency symbol.

help will be appreciated.

thanks![enter image description here]1

Pravin Soni
  • 23
  • 4

1 Answers1

0

When the Excel file is read by Pandas it reads the underlying value of the cell which fundamentally is either a string or a number. Things like currency symbols are just applied as formatting by Excel - they affect what you see on screen but don't actually 'exist' in the cell value. For example the number 1.1256 might appear as 1.1 if you select on decimal place, or £1.13 if you select currency, it could even appear as a date, but fundamentally it is just 1.1256 and this is the value Pandas reads. Generally this is useful because with numbers you can perform arithmetic whereas if pandas imported '£1.13' you would be unable to do any arithmetic (for example add this to another amount of money) until you had removed the £ symbol and converted to a decimal and in so doing you would have lost some precision (as the number is now 1.13 not 1.1256).

If you need to view the data with the currency symbol, I'd suggest just adding it on at the last moment, for example if you print the data to screen you could use print(f'£{your_number}')

Simon Notley
  • 2,070
  • 3
  • 12
  • 18
  • its not possible to use currency symbol as constants, It can vary based on the situation. I think, i need to use static text instead of formatting the cell. – Pravin Soni Dec 15 '19 at 09:04
  • Seems the answer is no when it comes to reading the formatting. This question has some useful info and a possible solution. https://stackoverflow.com/q/38038428/11432434 – Simon Notley Dec 15 '19 at 13:32
  • If possible the best answer would be to capture the currency in a separate field in the original Excel as you shouldn't really be relying on formatting to store data. – Simon Notley Dec 15 '19 at 13:35