Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Community is experiencing an influx of spam. As we work toward a solution, please use the 'Notify Moderator' option on the ellipsis menu to flag inappropriate posts.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Python Tool - Alteryx.read() dataframe as strings

Claje
14 - 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.

 

1 REPLY 1
jdunkerley79
ACE Emeritus
ACE Emeritus

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
Top Solution Authors