I am SO STUCK and its driving me mad. I have a CSV like so:
For some orders, there is duplicate amounts of product_charges and product_taxes - and I just dont know how to handle these...
I want my output to be like:
Quote:date, order_id, sku, transaction_type, payment_type, payment_detail, amount, quantity
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Amazon fees Commission £-5.82
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Amazon fees FBA fulfilment fee per unit £-7.73
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Product charges £40.42 1
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Promo rebates £0.00 1
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Other Product Tax £8.08
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Amazon fees Commission £-5.82
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Amazon fees FBA fulfilment fee per unit £-7.73
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Product charges £40.42 1
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Promo rebates £0.00 1
20 Nov 2019 026-8286010-8145911 P6-VZ65-2FQT Order Payment Other Product Tax £8.08
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Amazon fees Commission £-8.58
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Amazon fees FBA fulfilment fee per unit £-5.97
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Amazon fees Shipping chargeback £-0.93
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Product charges £59.58 1
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Other Product Tax £11.92
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Other Shipping tax £0.19
19 Nov 2019 206-3581742-6918727 N4-AMGB-L9HS Order Payment Other Shipping £0.93
1
import csv order_ids = [] info = [] with open('report6.csv', mode='r', encoding='utf-8') as infile: product_charges = "" product_tax = "" reader = csv.reader(infile) data = [rows for rows in reader] skipped = data[4:] for i in skipped: order_id = i[1] if order_id in order_ids: sku = i[2] transaction_type = i[3] payment_type = i[4] if payment_type == "Product charges": product_charges = i[6] product_charges = product_charges.replace("£", "") payment_detail = i[5] if payment_detail == "Product Tax": product_tax = i[6] product_tax = product_tax.replace("£", "") format = [order_id, [product_charges, product_tax]] info.append(format) continue else: order_ids.append(order_id) sku = i[2] transaction_type = i[3] payment_type = i[4] if payment_type == "Product charges": product_charges = i[6] product_charges = product_charges.replace("£", "") payment_detail = i[5] if payment_detail == "Product Tax": product_tax = i[6] product_tax = product_tax.replace("£", "") format = [order_id, [product_charges, product_tax]] info.append(format) continue for a in range(0, 1, len(order_ids)): for b in info: while order_ids[a] == b[0]: a +=1And all the data per order_id is on a separate row, and I am trying to get it all on the same row - in particular, the product_charges and product_tax.
For some orders, there is duplicate amounts of product_charges and product_taxes - and I just dont know how to handle these...
I want my output to be like:
Output:026-8286010-8145911, 80.82, 16.16
206-3581742-6918727, 59.58, 11.92
Any help would be immensely appreciated!