CODEWITHSUNDEEP
pythonpython-2.7csvpandasglob
john doe
john doe

Reputation: 2253

How to concatenate for the right side in one file all the .csv files of a directory with python?

I have a folder with .csv files all the files have the same ids but different contet, like this:

File one:

id, content
jdhfs_SDGSD_9403, bla bla bla bla
aadaaSDFDS__ASdas_asad_342, bla bla
...
asdkjASDAS_asdasSFSF_sdf, bla bla

File two:

id, content
jdhfs_SDGSD_9403, string string string
aadaaSDFDS__ASdas_asad_342, string string string
...
asdkjASDAS_asdasSFSF_sdf, string string string

I would like to leave the id column but merge in one new file the content, something like this(i.e. generate a new file):

id, content
jdhfs_SDGSD_9403, bla bla bla bla string string string
aadaaSDFDS__ASdas_asad_342, bla bla string string string
...
asdkjASDAS_asdasSFSF_sdf, bla bla string string string

This is what I tried:

from itertools import izip_longest
with open('path/file1.csv', 'w') as res, \
        open('/path/file1.csv') as f1,\
        open('path/file1.csv') as f2:
    for line1, line2 in izip_longest(f1, f2, fillvalue=""):
        res.write("{} {}".format(line1.rstrip(), line2))

The problem with this is that is merging everthing in one line. Any idea of how to do this in a more pythonic way?.

Edit:

import pandas as pd

df1= pd.read_csv('path/file1.csv')
df2=pd.read_csv('path/file2.csv')    

new_df = pd.concat([df1, df2], axis=1)
print new_df


new_df.to_csv('/path/new.csv')

Then the header was merged like this:

,id,content,id,content

And the content like this:

0jdhfs_SDGSD_9403, bla bla bla bla jdhfs_SDGSD_9403, string string string.

How can I get something like this?:

jdhfs_SDGSD_9403, bla bla bla bla string string string

Without the index number of the dataframe?.

Upvotes: 0

Views: 238

Answers (2)

horns
horns

Reputation: 1933

Use the csv standard python module

i.e.

import csv

with open(filename1) as file1, open(filename2) as file2, open(newname, "w") as newfile:
    csv1 = csv.reader(file1)
    csv2 = csv.reader(file2)
    newcsv = csv.writer(newfile)

    header = next(csv1)
    next(csv2) # Skip the header

    newcsv.writerow(header)

    for row1, row2 in zip(csv1, csv2):
        id, content1 = row1
        id, content2 = row2
        newcsv.writerow((id, " ".join((content1, content2))))

Upvotes: 1

Liam Foley
Liam Foley

Reputation: 7822

read the csvs's in using pd.read_csv(FILE)

Then do this:

import pandas as pd
pd.concat([df1, df2], axis=1)

Or merge them (pd.merge())

See this question:

Combine two Pandas dataframes with the same index

Upvotes: 1

Related Questions