csv file data appending using python

Question

I've been trying to append certain data from input.csv to output.csv using python.

My code is as under:


    import csv
    from csv import writer
    from csv import reader
    
    
    csvPath = r'C:\Users\Nitin Kumar\Downloads\annual-enterprise-survey-2020-csv-new.csv'
    csvWrite = r'C:\Users\Nitin Kumar\Downloads\copied.csv'
    
    rslt=[]
    with open(csvPath, encoding='utf-8-sig') as csvfile:
    reader = csv.reader(csvfile)

    count=0
    fsa=[]
    for row in reader:
        count=count+1
        print(row)
        rslt.append(row)
        # if count>20:
        #     break
print(rslt)

with open(csvPath, 'r') as read_obj, \
        open(csvWrite, 'a', newline='') as write_obj:
    csv_reader = csv.reader(read_obj)
    csv_writer = csv.writer(write_obj)
    for row_ in csv_reader:
        row_.append(rslt)
        csv_writer.writerow(row_)

The input csv file is:


    Year,Industry_agg,Industry_code,Industry_name,Units,Variable_code,Variable_name,Variable_category,Value,Industry_code_ANZSIC06
    2020,Level 1,99999,All industries,Dollars (millions),H01,Total income,Financial performance,"7,33,258",ANZSIC06 divisions A-S 
    2020,Level 1,99999,All industries,Dollars (millions),H04,Sales,Financial performance,"6,60,630",ANZSIC06 divisions A-S
    2020,Level 1,99999,All industries,Dollars (millions),H05,"Interest, dividend",Financial performance,"54,342",ANZSIC06 divisions A-S 
    2020,Level 1,99999,All industries,Dollars (millions),H07,Non-operating income,Financial performance,"18,285",ANZSIC06 divisions A-S
    2020,Level 1,99999,All industries,Dollars (millions),H08,Total expenditure,Financial performance,"6,54,872",ANZSIC06 divisions A-S

The output file I'm getting is:

    Name
    Nitin
    Nitin1
    Nitin2
    Nitin3
    Nitin4
    Nitin5
    Year,Industry_agg,Industry_code,Industry_name,Units,Variable_code,Variable_name,Variable_category,Value,Industry_code_ANZSIC06,[]
    2020,Level 1,99999,All industries,Dollars (millions),H01,Total income,Financial performance,"7,33,258",ANZSIC06 divisions A-S ,[]
    2020,Level 1,99999,All industries,Dollars (millions),H04,Sales,Financial performance,"6,60,630",ANZSIC06 divisions A-S,[]
    2020,Level 1,99999,All industries,Dollars (millions),H05,"Interest, dividend",Financial performance,"54,342",ANZSIC06 divisions A-S ,[]
    2020,Level 1,99999,All industries,Dollars (millions),H07,Non-operating income,Financial performance,"18,285",ANZSIC06 divisions A-S,[]
    2020,Level 1,99999,All industries,Dollars (millions),H08,Total expenditure,Financial performance,"6,54,872",ANZSIC06 divisions A-S ,[]

However, the output csv file was having following data previously before appending data:


    Name
    Nitin
    Nitin1
    Nitin2
    Nitin3
    Nitin4
    Nitin5

I need the output file to be like below:


    Name,Year,Industry_agg,Industry_code,Industry_name,Units,Variable_code,Variable_name,Variable_category,Value,Industry_code_ANZSIC06
    Nitin,2020,Level 1,99999,All industries,Dollars (millions),H01,Total income,Financial performance,"7,33,258",ANZSIC06 divisions A-S 
    Nitin1,2020,Level 1,99999,All industries,Dollars (millions),H04,Sales,Financial performance,"6,60,630",ANZSIC06 divisions A-S
    Nitin2,2020,Level 1,99999,All industries,Dollars (millions),H05,"Interest, dividend",Financial performance,"54,342",ANZSIC06 divisions A-S 
    Nitin3,2020,Level 1,99999,All industries,Dollars (millions),H07,Non-operating income,Financial performance,"18,285",ANZSIC06 divisions A-S
    Nitin4,2020,Level 1,99999,All industries,Dollars (millions),H08,Total expenditure,Financial performance,"6,54,872",ANZSIC06 divisions A-S
    Nitin5

