FormatException when converting pandas.DataFrame to iterable quotes

[masoudaz89]

FormatException: The input string '42.84749984741211' was not in a correct format.
at System.Number.ThrowFormatException[TChar](ReadOnlySpan1 value) at System.Number.ParseDecimal[TChar](ReadOnlySpan1 value, NumberStyles styles, NumberFormatInfo info)
at System.Decimal.Parse(String s)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)

[DaveSkender]

Can you share a bit more about your setups that led to this error? Ideally, a code snippet of how you're converting to iterable Quotes? Here's an example, if helpful: Williams Fractal with DataFrames conversion

More info to consider:

• Help! Converting Pandas DataFrame to iterable Quotes #1165

[DaveSkender]

My theory is that as long as users provide dates and numeric values that match their environment, there shouldn't be a problem. Forcing the use of InvariantCulture will force a requirement to use decimal points instead of the locally set culture, so that's not likely a solution. Let me do some .NET Fiddle to try out some scenarios. We may also need to add some unit tests in both packages to isolate if there's an actual code problem on our end, or to confirm my theory. It could just be a mismatched user environment setup, which we have no control over.

[LeeDongGeon1996]

@DaveSkender I've tested on all CultureInfos with the codes below

import stock_indicators
from System.Globalization import CultureInfo, CultureTypes
from stock_indicators._cslib import CsDecimal

test_num_string= '0.50000000'

incompatible_culture_info = []
all_cultures = CultureInfo.GetCultures(CultureTypes.AllCultures)
for c in all_cultures:
    try:
        print(f'{CsDecimal.Parse(test_num_string, c)} {c.Name} {c.EnglishName}')
    except:
        incompatible_culture_info.append(c.Name)

print(incompatible_culture_info)

And found all incompatible culture with normal (at least in my culture) decimal string, '0.50000000'. You can see many cultures are incompatible with that number. Furthermore, there are even some cultures, for example tr (Turkish), which converts '0.50000000' into '50000000'.

[LeeDongGeon1996]

can be quite tricky. Because we don't know which locale of data user has.

• https://herrmann.tech/en/blog/2021/02/05/how-to-deal-with-international-data-formats-in-python.html
• https://stackoverflow.com/questions/3425294/how-to-detect-the-os-default-language-in-python

[DaveSkender]

so that’s a test of how each culture handles decimals, which in itself isn’t necessary something to fix in our library.

If a user is working on a machine with a different environment culture from that of the quotes, and trying to import data that conflict with it, it’s up to them to notate the culture of the quotes OR override locale settings in their code to match the quotes.

What’s probably confusing people is that DataFrame doesn’t use locale culture info unless specified, but .NET does, which is why it’s breaking on conversion.

They may simply need to set locale before converting locale.setlocale(locale.LC_ALL, 'C') (not sure I picked the right syntax) or as a parameter in the converter they’re using noting quotes culture, but only when they’re incompatible. So, this is correct (below), but applied by the user, not in our library code … or the Python equivalent setlocale

CultureInfo.InvariantCulture for the general usage, ignoring user's local

[DaveSkender]

On a meta level, this is a conflict over whether you honor or allow use of locale culture standards or force them to use international or western standards. I prefer to honor them, so am not a fan forcing one model, but may be open to an international standard default — since most quotes sources are in that format — as long as it can be overridden and set to local, like they do with DataFrame. For now, I think users will need to make an explicit choice.

[DaveSkender]

Right now we’re using this string converter:
https://github.com/DaveSkender/Stock.Indicators.Python/blob/88c887b09782cd7614286944add6ac6bc2b104af/stock_indicators/_cstypes/decimal.py#L21

Maybe what we can do here is have this failover to try a second method to make it more resilient when user environments don’t match source quote formats. It’d still take the local culture first, but then try a more standard one

return CsDecimal.Parse(str(decimal), AllowDecimalPoint)

[DaveSkender]

I may not have the exactly correct syntax. General invariant culture might work for the failover too. I don't think we should try to address every possible cultural variation, like the Turkish one -- we'd never really know if their source quotes were in an aligned culture and overriding it would be bad and irreversible if we did. At some point, the user will need to specify culture info appropriately themselves.

[DaveSkender]

We should probably just start with some guidance on how to convert quotes when your local culture settings are incompatible and see if that's sufficient. While the failover could help, I do see that it isn't a perfect solution either. Makes you wonder how DataFrame handles import of non-standard culture data; does it fail or does it convert them all on import?

[LeeDongGeon1996]

@DaveSkender

does it fail or does it convert them all on import?

After a quick search, It looks like it'd be failed, if users input wrong("wrong" means locale is not matched with their LC_NUMERIC) format of data. The belows are just stackoverflow though, but anyway can be reffered to as some examples:

• https://stackoverflow.com/questions/22137723/convert-number-strings-with-commas-in-pandas-dataframe-to-float
• https://stackoverflow.com/questions/57575535/trying-to-use-locale-atof-for-changing-the-period-in-decimals-within-a-datafra
• https://stackoverflow.com/questions/53889860/display-pandas-dataframe-with-european-formatting

And I could have found some usecases on above. Usually, they convert their numeric data into their desired format, before they input to DataFrame. The most of users who have submitted ISSUEs to us have data in International format, which uses .(comma) as a separator. It means the problem is locale setting on their machine. And I don't want to override their locale setting with setlocale(), because it can also affect to unexpected somthing.

We should probably just start with some guidance on how to convert quotes when your local culture settings are incompatible and see if that's sufficient.

As a result, YES. totally agree with it. We can also add failover step on our decimal.py, I'm worried about that could have a negative impact on performance. so let's just start with guiding how to convert their data.

[LeeDongGeon1996]

Also, we can add some explanatory error message when FormatException is thrown.

[LeeDongGeon1996]

The patched version has been released in v1.3.1.

[LeeDongGeon1996]

@DaveSkender
I think there are two ways after adding error message:

1. Add guide(FAQ) page in docs. And add that link in error message (feat: Add explanatory error message for FormatException stock-indicators-python#351)
2. Just tell users how to resolve this problem every time they create issue.

How do you think?

[DaveSkender]

Yes, having something in the guide is a good idea, and a more helpful error message is always a good to do.

Just tell users how to resolve this problem every time they create issue.

Well, if we have to, but let's have some good docs in the guide to make it easier to direct them to (and possibly a cleaned up #1165)

[LeeDongGeon1996]

Yeah, agree with you.
Now I'm thinking about how/where we can compose this guide well, in terms of docs site UX

[DaveSkender]

Since quotes are really the only place we see this, I'd suggest somewhere in the https://python.stockindicators.dev/guide/#historical-quote section. It'd only need to be a short blurb, possibly with a link to #1165 for more troubleshooting information.

[LeeDongGeon1996]

I'll try to make a draft.