Repair Data for Markov Chain Monte Carlo Simulation

3
votes

As is known all probabilities need to sum up to 1. I do have a Pandas Dataframe where sometimes the probabiltiy of one event does miss.
Since I know all elements of a row need to sum up to one. I want to replace Nan by a calculated Value. With something like the following for each row in my Pandas Data Frame

for item, row in df:
    df.replace(Nan,(1-sum of row())

As an example, here's the array I do use as testing Data the moment:

    matrixsum
     e    f    g
a  0.3  0.2  Nan
b  0.2  0.2  0.6
c  0.7  0.1  Nan

By using df.fillna(0) i do get this:

  matrixsum
     e    f    g
a  0.3  0.2  0.0
b  0.2  0.2  0.6
c  0.7  0.1  0.0

An additional problem is the fact that only rows with float or int format can be summed to 1, but nan has string-formated. At the moment I just use df.fillna(0) but this is a bad thing to do.

Expectedt Output:

  matrixsum
     e    f    g
a  0.3  0.2  0.5
b  0.2  0.2  0.6
c  0.7  0.1  0.2
2
python-3.xpandasdataframemarkov-chains
Where's the nan in your sample dataframe? Please share a proper one with expected output. - Mayank Porwal
Thanks for your advice, i did change the Question and tried to implement the things you asked for. - Hans Peter
What happens if a row contains 2 nan? - Serge de Gosson de Varennes
If a row contains more than one nan theres no Solution, and the Data cant be repaired. But i want to reduce the ammount of data the user has to enter. - Hans Peter

2 Answers

1
votes

You can first convert your dataframe to numeric values, and then fill the NaNs of each row by 1- row.sum():

df = df.apply(pd.to_numeric, errors="coerce")
df = df.apply(lambda row: row.fillna(1 - row.sum()), axis=1)

or equivalently, you can combine these two in a function:

def markovize(row):
    row = pd.to_numeric(row, errors="coerce")
    return row.fillna(1 - row.sum())

df = df.apply(markovize, axis=1)

Before:

     e    f    g
a  0.3  0.2  Nan
b  0.2  0.2  0.6
c  0.7  0.1  Nan

After:

     e    f    g
a  0.3  0.2  0.5
b  0.2  0.2  0.6
c  0.7  0.1  0.2
2
votes

If you are sure that your Nan for all rows always appear in a single column(let's say g), you can do this:

Consider below df:

In [21]: df
Out[21]: 
     e    f    g
a  0.3  0.2  Nan
b  0.2  0.2  0.6
c  0.7  0.1  Nan

In [22]: df['g'] = 1 - df.sum(1)

In [23]: df
Out[23]: 
     e    f    g
a  0.3  0.2  0.5
b  0.2  0.2  0.6
c  0.7  0.1  0.2