pandas 方法到Excel筛选器 [英] Pandas approach to Excel Filter

问题描述

下面有一个我在excel文件上运行的示例脚本.基本上是之后 第6行，我想过滤掉F列中包含BFD和SFD的行(我知道刚刚在上面的代码中分配了这些值，而且还有更多这些值存在于脚本之前的不同行中).过滤之后，只需将第7-8行中的逻辑应用于数据帧.因此，从根本上来说，您可以使用pandas按列中的单元格值过滤出行，然后对未过滤的内容应用逻辑，然后对所有未过滤的内容进行过滤(就好像您要在excel中清除过滤器一样)，并准备好我的数据框

I have an example script below that I run on an excel file. Essentially after line 6 I want to filter out rows that contain BFD and SFD in Column F( I know just assigned these values in the code above, and more of those values also existed in different rows prior to the script). After this is filtered just apply the logic in lines 7-8 to the data frame. So essentially ya use pandas to filter out rows by cell value in column then apply logic to what did not get filtered out and then just unfiltered everything ( as if you were to clear filters in excel) and have my data frame be ready to go

import pandas as pd
import numpy as np
data = pd.read_excel("2.27.xlsm", encoding = "ISO-8859-1", dtype=object)
data.loc[data.F == 'DBD', 'F'] = 'BFD'
data.loc[data.F == 'DUB', 'F'] = 'BFD'
data.loc[data.F == 'DCD', 'F'] = 'SFD'
#Now I want to apply this logic and filter out any rows that have values of BFD or SF in column F and then apply to logic below (to not overwrite any work done above)
data.loc[data.Fm == 'B25', 'F'] = 'BFD'
data.loc[data.Fm == 'B50', 'F'] = 'BFD'

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
data.to_excel(writer, sheet_name='Sheet1')
writer.save()

推荐答案

再次感谢所有帮助，这就是我最终要做的.如果您认为这不等同于对以UN进行调整然后清除过滤器开头的项目进行过滤的excel过滤器，请戳一下漏洞.

Thanks again for all the help, this is what I ended up doing. Please poke holes if you think this does not equate to an excel filter of filtering for items that start with UN making adjustments then clearing filters.

ta =  data[~data.Format.str.contains(r'UN', case = False, na=False, regex = False)] # trying regex
da = data[data.Format.str.contains(r'UN', case = False, na=False, regex = False)] # trying regex


final = pd.concat([ta, da],0) # now append seperate dataframes 

这篇关于 pandas 方法到Excel筛选器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！