Combine multiple 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: date are all with in the same period, Adj Close is the column that I wanna combine

For example:

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

I want the output be like something like this:

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

I have browse stackoverflow posts and some youtube videos and found the code below:

import os
import glob
import pandas as pd
def concatenate(indir = "C:\\Users\\Nathan\\Desktop\\Stock Data",
                outfile = "C:\\Users\\Nathan\\Desktop\\Stock Data\\combinestockdata.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 = 0)
    concatDf.to_csv(outfile,index=None)

However, below is what i get in the combine file:

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

Instead of merging the list it simply adding file2 to file1, what should I do?

Search the web before asking something here, that's what I did to get my answer! It's not a big problem (I've done this before) :) — Suyash, Apr 10 '20 at 12:27
@FurkanÖyken ^ SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape >>> — Nathan Chan, Apr 10 '20 at 12:35
header= 0,encoding= 'unicode_escape' <------ add this text to the read_csv() function — Suyash, Apr 10 '20 at 12:45
@Suyash still the same SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape — Nathan Chan, Apr 10 '20 at 12:56
Does it answer your question? https://stackoverflow.com/questions/61087334/merging-two-csv-files-if-matched-column-then-concatenate-extra-columns/61087527 — Karl Olufsen, Apr 10 '20 at 13:26

Suyash · Answer 1 · 2020-04-10T13:23:38.403

0

Take a look at this stackoverflow answer or this free code camp article

For the huge edit made to the answer: concat along axis = 1

edited Apr 10 '20 at 13:23

answered Apr 10 '20 at 12:23

Suyash

375
6
18

this should be a comment not an answer. – Sahil Apr 10 '20 at 12:24
i have followed this free code camp article however SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape occured and I dont even know why – Nathan Chan Apr 10 '20 at 12:36
I got you, wait a minute. Also, were these files downloaded from the internet? and what os are you using? – Suyash Apr 10 '20 at 12:37
and I have checked the stackoverflow answer, it only merge two files. What if I want to merge 200 files? with each of the file name does not have any relationship(e.g file 1 name is tsla.csv and second one is GOOG.csv? where I cant loop through the list of files? – Nathan Chan Apr 10 '20 at 12:39
@Suyash yes they are downloaded from the internet. I am using windows 10 – Nathan Chan Apr 10 '20 at 12:40
@Suyash ok now it worked but instead of merging two list it is combing so it is after 31/12/2019 of file 1 data it continued with 1/12/2014 of file 2, instead of merging 2 csv file together group by date – Nathan Chan Apr 10 '20 at 13:01
@NathanChan could you explain that with a bit more detail. – Suyash Apr 10 '20 at 13:05
so what i am expecting the combine script to do is to copy the add one column after each merge( combine 2 files i get the column: Date, Adj close, (another) Adj close) yes? however the what the code does not adding any column but copied the whole file 2 of csv two to after file 1 data, you still have 2 columns only – Nathan Chan Apr 10 '20 at 13:10
@NathanChan As far as the current question is concerned, I have answered it... I suggest you to ask another question regarding this specific issue as I (or anyone else) need more detail to be able to see what exactly is going on in your code... – Suyash Apr 10 '20 at 13:17
@NathanChan Most welcome. Another suggestion, remove the latest edit to the question, so that anyone else with the same issue would be able to see it. Also, you may ''accept'' my answer by clicking on the tick below the answer as that helped you :) – Suyash Apr 10 '20 at 13:35

Combine multiple csv files with Python

1 Answers1