Error to read csv encoding utf-8 with bom and export back to parquet

[tanyaofei]

the error is exception recovered: reflect.StructOf: field 0 has invalid name at ompluscator/[email protected]/builder.go:192 export.csv

[pjebs]

Can got provide your code too

[tanyaofei]

Can got provide your code too

var ctx = context.Background()

func main() {
    //c := config.Config.Server
    //web.Server.SetAddr(c.GetAddress() + ":" + strconv.Itoa(int(c.GetPort())))
    //web.Server.Run()
    exportParquet("export.parquet", readCSV("export.csv"))
}

func readCSV(filepath string) *dataframe.DataFrame {
    fr, err := os.Open(filepath)
    if err != nil {
        panic(err)
    }

    df, err := imports.LoadFromCSV(ctx, fr)
    if err != nil {
        panic(err)
    }

    return df

}

func exportParquet(out string, df *dataframe.DataFrame) {
    f, err := os.Create(out)
    if err != nil {
        panic(err)
    }
    defer f.Close()

    err = exports.ExportToParquet(ctx, f, df)
    if err != nil {
        panic(err)
    }
}

[pjebs]

Your column names are:

编号 | 年龄 | 性别 | 地区 | 身高cm | 体重kg | 肺活量 | 舒张压 | 收缩压 | 心率 | 最大心率 | 最大吸氧量 | 负荷时间 | 做功 | 日常锻炼情况 | 吃零食情况 | 跑步情况 | 玩电脑游戏情况 | 逛街情况 | 散步情况 | 夜宵情况
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --

Currently I implemented this function for exporting to Parquet: https://github.com/rocketlaunchr/dataframe-go/blob/master/exports/parquet.go#L163

It was based on this article: https://html.developreference.com/article/11087043/Spark+dataframe+column+naming+conventions+++restrictions

Do you know if parquet supports chinese characters? Can you point me to the specs?

[pjebs]

Actually, I think the issue is from this package: https://github.com/Ompluscator/dynamic-struct

It is used to create a struct dynamically. I think Go prohibits using chinese characters for the first letter for an export field. I will have to explore it further.

[tanyaofei]

Your column names are:

编号 | 年龄 | 性别 | 地区 | 身高cm | 体重kg | 肺活量 | 舒张压 | 收缩压 | 心率 | 最大心率 | 最大吸氧量 | 负荷时间 | 做功 | 日常锻炼情况 | 吃零食情况 | 跑步情况 | 玩电脑游戏情况 | 逛街情况 | 散步情况 | 夜宵情况
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --

Currently I implemented this function for exporting to Parquet: https://github.com/rocketlaunchr/dataframe-go/blob/master/exports/parquet.go#L163

It was based on this article: https://html.developreference.com/article/11087043/Spark+dataframe+column+naming+conventions+++restrictions

Do you know if parquet supports chinese characters? Can you point me to the specs?

Yes, it supported. I think the problem is the first character is the bom character \ufeff, it can't be seen but it existed in the first series name, it will cause dynamicstruct field name check error.

[tanyaofei]

I think removing the first character if it's \ufeff will solve this problem

[pjebs]

How did that character get there?

[tanyaofei]

by reading a csv encoded with UTF-8 with bom?

import pandas

df = pandas.DataFrame({
    "A": [1, 2, 3, 4, 5],
    "B": [1, 2, 3, 4, 5],
})

df.to_csv("withbom.csv", encoding="UTF-8-sig", index=False) # encoding is UTF-8-BOM

withbom.csv

It cause the same problem

panic: reflect.StructOf: field 0 has invalid name

goroutine 1 [running]:
reflect.StructOf({0xc0001711e0, 0x2, 0x10?})
        /usr/local/opt/go/libexec/src/reflect/type.go:2454 +0x28e5

[pjebs]

As an experiment, can you add a Upper-case English letter at the front of each column name and tell me if it exports?

eg. A年龄

I believe the actual issue is that in Go, structs must have a uppercase english letter for the first letter of the field, in order to be exported.

Without it, the field is not exported and the parquet writer package (used for exporting) can't see the field:

https://stackoverflow.com/questions/40256161/exported-and-unexported-fields-in-go-language

