Python Tile fillna on integer column keep return 0 unless I change the column to text

I have a column Bill-To (interger)
I use Python Tile to run this script to fill the nulls cell with the previous cell value
#Import the domomagic package into the script from domomagic import * #read data from inputs into a data frame input1 = read_dataframe('Select Columns') #write your script here input1['Bill-To'] = input1['Bill-To'].fillna(method = 'ffill') #write a data frame so it's available to the next action write_dataframe(input1)
But it return 0 for null cells instead
But if I change the column to String before running the script then the fillna works. But then I have to change the data type to Integer again. Does fillna not work on Integer?
Best Answers
-
I did some digging around and apparently Pandas has two Integer Data Types:
- int64: The standard integer type in pandas for 64-bit signed integers.
- Nullable Integer Type (Int64): This is a special data type in pandas (note the capital "I") that allows integers to coexist with NaN values. This is useful because the regular int64 type does not allow NaN values (missing data).
- So it must be that the read_dataframe() function in domomagic converts integers to the small-i int64 that doesn't support NaN and they get converted to zero's but the Text and Decimal data types all support NaN and therefore the ffill() function works on them.
1 -
Hello @verytiredgirl,
Could you please try the following Python script:
input1['Bill-To'] = input1['Bill-To'].map(lambda x: None if not x else x).fillna(method='ffill')
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
1
Answers
-
This is interesting, and I have no idea why it happens, but for whatever reason your Nulls get converted to 0's by the read_dataframe() function only when they are Integers. If you add print(input1.head()) right after line 5 you'll see it in the console.
Since the values are 0's then there is no NaN value for fillna to work on. However if you change it to text ( or even floating and fixed decimals in my tests, which again I have no idea why that is) then it comes in as NaN and the function works. So it seems like the best solution is to change it to text prior to the python tile and then back to an integer either in the Schema section of the python tile or by using an alter columns.
0 -
I did some digging around and apparently Pandas has two Integer Data Types:
- int64: The standard integer type in pandas for 64-bit signed integers.
- Nullable Integer Type (Int64): This is a special data type in pandas (note the capital "I") that allows integers to coexist with NaN values. This is useful because the regular int64 type does not allow NaN values (missing data).
- So it must be that the read_dataframe() function in domomagic converts integers to the small-i int64 that doesn't support NaN and they get converted to zero's but the Text and Decimal data types all support NaN and therefore the ffill() function works on them.
1 -
Hello @verytiredgirl,
Could you please try the following Python script:
input1['Bill-To'] = input1['Bill-To'].map(lambda x: None if not x else x).fillna(method='ffill')
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 655 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive