Import csv and apply mathematical operations
Import csv and apply mathematical operations
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
ID exam1 exam2 exam3
1 59 23 98
2 28 45 89
-
- Addict
- Posts: 1309
- Joined: Fri Aug 28, 2015 6:10 pm
- Location: Portugal
Re: Import csv and apply mathematical operations
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.
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.
Re: Import csv and apply mathematical operations
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.
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.
- StarBootics
- Addict
- Posts: 984
- Joined: Sun Jul 07, 2013 11:35 am
- Location: Canada
Re: Import csv and apply mathematical operations
Something like this !?!
Best regards
StarBootics
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 <<<<<
; <<<<<<<<<<<<<<<<<<<<<<<
StarBootics
The Stone Age did not end due to a shortage of stones !
Re: Import csv and apply mathematical operations
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?StarBootics wrote: ↑Mon Sep 27, 2021 2:33 am Something like this !?!Best regardsCode: 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 <<<<< ; <<<<<<<<<<<<<<<<<<<<<<<
StarBootics
- StarBootics
- Addict
- Posts: 984
- Joined: Sun Jul 07, 2013 11:35 am
- Location: Canada
Re: Import csv and apply mathematical operations
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 !
Re: Import csv and apply mathematical operations
Yes that sounds interesting will have a look at arrays as well, thanks again.StarBootics wrote: ↑Mon Sep 27, 2021 4:57 pmWith 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
Re: Import csv and apply mathematical operations
@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!
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 22.04 64-bit
Purebasic 6.00 (as of 5 Sep 2022)
(native tongue: English)
Purebasic 6.00 (as of 5 Sep 2022)
(native tongue: English)
Re: Import csv and apply mathematical operations
Ok thanks for your insights. I see you are also a newer member. Do you find PB adequate for what you do?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!
Re: Import csv and apply mathematical operations
@craig7
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!Ok thanks for your insights. I see you are also a newer member. Do you find PB adequate for what you do?
Ubuntu 22.04 64-bit
Purebasic 6.00 (as of 5 Sep 2022)
(native tongue: English)
Purebasic 6.00 (as of 5 Sep 2022)
(native tongue: English)