Windows SW for advanced data analysis

[jumpjack]

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/?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?

 

[jhae2.718]

I generally use MATLAB or Python (using the Anaconda distribution on Windows).

 

[jumpjack]

I meant a visual interactive data analyzer...

 

[jumpjack]

No ideas?

 

[alphy]

I would recommend using a software specifically designed for advanced data analysis, such as MATLAB or R. These programs offer a range of tools and functions for data manipulation, visualization, and statistical analysis. They also have the ability to handle larger datasets and provide more complex analyses than Excel. Additionally, both MATLAB and R have a strong community of users and resources available for support and guidance.