Why does my barcode show up as 7.501235e+12?
Tell apart when scientific notation is just a way of displaying a number and when digits were really lost, and read long IDs and codes without losing a single one.
Free course. Your Maestro, an AI, .
The inventory file has two long columns: each product's barcode (codigo_barras), 13 digits, and the invoice number (folio_factura) it arrived with, 18 digits. When you review it, things like 7.501235e+12 show up or, in Excel, something with E+. The natural reaction is to think the number got ruined.
Sometimes it did and sometimes it didn't. This page is so you can tell the difference, because they're two different things:
- Displaying a long number in scientific notation.
7.501235e+12means 7.501235 times 10 to the 12th, that is, a 13-digit number rounded to fit on screen. The full value may still be there. - Storing a number with fewer digits than it had. That is real loss, and changing how it's displayed won't fix it.
Microsoft documents both in Excel. It says Excel "converts large numbers to scientific notation, like 1.23E+15", and that "Excel has a maximum precision of 15 significant digits", so that "any numbers past the 15th digit are rounded down to zero." For example, a 20-digit number like 12345678901234567890 becomes 12345678901234500000, shown as 1.23457E+19.
A 13-digit barcode fits in those 15; an 18-digit invoice number doesn't. pandas has its own version of this story. Predict:
How pandas shows it
Example 1 of 4
import pandas as pd
with open("productos.csv", "w", encoding="utf-8") as archivo:
archivo.write("sku,codigo_barras,folio_factura\n00731,7501234500017,120000004567891234\n01204,,120000004567891235\n00090,7501234500093,\n")
productos = pd.read_csv("productos.csv")
print(productos.dtypes)
print(productos)
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 4, opens once you explain this one
Look at the chain of causes for the invoice number: a single invoice with no number entered forced pandas to store the column as float64, and that type can't hold 18 digits. If the column had had no gaps, pandas would have read it as an integer and lost nothing. That's why this error is so treacherous: the same script works one month and the next, when someone leaves a cell empty, it merges different invoices.
The way out is the same rule as the previous page: a barcode or an invoice number is a label, not a quantity. You'll never add them. Read them as text and forget about how many digits fit in a number.
Ask your Maestro why a number with decimals can't store 18 digits correctly.
In Excel
In Excel the limit is 15 significant digits. As long as the code has 15 or fewer, the E+ you see is just a way of displaying it. With 16 or more, Excel stores the extra digits as zeros, and there's no going back in that sheet. To keep them, Microsoft gives two routes: turn off the automatic conversion before opening the file, or treat the column as text.
Before opening the file: on the File tab > Options > Data tab > Automatic Data Conversion, disable Keep the first 15 digits of long numbers and display in scientific notation if required. That way, a number with 16 or more digits is stored as text with all its digits.
When typing the data in: select the range, press Ctrl+1 to open Format Cells and, on the Number tab, choose Text. Or type an apostrophe (') before the number. Microsoft clarifies that the Text format doesn't change numbers that were already entered.
Before opening the file: under Excel > Preferences > Edit tab > Automatic Data Conversion, disable Keep the first 15 digits of long numbers and display in scientific notation if required. That way, a number with 16 or more digits is stored as text with all its digits.
When typing the data in: type an apostrophe (') before the number so Excel treats it as text. This doesn't change numbers that were already in the sheet.
One more point from Microsoft's help: Excel's maximum precision is 15 significant digits, and it lists "Truncating numerical data to 15 digits of precision and converting to a number displayed in scientific notation" among the conversions it applies on its own. Another reason an invoice number should be text from the start.
Try it
In the invoices file there are three invoices with a number and one with none entered. The program counts how many distinct invoice numbers (folio) there are, a common check before billing. Run it and compare what it says with what you see in the file. Then fix the reading. If you don't understand why it counts a single folio, ask your Maestro.
Run the program: it says there are 3 invoices with a folio but only 1 distinct folio. Change the reading so folio is text. You're done when it says 3 distinct folios and the list shows all three folios in full.
Run the program: it says there are 3 invoices with a folio but only 1 distinct folio. Change the reading so folio is text. You're done when it says 3 distinct folios and the list shows all three folios in full.
Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your code.
Run your code to see its output here.