Register to reply

Windows SW for advanced data analysis

by jumpjack
Tags: advanced, analysis, data, windows
Share this thread:
jumpjack
#1
Mar24-14, 06:29 AM
P: 201
I wrote this small Excel macro to gather data from vehicles database:

Sub ImportCarQueryDB()
    Call JSON2Excel("2,3,4,8,12,14,20,21,26,27,28,29,31,32,33") ' Specify which fields to extract/import
End Sub

Sub JSON2Excel(ByVal fields As String)
    Filename$ = "C:\temp\fiat2.txt"
    Open Filename$ For Input As #1
        contents = Input(LOF(1), 1) ' Legge intero file
        FillNull$ = Replace(contents, "null", """""") ' sotituisce campi vuoti
        contents = Replace(Replace(FillNull$, "]});", ""), "?({""Trims"":[", "") ' elimina marcatori inizio-fine
        DestRow = 2
        FirstTime = True
        While InStr(contents, ":") > 0
            InizioRecord = InStr(contents, "{")
            FineRecord = InStr(contents, "}")
            rowtext = Mid$(contents, InizioRecord, FineRecord - InizioRecord + 1) ' estrapola singola riga
            Debug.Print rowtext
            contents = Replace(contents, rowtext, "")
            DestCol = 1
            FieldNumber = 1
            While InStr(rowtext, """") > 0
                FirstQuote = InStr(rowtext, """")
                SecondQuote = InStr(FirstQuote + 1, rowtext, """")
                ThirdQuote = InStr(SecondQuote + 1, rowtext, """")
                FourthQuote = InStr(ThirdQuote + 1, rowtext, """")
                If IncludeField(FieldNumber, fields) = True Then
                    FieldValue = Mid$(rowtext, ThirdQuote, FourthQuote - ThirdQuote + 1) ' valore campo
                    If FirstTime = True Then
                        FieldName = Mid$(rowtext, FirstQuote + 1, SecondQuote - FirstQuote - 1)
                        Cells(1, DestCol) = FieldName
                    End If
                    Cells(DestRow, DestCol) = Mid$(FieldValue, 2, Len(FieldValue) - 2) ' memorizza campo in foglio
                    DestCol = DestCol + 1
                End If
                CutField = Mid$(rowtext, FirstQuote, FourthQuote - FirstQuote + 1) ' stringa-campo da eliminare da riga
                rowtext = Replace(rowtext, CutField, "") ' elimina stringa-campo da riga
                FieldNumber = FieldNumber + 1
            Wend
            DestRow = DestRow + 1
        Wend
        FirstTime = False
    Close #1
End Sub

Function IncludeField(ByVal i As Integer, ByVal s As String) As Boolean
    s = s & ","
    temp = False
    While InStr(s, ",") > 0
        FieldNumber = Mid$(s, 1, InStr(s, ",") - 1)
        'DeleteField = Mid$(s, 1, InStr(s, ","))
        s = Mid$(s, InStr(s, ",") + 1, Len(s) - InStr(s, ","))
        If Val(FieldNumber) = i Then
            IncludeField = True
            Exit Function
        End If
    Wend
End Function
Data source:
http://www.carqueryapi.com/documentation/api-usage/

Example query:
http://www.carqueryapi.com/api/0.3/?...rims&make=fiat

Macro works fine... but now I need something more powerful than Excel to analyse data!
For example, I created an additional column showing Nm/kg ratio and another one showing W/kg ratio; if I create a scatter plot between these columns, I get a multi-dots plot where I can apparently identify three groups of data; I'd like to be able to select those groups to figure out to which vehicles they match, or click on a single dot and see which vehichle it represents, or delete single dots from the chart. Excel does not help in this.


Another interesting scatter plot is Nm/kg vs "seconds to 100kph", which higlights a group of vehicles I'd like to identify.


Which SW do you usually use on Windows to perform advanced data analysis like this?
Phys.Org News Partner Engineering news on Phys.org
A spray-on light show on four wheels: Darkside Scientific
Research project on accident-avoiding vehicle concluded
Smaller artificial magnetic conductors allow for more compact antenna hardware
jhae2.718
#2
Mar24-14, 09:02 AM
PF Gold
jhae2.718's Avatar
P: 1,160
I generally use MATLAB or Python (using the Anaconda distribution on Windows).
jumpjack
#3
Mar24-14, 12:51 PM
P: 201
I meant a visual interactive data analyzer....

jumpjack
#4
Mar27-14, 08:45 AM
P: 201
Windows SW for advanced data analysis

No ideas?


Register to reply

Related Discussions
Image reduction and analysis software for windows Astronomy & Astrophysics 5
Looking for advanced books on error/data analysis Set Theory, Logic, Probability, Statistics 6
Data analysis: 2D Surface fitting from raw data Programming & Computer Science 2
Complete data transfer from one Windows 98 computer to another Computers 10
Advanced Calculus I (Analysis) Calculus 9