Merge Pandas sub dataframes in one sorted by date and add column symbol

Question

I have a number of csv files with stock data and i create a Pandas data frame with a for symbol in symbol list loop by providing the path and reading these files.. The data frame has each stock sub data frame separated..

sym_list = ['AAPL','TSLA','GOOG']


for symbol in sym_list:
    print(symbol)

    df = pd.read_csv(fr'--some path--{symbol}.csv')
    # this dataframe prints as separated sub frames for each symbol 
    # the data frame has Date,Open,High and similar Columns of data

I want to add a symbol column to each sub frame,

Merge the 3 sub frames into one continuous final_df ,

Sort the final_df by column ['Date'] .

if anyone can help with this it will be very appreciated! Regards

I have edited the question..the problem is that one has to have the csv files to see,but i thought that there might be a method that does the adding the column and another method for merging the sub frames..i already know that you can sort by column but the point is to create the new data frame by Date and have symbol name data to tell you what symbol the rows are about! — andypappy
– andypappy, Commented Apr 9, 2024 at 9:57

jezrael · Accepted Answer · 2024-04-09 10:05:13Z

0

Use concat with dict comprehension:

final_df = pd.concat({symbol: pd.read_csv(fr'--some path--{symbol}.csv')
                      for symbol in sym_list})

For new column remove MultiIndex:

final_df = (final_df.droplevel(1)
                    .rename_axis('sym')
                    .reset_index()
                    .sort_values('Date', igore_index=True))

Or append new column and join together in list comprehension, last sorting by Date:

final_df = (pd.concat([pd.read_csv(fr'--some path--{symbol}.csv').assign(sym=symbol) 
                        for symbol in sym_list])).sort_values('Date', igore_index=True)

Your solution should be changed with create liest of DataFrames:

dfs = []
for symbol in sym_list:
    print(symbol)

    df = pd.read_csv(fr'--some path--{symbol}.csv').assign(sym=symbol)
    dfs.append(df)
    
final_df = pd.concat(dfs).sort_values('Date', igore_index=True)

edited Apr 9, 2024 at 10:05

answered Apr 9, 2024 at 9:53

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

andypappy Over a year ago

after some code on the initial dataframe df i create another dataframe named trades_df which is also separated by symbol name in three sub frames..each sub frame though has different number of rows(length) ..how can i merge all the sub frames into one?

jezrael Over a year ago

@andypappy - Is possible use out = pd.concat([df1, df2, df3]) ?

Collectives™ on Stack Overflow

Merge Pandas sub dataframes in one sorted by date and add column symbol

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related