Merging over csv files with Python

Question

I would like to combine multiple csv files into one csv file. The csv file are all in same format with 2 columns: 1.date are all with in the same period (1/12/2014-31/12/2019) 2.Adj Close (the column that I wanna combine with, like in the following format)

Excel file 1:

Date            Adj Close
1/12/2014       100
.....
31/12/2019      101

Excel file 2:

Date            Adj Close
1/12/2014       200
.....
31/12/2019      201

expected output:

Date            Adj Close    Adj Close
1/12/2014       100          200
.....
31/12/2019      101          201

Following is the current code I have:

import os
import glob
import pandas as pd
def concatenate(indir = "C:\\Users\\Nathan\\Desktop\\Stock Data",
                outfile = "C:\\Users\\Nathan\\Desktop\\Stock Data\\combinedata.csv"):
    os.chdir(indir)
    filelist = glob.glob("*.csv")
    dflist = []
    for filename in filelist:
        print(filename)
        df = pd.read_csv(filename,header= None)
        dflist.append(df)
    concatDf = pd.concat(dflist,axis = 1)
    concatDf.to_csv(outfile,index=None)

The output of current code is:

Date            Adj Close    Date          Adj Close
1/12/2014       100          1/12/2014     200
.....           ...          .....         ...
31/12/2019      101          31/12/2019    201

how can I get my expected output with over 200 files to combine? Thanks

If I am understanding correctly, you can use pandas merge to get the desired result. Use Date column as an argument for "on" and perform outer merge. It will be helpful if you can add some examples to work with — Mahendra Singh
– Mahendra Singh, Commented Apr 10, 2020 at 15:23
hi i just wonder if I have 200 files how do i do a pandas merge loop? — Nathan Chan
– Nathan Chan, Commented Apr 10, 2020 at 16:12
Just iter your dflist and do a df.merge. But the column names will have suffix (a lot of them). — Mahendra Singh
– Mahendra Singh, Commented Apr 11, 2020 at 5:07

gosuto · Accepted Answer · 2020-04-10 16:56:53Z

1

The argument axis=1 refers to row-wise concatenation (horizontal direction). Default is axis=0 which is vertical.

If you want to join them on the same index, use .join().

edited Apr 10, 2020 at 16:56

answered Apr 10, 2020 at 16:20

gosuto

5,8316 gold badges42 silver badges61 bronze badges

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

1 Comment

Nathan Chan Over a year ago

yes i figured it out but how could i do with the extra Date column from combined list?

Collectives™ on Stack Overflow

Merging over csv files with Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related