pandas: Default value for missing values on merge

you can add .fillna(0, downcast='infer')if you want.

This is just adding more complexity to an already very complex pd.merge. we already have .fillna for this purpose.

So I just stumbled into this thread looking for the solution the OP is asking.

I have a different case, in which I have a DataFrame with NaNs, which should remain NaNs, which I need to merge with another DataFrame which has only partial information for the first DataFrame:

df1 = pd.read_csv("all.csv")
df2 = pd.read_csv("activity.csv")
df3 = df1.merge(df2, left_on='dealId', right_on='dealId')
df3.fillna(0)

In this example, all.csv contains missing information in some columns, some rows, and I don’t want to make up some fake value for them (and later on I will most likely want to filter out those rows, only in some cases, and depending on whether I am selecting that column with NaNs or not). activity.csv contains activity data for only some of the dealIds in all.csv, and I do want the dealIds that are not in activity.csv to be filled up with 0 (no activity). However, if I use the code above I will end up with a DataFrame where the NaNs in the original all.csv DataFrame are replaced by 0, which is not what I intend to do.

One possible workaround would be something like this:

some_very_odd_value=123123123123123123
df1 = pd.read_csv("all.csv").fillna(some_very_odd_value)
df2 = pd.read_csv("activity.csv")
df3 = df1.merge(df2, left_on='dealId', right_on='dealId')
df3.replace(some_very_odd_value, np.nan)

But this feels like something I should not be doing.

Same issue here. Like @GSanchis I want to left-join (merge) two DataFrames where, in my case, the second one has a single column of ints or Strings where the missing values need to filled with 0 or empty String, respectively. This is very common use case in many data science/data mining task. But that automatic conversion to float type for capturing nan with no means to fill the nan values without much effort always breaks stuff. With all due respect for the greatness of pandas I really don’t understand why such basic requirements for data manipulation won’t be fixed?

+1 Fillna is no solution. If yours merge produces NULLs for an int64 column, casting to floats is a disaster since the integer values are lost when casting to floats.

It does handle the NA type. NA ist the missing value indicator for extension dtypes, e.g. if you are merging

left = pd.DataFrame({"a": [1, 2, 3]}, dtype="Int64")
right = pd.DataFrame({"a": [1, 2, 4], "b": 1}, dtype="Int64")

pd.merge(left, right, how="outer")

you keep Int64 dtype. But if you change the dtypes to int64 np.nan is used, hence the conversion to float.

Hello this feature is needed since on very large and complex dataset we focus on memory size and pandas changes column types to float32 (even if you have int8) 😦

Thanks. Can understand now.

But maybe the docu can improved here? I am not sure about it.

Despite explaining the technical details and backgrounds just add a FAQ like section to merge() docu Why merge convert my integer column to float and how to solve this.

For newbies it is quite hard to understand the difference between Int64 and int64.

fillna() is definitely not a solution but a workaround.

• You want an int column and have an int column.
• But the merge() converts that int column to float (because of missing values).
• After that you set the missing values to something you want (e.g. 0).
• And that you convert back(!) the whole column to int.

Does anyone disagree that this is only a workaround but not a solution?

But I am not sure if we need an fillna argument in merge() because we also could discussed about why merge() does the conversion! pandas.NA is not always float anymore!

You can have NA int integer columns.

# pandas 1.2.5
>>> df = pd.DataFrame({'n': [1, 3, pd.NA]}, dtype='Int64')
>>> df.n
0       1
1       3
2    <NA>
Name: n, dtype: Int64

Question: Why does not merge() handle the NA type by itself?

+1 we need this feature

Perhaps something similar to fill_value in reindex ?

I have a similar issue: Working only with string data, the missing values still becomes NaN, which makes no sense in a string column. My target is to have None values.

The proposed fix .fillna(value=None, downcast=‘defer’) does not work, because fillna thinks the value parameter is missing. “ValueError: Must specify a fill ‘value’ or ‘method’.”