CODEWITHSUNDEEP
pythonexcelpandasxlwings
Yuson Kong
Yuson Kong

Reputation: 23

How to use xlwings or pandas to get all the non-null cell?

Recently I need to write a python script to find out how many times the specific string occurs in the excel sheet.

I noted that we can use *xlwings.Range('A1').table.formula* to achieve this task only if the cells are continuous. If the cells are not continuous how can I accomplish that?

Upvotes: 0

Views: 1775

Answers (2)

DeepSpace
DeepSpace

Reputation: 81624

As clarified in the comments, if you already have a dataframe, you can simply use count (Note: there must be a better way of doing it):

df = pd.DataFrame({'col_a': ['a'], 'col_b': ['ab'], 'col_c': ['c']})

string_to_search = '^a$' # should actually be a regex, in this example searching for 'a'
print(sum(df[col].str.count(string_to_search).sum() for col in df.columns))
>> 1

Upvotes: 0

OmerBA
OmerBA

Reputation: 842

It's a little hacky, but why not. By the way, I'm assuming you are using python 3.x.

First well create a new boolean dataframe that matches the value you are looking for.

import pandas as pd
import numpy as np

df = pd.read_excel('path_to_your_excel..')

b = df.applymap(lambda x: x == 'value_you_want_to_find' if isinstance(x, str) else False)

and then simply sum all occurences.

print(np.count_nonzero(b.values))

Upvotes: 1

Related Questions