Import csv and apply mathematical operations

Just starting out? Need help? Post your questions and find answers here.
craig7
User
User
Posts: 23
Joined: Thu Sep 23, 2021 2:49 pm

Import csv and apply mathematical operations

Post by craig7 »

How would you code so that you could import a CSV file, such as that in the example below, such that you could then perform mathematical operations on specific columns - such as adding exam1 and exam3 (based on selections in a GUI that has drop downs generated based on the CSV headers) - per row and then storing the result per row. The contents of the CSV will vary with differing numbers of variables and differing variable names depending on user preferences , and so the variable names should be determined by the first row.

ID exam1 exam2 exam3
1 59 23 98
2 28 45 89
collectordave
Addict
Addict
Posts: 1272
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: Import csv and apply mathematical operations

Post by collectordave »

My fist question would be how much control over the format of the CSV file do you?

Is the first line always strings and the rest numbers?

The easiest after knowing that is to read the CSV into a 2 dimensional array.

This can then be displayed in a list icon gadget.

No need to look at the first row being the variable names.

There is code on the forum to read lines from a CSV into a 1 dimensional array.

After that it is just your own GUI that determines the operations.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
craig7
User
User
Posts: 23
Joined: Thu Sep 23, 2021 2:49 pm

Re: Import csv and apply mathematical operations

Post by craig7 »

Please see my answers to your questions, in italics below.

My fist question would be how much control over the format of the CSV file do you? No control as it would be up to the user. They can change the names of the columns and the number of columns, and the code must detect an adapt.

Is the first line always strings and the rest numbers? Yes correct.
User avatar
StarBootics
Enthusiast
Enthusiast
Posts: 715
Joined: Sun Jul 07, 2013 11:35 am
Location: Canada

Re: Import csv and apply mathematical operations

Post by StarBootics »

Something like this !?!

Code: Select all

; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; Project name : CSV Loader 
; File Name : CSV Loader.pb
; File version: 1.0.0
; Programming : OK
; Programmed by : StarBootics
; Date : 26-09-2021
; Last Update : 26-09-2021
; PureBasic code : V5.73 LTS
; Platform : Windows, Linux, MacOS X
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Structure Entries
  
  List Entry.l()
  
EndStructure

Structure CSVData
  
  List Header.s()
  List Entries.Entries()
  
EndStructure

Procedure LoadExams(*Exams.CSVData, FileName.s)
  
  If ReadFile(0, FileName)
    
    ; First we read the Header into a single string
    
    Header.s = ReadString(0)
    
    ColumnCount.l = CountString(Header, " ") + 1
    
    For ColumnID = 1 To ColumnCount
      AddElement(*Exams\Header())
      *Exams\Header() = StringField(Header, ColumnID, " ")
    Next
    
    ; Then we read the entries with a While loop
    
    While Eof(0) = 0
      Entries.s = ReadString(0)
      
      AddElement(*Exams\Entries())
      
      For ColumnID = 1 To ColumnCount
        AddElement(*Exams\Entries()\Entry())
        *Exams\Entries()\Entry() = Val(StringField(Entries, ColumnID, " "))
      Next
      
    Wend
    
    CloseFile(0)
    
  EndIf
  
  
EndProcedure

LoadExams(Exams.CSVData, "exams.csv")

Debug "; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
Debug "; The Header information"
Debug ""

ForEach Exams\Header()
  Debug Exams\Header()  
Next

Debug ""
Debug "; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
Debug "; The entries information"
Debug ""

ExamCount.l = ListSize(Exams\Header()) - 1

ForEach Exams\Entries()
  
  ForEach Exams\Entries()\Entry()
    
    SelectElement(Exams\Header(), ListIndex(Exams\Entries()\Entry()))
    Debug "It's the " + Exams\Header() + " : " + Str(Exams\Entries()\Entry()) 
    
    If ListIndex(Exams\Entries()\Entry()) >= 1
      Sum.q + Exams\Entries()\Entry()
    EndIf
    
  Next
  
  Debug "The Mean result is : " + StrF(Sum / ExamCount, 2)
  Debug ""
  Sum = 0
  
