Windows SW for advanced data analysis

  1. I wrote this small Excel macro to gather data from vehicles database:

    Code (Text):
    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/?callback=?&cmd=getTrims&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?
     
  2. jcsd
  3. jhae2.718

    jhae2.718 1,160
    Gold Member

    I generally use MATLAB or Python (using the Anaconda distribution on Windows).
     
  4. I meant a visual interactive data analyzer....
     
  5. No ideas?
     
Know someone interested in this topic? Share a link to this question via email, Google+, Twitter, or Facebook

Have something to add?

0
Draft saved Draft deleted