英文:
Looking for conditional string-appended values in csv.reader
问题
我知道你要求只翻译代码部分，下面是你提供的代码的翻译：
companyList = {'1000000': 'Vendor1', ...}with open('Vendor Report.csv', mode='r', encoding='latin1') as file:csvreader = csv.reader(file)for row in csvreader:print(' '.join(row))if 'Functional Amount Not Invoiced:' in row:...
请注意，这是你提供的代码的翻译，只包含代码部分，没有其他内容。
英文:
I have a vendor payables aging report I'm trying to automate which is provided as a .csv file exported from a financial system. In the report, a line called 'functional amount not invoiced' is listed, followed by a $xx.xx amount for each vendor on the list. Below is an example of the report output (with numbers changed):
1000000 Vendor1 USD PO Number 1/1/1900Item1, Description100 Each $1.00INV000000 1/1/1900 000 Each 100 0 $1.00 $24.000 0 $24.00INV000001 1/1/1900 000 Each 50 0 $1.00 $10.500 0 $10.50-------------------Functional Amount Not Invoiced: $250.00Amount Not Invoiced Less Returned: $250.001000001 Vendor2 USD PO2061994 6/2/2015Item2, Description 30 Each $38.00INV000002 7/23/2015 000 Each 9 0 $38.00 $342.000 0 $342.00INV000003 7/23/2015 000 Each 7 0 $38.00 $266.000 0 $266.00-------------------Functional Amount Not Invoiced: $346,955.00Amount Not Invoiced Less Returned: $1,245.00
I would like to know how I can parse a .csv file for all instances of 'Functional Amount Not Invoiced' greater than or equal to $10,000.00, and in those cases, take the first two strings and return them (in the case above, I would return 1000000 Vendor1). Here's my code so far:
companyList={'1000000':'Vendor1',...}with open('Vendor Report.csv',mode='r',encoding='latin1') as file:csvreader=csv.reader(file)for row in csvreader:print(' '.join(row))if 'Functional Amount Not Invoiced:' in row:...
I've gotten to the ... part, and I know the logic is 'if amount after string is at least $10,000.00, find the vendor ID and vendor name and return them. The goal would be to have a list of all vendors over $10,000.00 appended automatically to a list. My expected output would be as follows:
Vendor ID Vendor Name $346,955.00...
答案1
得分: 1
以下是代码部分的翻译：
#pip install pandasimport pandas as pdMIN_AMOUNT = 10000df = pd.read_fwf("input.csv", header=None)vendor_vals = df[0].str.extract(r"(\d+) ([a-zA-Z]+\d+)", expand=False).ffill()fani_vals = (df.pop(0).str.extract(r"Functional Amount Not Invoiced: $(.*)",expand=False).replace(",|\.0+": "", regex=True).astype(float))companyList = (df.assign(VENDOR = vendor_vals, FANI = fani_vals).dropna().loc[lambda df_: df_["FANI"].gt(MIN_AMOUNT)].to_dict("list"))
df = pd.read_fwf("input.csv", header=None)out = (df.join(df[0].str.extract(r"(\d+) ([a-zA-Z]+\d+)").rename(columns={0: "VENDOR_ID", 1:"VENDOR_NAME"}).ffill()).assign(FANI = lambda df_: df_.pop(0).str.extract(r"Functional Amount Not Invoiced: $(.*)",expand=False).replace(",|\.0+": "", regex=True).astype(float)).dropna().loc[lambda df_: df_["FANI"].gt(MIN_AMOUNT)].reset_index(drop=True))
希望这些翻译对您有所帮助。
英文:
IIUC, here is one option with [tag:pandas] by using read_fwf and extract :
#pip install pandasimport pandas as pdMIN_AMOUNT = 10000df = pd.read_fwf("input.csv", header=None)vendor_vals = df[0].str.extract(r"(\d+) ([a-zA-Z]+\d+)", expand=False).ffill()fani_vals = (df.pop(0).str.extract(r"Functional Amount Not Invoiced: $(.*)",expand=False).replace({r",|\.0+": ""}, regex=True).astype(float))companyList = (df.assign(VENDOR = vendor_vals, FANI = fani_vals).dropna().loc[lambda df_: df_["FANI"].gt(MIN_AMOUNT)].to_dict("list"))
Output :
>>> print(companyList){'VENDOR': ['1000001 Vendor2'], 'FANI': [346955.0]}
Update :
If you need a dataframe (to make a .csv), use this :
df = pd.read_fwf("input.csv", header=None)out = (df.join(df[0].str.extract(r"(\d+) ([a-zA-Z]+\d+)").rename(columns={0: "VENDOR_ID", 1:"VENDOR_NAME"}).ffill()).assign(FANI = lambda df_: df_.pop(0).str.extract(r"Functional Amount Not Invoiced: $(.*)",expand=False).replace({r",|\.0+": ""}, regex=True).astype(float)).dropna().loc[lambda df_: df_["FANI"].gt(MIN_AMOUNT)].reset_index(drop=True))
Output :
>>> print(out)VENDOR_ID VENDOR_NAME FANI0 1000001 Vendor2 346955.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论