Next

; <<<<<<<<<<<<<<<<<<<<<<<
; <<<<< END OF FILE <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<
Best regards
StarBootics
The Stone Age did not end due to a shortage of stones !
craig7
User
User
Posts: 23
Joined: Thu Sep 23, 2021 2:49 pm

Re: Import csv and apply mathematical operations

Post by craig7 »

StarBootics wrote: Mon Sep 27, 2021 2:33 am Something like this !?!

Code: Select all

; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
; Project name : CSV Loader 
; File Name : CSV Loader.pb
; File version: 1.0.0
; Programming : OK
; Programmed by : StarBootics
; Date : 26-09-2021
; Last Update : 26-09-2021
; PureBasic code : V5.73 LTS
; Platform : Windows, Linux, MacOS X
; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Structure Entries
  
  List Entry.l()
  
EndStructure

Structure CSVData
  
  List Header.s()
  List Entries.Entries()
  
EndStructure

Procedure LoadExams(*Exams.CSVData, FileName.s)
  
  If ReadFile(0, FileName)
    
    ; First we read the Header into a single string
    
    Header.s = ReadString(0)
    
    ColumnCount.l = CountString(Header, " ") + 1
    
    For ColumnID = 1 To ColumnCount
      AddElement(*Exams\Header())
      *Exams\Header() = StringField(Header, ColumnID, " ")
    Next
    
    ; Then we read the entries with a While loop
    
    While Eof(0) = 0
      Entries.s = ReadString(0)
      
      AddElement(*Exams\Entries())
      
      For ColumnID = 1 To ColumnCount
        AddElement(*Exams\Entries()\Entry())
        *Exams\Entries()\Entry() = Val(StringField(Entries, ColumnID, " "))
      Next
      
    Wend
    
    CloseFile(0)
    
  EndIf
  
  
EndProcedure

LoadExams(Exams.CSVData, "exams.csv")

Debug "; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
Debug "; The Header information"
Debug ""

ForEach Exams\Header()
  Debug Exams\Header()  
Next

Debug ""
Debug "; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
Debug "; The entries information"
Debug ""

ExamCount.l = ListSize(Exams\Header()) - 1

ForEach Exams\Entries()
  
  ForEach Exams\Entries()\Entry()
    
    SelectElement(Exams\Header(), ListIndex(Exams\Entries()\Entry()))
    Debug "It's the " + Exams\Header() + " : " + Str(Exams\Entries()\Entry()) 
    
    If ListIndex(Exams\Entries()\Entry()) >= 1
      Sum.q + Exams\Entries()\Entry()
    EndIf
    
  Next
  
  Debug "The Mean result is : " + StrF(Sum / ExamCount, 2)
  Debug ""
  Sum = 0
  
Next

; <<<<<<<<<<<<<<<<<<<<<<<
; <<<<< END OF FILE <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<
Best regards
StarBootics
Great thanks StarBootics that gives me the sense that it is possible in PB! I did not know that you could make the Structure command automatically adapt to the file in that way?
User avatar
StarBootics
Enthusiast
Enthusiast
Posts: 715
Joined: Sun Jul 07, 2013 11:35 am
Location: Canada

Re: Import csv and apply mathematical operations

Post by StarBootics »

craig7 wrote: Mon Sep 27, 2021 2:43 pm Great thanks StarBootics that gives me the sense that it is possible in PB! I did not know that you could make the Structure command automatically adapt to the file in that way?
With a lot of practice and imagination everything is possible with PureBasic. I have used Linked lists one simple and one nested via a Structure but you can get away with an array if you prefer. That being said when you don't know in advance how the data structure look like, linked list are more flexible in my opinion. I will let you explore the array possibility by your self.

Best regards
StarBootics
The Stone Age did not end due to a shortage of stones !
craig7
User
User
Posts: 23
Joined: Thu Sep 23, 2021 2:49 pm

Re: Import csv and apply mathematical operations

Post by craig7 »

