Convert strings in numpy ndarray to nan (or even better replace by interpolated values)

I have an excel sheet that contains a matrix. Unfortunately, it happens that some random cells are arbitrary strings. The first row and column are the axes (independent variables). An example:

I read this in with pandas and convert it to numpy ndarray in order to work with it.

tmp = pd.read_excel('path')
y = tmp.iloc[1:,0].values
x = tmp.iloc[0,1:].values
mat = tmp.iloc[1:,1:].values

My first hope was that numpyconverts the strings automatically to some numerical representation. But I needed to learn that a ndarray can hold mixed types.

Sorry, I am not even able to present a minimal example of how mat looks like.

import numpy as np
mat = np.ndarray(((3,9,'blob',27),(-1,10,'blib',12),(0,'blub','bleb',17))) # raises an error
mat = np.asarray(((3,9,'blob',27),(-1,10,'blib',12),(0,'blub','bleb',17))) # fills the ndarray with strings only (even the numbers become '3', '9' etc.)

The above excel import yields numbers in some cells but strings in others.

Is there any efficient way to replace all those strings in mat by np.nan? I mean, without scanning through all cells and replace based on the type? I tried to do this by the dtype argument of pd.read_excel() but without much success.

What I finally want to achieve is to replace those original strings by linear interpolated values. So maybe there is even a more efficient way to directly interpolate instead of first numerize the strings. As you see, the strings can also sit in a complete column, so 2D interpolation is required.

Thank you for your ideas!

You can use Pandas replace on a whole dataframe in combination with regex expression to get rid of strings, and then use Pandas interpolate to fill in the missing values.

Here is a naive example based on your post, where you seem to deal only with signed integers and simple strings (otherwise, a different regex pattern might be necessary).

import numpy as np
import pandas as pd

df = pd.DataFrame({"col1": [1, "a", 2], "col2": [-9, 7, "b"]})

print(df)
# Output  col1 col2
0    1   -9
1    a    7
2    2    b

df = df.replace(regex=r"\w+", value=np.nan).interpolate(method="linear")

print(df)
# Output

   col1  col2
0   1.0  -9.0
1   1.5   7.0
2   2.0   7.0