回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>你好下面请查看我的VBA代码的工作。我正在尝试用Pandas重新编写它,但是我的Pandas脚本没有工作到一半(我尝试的Pandas脚本低于VBA)如果可能的话,有人能帮我完成这个吗(我想是的)</p>
<pre><code>Sub mymacro()
Columns(19).Replace "DFHD", "SFD"
Columns(19).Replace "DFBG", "SFD"
Columns(19).Replace "DFVD", "SFD"
Columns(19).Replace "MFUB", "BFD"
Columns(19).Replace "MFBD", "BFD"
Columns(19).Replace "DFBD", "BFD"
Columns(19).Replace "UFNC", "CFD"
Columns(19).Replace "UFNC", "CFD"
Columns(19).Replace "BFYD", "BFD"
'Having trouble starting below here'
Columns("T:AC").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$1:$AS$1000000").AutoFilter Field:=19, Criteria1:=Array( _
"U*"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-100
ActiveSheet.Range("$A$1:$AS$1000000").AutoFilter Field:=30, Criteria1:=Array( _
"350", "B*"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-100
Range("S3").Select
ActiveCell.FormulaR1C1 = "BD"
Range("S3").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("S3").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$AS$1000000").AutoFilter Field:=19, Criteria1:="=UND", Operator:=xlOr, Criteria2:="=UNH"
ActiveWindow.SmallScroll Down:=-21
ActiveSheet.Range("$A$1:$AS$1000000").AutoFilter Field:=30, Criteria1:=Array( _
"DR9", "DV0", "DV5", "DV8", "DV9", "DVG", "DV*"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-36
Range("S11").Select
ActiveCell.FormulaR1C1 = "SD"
Range("S11").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("S11").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
ActiveWindow.SmallScroll Down:=-10
ActiveSheet.Range("$A$1:$AS$1000000").AutoFilter Field:=19, Criteria1:="UNH"
ActiveWindow.SmallScroll Down:=-27
Range("S1815").Select
ActiveCell.FormulaR1C1 = "FUHD"
Range("S1815").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("S1815").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-30
ActiveSheet.ShowAllData
ActiveWindow.SmallScroll Down:=-240
</code></pre>
<p>下面是我的Pandas脚本,请注意我遇到问题的地方是注释,因为前12行代码工作得很好。你知道吗</p>
<pre><code>import pandas as pd
import numpy as np
data = pd.read_excel("orsthrufirstarticledeltion.xlsx", encoding = "ISO-8859-1", dtype=object)
data.loc[data.Format == 'DFHD', 'Format'] = 'SFD'
data.loc[data.Format == 'DFBG', 'Format'] = 'SFD'
data.loc[data.Format == 'DFVD', 'Format'] = 'SFD'
data.loc[data.Format == 'MFUB', 'Format'] = 'BFD'
data.loc[data.Format == 'MFBD', 'Format'] = 'BFD'
data.loc[data.Format == 'DFBD', 'Format'] = 'BFD'
data.loc[data.Format == 'UFNC', 'Format'] = 'CFD'
data.loc[data.Format == 'BFYD', 'Format'] = 'BFD'
# Trouble starts below
data.loc[(data["Fmt"] != str) & (data["Format"] == "UN*"), "Format"] = 'BD' # the UN* did not work
#data.loc[(data["Fmt"] == '350') & (data["Format"] == "UNB"), "Format"] = 'BD'
#data.loc[(data["Fmt"] != str) & (data[data.Format.str.startswith('UN',na=False)]), "Format"] = 'BD'
#
writer = pd.ExcelWriter('mstrplc2.xlsx', engine='xlsxwriter')
data.to_excel(writer, sheet_name='Sheet1')
writer.save()
</code></pre>
<p><strong>----获得解决方案的新尝试------</strong></p>
<p>下面请查看带有原始数据的示例数据框,如果您愿意,我有可以导出到excel的代码。你知道吗</p>
<pre><code>import pandas as pd
startdf = pd.DataFrame({'Column_A':['DFHD', 'DFBG', 'DFVD', 'MFUB', 'MFBD', 'DFBD', 'UFNC', 'UFNC', 'BFYD',
'UNFZ', 'UNT', 'UNIX', 'UNFZ', 'UNT', 'UNIX','UNFZ', 'UNT', 'UNIX', 'UNFZ', 'UNT', 'UNIX','UNFZ', 'UNT', 'UNIX'],
'Column_B':['test','test','test','test','test','test','test','test','test','B50','DVG','DV9','DV5','DV0','B25','U66','U1C','350','357','BVG','DBG','BUG','UVG','DV8']})
writer = pd.ExcelWriter('testdf.xlsx', engine='xlsxwriter')
startdf.to_excel(writer, sheet_name='Sheet1')
</code></pre>
<p>第一步是获取A列中的所有值,并用下面列出的新值替换现有值(因此我们刚刚编辑了A列)</p>
<ul>
<li>“DFHD”->;“SFD”“DFBG”->;“SFD”“DFVD”->;“SFD”“MFUB”->;“BFD”“MFBD”->;
“BFD”“DFBD”->;“BFD”“UFNC”->;“CFD”“UFNC”->;“CFD”“BFYD”->;“BFD”</li>
</ul>
<p>写入此逻辑后,数据应如下所示:</p>
<pre><code>df2 = pd.DataFrame({'Column_A':['SFD', 'SFD', 'SFD', 'BFD', 'BFD', 'BFD', 'CFD', 'CFD', 'BFD',
'UNFZ', 'UNT', 'UNIX', 'UNFZ', 'UNT', 'UNIX','UNFZ', 'UNT', 'UNIX', 'UNFZ', 'UNT', 'UNIX','UNFZ', 'UNT', 'UNIX'],
'Column_B':['test','test','test','test','test','test','test','test','test','B50','DVG','DV9','DV5','DV0','B25','U66','U1C','350','357','BVG','DBG','BUG','UVG','DV8']})
</code></pre>
<p>现在我们将继续编辑A列,但是使用B列中的值来指定A列的值应该是什么,所以请逐行考虑每个值。首先从A列中筛选出SFD、BFD和CFD,因此剩余的值将是“UNFZ”、“UNT”、“UNIX”、“UNFZ”、“UNT”、“UNIX”、“UNFZ”、“UNT”、“UNIX”、“UNFZ”、“UNT”、“UNIX”、“UNFZ”、“UNT”、“UNIX”。对于这些剩余的值,我们将查看B列以决定如何更改A列中的内容。逻辑如下:</p>
<ol>
<li>以B开头的值或是B列中的数字,应该意味着a列中匹配的行值现在应该更改为BFD</li>
<li>以D或OPT-in列B开头的值意味着列A中匹配的行值现在应更改为SFD</li>
<li>以U开头的值或是B列中的数字,应该意味着A列中匹配的行值现在应该更改为UHFD</li>
</ol>
<p>在这个逻辑之后,最终的输出数据帧应该是</p>
<pre><code> resultdf = pd.DataFrame({'Column_A':['SFD', 'SFD', 'SFD', 'BFD', 'BFD', 'BFD', 'CFD', 'CFD', 'BFD',
'BFD', 'SFD', 'SFD', 'SFD', 'SFD', 'BFD','UHFD', 'UHFD', 'BFD', 'BFD', 'BFD', 'SFD','BFD', 'UHFD', 'SFD'],
'Column_B':['test','test','test','test','test','test','test','test','test','B50','DVG','DV9','DV5','DV0','B25','U66','U1C','350','357','BVG','DBG','BUG','UVG','DV8']})
writer = pd.ExcelWriter('finalresult.xlsx', engine='xlsxwriter')
resultdf.to_excel(writer, sheet_name='Sheet1')
</code></pre>