In Python, loop through sheet and only count Non-NaN cells in the first column. Output name of each sheet and total found

user_9789962 0
Viewed 220

I want to loop through each sheet, and count each non-NaN value in the first column. I want the total from each sheet, to be the name of the sheet. The header for Column A and the sheet names are unknown.

Input:

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

Output:

sheet            total
sh1                2
sh2                1

Here is what I have so far, but can't seem to get it right.

# 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)

I changed:

pd.read_excel() to include usecols=[0] and removed iloc[:, 0]

.count to .value_counts()

It gave me this output:


   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

I have also tried the following:


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)
pandas spreadsheet nan python
4 Answers

IIUC,

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

print(out)

  sheet  total
0   sh1      2
1   sh2      1
user_103151630

You may use this:

import pandas as pd

path = "E:\my_file.xlsx"
main_df = pd.ExcelFile(path)
sheets = main_df.sheet_names
for sheet in sheets:
    df = pd.read_excel(path, sheet_name=sheet)
    na_count = df.isna().sum().char
    print(sheet, na_count)

Example Output:

Sheet1 1
Sheet2 5

I'm adding .char after sum because it returns the data type too and you need the count only. Then you can write your values to a data frame immediately or by creating a dictionary and converting it to a data frame.

user_56176080

This code uses a list comprehension to loop through the sheets and count the sum of the not null values. It uses this for the data of the DataFrame. It then uses the keys of the workbook dictionary as the index of the DataFrame. Note that I used an OrderedDict to ensure the the order stays consistent between the values and the index. Note also that I left out the skiprows parameter to pandas.read_excel in order to make the code more generally reproducible, so you will likely need to add that in.

from collections import OrderedDict

import pandas

# Use OrderedDict to ensure that data and index have the same order
workbook = OrderedDict(pandas.read_excel('C:/file/path/File.xlsx', sheet_name=None))
result = pandas.DataFrame(
    data={
        'total': [
            df.iloc[:, 0].notnull().sum()
            for df in workbook.values()
        ]
    },
    index=pandas.Index(workbook.keys(), name='sheet'),
)
result.to_csv("C:/file/path/NewFile.csv")
user_36694650 
from openpyxl import load_workbook

wb = load_workbook('file.xlsx')

data = {}
for ws, name in zip(wb.worksheets, wb.sheetnames):
    data[name] = len([x for x in ws['A'][6:] if x])

df = pd.DataFrame(data)
user_118659560
Related