Write output from for loop to a csv in python

Question

I am opening a csv called Remarks_Drug.csv which contains product names and mapped filenames in consecutive columns. I am doing some operations on the product column to remove all string content after + character. After stripping the string from + characters, I am storing the result in a variable called product_patterns.

Now I am opening a new csv and I want to write the output from the for loop into two columns, the first one containing the product_patterns and the second one containing the corresponding filenames.

What I am getting as output now is only the last row of the output csv that I am looking for. I think I am not looping properly so that each row of product_patterns and filename gets appended in the output csv file.

Can someone please help me with this.

Attaching code below:

import csv


with open('Remarks_Drug.csv', newline='', encoding ='utf-8') as myFile:
    reader = csv.reader(myFile)
    for row in reader:
        product = row[0].lower()
        #print('K---'+ product)
        filename = row[1]
        product_patterns = ', '.join([i.split("+")[0].strip() for i in product.split(",")])


        #print(product_patterns, filename)

    with open ('drug_output100.csv', 'a') as csvfile:
        fieldnames = ['product_patterns', 'filename']
        print(fieldnames)
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
        print(writer)
        #writer.writeheader()
        writer.writerow({'product_patterns':product_patterns, 'filename':filename})

Sample input:

    Film-coated tablet + TERIFLUNOMIDE, 2011-07-18 - Received approval letter_EN.txt
    Film-coated tablet + VANDETANIB,             2013-12-14 RECD Eudralink_Caprelsa II-28 - RSI - 14.12.2017.txt
    Solution for injection + MenQuadTT, 395_EU001930-PIP01-16_2016-02-22.txt
    Solution for injection + INSULIN GLARGINE,  2017-11-4 Updated PR.txt
    Solution for injection + INSULIN GLARGINE + LIXISENATIDE,   2017 12 12 Email Approval Texts - SA1006-.txt

Holtz

Can you please post example data? – Taciturnity

csv.DictWriter uses a dictionary for each row (with keys being the field names and values being the value for the corresponding cell), and you've only written one row. – Coparcenary

Yeah, I am unable to iterate over it – Holtz

Added example data in existing code. – Holtz

Because, you have a list and not dictionary – Taciturnity

Correct. Can you please suggest the code changes – Holtz

Can you share the example data as text and not as image please? – Whirlabout

Shared example input data. Please check – Holtz

Thanks, the data are divided by the space char or by the comma? – Whirlabout

It's a csv, I have copied the text from excel. – Holtz

open with some txt editor please, and paste the data. Not from excel, or I can't know what char it use as delimiter – Whirlabout

Please check now – Holtz

Ok, thanks, just a couple of minutes – Whirlabout

Answer 1

I hope this is the right way for you, if is not, tell me and we check.

import csv

with open('Remarks_Drug.csv') as myFile:
    reader = csv.reader(myFile)
    products_list = list()
    filenames_list = list()

    for row in reader:
        products_list.append(row[0].lower().split("+")[0].strip())
        filenames_list.append(row[1])

    for index, product in enumerate(products_list):
        with open ('drug_output100.csv', 'a') as csvfile:
            fieldnames = ['product_patterns', 'filename']
            print(fieldnames)
            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
            print(writer)
            writer.writerow({'product_patterns':product, 'filename':filenames_list[index]})

1. Open the Remarks_Drug.csv file and create two list where store the row value elaborated as you prefer.
2. Iterate on the product list and enumerate it so you have an index to use on the filename list.
3. Open the output file and append to it the result.

You can also use pandas to elaborate csv files, faster and in a smart way.

Here the pandas solution:

import pandas as pd

def select_real_product(string_to_elaborate):
    return string_to_elaborate.split('+')[0].strip()

df = pd.read_csv("Remarks_Drug.csv", delimiter=',', names=("product", "filename"))

df['product'] = df['product'].apply(select_real_product)

df.to_csv("drug_output100.csv", sep=',', na_rep='empty',index_label=False, index=False)

Whirlabout

Works perfect. Thanks a lot. – Holtz

Check also the pandas solution. I think is better and working with pandas is fun. You can do more with pandas and csv file. – Whirlabout

Thanks. Sure, I will check and clarify with you if I have got any doubts – Holtz

Ok well, I think that pandas is the best solution, for smallest files and also biggest files. – Whirlabout

What's happend? Why you have removed my answer as correct? – Whirlabout

Sorry, I thought I could tick more than 1, so it got removed for your answer. I hv corrected it. – Holtz

Answer 2

import csv
import pandas as pd

with open('Remarks_Drug.csv', newline='', encoding ='utf-8') as myFile:
    reader = csv.reader(myFile)
    mydrug = []
    for row in reader:
        product = row[0].lower()
        #print('K---'+ product)
        filename = row[1]
        product_patterns = ', '.join([i.split("+")[0].strip() for i in product.split(",")])
        mydrug.append([product_patterns, filename])

#     print(mydrug)

    df = pd.DataFrame(mydrug, columns=['product_patterns', 'filename'])
    print(df)
    df.to_csv('drug_output100.csv', sep=',', index=False)

This utilizes pandas library. If you're to deal with large csv files using pandas will be handy and efficient in terms of performance and memory. This is just an alternative solution for the above.

Taciturnity