6 Replies Latest reply on Apr 15, 2018 9:54 PM by Benjamin Wiley

    How to put a pandas dataframe into a .tde?

    Colin O'Brien

      I'm working with a Pandas dataframe.

      I have all of my data loaded and all of the manipulations I would like to perform, done.

      I'm looking for the specific lines of code which can take this dataframe and copy the rows to a table which I have defined as part of a .tde extract.

      If anyone has experience with this I'd love to see what you wrote. There are examples online for using a .csvreader with python which loops through the rows and adds them as they are being read but I can't find any example of how to take data stored in a dataframe and apply it to a table defined as part of an extract.

        • 1. Re: How to put a pandas dataframe into a .tde?

          Hey Colin,

           

          I've heard of users being able to accomplish these types of tasks using Python and our Data Extract API which you seemed to allude to. Here is some more info:

           

          Tableau Data Extract API

           

          and this blog is pretty useful as well:

           

          Building Tableau Data Extract files with Python in Tableau 8 – Sample Usage | Ryan Robitaille

           

          I hope this helps!

           

          Diego

          • 2. Re: How to put a pandas dataframe into a .tde?
            Francis McGregor-Macdonald

            Did you ever get the answer on this one? I am looking at the same issue. Thanks.

            • 3. Re: How to put a pandas dataframe into a .tde?
              Timothy Lombard

              Hi Colin-

               

              While I'm not using pandas on a regular basis,  I do regularly write out .tde files with python.  I'm sure you could make some modifications to my simple example for use in your case.

              Good luck!

               


              # TDE Creation
              if os.path.isfile("DeptData.tde"):  #check for the file name

                os.remove("DeptData.tde")        #remove it if it's there

              deptdata= tde.Extract("DeptData.tde")   #assign your output name

               

              #create table def
              DeptDataDef = tde.TableDefinition()

              DeptDataDef.addColumn("Dept Number", tde.Types.Type.CHAR_STRING) #0
              DeptDataDef.addColumn("Expense Type", tde.Types.Type.CHAR_STRING) #1
              DeptDataDef.addColumn('Expense', tde.Types.Type.DOUBLE)#2
              DeptDataDef.addColumn('Dept Name', tde.Types.Type.CHAR_STRING) #3
              DeptDataDef.addColumn('Function', tde.Types.Type.CHAR_STRING) #4
              DeptDataDef.addColumn('Node Owner', tde.Types.Type.CHAR_STRING) #5
              DeptDataDef.addColumn('Date of Report', tde.Types.Type.CHAR_STRING) #6

              #use the table definition to create the table and row
              DeptDatatable = deptdata.addTable("Extract", DeptDataDef)

              deptdatarow = tde.Row(DeptDataDef)

               

              for d in dept_dict.keys():    # I loop thru a dict but I think this could also be done for a pandas dataframe

                 if dept_dict[d].FY15Q4A > 0.0:

                deptdatarow.setCharString(0, dept_dict[d].deptno )

                deptdatarow.setCharString(1, 'FY15Q4A')

                deptdatarow.setDouble(2, dept_dict[d].FY15Q4A)

                deptdatarow.setCharString(3, dept_dict[d].deptname )

                deptdatarow.setCharString(4, dept_dict[d].gds_function )

                deptdatarow.setCharString(5, dept_dict[d].node_owner )

                deptdatarow.setCharString(6, dor )

                DeptDatatable.insert(deptdatarow)


              deptdata.close()  #don't forget to close your file!!!

              1 of 1 people found this helpful
              • 4. Re: How to put a pandas dataframe into a .tde?
                Frank Lamberson

                Colin, I know this is older did you ever get your answer on this?

                 

                I ran it across it doing research and I have solved this using Pandas. 

                 

                I followed along the API instructions to create a TDE from Tableau then used a DataFrame to populate the data in a loop reading through all the rows.  I pasted a sample Python script I wrote below.  Using some dummy data I created the TDE file.

                 

                I am sure there are other ways to make this more efficient but this worked for me.

                 

                Regards,

                 

                Frank

                 

                import dataextract as tde

                import os

                import pandas as pd

                import numpy as np

                import datetime as dt

                 

                 

                try:

                    tdefile = tde.Extract('data.tde')

                except:

                    os.remove('data.tde')

                    tdefile = tde.Extract('data.tde')

                 

                 

                #tdefile = tde.Extract('data.tde')

                tableDef = tde.TableDefinition()

                tableDef.addColumn('ID', tde.Type.CHAR_STRING) #0

                tableDef.addColumn('Name', tde.Type.CHAR_STRING) #1

                tableDef.addColumn('Age', tde.Type.INTEGER) # 2

                table = tdefile.addTable('Extract', tableDef)

                newrow = tde.Row(tableDef)

                 

                 

                data = pd.read_excel('data.xlsx')

                for i in range(0,data.shape[0]):

                    newrow.setCharString(0, str(data.ix[i,0]))

                    newrow.setCharString(1, str(data.ix[i,1]))

                    newrow.setInteger(2, int(data.ix[i,2]))

                    table.insert(newrow)

                 

                 

                tdefile.close()

                1 of 1 people found this helpful
                • 5. Re: How to put a pandas dataframe into a .tde?
                  Andrew Mendoza

                  I'm taking a similar approach to what Frank Lamberson shared. Not sure if there's a way to use vectorization or something else to speed this up, but I ran into issues when I tried to use multithreading.

                  • 6. Re: How to put a pandas dataframe into a .tde?
                    Benjamin Wiley

                    If any of you are still looking for a streamlined solution to this question, check out pandleau on GitHub!

                     

                     

                    The module currently writes to .tde files, but check back soon for updates on writing to the new .hyper extracts.

                     

                    If you have any feedback, feel free to open an issue on GitHub. Thanks!

                    1 of 1 people found this helpful