How to merge two csv files with rows and columns merged in a single sheet and create a new single me-CodePudding

How to merge two csv files using python and create a merged csv file. I tried below code from available sources but was not able to attain my requirements.

def merge_csv():
    # reading csv files
    data1 = pd.read_csv('C:/folder/csv1.csv')
    data2 = pd.read_csv('C:/folder/csv2.csv')
    # using merge function by setting how='outer'
    output4 = pd.merge(data1, data2,
                       how='outer')
    print(output4)
    # I tried below code to create new csv but not working. I dont want to create a csv manually instead create automatically.
    output4.to_csv('C:\\folder\\merged.csv')
                   or
    with open('C:/folder/merged.csv', 'w ', newline='') as data:
         dict_writer = csv.DictWriter(data)
         dict_writer.writeheader()
         dict_writer.writerows(output4)

Finaly I am trying to merge both columns .

Examples:
csv1

   id  name
   1   abc

csv2

  dept   location
  cse     xyz

merged csv to be like

 id  name     dept   location
  1   abc      cse     xyz

CodePudding user response：

Outer product merges two csv on common columns. You cannot merge without anything in common. In fact you do not want to merge but append.

Convert your cvs files to DataFrames:

df1 = pd.read_csv('C:/folder/csv1.csv')
df2 = pd.read_csv('C:/folder/csv2.csv')

Append both columns in the first DataFrame:

a = df2["dept"]
df1 = df1.join(a)

b = df2["location"]
df1 = df1.join(b)

And then convert to CSV again (and zip if you want):

compression_opts = dict(method='zip',archive_name='out.csv')  
df1.to_csv('out.zip', index=False,compression=compression_opts)

CodePudding user response：

I'm not sure if I understand correctly your need but have you tried this solution:

import pandas as pd

data1 = pd.read_csv('csv1.csv')
data2 = pd.read_csv('csv2.csv')

output4 = pd.concat((data1, data2), axis='columns')
print(output4)
output4.to_csv('merge1.csv', index=False)

Or maybe this solution?

import csv

with open('csv1.csv', newline='') as data1_file:
    with open('csv2.csv', newline='') as data2_file:
        with open('merge2.csv', 'w', newline='') as data_file:
            data1 = csv.reader(data1_file)
            data2 = csv.reader(data2_file)
            data = csv.writer(data_file)
            
            data.writerows((data1_row[0], data1_row[1], data2_row[0], data2_row[1]) for (data1_row, data2_row) in zip(data1, data2))