StarBootics wrote: Mon Sep 27, 2021 4:57 pm
craig7 wrote: Mon Sep 27, 2021 2:43 pm Great thanks StarBootics that gives me the sense that it is possible in PB! I did not know that you could make the Structure command automatically adapt to the file in that way?
With a lot of practice and imagination everything is possible with PureBasic. I have used Linked lists one simple and one nested via a Structure but you can get away with an array if you prefer. That being said when you don't know in advance how the data structure look like, linked list are more flexible in my opinion. I will let you explore the array possibility by your self.

Best regards
StarBootics
Yes that sounds interesting will have a look at arrays as well, thanks again.
eck49
Enthusiast
Enthusiast
Posts: 124
Joined: Sat Nov 14, 2020 10:08 pm
Location: England

Re: Import csv and apply mathematical operations

Post by eck49 »

@craig7
It's not too hard - I have several projects which start by reading in data like this, but in my case (a) the number of entries in each row is fixed in code and (b) the operations being done on them are hard-coded according to the purpose of the project.

Consider the following suggestions:
1. The very first line of the file is an identifier which your program will recognise - say, the name of the program - so that the program can be confident to reject any file which is not intended to be input for it.
2. if the first character of any line is, for example, an apostrophe (') the rest of the line is ignored as a comment
3. let the line containing the list of field names have its own first character - I usually use an open square bracket ([) for this. This readily distinguishes the line visually. Perhaps your list of names does not readily fit neatly on a single line in your editor? Have a second or third such line starting with [.
4. If there is a chance that the number of values might also make it difficult to fit them onto an editor line, you could also have a character serve as a continuation flag.
5. Let the number of fields in the list dictate the number of values expected: any excess values are discarded and any deficit defaulted.

Once you have the values read into a 2D array or a 1D list of 1D arrays or whatever, you have a powerful base for your project.

My bias is towards PB arrays rather than PB lists, but however you proceed, be encouraged.

In my case, some of the fields may be strings rather than numbers, which adds a level of complexity which can be handled. Another story!
Ubuntu 16.04 64-bit
Purebasic 5.70
(native tongue: English)
craig7
User
User
Posts: 23
Joined: Thu Sep 23, 2021 2:49 pm

Re: Import csv and apply mathematical operations

Post by craig7 »

eck49 wrote: Tue Sep 28, 2021 3:46 pm @craig7
It's not too hard - I have several projects which start by reading in data like this, but in my case (a) the number of entries in each row is fixed in code and (b) the operations being done on them are hard-coded according to the purpose of the project.

Consider the following suggestions:
1. The very first line of the file is an identifier which your program will recognise - say, the name of the program - so that the program can be confident to reject any file which is not intended to be input for it.
2. if the first character of any line is, for example, an apostrophe (') the rest of the line is ignored as a comment
3. let the line containing the list of field names have its own first character - I usually use an open square bracket ([) for this. This readily distinguishes the line visually. Perhaps your list of names does not readily fit neatly on a single line in your editor? Have a second or third such line starting with [.
4. If there is a chance that the number of values might also make it difficult to fit them onto an editor line, you could also have a character serve as a continuation flag.
5. Let the number of fields in the list dictate the number of values expected: any excess values are discarded and any deficit defaulted.

Once you have the values read into a 2D array or a 1D list of 1D arrays or whatever, you have a powerful base for your project.

My bias is towards PB arrays rather than PB lists, but however you proceed, be encouraged.

In my case, some of the fields may be strings rather than numbers, which adds a level of complexity which can be handled. Another story!
Ok thanks for your insights. I see you are also a newer member. Do you find PB adequate for what you do?
eck49
Enthusiast
Enthusiast
Posts: 124
Joined: Sat Nov 14, 2020 10:08 pm
Location: England

Re: Import csv and apply mathematical operations

Post by eck49 »

@craig7
Ok thanks for your insights. I see you are also a newer member. Do you find PB adequate for what you do?
I do, but having spent a career in procedural programming, starting with Fortran II, there are a lot of bells and whistles in PB which I haven't got around to. Much of which are aimed to help the games writer, which is not me!
Ubuntu 16.04 64-bit
Purebasic 5.70
(native tongue: English)
Post Reply