CODEWITHSUNDEEP
pythonpandasdataframe
Rubrix
Rubrix

Reputation: 216

Python Pandas concatenate multiple data frames

I am following a Python for Finance tutorial where you are combining all the S&P500 stocks into one data frame, i. e. outer joining all the stored CSV files for all the different stocks.

current output:sp500

The code for this looks like this:

def compile_data():
with open("sp500tickers.pickle", "rb") as f:
    tickers = Cpickle.load(f)

main_df = pd.DataFrame()

for count, ticker in enumerate(tickers):
    df = pd.read_csv('stock_dfs/{}.csv'.format(ticker))
    df.set_index('Date', inplace=True)

    df.rename(columns={'Adj Close': ticker}, inplace=True)
    df.drop(['Open', 'High', 'Low', 'Close', 'Volume'], 1, inplace=True)

    if main_df.empty:
        main_df = df
    else:
        main_df = main_df.join(df, how='outer')


    if count % 10 == 0:
        print(count)
print(main_df.head())
main_df.to_csv('sp500_joined_closes.csv')

compile_data()

Ideally I would like the data frames to be joined/concatenated like this: Desired output

All ideas and tips are greatly appreciated.

Best regards, Rubrix

Upvotes: 1

Views: 390

Answers (1)

Franco Piccolo
Franco Piccolo

Reputation: 7410

Use melt like:

pd.melt(df, id_vars=['Date'], var_name='Ticker', value_name='Closed')

Upvotes: 3

Related Questions