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

verytiredgirl
verytiredgirl Member
in Magic ETL

I have a column Bill-To (interger)

image.png

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

image.png

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?

Tagged:

Answers

  • ggenovese
    ggenovese Coach

    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.

  • ggenovese
    ggenovese Coach
    edited February 12

    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.
  • Manasi_Panov
    Manasi_Panov Contributor

    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.

Categories