How can I read and format a CSV file with headers in Iron Python

[. . 17]

Dear all.

I was able to read a CSV file with headers using Iron Python, but I don't know how to format it. Please tell me how to do it.

The file to be read is the following CSV file.

The first three lines are the header and the fourth and subsequent lines are the data.

(sample2.csv)

202101121900,A10100,2021/01/12,19:00:00 ,ABC1230

Number,p,0,4,1

Small,28,Tall,23,Large,5,Huge,56

1,10.21,219.92,3278,M

2,10.22,319.71,6036,M

3,10.46,419.56,7135,L

4,10.08,519.34,2282,M

5,10.16,618.75,2176,M

When I read this in Iron Python and output it to a table, it looks like this.

 

 

 

1

2

3

4

5

6

7

8

 

 

202101121900

A10100

2021/1/12

19:00:00

ABC1230

 

 

 

 

 

Number

p

0

4

1

 

 

 

 

 

Small

28

Tall

23

Large

5

Huge

56

 

 

1

10.21

219.92

3278

M

 

 

 

 

 

2

10.22

319.71

6036

M

 

 

 

 

 

3

10.46

419.56

7135

L

 

 

 

 

 

4

10.08

519.34

2282

M

 

 

 

 

 

5

10.16

618.75

2176

M

 

 

 

 

 

 

I would like to format the data to look like the following.

The goal is to read many files in this format and combine them into one data table.

I have attached a sample file and would appreciate your advice.

 

 

 

1

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.21

219.92

3278

M

 

 

2

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.22

319.71

6036

M

 

 

3

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.46

419.56

7135

L

 

 

4

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.08

519.34

2282

M

 

 

5

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.16

618.75

2176

M

[. . 17]

The first thing I can't figure out is how to extract the header section elements one by one.

The second is how to combine the header and data parts to create a record with one row each, and output this to the data table (additional output). With the current method, we have to create new data for each output.The following script was adapted from # https://community.tibco.com/questions/script-import-contents-text-file-or-csv-file.(Iron Python script)

from Spotfire.Dxp.Data import *

Dxp.Data.Import import *filePath=r "C:sample2.csv"

#specify any settings for the file

settings= TextDataReaderSettings()

settings.Separator=","

dataSource=TextFileDataSource(filePath,settings)DataManager=Document.

#datasource name and datasource to add table

DataManager.Tables.Add("myNewDataTable",dataSource)Document.Properties['file']= dataSource.DocumentTitle

#Document.Properties['file']= "TEST" 

[Gaia Paolini]

To skip the first three lines: Spotfire wants an object of type Array of integer with the list of lines. Note it starts from 0.

 

from System import Array

linesToSkip=[0,1,2]

linesArray = Array[int](linesToSkip)

....

#after creating the settings object

settings.AddIgnoreRows(linesArray)

 

I don't understand the second requirement though.

[. . 17]

Dear Gaia.Thank you for your suggestion.

I have confirmed that I can extract the data part by the method you taught me.

I really need the information in the header section, so I will continue to investigate.

If I can't do it with IronPython script, I'm thinking of using TERR. (I'm more familiar with R.)Best regards

[Gaia Paolini]

sure, TERR is an alternative. What I don't understand is the header: there are three lines that do not look like column names and do not seem to relate to the data columns. Can you show the desired result

[. . 17]

This matter was resolved by reviewing the data aggregation method. Instead of doing the data processing within Spotfire, we decided to create a special program to do the data aggregation before loading it into Spotfire. Thank you very much for your suggestion.