Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Windows SW for advanced data analysis

  1. Mar 24, 2014 #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. Mar 24, 2014 #2

    jhae2.718

    User Avatar
    Gold Member

    I generally use MATLAB or Python (using the Anaconda distribution on Windows).
     
  4. Mar 24, 2014 #3
    I meant a visual interactive data analyzer....
     
  5. Mar 27, 2014 #4
    No ideas?
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Windows SW for advanced data analysis
  1. Data Acquisition (Replies: 4)

  2. Empirical data (Replies: 15)

  3. Window material (Replies: 8)

Loading...