# Windows SW for advanced data analysis

by jumpjack
 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?