How to process column of currency

[hmf]

I have a CSV file that uses:

• Decimal Separator = ',' (comma)
• Grouping Separator = '.' (period)

When I load the file I get a double column. But the values are incorrect. What seems to be happening is that the period is used as a decimal separator and the comma is ignored. I tried using:

    val builder = CsvReadOptions.builder( data )
                                .separator('\t')	   // table is tab-delimited
                                .header(header)                        // no header
                                .dateFormat(dateFormatter)            // the date format to use
                                .missingValueIndicator("-")
                                .tableName(name)
                                .locale(Locale("pt-PT"))

but this doesn't work. My file does not match the Locale.

EDIT: I have realized that setting your locale is not the way to handle currency.

How can one parse and load the currency correctly? I have also looked into using DecimalFormat and NumberFormat but don't see how with Tablesaw unless I parse the column myself.

TIA

[hmf]

I have come up with the following "solution":

• Load the CSV file using CsvReadOptions.builder( data ).columnTypesPartial(getType)
• Provide the column name and type for the currency column only
• Use the ColumnType.STRING for that column
• Replace the string for a "valid" float and parse that as a Double

Here are the last steps:

val column_a = t.stringColumn("Currency")
                           .map( s => s.replace(".", "").replace(",", "."))
                           .parseDouble()
                           .setName("Currency")

If anyone knows of a better (or more idiomatic) way do do this, please tell me.
Still unsure wether or not to use this column as a Long.

TIA

[lwhite1]

I may be mis-remembering, but I would have tried to make that work using Locales in the import settings to handle the period/comma switch. Idk what currencies you're using. It may be that the currency symbol support issue is still open, or it doesn't work with all currencies, but I thought there was some work on that front as well.

…

On Mon, Oct 3, 2022 at 3:47 AM hmf ***@***.***> wrote: I have come up with the following *"solution"*: - Load the CSV file using CsvReadOptions.builder( data ).columnTypesPartial(getType) - Provide the column name and type for the currency column only - Use the ColumnType.STRING for that column - Replace the string for a "valid" float and parse that as a Double Here are the last steps: val column_a = t.stringColumn("Currency") .map( s => s.replace(".", "").replace(",", ".")) .parseDouble() .setName("Montante") If anyone knows of a better (or more idiomatic) way do do this, please tell me. Still unsure wether or not to use this column as a Long. TIA — Reply to this email directly, view it on GitHub <#1152 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2FPAQNTYO4MCT5VTFKNBDWBKFQ3ANCNFSM6AAAAAAQ24JAVM> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[hmf]

@lwhite1 I am using pt-PT (Portugal) whose currency is euros. I looked at the Locale API for information on the number formatting. I can extract the currency, which is an "XXX". Even if this were correct I don't see how one can extract information on the decimal and grouping separators because it doesn't provide these. I am assuming that different countries use different formatting for the same currency (Euro). The format I refer to above may in fact be non-standard.

EDIT: I was initializing the Locale incorrectly. It should be Locale("PT", "pt"), but I think what I said above still stands.

The java.text.DecimalFormat however lets us define DecimalFormatSymbols that allow us to set the grouping and decimal separators. Here is an example I found in StackOverflow:

    import java.text.DecimalFormat

    DecimalFormat df = new DecimalFormat();
    DecimalFormatSymbols symbols = new DecimalFormatSymbols();
    symbols.setDecimalSeparator(',');
    symbols.setGroupingSeparator(' ');
    df.setDecimalFormatSymbols(symbols);
    df.parse(p);

Parsing currencies does not seem to be simple. Most probably I am doing something wrong on my side.

[hmf]

I have some more information. So one can in fact get the required information with casting. Here is the code in Scala:

    import java.text.DecimalFormat

    val locale = Locale("PT", "pt")
    val format = NumberFormat.getInstance(locale)
    println(s"Currency = ${format.getCurrency()}")
    println(s"Grouping used = ${format.isGroupingUsed()}")

    val f = NumberFormat.getCurrencyInstance(locale)
    val df = f.asInstanceOf[DecimalFormat]
    val dfSymbols = df.getDecimalFormatSymbols()
    println(s"Currency symbol = '${dfSymbols.getCurrencySymbol()}'")
    println(s"Grouping separator = '${dfSymbols.getGroupingSeparator()}'")
    println(s"Decimal separator = '${dfSymbols.getDecimalSeparator()}'")

and here is the output:

Currency = EUR
Grouping used = true
Currency symbol = '€'
Grouping separator = ' '
Decimal separator = ','

As I suspected, the grouping separator of the file is non-standard. Now the question is, assuming JTablesaw is doing the right thing, how can one set the grouping separator of a locale?