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
DIY glove-based tutor indicates muscle-memory potential
Tricorder XPRIZE: 10 teams advance in global competition to develop consumer-focused diagnostic device
Study shows local seismic isolation and damping methods provide optimal protection for essential computing equipment
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