CODEWITHSUNDEEP
pythoncsv
CodeOrDie
CodeOrDie

Reputation: 41

Joining the columns of one CSV to another CSV

So I'm trying to combine column values from one csv to another while saving it into a final csv file. But I want to iterate through all the rows adding the column values of each row to each row of the original csv.

In other words say csv1 has 3 rows. 

Row 1: Frog,Rat,Duck
Row 2: Cat,Dog,Cow
Row 3: Moose,Fox,Zebra

And I want to combine 2 more column values from csv2 to each of those rows. 

Row 1: Chicken,Pig
Row 2: 
Row 3: Bear,Boar

So csv3 would end up looking like.

Row 1: Frog,Rat,Duck,Chicken,Pig
Row 2: Moose,Fox,Zebra,Bear,Boar

But at the same time if there's a row in csv2 that has no values at all I don't want it to copy the row from csv1. In other words that row will not exist at all in the final csv file. I prefer not to use pandas as I have just been using the csv module thus far throughout my code but any method is appreciated.

So far I have come across this method which works if there's only one single row. But when there's more than that it just adds random lines and appends the values all over the place. And it combines both of the columns into one string while adding an extra blank line at the end of the csv for some odd reason.

import csv

f1 = open ("2.csv","r", encoding='utf-8')

with open("3.csv","w", encoding='utf-8', newline='') as f:
    writer = csv.writer(f)
    with open("1.csv","r", encoding='utf-8') as csvfile:
        reader = csv.reader(csvfile, delimiter=",")
        for row in reader:
            row[6] = f1.readline()
            writer.writerow(row)

f1.close()

Using the same example csvs above the results given are.

Frog,Rat,Duck,Chicken,Pig 
Cat,Dog,Cow
Moose,Fox,Zebra,Bear,Boar

Upvotes: 0

Views: 511

Answers (1)

gawdn
gawdn

Reputation: 125

You can zip together the two files and then iterate through each row. Then you can concatenate the two lists and write the result to a file.

To check if there is an empty row we can compare the set of the row to the set of an empty string.

import csv

new_csv_data = []
EMPTY_ROW = set([""])


with open("1.csv", "r", newline="") as first_file, open("2.csv", "r", newline="") as second_file, open("3.csv", "w", newline="") as out_file:
    first_file_reader = csv.reader(first_file)
    second_file_reader = csv.reader(second_file)
    out_file_writer = csv.writer(out_file)

    # The iterator will stop when the shortest file is finished
    for row_1, row_2 in zip(first_file_reader, second_file_reader):
        # Check if the second row is empty, skipping if it is
        if not row_2 or set(row_2) == EMPTY_ROW:
            continue

        out_file_writer.writerow(row_1 + row_2)

Upvotes: 1

Related Questions