How to convert multi-index columns to single index-column for pandas dataframe?-CodePudding

I have a multi-index dataframe columns that I have retrieved from Yahoo Finance and would like to convert it to a single-index columns table. Instead of having each symbol (SPY,AMZN) correspond to the attributes (high, low, close), I would need to have an extra column with symbol name. The new symbol column would have repeated symbol name as it shows the high/low/close for each trading date.

Below is a sample output that I would like to have.
Sample Output

Below is the multi-index dataframe that yahoo finance populate on that dataframe.
Multi-index dataframe

Here is the code to retrieve data from Yahoo Finance.

#Import libraries
from pandas_datareader import data 
import pandas as pd
import numpy as np

tickers = ['SPY','APPL','GOOGL'] 
start_date = '2020-01-01'
end_date = '2020-12-31'
panel_data = data.DataReader(tickers,'yahoo', start_date, end_date)

relv_data = panel_data[['High','Low','Close']]

#Inspect first 5 columns of pandas dataframe
print(relv_data.head())

#Display columns attributes
print(relv_data.columns)

Hope someone who is an expert with pandas dataframe could help me with this. Thank you!

CodePudding user response：

use df.groupby() to split each index and droplevel() for remove ticker labels

for name, data in relv_data.groupby(level=1, axis=1):
    print(f"----------{name}----------")
    data.columns = data.columns.droplevel(1)
    print(data)

you can concat their:

tickers_data = []
for ticker, data in relv_data.groupby(level=1, axis=1):
    data.columns = data.columns.droplevel(1)
    data = data.reset_index()
    data.insert(0, 'ticker', ticker)
    tickers_data.append(data)

result = pd.concat(tickers_data)