python - 在 Python 中，遍历工作表并仅计算第一列中的非 NaN 单元格。每张表的输出名称和找到的总数

Question

我想遍历每个工作表，并计算第一列中的每个非 NaN 值。我希望每张纸的总数成为该纸的名称。 A 列的标题和工作表名称未知。

输入:

Column A          Sheet
Non-NaN0          sh1
Non-NaN1          sh1
Non-NaN2          sh2

输出:

sheet            total
sh1                2
sh2                1

这是我目前所拥有的，但似乎无法正确处理。

# create data frame from workbook, skipping rows, keys are the sheet names and the values are the sheets as dataframess 
df_xlsx = pd.read_excel("C:/file/path/File.xlsx", skiprows=[0, 1, 2, 3, 4,], sheet_name=None) 

# List holding sheets that have been counted
all_sheets = []

# Loop through each sheet
for SheetNameKeys, dfSheetValues in df_xlsx.items():
    dfSheetValues = dfSheetValues.iloc[:, 0] # first column as df
    dfSheetValues['SheetNames'] = SheetNameKeys # create new column and name it each sheetname filling it with sheets/keys
    dfSheetValues['TotalNonNaN'] = dfSheetValues['SheetNames'].count # add column and total non-NaN found in sheet
    all_sheets.append(dfSheetValues) # Collect totals from each sheet into list

full_table = pd.concat(all_sheets) # put all sheets together 
#full_table.reset_index(inplace=True, drop=True)

print(full_table)
full_table.to_csv("C:/file/path/NewFile.csv", index = False)

我改变了:

pd.read_excel() 以包含 usecols=[0] 并移除 iloc[:, 0]

.count 到 .value_counts()

它给了我这个输出:

   Non-Nan SheetNames  TotalNonNaN
0  Non-Nan     Sheet1          NaN
1  Non-Nan     Sheet1          NaN
2  Non-Nan     Sheet1          NaN
3  Non-Nan     Sheet1          NaN
4      NaN     Sheet1          NaN
5      NaN     Sheet1          NaN
6      NaN     Sheet1          NaN
7  Non-Nan     Sheet1          NaN
0  Non-Nan     Sheet2          NaN
1  Non-Nan     Sheet2          NaN
2  Non-Nan     Sheet2          NaN
3      NaN     Sheet2          NaN
4  Non-Nan     Sheet2          NaN

我也试过以下方法:

path = "C:/Path/file.xlsx"
main_df = pd.ExcelFile(path)
sheets = main_df.sheet_names
for sheet in sheets:
    df = pd.read_excel(path, sheet_name=sheet)
    df.to_frame()
    na_count = df.isna().sum().char
    print(sheet, na_count)

Answer 1

国际工业联合会

out = (df.loc[df['Value'].notna(), ['sheet']].value_counts()
       .to_frame('total')
       .reset_index())

print(out)

  sheet  total
0   sh1      2
1   sh2      1