I have found some strange behavior when using the Pandas read_excel function. I have a contrived example of a spreadsheet that looks like:
Name age weight
Bob 35 70
Alice 15 ...
Sam ... 65
Susan 92 63
Harold 42 ...
Missing values are indicated by '...' and I would like them read as NaN. I would also like to convert the weights to grams. My first attempt was:
df=pd.read_excel('test.xls',na_values='...',converters={'weight':lambda y:y*1000})
Unfortunately, this applies the converter function first, so that Susan and Harold's missing weights become not three dots but 3000 dots, which does not match the given "na_value" entry and are thus not turned into NaN. This is highly unexpected.
Is there a reason this is the best behavior for this function?
Did I do something obviously wrong in my implementation of it?
What's the best way to get the behavior I want?