Python Pandas concatenate multiple data frames

Question

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

Not sure how to use pivot in pandas thats why i transformed it in excel — Rubrix
– Rubrix, Commented Nov 2, 2018 at 9:55

Franco Piccolo · Accepted Answer · 2018-11-02 10:42:21Z

3

Use melt like:

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

edited Nov 2, 2018 at 10:42

answered Nov 2, 2018 at 8:53

Franco Piccolo

7,4909 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rubrix Over a year ago

So would it be like this then ? main_df = main_df.melt(df, id_vars='Date', var_name='Ticker', value_name='Closed') instead of: main_df = main_df.join(df, how='outer')

Rubrix Over a year ago

TypeError: melt() got multiple values for argument 'id_vars'

Rubrix Over a year ago

The new code: codeshare.io/amDv7o Gives me this error: new = arr[tuple(slobj)] MemoryError

Collectives™ on Stack Overflow

Python Pandas concatenate multiple data frames

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related