Hello I am trying to group my csv file only using the csv module and input the result in another csv file. The problem that I am having is that the other fields get appended to the first field which is manufacturer and I want this to be done in the correct fields. in row[3] the car_details are saved and in row[4] the car. import csv

result = {}
fields=['test','name','object']

with open('work.csv', 'r') as csvfile:
    csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
    writer = csv.writer(open("art1.csv",'w',newline=''), delimiter=';')
    for row in csvreader:
        if row[0] in result:
          result[row[0]].append(row[3])
        else:
            result[row[0]] = [row[1]]
with open('art1.csv', 'w') as csvfile:
   # writer.writerow(fields)
    csvwriter = csv.writer(csvfile)
    for row in result.items():
        csvwriter.writerow(row)

reader = csv.reader(open("art1.csv", "r",newline=''), delimiter=',')
writer = csv.writer(open("artgain2.csv",'w',newline=''), delimiter=';')
writer.writerow(fields)
writer.writerows(reader)

the csv file:

toyota         fast           Prius
toyota         slow           yaris
toyota         fast           corolla
toyota         slow           sunshine
mercedes       fast           benz
mercedes       fast           something
mazda          fast           bus

the result I want

manufacturer   car_details    car
toyota         fast           Prius
               slow           yaris
               fast           corolla
               slow           sunshine
mercedes       fast           benz
               fast           something
mazda          fast           bus

the result I get:

manufacturer                      car_details    car
toyota,fast,Priusslow,yaris,fast,corolla,slow,sunshine,
mercedes,fast,benz,fast,something
mazda,fast,bus

Everything is in the manufacturer column and I dont know how to split this correctly in the corresponding fields I feel that I am pretty close to the solution and I am probably forgetting something Can someone help me out ?

CodePudding user response：

I'm assuming you open your .CSV files with MS excel and you have a European language setting? The reason for this assumption is, that you seem to use ";" as separator in your output file. So it seems reasonable that excel won't interpret your "," in your result files as separators. Additionally, it seems like you have no proper line breaks in your result file.

In all your files, I can't see any row with index 3 or 4. This seems to bee to high an index...

Proposed solution

import csv

class ExcelEuropeDialect(csv.Dialect):
        delimiter      = ';'
        lineterminator = '\r\n'
        quotechar      = '"'
        quoting        = csv.QUOTE_MINIMAL     
csv.register_dialect('excel_eu', ExcelEuropeDialect)

result = []
item_occured = [] # list with all items already occured
fields=['test','name','object']

with open('work.csv', 'r', newline='') as csvfile:
    csvreader = csv.reader(csvfile, dialect='excel')
    for row in csvreader:
        if row[0] in item_occured:
            row[0] = ''                     # empty first cell
        else:
            item_occured.append(row[0]) # add item to item_occured
        row = [s.strip() for s in row]
        result.append(row)                  # append whole row
with open('art1.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile, dialect=csv.get_dialect('excel_eu'))
    csvwriter.writerow(fields)
    csvwriter.writerows(result)