CODEWITHSUNDEEP
pythonpandasdataframe
Pyd
Pyd

Reputation: 6159

how to remove None cell from a dataframe in python

I have a df,

        contents    values  scoresBinned    categories
    0   Buy           484   (375, 500]       Bin 4
    1   Sell          429   (375, 500]       Bin 4
    2   Blanks        130   (125, 250]       Bin 2
    3   A             108   (0, 125]         Bin 1
    4   B             77    (0, 125]         Bin 1
    5   F              2    (0, 125]         Bin 1

as I need Bin as my header I am doing df,pivot

 filter_df1=df[["contents","categories"]]

 pivot_df=filter_df1.pivot(columns="categories")

 print(pivot_df)

                contents
    categories  Bin 1      Bin 2    Bin 4
       0        None        None    Buy
       1        None        None    Sell
       2        None        Blanks  None
       3        A           None    None
       4        B           None    None
       5        F           None    None

I tried df.dropna() but it deletes entire row,

My desired df is,

            Bin 1      Bin 2    Bin 4
             A         Blanks   Buy
             B                  Sell
             F

Upvotes: 2

Views: 3979

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

You can use sorted, boolean indexing and fillna to do that. 

pm = pivot_df.apply(sorted,key=pd.isnull)
new = pm[~pd.isnull(pm).all(1)].fillna('')

Output : 

           contents              
categories    Bin 1   Bin 2 Bin 4
0                 A  Blanks   Buy
1                 B          Sell
2                 F

Explanation :

Sorting based on None will give 

           contents              
categories    Bin 1   Bin 2 Bin 4
0                 A  Blanks   Buy
1                 B    None  Sell
2                 F    None  None
3              None    None  None
4              None    None  None
5              None    None  None

Now select those rows where the all the values in a row is not null. So we can use ~pd.isnull(pm).all(1)(Here ~ is not operator which will covert false to true and vise versa). Which will give

0     True
1     True
2     True
3    False
4    False
5    False
dtype: bool

Later boolen indexing to select the data i.e pm[~pd.isnull(pm).all(1)]

        contents              
categories    Bin 1   Bin 2 Bin 4
0                 A  Blanks   Buy
1                 B    None  Sell
2                 F    None  None

fillna('') will fill the none values with ''. Hope it helps.

Upvotes: 3

Related Questions