Consider the following dataframe example:
maturity_date simulation simulated_price realized_price
30/06/2010 1 0.539333333 0.611
30/06/2010 2 0.544 0.611
30/06/2010 3 0.789666667 0.611
30/06/2010 4 0.190333333 0.611
30/06/2010 5 0.413666667 0.611
Apart from setting aside the value of the last column and concatenating, is there any other way to adjust the dataframe such that the last column becomes row?
Here is the desired output:
maturity_date simulation simulated_price
30/06/2010 1 0.539333333
30/06/2010 2 0.544
30/06/2010 3 0.789666667
30/06/2010 4 0.190333333
30/06/2010 5 0.413666667
30/06/2010 realized_price 0.611
Maybe easier is processing dictionary from last row, DataFrame.pop trick is for remove original column realized_price:
d = df.iloc[-1].to_dict()
d['simulated_price'] = d.pop('realized_price')
d['simulation'] = 'realized_price'
df.loc[len(df.pop('realized_price'))] = d
Alternative:
last = df.columns[-1]
d = df.iloc[-1].to_dict()
d['simulated_price'] = d.pop(last)
d['simulation'] = last
df.loc[len(df.pop(last))] = d
print (df)
maturity_date simulation simulated_price
0 30/06/2010 1 0.539333
1 30/06/2010 2 0.544000
2 30/06/2010 3 0.789667
3 30/06/2010 4 0.190333
4 30/06/2010 5 0.413667
5 30/06/2010 realized_price 0.611000
Another idea is use DataFrame.loc for set new row with default index of DataFrame by select last row in DataFrame.iloc, rename and reappend simulation with new value realized_price in Series.reindex:
s = (df.iloc[-1].drop(['simulated_price','simulation'])
.rename({'realized_price':'simulated_price'})
.reindex(df.columns[:-1], fill_value='realized_price'))
df.loc[len(df.pop('realized_price'))] = s
print (df)
maturity_date simulation simulated_price
0 30/06/2010 1 0.539333
1 30/06/2010 2 0.544000
2 30/06/2010 3 0.789667
3 30/06/2010 4 0.190333
4 30/06/2010 5 0.413667
5 30/06/2010 realized_price 0.611000
Alternative is first reassign column simulation, then get last row and processing Series:
s = (df.assign(simulation='realized_price')
.iloc[-1]
.drop(['simulated_price'])
.rename({'realized_price':'simulated_price'}))
df.loc[len(df.pop('realized_price'))] = s
print (df)
maturity_date simulation simulated_price
0 30/06/2010 1 0.539333
1 30/06/2010 2 0.544000
2 30/06/2010 3 0.789667
3 30/06/2010 4 0.190333
4 30/06/2010 5 0.413667
5 30/06/2010 realized_price 0.611000
Another idea with concat:
out = (pd.concat([df,
df.iloc[[-1]]
.assign(simulation='realized_price',
simulated_price=df['realized_price'].iat[0])],
ignore_index=True)
.drop('realized_price', axis=1))
print (out)
maturity_date simulation simulated_price
0 30/06/2010 1 0.539333
1 30/06/2010 2 0.544000
2 30/06/2010 3 0.789667
3 30/06/2010 4 0.190333
4 30/06/2010 5 0.413667
5 30/06/2010 realized_price 0.611000