community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Python Tool - Alteryx.read() dataframe as strings

Magnetar
Magnetar

I'm working on a proof of concept leveraging the Python tool, wherein I am going to loop through a dataframe provided by Alteryx input and pass values from each column into another application.


I want these values to exactly match to the way they are read in, which I think means I want to convert all of these values to strings beforehand.  This avoids some cases like certain values being saved as Float, which causes the number of decimals to change.

While I could do this type conversion directly in Alteryx with a Select tool, I'm hoping to have the python code implicitly perform this type conversion so that I can share this module with other Alteryx users without extensive instructions.

 

I have tried doing some conversion and null handling, but it doesn't seem to be accomplishing what I want.

 

The input data I am working with for my test case is below:


testfield2
123456a
654321b
777777c
12345d
 e

Here's the (very basic) code I have running right now that is having an issue.

 

from ayx import Alteryx

collection = Alteryx.read("#1")
print(collection)
print(collection.dtypes)
collection.fillna('',inplace=True)
collection = collection.astype(str)

print(collection)
print(collection.dtypes)

 

Here's the results:

SUCCESS: reading input data "#1"
       test field2
0  123456.0      a
1  654321.0      b
2  777777.0      c
3   12345.0      d
4       NaN      e

test      float64
field2     object
dtype: object
       test field2

0  123456.0      a
1  654321.0      b
2  777777.0      c
3   12345.0      d
4                e

test      object
field2    object
dtype: object


Any help would be greatly appreciated.

 

One option would be to access the sqlite cache directly:

 

from ayx import CachedData
import sqlite3
sqlliteFile = CachedData.CachedData(debug=False).config.input_file_map['#1']


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

connection = sqlite3.connect(sqlliteFile)
connection.row_factory = dict_factory

output = []
for row in connection.execute("select * from TableMcTableFace"):
    output.append(row)
connection.close()

print(output)

This code is based on a lot of digging inside of ayx module but works in 2018.4 and will read data into array of dictionaries

 

With the usual proviso:

download.jpg

 

 

Labels