Styjun

I have a csv like the following

SKU;price;availability;Title;Supplier
SUV500;21,50 €;1;27-03-2019 14:46;supplier1
MZ-76E;5,50 €;1;27-03-2019 14:46;supplier1
SUV500;49,95 €;0;27-03-2019 14:46;supplier2
MZ-76E;71,25 €;0;27-03-2019 14:46;supplier2
SUV500;32,60 €;1;27-03-2019 14:46;supplier3

I am trying to get as an output a csv that will have the following

SKU;price;availability;Title;Supplier
SUV500;21,50 €;1;27-03-2019 14:46;supplier1
MZ-76E;5,50 €;1;27-03-2019 14:46;supplier1

Where for each SKU I want to get only the record in which the price is the minimum

How can I do it because I am totally lost with pandas? with classical if for? with lists?sets?

Any ideas?

A non pandas solution, this could get your required output.

EDIT: Added csv writer to solution

EDIT: only accept records with '1' in at row[2]

from collections import defaultdict
import re
from operator import itemgetter
import csv

fin = open('SKU_csv.csv', 'r', encoding="utf8")
csv_reader = csv.reader(fin, delimiter=';')

fout = open('test_out.csv', 'w', newline = '')
csv_writer = csv.writer(fout, delimiter=';')

csv_writer.writerow(next(csv_reader)) # print header

d = defaultdict(list)

for row in csv_reader:
 if int(row[2]) != 1:
 continue
 key = row[0]
 val = row[1].replace(',', '.')
 price = float(re.search('d+.d+', val).group(0))
 d[key].append([row, price])

fin.close()

for arr in d.values():
 minimum, _ = min(arr, key=itemgetter(1)) # minimum price (at arr idx 1)
 csv_writer.writerow(minimum)

fout.close()


'''
*** test_out.csv contents

SKU;price;availability;Title;Supplier
SUV500;21,50 €;1;27-03-2019 14:46;supplier1
MZ-76E;5,50 €;1;27-03-2019 14:46;supplier1
'''

In pandas you can do the following

import pandas as pd

df= pd.read_csv('your file')

As andy pointed out below this returns only the price and SKU columns

df_reduced= df.groupby('SKU')['price'].min()

for all the columns you can change the groupby to a list of all the columns you want to keep

df_reduced= df.groupby(['SKU', 'availability', 'Title', 'Supplier'])['price'].min()

Edited: taking out the previous confusing assumption

After reading from csv file

In [8]: df = pd.read_csv(filename, delimiter=';', encoding='utf-8')

In [9]: df
Out[9]:
 SKU price availability Title Supplier
0 SUV500 21,50 € 1 27-03-2019 14:46 supplier1
1 MZ-76E 5,50 € 1 27-03-2019 14:46 supplier1
2 SUV500 49,95 € 0 27-03-2019 14:46 supplier2
3 MZ-76E 71,25 € 0 27-03-2019 14:46 supplier2
4 SUV500 32,60 € 1 27-03-2019 14:46 supplier3 

Add a new columns to hold the float value of price

In [12]: df['f_price'] = df['price'].str.extract(r'([+-]?d+,d+)', expand=False).str.replace(',', '.').astype(float)
#Note: if your locality using denotion `,` for decimal point, you don't need additional `str.replace`. Just use below
#df['f_price'] = df['price'].str.extract(r'([+-]?d+,d+)', expand=True).astype(float)

In [13]: df
Out[13]:
 SKU price availability Title Supplier f_price
0 SUV500 21,50 € 1 27-03-2019 14:46 supplier1 21.50
1 MZ-76E 5,50 € 1 27-03-2019 14:46 supplier1 5.50
2 SUV500 49,95 € 0 27-03-2019 14:46 supplier2 49.95
3 MZ-76E 71,25 € 0 27-03-2019 14:46 supplier2 71.25
4 SUV500 32,60 € 1 27-03-2019 14:46 supplier3 32.60 

Get the list of min(f_price) per group from groupby

In [28]: idxmin_list = df.groupby('SKU', as_index=False)['f_price'].idxmin().tolist()

In [29]: idxmin_list
Out[29]: [1, 0] 

Finally, passing idxmin_list to df and drop f_price column to get the final result

In [33]: df_final = df.loc[idxmin_list].drop('f_price', 1)

In [34]: df_final
Out[34]:
 SKU price availability Title Supplier
1 MZ-76E 5,50 € 1 27-03-2019 14:46 supplier1
0 SUV500 21,50 € 1 27-03-2019 14:46 supplier1 

Write to csv file

In [65]: df_final.to_csv('Sku_min.csv', sep=';', index=False) 

File Sku_min.csv is created in your working folder and its content is

SKU;price;availability;Title;Supplier
MZ-76E;5,50 €;1;27-03-2019 14:46;supplier1
SUV500;21,50 €;1;27-03-2019 14:46;supplier1 

There's no real need to use pandas here. This may not be the optimal solution but it'd be mine:

import csv

class Product:
 def __init__(self, sku, price, availability, title, supplier):
 self.sku = sku
 self.price = float(price.replace(',', '.')[:-2]) # allows sorting 
 self.availability = availability
 self.title = title
 self.supplier = supplier

unparsed_products = []

with open('name_of_csv.csv', 'r') as csvfile:
 csv_reader = csv.reader(csvfile, delimiter=';')
 next(csv_reader) # to skip past header line when parsing.
 for row in csv_reader:
 p = Product(*row)
 unparsed_products.append(p)

suv500_products = [i for i in unparsed_products if i.sku == 'SUV500']
lowest_priced_suv500_product = sorted(suv500_products, key=lambda x: x.price, reverse=True)[0] # gets the first entry from the sorted list of suv500_products
print(lowest_priced_suv500_product.price)
>>> 21.50

You could easily extend this out to cover other products by changing the value of X in if i.sku == X .