使用Python编写为Excel的增量公式 [英] Increment formula written to excel with Python

问题描述

下面的脚本读取多个csv文件，合并其中的一些文件并将它们写入到两个不同工作表中的Excel文件中.

The script below reads in multiple csv files, merges some and writes them to an Excel file in two different sheets.

它还将此公式(=IF(COUNTIFS(Meds!A:A,B2)>0,1,0))添加到Meds列中每个单元格的最后一列，但我需要将其递增，因此第二个单元格将为=IF(COUNTIFS(Meds!A:A,B3)>0,1,0)，依此类推.我不知道如何编写一个循环来做到这一点.我看到了此帖子，但是我在使用openpyxl时遇到了问题，因此请避免那个图书馆.

It also adds this formula (=IF(COUNTIFS(Meds!A:A,B2)>0,1,0)) to the last column in every cell in the Meds column, but I need it to increment, so the second cell would be =IF(COUNTIFS(Meds!A:A,B3)>0,1,0) and so on. I can't figure out how to write a loop which will do this. I saw this post but I have issues using openpyxl, so would like to avoid that library.

import pandas as pd

# read in multiple csv files 
df1 = pd.read_csv("file1.csv", encoding = 'utf-8')
df2 = pd.read_csv("file2.csv", encoding = 'utf-8')
meds = pd.read_csv("meds.csv", encoding = 'utf-8')

# create a list of dataframes (excluding meds)
dfs = [df1, df2]

# merge dataframes in list
df_final = reduce(lambda left,right: pd.merge(left,right,on='RecordKey'), dfs)

# add empty column
df_final["Meds"] = ""

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('final.xlsx', engine='xlsxwriter')

# add formula to Meds
df_final['Meds'] = '=IF(COUNTIFS(Sheet2!A:A,E2)>0,1,0)'

# write to csv
df_final.to_excel(writer, sheet_name='Combined')
meds.to_excel(writer, sheet_name='Meds')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

推荐答案

您可以使用循环和字符串格式来创建可插入到df中的公式列表.

You can use a loop and string formatting to create a list of formulas that can be inserted into your df.

length_of_df = len(df)
list_of_formulas = []

for i in range(2,length_of_df+2):
    formula = '=IF(COUNTIFS(Sheet2!A:A,E{0}>0,1,0)'.format(i)
    list_of_formulas.append(formula)

# print(list_of_formulas)

# ['=IF(COUNTIFS(Sheet2!A:A,E2>0,1,0)',
# '=IF(COUNTIFS(Sheet2!A:A,E3>0,1,0)',   
# '=IF(COUNTIFS(Sheet2!A:A,E4>0,1,0)',    
# '=IF(COUNTIFS(Sheet2!A:A,E5>0,1,0)', 
# '=IF(COUNTIFS(Sheet2!A:A,E6>0,1,0)']

# Assign list of formulas to df
df.loc[:, "Meds"] = list_of_formulas

这篇关于使用Python编写为Excel的增量公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！