5 Replies Latest reply on Jun 23, 2017 11:51 AM by Jeffrey Line

    Tableau & Custom Python Scripts

    Jeffrey Line

      I would like to know if anyone has integrated Tableau with a custom Python script (my assumption is that this would be deployed to a Python Server for execution) to connect to a database to return a result.

      Here is the use case I'm working on. I need to retrieve a percentile number for a given pair of input parameters.

       

      Below is a small sample fo the data that I'm using. The survey data is in the millions of records and many question ids and dates. The user would filter the initial view to what they need and it would then aggregate the average score for each question id. My thought is after this I would call a Python script to pass in the parameters and use it to query a database where the percentile data live.

       

      Does this sound feasible and has anyone ever tried doing this type of external Python call.

       

      I've attached a very simple work book of the basic concept that I'd like to do. You change the parameter and it will return the percentile. I just need to scale this for many columns and rows of data.

       

       

      My data looks like this.

      Survey Data:

      RecidDateQuestionIdMeanScore
      17/1/2011XXAID92.86
      27/1/2011XXAID90.63
      37/1/2011XXAID100.00
      47/1/2011XXAID87.50
      57/1/2011XXAID68.75
      67/1/2011XXAID87.50
      77/1/2011XXAID82.14
      87/1/2011XXAID95.00
      97/1/2011XXAID83.33
      107/1/2011XXAID93.18
      117/1/2011XXAID79.55
      127/1/2011XXAID88.33
      137/1/2011XXAID85.00
      147/1/2011XXAID86.11
      157/1/2011XXAID87.50
      167/1/2011XXAID75.00
      177/1/2011XXAID93.75

       

      Percentile Data:

          

      Question IDPercentileBottom ScoreTop Score
      XXAID10.0050.00
      XXAID250.0054.70
      XXAID354.7056.59
      XXAID456.5958.48
      XXAID558.4860.36
      XXAID660.3662.25
      XXAID762.2564.14
      XXAID864.1466.03
      XXAID966.0367.91
      XXAID1067.9169.80
      XXAID1169.8070.31
      XXAID1270.3170.82
      XXAID1370.8271.33
      XXAID1471.3371.84
      XXAID1571.8472.35
      XXAID1672.3572.86
      XXAID1772.8673.37
      XXAID1873.3773.88
      XXAID1973.8874.39
      XXAID2074.3974.90
      XXAID2174.9075.27
      XXAID2275.2775.64
      XXAID2375.6476.01
      XXAID2476.0176.38
      XXAID2576.3876.75
      XXAID2676.7577.12
      XXAID2777.1277.49
      XXAID2877.4977.86
      XXAID2977.8678.23
      XXAID3078.2378.60
      XXAID3178.6078.82
      XXAID3278.8279.04
      XXAID3379.0479.26
      XXAID3479.2679.48
      XXAID3579.4879.70
      XXAID3679.7079.92
      XXAID3779.9280.14
      XXAID3880.1480.36
      XXAID3980.3680.58
      XXAID4080.5880.80
      XXAID4180.8080.91
      XXAID4280.9181.02
      XXAID4381.0281.13
      XXAID4481.1381.24
      XXAID4581.2481.35
      XXAID4681.3581.46
      XXAID4781.4681.57
      XXAID4881.5781.68
      XXAID4981.6881.79
      XXAID5081.7981.90
      XXAID5181.9082.23
      XXAID5282.2382.56
      XXAID5382.5682.89
      XXAID5482.8983.22
      XXAID5583.2283.55
      XXAID5683.5583.88
      XXAID5783.8884.21
      XXAID5884.2184.54
      XXAID5984.5484.87
      XXAID6084.8785.20
      XXAID6185.2085.30
      XXAID6285.3085.40
      XXAID6385.4085.50
      XXAID6485.5085.60
      XXAID6585.6085.70
      XXAID6685.7085.80
      XXAID6785.8085.90
      XXAID6885.9086.00
      XXAID6986.0086.10
      XXAID7086.1086.20
      XXAID7186.2086.22
      XXAID7286.2286.24
      XXAID7386.2486.26
      XXAID7486.2686.28
      XXAID7586.2886.30
      XXAID7686.3086.32
      XXAID7786.3286.34
      XXAID7886.3486.36
      XXAID7986.3686.38
      XXAID8086.3886.40
      XXAID8186.4086.68
      XXAID8286.6886.96
      XXAID8386.9687.24
      XXAID8487.2487.50
      XXAID8587.5087.80
      XXAID8687.8088.08
      XXAID8788.0888.36
      XXAID8888.3688.64
      XXAID8988.6488.92
      XXAID9088.9289.20
      XXAID9189.2089.24
      XXAID9289.2489.28
      XXAID9389.2889.32
      XXAID9489.3289.36
      XXAID9589.3689.40
      XXAID9689.4089.85
      XXAID9789.8590.30
      XXAID9890.3090.75
      XXAID9990.7591.20
        • 2. Re: Tableau & Custom Python Scripts
          Jeffrey Line

          Diego,

           

          I'll definitely have to look into it. Thanks for letting me know about it.

          • 3. Re: Tableau & Custom Python Scripts
            Bora Beran

            Hi Jeffrey,

            Any reason you prefer using Python for this? Did you try the WINDOW_PERCENTILE function in Tableau? It will give you percentile rank of each value within the entire column (or per partition of your choice).

             

            Thanks,

             

            Bora

            • 4. Re: Tableau & Custom Python Scripts
              Jeffrey Line

              Bora,

               

              Unfortunately the Window_Percentile function won't be an option for me. The percentiles are not calculated based on the data but rather gotten by taking your average/median score and comparing it to an external data source which has the percentile ranges predefined.

              So my original thought was to call a function and pass it a value to grab the percentile for that value and pass it back to the Viz.

              • 5. Re: Tableau & Custom Python Scripts
                Jeffrey Line

                All,

                 

                Thought I would give an update on my question.

                 

                After many trials and errors, I was able to successfully write a Python script that will call a stored procedure in an external SQL Server database to grab my percentile. Currently I did not deploy this script onto the Python Server to call it. It is called directly from my calculation. 

                I'm sure I'll change it to make it a little better, here is the script I came with.

                 

                SCRIPT_INT(

                "

                import pyodbc

                import sys

                Scores=[]

                connection = pyodbc.connect('Driver={SQL Server};'

                                        'Server={Server_Name};'

                                        'Database=C{DB_Name};'

                                        'Integrated Security=SSPI')

                cursor = connection.cursor()

                SQLCommand = (""{CALL dbo.GetQuestionPercentiles (?,?,?)}"")

                for index, int in enumerate(_arg1):

                    Values =[_arg1[index],_arg2[index],_arg3[index]]

                    cursor.execute(SQLCommand,Values)

                    row = cursor.fetchone()

                    Scores.append(row[0])

                connection.close()

                return Scores

                ",[Mean Score],Attr([Quest Id]),[Year])