I need my output file to append the input data but not able to get the desired result. Previously I was even able to add header in outout csv file. Gone through lot of codes already present but none of them helped me in this.

Do you just want to copy the csv? – Cow May 20 '22 at 06:06 — Cow, May 20 '22 at 06:06

md9821 · Answer 1 · 2022-05-20T07:08:56.413

0

Combine the lines of the input and output files, and overwrite the output file with the new lines.

# read input file
with open(input_filename, 'r') as input_file:
    input_lines = input_file.read().split('\n')
    input_lines.append('') # because your input file has 
                           # one less line than your output file
    
# read output file
with open(output_filename, 'r') as output_file:
    output_lines = output_file.read().split()

# combine lines of input and output files
new_lines = [output_line + ',' + input_line for 
             input_line, output_line in zip(input_lines, output_lines)]

# overwrite output file with new lines
with open(output_filename, 'w') as output_file:
    for i, line in enumerate(new_lines):
        end = '\n' if i + 1 < len(new_lines) else '' # make new line except 
                                                     # after last line
        output_file.write(line + end)

edited May 20 '22 at 07:08

answered May 20 '22 at 07:03

md9821

1
2

Thanks @md9821, it worked exactly how I wanted. But can you explain the last part of code where overwriting part is there? I'm confused in there. – Nitin Kumar May 20 '22 at 09:03
It worked but somehow, for some diff csv, it's messing up everything. I don't know what's the reason. – Nitin Kumar May 20 '22 at 10:52
@NitinKumar The overwriting part goes as follows. For each line of the output file, we write the new line (i.e., the combination of lines from the original input and output files) and the appropriate ending (i.e., '\n' if the line is not the last line). – md9821 May 21 '22 at 06:54

Martin Evans · Answer 2 · 2022-05-20T10:54:59.193

0

You might find it better to use Python's zip_longest() function which is able to easily deal with lists of different lengths.

Try the following:

import csv
from itertools import zip_longest

csvPath = r'C:\Users\Nitin Kumar\Downloads\annual-enterprise-survey-2020-csv-new.csv'
csvWrite = r'C:\Users\Nitin Kumar\Downloads\copied.csv'

# Read all the existing entries
existing = []       # Rows holding: Name, Nitin, Nitin1

with open(csvWrite, encoding='utf-8-sig') as f_existing:
    existing = list(csv.reader(f_existing))

# Read the new entries and combine with the existing entries
with open(csvPath, 'r') as f_input, \
    open(csvWrite, 'w', newline='') as f_output:
    
    csv_input = csv.reader(f_input)
    csv_output = csv.writer(f_output)
    
    for row_existing, row_new in zip_longest(existing, csv_input, fillvalue=''):
        csv_output.writerow([*row_existing, *row_new])

The last line takes all elements from an existing row and combines them with all elements from the new row to create a combined output list row.

edited May 20 '22 at 10:54

answered May 20 '22 at 09:09

Martin Evans

45,791
17
81
97

This will not run if we've more than one column in the beginning. Like: ``` Name,age Nitin,3 Nitin1,5 Nitin2,6 Nitin3,2 Nitin4,3 Nitin5,8 ``` – Nitin Kumar May 20 '22 at 10:39
It worked when I tested it? Are your existing entries on one row? – Martin Evans May 20 '22 at 10:42
When I tried with multiple columns, like Name as well as age, it deleted the age column. I want all of the columns to be as it is. – Nitin Kumar May 20 '22 at 10:50
Your example only has one column, to keep all existing columns change to `*row_existing` – Martin Evans May 20 '22 at 10:52
Kindly update the code. Or help me in understanding where to use that argument. – Nitin Kumar May 20 '22 at 11:00
It was updated, you need to refresh. If you click on `edited..mins ago` link you will be able to see where the changes were made. – Martin Evans May 20 '22 at 11:01
Thanks, just ran the updated & working fine now for even 0 columns/rows. – Nitin Kumar May 20 '22 at 11:12
Great, glad it works. Don't forget to click on the grey tick mark under the up/down buttons to select an answer as the accepted solution when you're done – Martin Evans May 20 '22 at 11:13

csv file data appending using python

2 Answers2