Merge several .csv into one csv in python

Question

Good evening,

So I have a huge amount of .csvs which I either want to change in one giant csv before reading it with pandas, or directly creating a df with all the .csvs in it. The .csvs all have two columns "timestamp" and "holdings". Now I want to merge them on the "timestamp"-column if they match with each other and create a new column for each "holdings"-column. So far I produced this:

import os
import glob
import pandas as pd

os.chdir("C/USer....")
extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

dfs = [pd.read_csv(f, index_col=[0], parse_dates=[0])
        for f in os.listdir(os.getcwd()) if f.endswith('csv')]

The output is a list with dfs. How do I merge them on "timestamp" column now? I tried to concate and merge already, but it always puts them in a single column.

do all files have the same columns? can you post print(*dfs[:2], sep='\n\n')? — RichieV
– RichieV, Commented Aug 4, 2020 at 20:58
yes, they unfortunately have the same column, with the same name... Output looks like this; users_holding timestamp 2018-05-02 04:54:46 158 2018-05-02 06:39:57 158 2018-05-03 00:36:38 158 2018-05-03 06:35:01 158 2018-05-03 06:50:00 158 ... — Djerun
– Djerun, Commented Aug 4, 2020 at 21:02
you can edit your questions to include the dfs, it will help you get better answers — RichieV
– RichieV, Commented Aug 4, 2020 at 21:07

Python on Toast · Accepted Answer · 2020-08-04 21:02:53Z

1

What you are looking for is an outer join between the dataframes. Since the pandas merge function only operates between two dataframes, we need to loop over each dataframe and merge them individually. We can use the reduce iterator from functools to do this cleanly in one line:

import pandas as pd
from functools import reduce

df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['timestamp'],
                                        how='outer'), dfs)

Use the suffixes argument in the merge function to clean up your column headings.

answered Aug 4, 2020 at 21:02

Python on Toast

2741 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Merge several .csv into one csv in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related