Pandas DataFrame from Python

Question

Hi all,

I am using the python tool to send a get request and save the output to channel 1

pyl = helpers.serialize_object(results) 
df = pd.DataFrame(pyl)
Alteryx.write(df, 1)

The output is a cell with a string as follows:

[OrderedDict([('AXIS', '000'), ('SET', OrderedDict([('item', [OrderedDict([('TUPLE_ORDINAL', '000000'), ('CHANM', '00O2TGCMLNXWRYADEBUF6HJW2'), ('CAPTION', 'ACT PTD'), ('CHAVL', '001A'), ('CHAVL_EXT', None), ...

What tool could I use to export the data into the correct colums. Json parse does not seem to work.

PhilBalderson · Answer

So grateful for @Dennisklad1 solution. I play with Python API calls a lot, and have written some very complicated 'flows to clean up dataframe output. This has saved me hours!

Dennisklad1 · Answer

I cant get it to work. It's always an ordered list.

What worked for me is something like this;

with open("json_file.json",'w') as output:
json.dump(pyl,output)

df = pd.read_json("json_file.json")

Alteryx.write(df, 1)

It seems like a waste to save and read the file but the output is then in the correct json format. Getting following output:

[{'AXIS': '000', 'SET': {'item': [{'TUPLE_ORDINAL': '000000', 'CHANM': '0101', 'CAPTION': 'ACT PTD', 'CHAVL': '0102', 'CHAVL_EXT': None, 'NIOBJNM': None, 'TLEVEL': '00', 'DRILLSTATE': None, 'OPT': None, 'SIGN': ...

joshuaburkhow · Answer

You shouldn't be outputting an ordereddict I think. You need to convert that OrderedDict to a true Pandas DF.  Something like this https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.from_dict.html