-1
$\begingroup$

I am trying to sample random values from a dataframe where the NaN values should be ignored, without dropping the entire row or column.

My sampling function at the moment looks like this:

def random_port(x, y):
    port = df.sample(n=x, axis=1).sample(n=y)
    return port

The problem shows up when the Output has NaN values in them, like so:

IN: random_port(5, 5)  

OUT:
name        a           b         c         d         e
date                                                           
2018-06-23  -0.382931  -0.740939  0.033059       NaN        NaN
2018-10-21   2.230166  -0.632479 -0.499691       NaN  -0.532929
2018-05-30   0.432295   0.101531       NaN       NaN        NaN
2018-03-02        NaN  11.006190  4.427038       NaN        NaN
2018-08-17  -0.038829  -0.603785 -0.104375       NaN        NaN

I want to be able to exclude these values from the sample, before they are sampled. I have tried with df.isna() without luck. df.dropna() would also not work as this would drop all rows of the dataframe.

Hope you could help me with some inputs!

EDIT
My dataframe looks like this:

name              0x      2GIVE  300 Token  ...   iTicoin    imbrex    vSlice
date                                        ...                              
2018-01-01       NaN  65.290909        NaN  ...       NaN       NaN  1.710043
2018-01-02       NaN  80.463768        NaN  ...       NaN       NaN  2.435115
2018-01-03       NaN  57.126316        NaN  ...       NaN       NaN  3.717667
2018-01-04       NaN  60.589286        NaN  ...       NaN       NaN  4.230297
2018-01-05       NaN  93.228137        NaN  ...       NaN       NaN  6.291709
             ...        ...        ...  ...       ...       ...       ...
2018-11-21  1.299640  -0.722204   0.251369  ... -0.871292 -0.385648 -0.972958
2018-11-22  0.822972  -0.698515  -0.005144  ... -0.872788 -0.509496 -0.973531
2018-11-23  0.849339  -0.689389   0.017049  ... -0.863086 -0.583974 -0.976263
2018-11-24  0.537992  -0.709757  -0.005032  ... -0.874165 -0.543323 -0.979586
2018-11-25  0.615335  -0.726006  -0.081572  ... -0.883667 -0.637062 -0.974509

[329 rows x 879 columns]

As you can see there are columns that have both NaN and values that I want to use in the sampling.
Now I would like to exclude these NaN values, so that they are not included in the sampling at all. This way I when I calculate the mean of the sample it will have no NaN. So:
Sample from df where value is not equal to NaN.

I want my output from the function random_port to not have any NaN values.

OUT:
name        ChessCoin  Etheroll  AquariusCoin     MyBit  Megacoin
date                                                             
2018-10-29   0.684864 -0.873093     -0.035047 -0.988149 -0.736966
2018-01-28   0.684864 -0.873093     -0.035047 -0.988149 -0.736966
2018-04-22   0.684864 -0.873093     -0.035047 -0.988149 -0.736966
2018-11-05   0.089559 -0.849822     -0.053746 -0.987191 -0.757519
2018-07-16   0.292095 -0.634921      3.961392 -0.053746 -0.987191
$\endgroup$
2
  • $\begingroup$ Thanks for updating the question, but its still not clear why you cannot remove NA pre-sampling, even if you lose some data due to NA, you will still have valid samples. Also, I can't see a connection between your data frame and desired output, because the DF structures are completely different. $\endgroup$ Feb 27, 2020 at 15:40
  • $\begingroup$ In my case, my dataframe is the result of pandas.DataFrame.mode, which adds in NA values into results when one of the columns has more than one mode but the others don't. $\endgroup$
    – Pro Q
    Nov 1, 2022 at 7:47

2 Answers 2

1
$\begingroup$

EDIT

If you are using Pandas and you want skip NA when you calculate mean

mean = df["col_name"].mean(skipna=True)

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.mean.html

$\endgroup$
5
  • $\begingroup$ I have thought about doing this, however, because the I am working returns on crypotcurrencies, would this not be possible, as there doesn't exist data for the dates with missing values. I hope I have explained myself clearly enough, thank you for your reply. $\endgroup$
    – Nicolai
    Feb 27, 2020 at 12:12
  • $\begingroup$ @Nicolai Whats stopping you from filtering df before sampling? You can select only non-NA rows from df and then do the sampling. $\endgroup$ Feb 27, 2020 at 12:21
  • 1
    $\begingroup$ @YaroslawHomenko the df has rows with both non-NaN and NaN, so I cannot filter it using the .dropna() as this would drop the whole row, when in fact the other values in that row are usable. I use the following line to filter out rows and columns that completely consist of NaN: df.dropna(axis=1, how='all').dropna(how='all') $\endgroup$
    – Nicolai
    Feb 27, 2020 at 12:26
  • 1
    $\begingroup$ @Nicolai Its not really clear what you trying to achieve - from your question you want to exclude na values before sampling, but on the other hand you dont want to exclude or replace them as suggested by fuwiak. Are there any particular columns with NA that you want to exclude? (Like column c or e in your example). Can you also add desired output to your question? It might make the answering easier $\endgroup$ Feb 27, 2020 at 12:54
  • 1
    $\begingroup$ @YaroslawHomenko I have updated my question with the dataframe and the output I want to achieve with my function. I hope it is more clear now. $\endgroup$
    – Nicolai
    Feb 27, 2020 at 13:13
0
$\begingroup$

Apply the sample function row-by-row and weight each row individually such that NaNs have a 0% chance of being chosen.

That is, do:

def sample_ignore_nan(df, n=1, axis=1):
    return df.apply(lambda row: row.sample(n=n, weights=row.notna()).iloc[0], axis=axis)

def random_port(x, y):
    port = sample_ignore_nan(port, n=x, axis=1).sample(n=y)
    return port
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.