Why won't pandas add up my amounts? Numbers stored as text

The inventory said monto is of type str. If you open it in a spreadsheet, you see "$7,784.00", "4,692", "14,130", "1722": four ways of writing money in one column, because everyone typed it however they liked. You read amounts. pandas reads text, and text doesn't add up: it gets glued together.

Predict what happens in three attempts to work with amounts like these.

Adding text

Example 1 of 3

example.py

import pandas as pd

montos = pd.Series(["1250", "980"])

print(montos.sum())

What does the sum print?

Why?

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 3, opens once you explain this one

Make your prediction

First remove, then convert, then count

The safe conversion has three steps:

Remove what isn't a number. The peso sign and the thousands comma: str.replace("$", "", regex=False) and str.replace(",", "", regex=False). The regex=False makes it clear you're looking for that exact character, not a search pattern. Then str.strip() in case a space is left over.
Convert with pd.to_numeric(..., errors="coerce"). By default, to_numeric stops at the first value it doesn't understand. With "coerce" it turns it into a blank and keeps going.
Count the blanks before and after. It's the step nothing reminds you of: skip it and nothing fails. If there was 1 blank before converting and there are 2 after, the conversion swallowed a value, and you have to go see which one it was.

Watch what coerce can hide. In the March sheet, before converting, monto had one blank (sale V-0036, waiting on its invoice). After converting there are two. The second is sale V-0050, which someone entered with the word "pendiente". Without counting, that amount would have vanished from the total without anyone noticing.

The new blank is not an error you have to fix yourself. It's another question for Marisol, and while she answers, you leave it empty and say so in the report.

Does your column have amounts in another format, like 1.250,00? Ask your Maestro what changes.

Warning: Dots and commas

In this sheet the comma separates thousands and the dot separates decimals. Some sheets do it the other way around, and removing the commas there changes the value. Before removing any character, look at several complete amounts and confirm what each sign does in your sheet.

Keep the original while you check

A detail that will save you work: while you test the conversion, put it in a new column (monto_num) instead of over monto. That way you can put the two side by side and see which text produced each blank. If you overwrite the column, the "pendiente" disappears and there's nothing left to look for. Once the conversion is tested and the log says how many amounts ended up empty, you can replace the column in the final script.

And one more temptation: open the sheet, delete "pendiente" by hand and type an amount that seems reasonable. Don't. Nobody confirmed that number, and next month you'll have to remember to do it again. The code leaves the blank, the log counts it and Marisol decides.

Try it

Convert monto in the full sales sheet and find which sale was lost in the conversion. If your blank count doesn't change and you think something is off, ask your Maestro.

Remove the peso sign and the commas from monto, convert it with pd.to_numeric(..., errors="coerce") into a new column monto_num, and count the blanks before and after. Then print the folio and the original amount of every sale that had an amount written and didn't convert. You're done when you see the count go from 1 to 2 and you know which folio caused it.

Your code (Python)

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.

Data: ventas-cooperativa, 60 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "ventas-cooperativa". Your edits run with your code.
Row										Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 3

Run it yourself

If you did it right, the sale that was lost is V-0050, with "pendiente" written in the amount. With the amounts now as numbers, the column sums to 756,656 pesos. Before you send that number to anyone, answer the question below.

Check yourself: With `monto_num` converted, `ventas["monto_num"].sum()` gives 756,656. Marisol asks you for the total sold in March. What do you send her?

pandas: to_numeric and its errors parameter