write_dataframe fails to write output of pd.pivot_table having Nulls
Domo python tile fails to write the pivoted dataframe with pd.pivot_table if it has nulls while converting - even if you add 'fill_value=np.nan'.
See below:
The pivot function works perfectly fine - it does generate the df, however, when you try to write it, it fails - see below if I write the unpivoted df, it works fine and also see the print(pivot_df) in console.
So, it's evident that pivot_table is fine, but Domo fails while writing it with native method "write_dataframe".
Any idea? or workaround? (other than using Domo native Pivot tile)
Best Answer
-
write_dataframe
is failing here due to column names, not values. Each item inpivot_df.columns
is expected to be a non-null non-empty string. Eitherpivot_table
is returning a DataFrame with null column names, empty string column names, or non-string column names.Randall Oveson <randall.oveson@domo.com>
1
Answers
-
@Pritesh I haven't bumped into that one, but a workaround to consider since you are using scripting tiles in Magic ETL may be to handle the null values prior to the python tile using a formula tile.
For example, I have a couple columns in my dataset with a null value and choose to write formulas to set defaults for text or numeric types.
Give that a try and let me know if it helps.
Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi @JacobFolsom - Thanks for the response and suggestion. However, in this case it wouldn't help because the NULLs are generated real-time while pivoting it. Please see an example below to further explain this:
For e.g., if you have flat data like below (where you see Mary doesn't have a row for .NET and Mike doesn't have a row for PYTHON) that you try to pivot on column "Skills"……it turns something like following:
So, these NULLs are generated automatically for the missing rows for "Skills" values in that dataset. And, if the data is incrementing periodically with new skill, you can't predict those (or it wouldn't make sense to do that).
The native PIVOT tile of Domo gracefully takes care of these NULLs but the write_dataframe surprisingly fails it, even with the 'fill_value=np.nan' of pandas.pivot_table.
I wanted to check if anyone has already found a workaround for this before going to Domo support (not even sure if Domo provides any support on scripting tile).
0 -
write_dataframe
is failing here due to column names, not values. Each item inpivot_df.columns
is expected to be a non-null non-empty string. Eitherpivot_table
is returning a DataFrame with null column names, empty string column names, or non-string column names.Randall Oveson <randall.oveson@domo.com>
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive