英文:
Loop for filtering NaN's in dictionaries
问题
我打开了我的字典，我想写一个循环，以便只获得"favorite color"列等于NaN的行作为输出。
我的代码到目前为止：
# 导入模块import openpyxl as opimport pandas as pdimport numpy as npimport xlsxwriterfrom openpyxl import Workbook, load_workbook# 定义文件路径my_file_path = r'C:\Users\machukovich\Desktop\stack.xlsx'# 将文件加载到数据框字典中my_dict = pd.read_excel(my_file_path, sheet_name=None, skiprows=2)
my_dict输出：
my_dict = {'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color1 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN2 Christina Rodwell Christina Rodwell 100.0 3.0 black3 Ziggy Stardust Ziggy Stardust 40.0 7.0 red ,'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color0 Lucy Diamonds Lucy Diamonds 22.0 9.0 brown1 Grace Kelly Grace Kelly 50.0 7.0 white2 Uma Thurman Uma Thurman 105.0 7.0 purple3 Lola King Lola King NaN NaN NaN ,'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color0 Eleanor Rigby Eleanor Rigby 104.0 6.0 blue1 Barbara Ann Barbara Ann 168.0 8.0 pink2 Polly Cracker Polly Cracker 450.0 7.0 black3 Little Joe Little Joe NaN NaN NaN }
我想要的输出：
my_dict = {'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color1 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color3 Lola King Lola King NaN NaN NaN'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color3 Little Joe Little Joe NaN NaN NaN
最后，我想将"desired output"写入一个新的Excel文件（在单独的工作表中）。请为我提供指导。我是Python新手。
英文:
I have opened my dictionary and I would like to write a loop so to obtain as an output only those rows for which the ''favorite color'' column equals to NaN.
- My code so far:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
# Importing modulesimport openpyxl as opimport pandas as pdimport numpy as npimport xlsxwriterfrom openpyxl import Workbook, load_workbook# Defining the file pathmy_file_path = r'C:\Users\machukovich\Desktop\stack.xlsx'# Loading the file into a dictionary of Dataframesmy_dict = pd.read_excel(my_file_path, sheet_name=None, skiprows=2)
<!-- end snippet -->
- my_dict output:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
my_dict = {'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color1 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN2 Christina Rodwell Christina Rodwell 100.0 3.0 black3 Ziggy Stardust Ziggy Stardust 40.0 7.0 red ,'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color \0 Lucy Diamonds Lucy Diamonds 22.0 9.0 brown1 Grace Kelly Grace Kelly 50.0 7.0 white2 Uma Thurman Uma Thurman 105.0 7.0 purple3 Lola King Lola King NaN NaN NaN ,'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color \0 Eleanor Rigby Eleanor Rigby 104.0 6.0 blue1 Barbara Ann Barbara Ann 168.0 8.0 pink2 Polly Cracker Polly Cracker 450.0 7.0 black3 Little Joe Little Joe NaN NaN NaN }
<!-- end snippet -->
- My desired output:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
my_dict = {'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color1 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color \3 Lola King Lola King NaN NaN NaN'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color \3 Little Joe Little Joe NaN NaN NaN
<!-- end snippet -->
And, finally I would like to write the desired output to a new excel file (in separate sheets).
Please, enlighten me. I am new to python.
答案1
得分: 1
这是您提供的代码的翻译：
我会这样做：使用 pd.ExcelWriter("output.xlsx", engine="xlsxwriter") 作为 writer:对于 sn, df in my_dict.items():(df.loc[df["favourite color"].isnull()] # 我们使用布尔索引.to_excel(writer, sheet_name=sn, index=False)) # 是否使用 startrow, startcol ?# 这是可选的对于 ws in writer.sheets:writer.sheets[ws].autofit() # xlsxwriter 3.0.6+
输出（只有 Sheet_1）：
更新:
如果您想先更新 my_dict，可以使用以下方式：
对于 sn, df in my_dict.items():my_dict[sn] = df.loc[df["favourite color"].isnull()]
输出：
print(my_dict){'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color0 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN,'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color0 Lola King Lola King NaN NaN NaN,'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color0 Little Joe Little Joe NaN NaN NaN}
然后（如果需要），您可以循环遍历每个筛选后的 df 将其存储在电子表格中：
使用 pd.ExcelWriter("output.xlsx", engine="xlsxwriter") 作为 writer:对于 sn, df in my_dict.items():df.to_excel(writer, sheet_name=sn, index=False)
请注意，我已经翻译了您提供的代码和相关注释，但没有翻译其他内容。<details><summary>英文:</summary>I would do it this way :with pd.ExcelWriter("output.xlsx", engine="xlsxwriter") as writer:for sn, df in my_dict.items():(df.loc[df["favourite color"].isnull()] # we use boolean indexing.to_excel(writer, sheet_name=sn, index=False)) # with startrow, starcol ?#this is optionalfor ws in writer.sheets:writer.sheets[ws].autofit() # xlsxwriter 3.0.6+Output (*only* `Sheet_1`):[![enter image description here][1]][1]***Update :***If you want to update `my_dict` first, you can use this :for sn, df in my_dict.items():my_dict[sn] = df.loc[df["favourite color"].isnull()]Output :print(my_dict){'Sheet_1': Name Surname Concatenation ID_ Grade_ favourite color0 Delilah Gonzalez Delilah Gonzalez NaN NaN NaN,'Sheet_2': Name Surname Concatenation ID_ Grade_ favourite color0 Lola King Lola King NaN NaN NaN,'Sheet_3': Name Surname Concatenation ID_ Grade_ favourite color0 Little Joe Little Joe NaN NaN NaN}Then (*if needed*) you can loop through each filtered `df` to store it in a spreadsheet :with pd.ExcelWriter("output.xlsx", engine="xlsxwriter") as writer:for sn, df in my_dict.items():df.to_excel(writer, sheet_name=sn, index=False)[1]: https://i.stack.imgur.com/iUPhm.png</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论