How to update one element of a csv with information from another csv?

Question

I have a csv with rows of products like

SKU;price;availability;Time;Supplier;CatCode
x1;10.00;0;1-4-2019;sup1;K1
x1;10.00;0;1-4-2019;sup1;K3
x1;10.00;0;1-4-2019;sup1;K2

And another csv

CATCODE;MARGIN
K1;0.08

I tried the following code to update only one element of the list row wher ethe catcodes from each csv match. In this case it should only update K1, while the other values stay the same. Here is what I tried:

def ChangeToFinalCSV():
    SetFixMinPrices = SetFixPrices()
    CatCodes = GetCatCodes()
    for FixMinPrice in SetFixMinPrices:                         
        for cat in CatCodes:
            if cat[0] == FixMinPrice[5]:
                FixMinPrice[1] = (((float(FixMinPrice[1].replace(',','.').replace('€','')) + float(SupplierShipping)) * float(BankingComission))*(1+float(cat[1]))) * float(1.24)
                FixMinPrice[1] = "{:.2f}".format(FixMinPrice[1])
                FixMinPrice[1] = str(FixMinPrice[1]).replace('.',',') + ' €'            
                retailed.append(FixMinPrice) 

    return retailed   
retailed = ChangeToFinalCSV()

But this code changes all elements, not only the row with K1 as CatCode I thought of doing it with enumerate of Python but I don't know how. How do I update only where the catcodes in both files match? I want to multiply the price by the margin with the formula new_price=price(1+margin).

I try to have a csv like the initial not a pandas table

like

SKU;price;availability;Time;Supplier;CatCode
x1;10.80;0;1-4-2019;sup1;K1
x1;10.00;0;1-4-2019;sup1;K3
x1;10.00;0;1-4-2019;sup1;K2

Answer 1

This can be done with pandas using a merge.

import pandas as pd
import numpy as np

#put you file path here instead of mine
#Just change the stuff in quotes to the path where the csvs
#you want to process are, making sure to inlclude the correct names
#csv1 should have the bigger dataset, csv2 is just the margin and catcode
csv1_filename='U:/PLAN/BCUBRICH/Python/Tests/merge_test/csv1.txt'
csv2_filename='U:/PLAN/BCUBRICH/Python/Tests/merge_test/csv2.txt'

df1=pd.read_csv(csv1_filename, sep=';') #save first csv as dataframe
df2=pd.read_csv(csv2_filename,sep=';')  #save second csv as dataframe

#merge the two so that if there is a catcode in the second file the new 
#column margin will be filled with the correct value
df_final=df1.merge(df2, left_on='CatCode',right_on='CATCODE', how='outer')

df_final['price']=np.where(df_final['MARGIN'].isnull(),df_final['price'],df_final['price']*(1+df_final['MARGIN'])*1.24)

df_final.to_csv('your_path\file_name_you_want.txt', sep=';',index=False)

Here is your original csv.

Here is the final output of the merged dataframes.

Answer 2

Without pandas, using only standard python and the csv module, here's one way to do it. data.csv is the "csv with rows of products", update.csv is the other.

#coding=utf-8
import csv

def GetCatCodes():
    with open('data.csv') as csvfile:
        reader = csv.DictReader(csvfile, delimiter=';')
        return list(reader)

def SetFixPrices():
    with open('update.csv') as csvfile:
        reader = csv.DictReader(csvfile, delimiter=';')
        return list(reader)

def ChangeToFinalCSV():
    SetFixMinPrices = SetFixPrices()
    CatCodes = GetCatCodes()
    for cc in CatCodes:
        for sfp in SetFixMinPrices:
            if cc['CatCode'] == sfp['CATCODE']:
                update_value = float(cc['price']) + float(sfp['MARGIN'])
                cc['price'] = str(update_value)
    return CatCodes

retailed = ChangeToFinalCSV()

with open('newdata.csv', 'w') as f:
    fieldnames = ['SKU', 'price', 'availability', 'Time', 'Supplier', 'CatCode']
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(retailed)

newdata.csv now contains:

x1,10.08,0,1-4-2019,sup1,K1
x1,10.00,0,1-4-2019,sup1,K3
x1,10.00,0,1-4-2019,sup1,K2