Python Tool - Alteryx.read() dataframe as strings

Claje

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:

test	field2
123456	a
654321	b
777777	c
12345	d
	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.

jdunkerley79

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: