如何用另一个csv的信息更新csv的一个元素？

Question

我有一个csv的行产品，如

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

另一个csv

CATCODE;MARGIN
K1;0.08

我试着用下面的代码只更新列表行中的一个元素，而每个csv匹配的catcode都是这样。在这种情况下，它应该只更新K1，而其他值保持不变。以下是我尝试的：

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()

但是这个代码改变了所有的元素，不仅仅是K1作为CatCode的行 我想用Python的enumerate来做，但我不知道怎么做。如何仅更新两个文件中的CATCODE匹配的位置？我想用公式new_price=price(1+margin)把价格乘以保证金。你知道吗

我试着有一个csv像最初的不是熊猫表

就像

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

这可以通过熊猫使用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)

这是您的原始csv。你知道吗

下面是合并数据帧的最终输出。你知道吗

Answer 2

没有pandas，只使用标准python和csv模块，这里有一种方法。data.csv是“带产品行的csv”，update.csv是另一个。你知道吗

#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现在包含：

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