https://github.com/golang/go/issues/5763

[tanyaofei]

I use utf-8-sig because open a csv file with excel will use gbk default, the bom will tell excel to read with utf-8 encoding

[pjebs]

Is df, err := imports.LoadFromCSV(ctx, fr) correctly reading the csv?

[tanyaofei]

Is df, err := imports.LoadFromCSV(ctx, fr) correctly reading the csv?

yes

[pjebs]

As an experiment can you:

1. Iterate over []df.Series

2. Call Rename on each series and prepend each series name with "X" (e.g X年龄) see https://pkg.go.dev/github.com/rocketlaunchr/dataframe-go#SeriesFloat64.Rename

3. Then export to parquet

[tanyaofei]

As an experiment can you:

1. Iterate over []df.Series
2. Call Rename on each series and prepend each series name with "X" (e.g X年龄) see https://pkg.go.dev/github.com/rocketlaunchr/dataframe-go#SeriesFloat64.Rename
3. Then export to parquet

    df := readCSV("/Users/tanyaofei/Desktop/export.csv")

    for _, s := range df.Series {
        s.Rename("X" + s.Name())
    }

    fmt.Println(df)
    exportParquet("p.parquet", df)

panic: reflect.StructOf: field 0 has invalid name

goroutine 1 [running]:
reflect.StructOf({0xc000450000, 0x15, 0x50?})
        /usr/local/opt/go/libexec/src/reflect/type.go:2454 +0x28e5

[tanyaofei]

[pjebs]

Okay, also call https://pkg.go.dev/strings#TrimSpace

[pjebs]

Also when importing CSV:

type CSVLoadOptions struct {
	// If TrimLeadingSpace is true, leading white space in a field is ignored.
	// This is done even if the field delimiter, Comma, is white space.
	TrimLeadingSpace bool

That option is available.

[tanyaofei]

Okay, also call https://pkg.go.dev/strings#TrimSpace

no effect

[tanyaofei]

Also when importing CSV:

type CSVLoadOptions struct {
	// If TrimLeadingSpace is true, leading white space in a field is ignored.
	// This is done even if the field delimiter, Comma, is white space.
	TrimLeadingSpace bool

That option is available.

no effect either

[tanyaofei]

s.Rename("X" + strings.Trim(s.Name(), "\xEF\xBB\xBF")) works

[pjebs]

There are 2 different issues here:

1. Those weird space characters. I will update the csv importing function to remove them.
2. Currently the way it's implemented (using https://github.com/Ompluscator/dynamic-struct), it doesn't support columns with first character Chinese. I will need to find a solution to it.
3. Most likely, it will be an option that says if the columns start with Chinese,Japanese or Korean characters. I will then append X to the the front.
4. Afterwards, I will reopen the parquet file and re-modify the column names to remove the X character.

[pjebs]

This has been a problem for many years: https://github.com/golang/go/issues/5763.

[pjebs]

I believe I have a solution. Does Python always produce "\xEF\xBB\xBF" when saving csv? What is the purpose of it? I'm just asking because I'm trying to figure out the repercussions of filtering it out.

See: https://github.com/golang/go/issues/33887

[tanyaofei]

I believe I have a solution. Does Python always produce "\xEF\xBB\xBF" when saving csv? What is the purpose of it? I'm just asking because I'm trying to figure out the repercussions of filtering it out.

no, "\xEF\xBB\xBF" is only produced when using UTF-8-SIG encoding. Some people want to open csv file with excel on Windows, they will us UTF-8-SIG instead of UTF-8, only in this way, the Windows System can recognize the encoding correctly, otherwise, it's full of messy code.

[tanyaofei]

In fact, "\xEF\xBB\xBF" is used to mark that this is a UTF-8 file, without it then Windows will uses the locale encoding(which is GBK for China) for decoding

[pjebs]

Can you test out this branch: https://github.com/rocketlaunchr/dataframe-go/pull/63

It should solve both problems.

[tanyaofei]

I will test the branch later, but i am wondering why not name struct fields by index such as Z1, Z2 Z3. It's necessary to give names base on series.Name ?

[pjebs]

The Z is just to force the struct field to be exported. Nothing else. It was just a quick fix.