I need to find the number of decimals a float has in python. The data is imported from excel using pandas.
I tried converting to a string and counting the number of integers after the decimal. The problem is that this approach does not account for any trailing zeros so it seems as if there are less decimals than in reality. Multiplying by 10^x also does not work because the number of decimals varies.
Thanks in advance for the help!
If I understand correctly, you want to achieve the same format of a number as shown in Excel. Really, the number of trailing decimals in the cell of an Excel sheet is just a formatting option and has not much to do with the actual value of a number stored there.
You can use the openpyxl library to parse an Excel file, including the formatting settings of its individual cells. Assuming you want to know the number of decimals shown in cell A1 of the active sheet in your Excel file, and the format of this cell happens to be "0.00" (meaning the cell will show two decimals after the comma), you can do:
import re
from openpyxl import load_workbook
# Load the Excel file and select active sheet
sheet = load_workbook('/path/to/your/file.xlsx').active
# Get the number format of the cell you are interested in
number_format = sheet['A1'].number_format
# The number format should be something like "0.0..."
assert re.match('0\.0+$', number_format), \
f'Expected format "0.0...", got "{number_format}" instead'
# Count number of characters after the decimal point
decimals = len(number_format.split(".")[-1])
print(decimals)
This will print 2 if the format of cell A1 is indeed "0.00" (or 3 if the format is "0.000" etc. – I guess you get the concept). You can then use decimals to format any number (e.g. your values loaded with pandas) with two digits after the comma, e.g. like so:
print(f'{3.141592:.{decimals}f}') # prints 3.14 if `decimals` is 2
Note that there are many more formatting possibilities for numbers in Excel files other than "0.0..." (you might want to take a look here to see which formats openpyxl provides via constants to get an impression of what I mean). This means that (a) the assert in the code above (which is just a safeguard here) might fail in your case and (b) the determination of the value of decimals might need to be adjusted to the actual format string of the cell you are interested in (which might need a regular expression of its own for parsing more complicated cases).