pd.concat() not merging on same index

user_6303377 0
Viewed 2885

I have a DataFrame containing forecasts called fcst that looks like this:

             yhat        yhat_lower  yhat_upper
ds          
2015-08-31  -0.443522   -19.067399  17.801234
2015-09-30  6.794625    -31.472186  46.667981
...

After making this transformation:

fcst2 = fcst["yhat"].to_frame().rename(columns={"yhat":"test1"})
fcst3 = fcst["yhat"].to_frame().rename(columns={"yhat":"test2"})

I want to concatenate them, on the date index as such:

pd.concat([fcst2,fcst3])

But I receive a DataFrame that is not aligned on the index:

             test1     test2
ds      
2015-08-31  -0.443522   NaN
2015-09-30  6.794625    NaN
... ... ...
2017-05-31  NaN 95.563262
2017-06-30  NaN 85.829916

and this despite:

(fcst2.index == fcst3.index).any()

returning True.

My question is: why aren't the two DataFrames being concatenated on the index and what can I do to solve this?

I know of the join function, but as some dates will be missing in some of the other DataFrames which I'm planning on adding I believe that the concat function might be better.

pandas dataframe python-3.x python
2 Answers

Call concat with axis set to 1:

pd.concat([df1, df2], axis=1)
user_34718810

It does not work because pd.concat has as default value for the parameter axis=0. So you can either call the function with axis=1 as suggested by Dominique Paul or you can use the function join instead. Follows an example:

# data to create the dataframes with
data_1 = [1,2,3,4,5]
index_1 = ['a','b','c','d','e']
data_2 = [6,7,8,9,10]
index_2 = ['b','d','e','a','c']

# create dataframes
df_1 = pd.DataFrame({'data_1':data_1, 'new_index':index_1})
df_2 = pd.DataFrame({'data_2':data_2, 'new_index':index_2})

# setting new index to test unaligned indexes
df_1.set_index('new_index', inplace=True, drop=True)
df_2.set_index('new_index', inplace=True, drop=True)

# join operation is performed on indexes
df_1.join(df_2)
user_92048380
Related