Ggtcf

When you merge two indexed dataframes on certain values using 'outer' merge, python/pandas automatically adds Null (NaN) values to the fields it could not match on. This is normal behaviour, but it changes the data type and you have to restate what data types the columns should have.

fillna() or dropna() do not seem to preserve data types immediately after the merge. Do I need a table structure in place?

Typically I would run numpy np.where(field.isnull() etc) but that means running for all columns.

Is there a workaround to this?

I don't think there's any really elegant/efficient way to do it. You could do it by tracking the original datatypes and then casting the columns after the merge, like this:

import pandas as pd

# all types are originally ints
df = pd.DataFrame('a': [1]*10, 'b': [1, 2] * 5, 'c': range(10))
df2 = pd.DataFrame('e': [1, 1], 'd': [1, 2])

# track the original dtypes
orig = df.dtypes.to_dict()
orig.update(df2.dtypes.to_dict())

# join the dataframe
joined = df.join(df2, how='outer')

# columns with nans are now float dtype
print joined.dtypes

# replace nans with suitable int value
joined.fillna(-1, inplace=True)

# re-cast the columns as their original dtype
joined_orig_types = joined.apply(lambda x: x.astype(orig[x.name]))

print joined_orig_types.dtypes

This should really only be an issue with bool or int dtypes. float, object and datetime64[ns] can already hold NaN or NaT without changing the type.

Because of this, I'd recommend using the new Int64 type for your integer or bool columns, which is capable of stroring NaN. For Booleans, they need to be converted to 1 or 0 instead of True or False, then to Int64. You should do this for all int and bool columns before the join, but I'll just illustrate on df2 whose columns get NaN rows after the join:

import pandas as pd

df = pd.DataFrame('a': [1]*6, 'b': [1, 2]*3, 'c': range(6))
df2 = pd.DataFrame('d': [1,2], 'e': [True, False])

df2 = df2.astype('int').astype('Int64')
df2.dtypes
#d Int64
#e Int64
#dtype: object

df.join(df2)
# a b c d e
#0 1 1 0 1 1
#1 1 2 1 2 0
#2 1 1 2 NaN NaN
#3 1 2 3 NaN NaN
#4 1 1 4 NaN NaN
#5 1 2 5 NaN NaN

#a int64
#b int64
#c int64
#d Int64
#e Int64
#dtype: object

The benefit here is that nothing will be upcast until it needs to. For instance, in the other solutions if you do .fillna(-1.72) you may get an unwanted answer as you call int(-1.72) which then coerces the fill value to -1. This could be useful in some situations, but dangerous in others.

With Int64 the fill value remains true to what you specify and the column is only upcast if you fill with a non-int. Also it will not throw an error if you do something like .fillna('Missing'), as it never tries to typecast a string to an int.

Or you can just do a concat/append on dtypes of both dfs and applyastype():

joined = df.join(df2, how='outer').fillna(-1).astype(pd.concat([df.dtypes,df2.dtypes]))
#or joined = df.join(df2, how='outer').fillna(-1).astype(df.dtypes.append(df2.dtypes))
print(joined)

 a b c e d
0 1 1 0 1 1
1 1 2 1 1 2
2 1 1 2 -1 -1
3 1 2 3 -1 -1
4 1 1 4 -1 -1
5 1 2 5 -1 -1
6 1 1 6 -1 -1
7 1 2 7 -1 -1
8 1 1 8 -1 -1
9 1 2 9 -1 -1

A simpler version of @hume's answer, directly get the original types, then use astype with one shot and get the data-types back, here is the code:

orig = df.dtypes.to_dict()
orig.update(df2.dtypes.to_dict())
joined = df.join(df2, how='outer')
new_joined = joined.fillna(-1).astype(orig)
print(new_joined)
print(new_joined.dtypes)

Output:

 a b c d e
0 1 1 0 1 1
1 1 2 1 2 1
2 1 1 2 -1 -1
3 1 2 3 -1 -1
4 1 1 4 -1 -1
5 1 2 5 -1 -1
6 1 1 6 -1 -1
7 1 2 7 -1 -1
8 1 1 8 -1 -1
9 1 2 9 -1 -1
a int64
b int64
c int32
d int64
e int64
dtype: object