I am trying to horizontally concatenate a pair of data frames with identical indices, but the result is always a vertical concatenation with NaN values inserted into every column.
dct_l = {'1':'a', '2':'b', '3':'c', '4':'d'}
df_l = pd.DataFrame.from_dict(dct_l, orient='index', columns=['Key'])
dummy = np.zeros((4,3))
index = np.arange(1,5)
columns = ['POW', 'KLA','CSE']
df_e = pd.DataFrame(dummy, index, columns)
print(df_l)
Key
1 a
2 b
3 c
4 d
print(df_e)
POW KLA CSE
1 0.0 0.0 0.0
2 0.0 0.0 0.0
3 0.0 0.0 0.0
4 0.0 0.0 0.0
pd.concat([df_l, df_e], axis=1)
Actual Result
Key POW KLA CSE
1 a NaN NaN NaN
2 b NaN NaN NaN
3 c NaN NaN NaN
4 d NaN NaN NaN
1 NaN 0.0 0.0 0.0
2 NaN 0.0 0.0 0.0
3 NaN 0.0 0.0 0.0
4 NaN 0.0 0.0 0.0
Expected Result
Key POW KLA CSE
1 a 0.0 0.0 0.0
2 b 0.0 0.0 0.0
3 c 0.0 0.0 0.0
4 d 0.0 0.0 0.0
What is happening here?
You have different dtypes for your two indexes:
df_e.index
Index([1, 2, 3, 4], dtype='int64')
df_l.index
Index(['1', '2', '3', '4'], dtype='object')
which will break the alignment (1 != '1').
Make sure they are identical.
For example:
pd.concat([df_l.rename(int), df_e], axis=1)
Key POW KLA CSE
1 a 0.0 0.0 0.0
2 b 0.0 0.0 0.0
3 c 0.0 0.0 0.0
4 d 0.0 0.0 0.0