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.
Solved! Go to Solution.
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: