Compare two csv rows at a time python create new file-CodePudding

How do you append price2 to a new object
and create a new csv with python?

first csv file
date, name, qt, price1
9/12/22, AB, 2, 5.00
9/12/22, AB, 2, 5.08
9/12/22, BC, 1, 2.00
9/12/22, BC, 1, 2.03

new csv
date, name, qt, price1, price2
9/12/22, AB, 2, 5.00, 5.08
9/12/22, BC, 1, 2.00, 2.03

import csv

data = []

with open('test.csv', 'r') as f:
    reader = csv.reader(f)
    for row in reader:
        data.append({'date': row[0],  'qt': row[3], 'name': row[5], 'price':  row[10]})

    #data.append(myClass(row[0], row[2], row[3],  row[5],  row[10]))

for x in data:
    print(x)

Adrian's Answer is missing:
import csv
#newline requires python 3
csv must be UTF-8 and headers must match

CodePudding user response：

cat price_old.csv                                                                                                                                                         
date,name,qt,price1
9/12/22,AB,2,5.00
9/12/22,AB,2,5.08
9/12/22,BC,1,2.00
9/12/22,BC,1,2.03 

with open("price_old.csv") as old_csv:
    new_list = []
    dt, nm = None, None
    c_reader = csv.DictReader(old_csv)
    for row in c_reader:
        if row['date'] != dt and row['name'] != nm:
            dt, nm = row['date'], row['name']
            price_list = [row['price1']]
        else:
            price_list.append(row['price1'])
            row.update({'price1': price_list[0], 'price2': price_list[1]})
            new_list.append(row)
            price_list = []
            dt, nm = None, None
    with open('price_new.csv', 'w', newline='') as new_csv:
        c_writer = csv.DictWriter(new_csv, fieldnames= ['date', 'name','qt', 'price1', 'price2'])
        c_writer.writeheader()
        c_writer.writerows(new_list)

cat price_new.csv

date,name,qt,price1,price2
9/12/22,AB,2,5.00,5.08
9/12/22,BC,1,2.00,2.03

CodePudding user response：

Using pandas will make your life easier.

import pandas as pd

df = pd.read_csv('test.csv', delimiter=', ')

# Group by the first 3 columns, and use the rows to form the columns (unstack)
df = df.groupby(['date','name','qt'])['price1'].apply(
    lambda df: df.reset_index(drop=True)).unstack().reset_index()

# Rename the columns
df.columns = [*df.columns[:-2], 'price1', 'price2']

df.to_csv('output.csv', sep=',', index=False)

The output.csv is:

date,name,qt,price1,price2
9/12/22,AB,2,5.0,5.08
9/12/22,BC,1,2.0,2.03