69

I am curious why a simple concatenation of two data frames in pandas:

shape: (66441, 1)
dtypes: prediction    int64
dtype: object
isnull().sum(): prediction    0
dtype: int64

shape: (66441, 1)
CUSTOMER_ID    int64
dtype: object
isnull().sum() CUSTOMER_ID    0
dtype: int64

of the same shape and both without NaN values 

foo = pd.concat([initId, ypred], join='outer', axis=1)
print(foo.shape)
print(foo.isnull().sum())

can result in a lot of NaN values if joined.

(83384, 2)
CUSTOMER_ID    16943
prediction     16943

How can I fix this problem and prevent NaN values being introduced?

Trying to reproduce it like 

aaa  = pd.DataFrame([0,1,0,1,0,0], columns=['prediction'])
print(aaa)
bbb  = pd.DataFrame([0,0,1,0,1,1], columns=['groundTruth'])
print(bbb)
pd.concat([aaa, bbb], axis=1)

failed e.g. worked just fine as no NaN values were introduced.

Georg Heiler
  • 15,554
  • 27
  • 140
  • 255

2 Answers2

101

I think there is problem with different index values, so where concat cannot align get NaN:

aaa  = pd.DataFrame([0,1,0,1,0,0], columns=['prediction'], index=[4,5,8,7,10,12])
print(aaa)
    prediction
4            0
5            1
8            0
7            1
10           0
12           0

bbb  = pd.DataFrame([0,0,1,0,1,1], columns=['groundTruth'])
print(bbb)
   groundTruth
0            0
1            0
2            1
3            0
4            1
5            1

print (pd.concat([aaa, bbb], axis=1))
    prediction  groundTruth
0          NaN          0.0
1          NaN          0.0
2          NaN          1.0
3          NaN          0.0
4          0.0          1.0
5          1.0          1.0
7          1.0          NaN
8          0.0          NaN
10         0.0          NaN
12         0.0          NaN

Solution is reset_index if indexes values are not necessary:

aaa.reset_index(drop=True, inplace=True)
bbb.reset_index(drop=True, inplace=True)

print(aaa)
   prediction
0           0
1           1
2           0
3           1
4           0
5           0

print(bbb)
   groundTruth
0            0
1            0
2            1
3            0
4            1
5            1

print (pd.concat([aaa, bbb], axis=1))
   prediction  groundTruth
0           0            0
1           1            0
2           0            1
3           1            0
4           0            1
5           0            1

EDIT: If need same index like aaa and length of DataFrames is same use:

bbb.index = aaa.index
print (pd.concat([aaa, bbb], axis=1))
    prediction  groundTruth
4            0            0
5            1            0
8            0            1
7            1            0
10           0            1
12           0            1
jezrael
  • 729,927
  • 78
  • 1,141
  • 1,090
  • 1
    so you would suggest a resetIndex() / ignore_index=True ? because both did not work for me to solve the problem. – Georg Heiler Oct 31 '16 at 09:59
  • Hmmm, if reset index still same problem? – jezrael Oct 31 '16 at 10:01
  • 15
    Indeed your comment from above with `pd.concat([ypred.reset_index(drop=True), initId.reset_index(drop=True)], axis=1)` worked nicely! Thanks a lot. – Georg Heiler Oct 31 '16 at 10:47
  • 1
    It seems like any deletion of rows must be followed by `reset_index` to avoid such indexing issues later in the processing. – ayorgo Aug 18 '18 at 18:48
  • 1
    I had this same issue, and even when I tried to simply add a column it would give me NaNs. The drop & inplace solved this for me – James Oliver Nov 05 '19 at 09:34
  • The solution works, congrats and thank you. But I think it could be simplified and shortened by a factor of 5. – Nicolas Gervais May 14 '20 at 19:59
3

You can do something like this:

concatenated_dataframes = concat(
    [
        dataframe_1.reset_index(drop=True),
        dataframe_2.reset_index(drop=True),
        dataframe_3.reset_index(drop=True)
    ],
    axis=1,
    ignore_index=True,
)

concatenated_dataframes_columns = [
    list(dataframe_1.columns),
    list(dataframe_2.columns),
    list(dataframe_3.columns)
]
    
flatten = lambda nested_lists: [item for sublist in nested_lists for item in sublist]

concatenated_dataframes.columns = flatten(concatenated_dataframes_columns)

To concatenate multiple DataFrames and keep the columns names / avoid NaN.