Pandas concat DataFrames - keep original order of index

Question

I am trying to concat two dataframes:

df2:
CU  Pmt  2017-02-01
h    b        15
h    d        12
h    a        13

and df1:

CU  Pmt  'Total/Max/Min'
h    b        20
h    d        23 
h    a        22
a    b        16
a    d        13
a    a        14

such that df3:

CU  Pmt  2017-02-01  2017-02-02
h    b      15           20
h    d      12           23
h    a      13           22
a    b      NaN          16
a    d      NaN          13
a    a      Nan          14

I am using a multi index of index_col = [0,1] for both

This is what I have:

date = '2017-02-02'
df1 = pd.read_csv(r'Data\2017-02\2017-02-02\Aggregated\Aggregated_Daily_All.csv', usecols=['CU', 'Parameters', 'Total/Max/Min'], index_col =[0,1])
df1 = df1.rename(columns = {'Total/Max/Min':date})

df2 = pd.read_csv(r'Data\2017-02\MonthlyData\February2017.csv', index_col = [0,1])
df3  = pd.concat([df2, df1], axis=1)
df3.to_csv(r'Data\2017-02\MonthlyData\February2017.csv')

However, df3 is coming out as:

CU  Pmt  2017-02-01  2017-02-02
a    a      NaN          14
a    b      NaN          16
a    d      Nan          13
h    a      13           22
h    b      15           20
h    d      12           23

Which has CU and Pmt (the two index columns) in alphabetical order. How is it possible to keep the original order, so that for all new indexes added for a new date, they are added at the bottom?

Answer 1

You can try reindex if values of df1.index contains values of df2.index:

df3  = pd.concat([df2, df1], axis=1).reindex(df1.index)
print (df3)
        2017-02-01  'Total/Max/Min'
CU Pmt                             
h  b          15.0               20
   d          12.0               23
   a          13.0               22
a  b           NaN               16
   d           NaN               13
   a           NaN               14