how to delete nan values in pandas?

Question

How to delete NaN values in pandas? When I was to print the code to (.csv). The columns are irregular and filled with NaN values.

import pandas as pd

egzersizler = [{'Hareket Adı': 'Smith Machine Shrug', 'Url': 'https://www.bodybuilding.com/exercises/smith-machine-shrug'}, {'Hareket Adı': 'Leverage Shrug', 'Url': 'https://www.bodybuilding.com/exercises/leverage-shrug'}, {'Hareket Adı': 'Standing Dumbbell Upright Row', 'Url': 'https://www.bodybuilding.com/exercises/standing-dumbbell-upright-row'}, {'Hareket Adı': 'Kettlebell Sumo High Pull', 'Url': 'https://www.bodybuilding.com/exercises/kettlebell-sumo-high-pull'}, {'Hareket Adı': 'Dumbbell Shrug', 'Url': 'https://www.bodybuilding.com/exercises/dumbbell-shrug'}, {'Hareket Adı': 'Calf-Machine Shoulder Shrug', 'Url': 'https://www.bodybuilding.com/exercises/calf-machine-shoulder-shrug'}, {'Hareket Adı': 'Barbell Shrug', 'Url': 'https://www.bodybuilding.com/exercises/barbell-shrug'}, {'Hareket Adı': 'Barbell Shrug Behind The Back', 'Url': 'https://www.bodybuilding.com/exercises/barbell-shrug-behind-the-back'}, {'Hareket Adı': 'Upright Cable Row', 'Url': 'https://www.bodybuilding.com/exercises/upright-cable-row'}, {'Hareket Adı': 'Cable Shrugs', 'Url': 'https://www.bodybuilding.com/exercises/cable-shrugs'}, {'Hareket Adı': 'Upright Row - With Bands', 'Url': 'https://www.bodybuilding.com/exercises/upright-row-with-bands'}, {'Hareket Adı': 'Smith Machine Behind the Back Shrug', 'Url': 'https://www.bodybuilding.com/exercises/smith-machine-behind-the-back-shrug'}, {'Hareket Adı': 'Smith Machine Upright Row', 'Url': 'https://www.bodybuilding.com/exercises/smith-machine-upright-row'}, {'Hareket Adı': 'Clean Shrug', 'Url': 'https://www.bodybuilding.com/exercises/clean-shrug'}, {'Hareket Adı': 'Scapular Pull-Up', 'Url': 'https://www.bodybuilding.com/exercises/scapular-pull-up'}, {'Hareket Adı': 'Snatch Shrug', 'Url': 'https://www.bodybuilding.com/exercises/snatch-shrug'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Kas Grubu': 'Traps'}, {'Ekipmanlar': 'Machine'}, {'Ekipmanlar': 'Machine'}, {'Ekipmanlar': 'Dumbbell'}, {'Ekipmanlar': 'Kettlebells'}, {'Ekipmanlar': 'Dumbbell'}, {'Ekipmanlar': 'Machine'}, {'Ekipmanlar': 'Barbell'}, {'Ekipmanlar': 'Barbell'}, {'Ekipmanlar': 'Cable'}, {'Ekipmanlar': 'Cable'}, {'Ekipmanlar': 'Bands'}, {'Ekipmanlar': 'Machine'}, {'Ekipmanlar': 'Machine'}, {'Ekipmanlar': 'Barbell'}, {'Ekipmanlar': 'None'}, {'Ekipmanlar': 'Barbell'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Intermediate'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': ''}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Intermediate'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Beginner'}, {'Düzey': 'Level: Intermediate'}]

df=pd.DataFrame(egzersizler, columns = ['Hareket Adı','Url','Düzey','Kas Grubu','Ekipmanlar'] )

print (df)

You can refer to this https://stackoverflow.com/questions/13295735/how-can-i-replace-all-the-nan-values-with-zeros-in-a-column-of-a-pandas-datafra/57064663#57064663 — Farrukh Faizy, Nov 29 '19 at 01:16
I couldnt it. it changes the value but does not delete it. https://imgur.com/a/uCXO4NB — hcttepe, Nov 29 '19 at 01:33
Possible duplicate of [How to drop rows of Pandas DataFrame whose value in a certain column is NaN](https://stackoverflow.com/questions/13413590/how-to-drop-rows-of-pandas-dataframe-whose-value-in-a-certain-column-is-nan) — Asif Ali, Nov 29 '19 at 02:03
Are you trying to delete the rows with NaN values, or trying to fix the formatting/printing issue? Please be more specific. — AMC, Nov 29 '19 at 06:03

oppressionslayer · Answer 1 · 2019-11-29T05:37:52.773

Try this, it should fix you dataframe:

ndf = df
ndf['Kas Grubu'] = ndf['Kas Grubu'].dropna().reset_index().drop(columns='index') 
ndf['Ekipmanlar'] = ndf['Ekipmanlar'].dropna().reset_index().drop(columns='index') 
ndf['Düzey'][54]="Level: Unknown"
ndf['Düzey'] = ndf['Düzey'].dropna().reset_index().drop(columns='index') 
ndf = ndf.dropna()

To display the full URL, use this option:

pd.set_option('display.max_colwidth', -1)

jezrael · Accepted Answer · 2019-11-29T06:11:37.170

You can use Series.dropna with apply:

#pandas 0.24+
df1 = df.apply(lambda x: pd.Series(x.dropna().to_numpy()))

#pandas bellow
df1 = df.apply(lambda x: pd.Series(x.dropna().values))

If all data are strings and performance is important use you can use a bit changed justify function: :

df1 = pd.DataFrame(justify(df.values, invalid_val=np.nan, axis=0), 
                  index=df.index, 
                  columns=df.columns).dropna(how='all')

Function:

def justify(a, invalid_val=0, axis=1, side='left'):    
    """
    Justifies a 2D array

    Parameters
    ----------
    A : ndarray
        Input array to be justified
    axis : int
        Axis along which justification is to be made
    side : str
        Direction of justification. It could be 'left', 'right', 'up', 'down'
        It should be 'left' or 'right' for axis=1 and 'up' or 'down' for axis=0.

    """

    if invalid_val is np.nan:
        mask = pd.notna(a)
    else:
        mask = a!=invalid_val
    justified_mask = np.sort(mask,axis=axis)
    if (side=='up') | (side=='left'):
        justified_mask = np.flip(justified_mask,axis=axis)
    out = np.full(a.shape, invalid_val, dtype=object) 
    if axis==1:
        out[justified_mask] = a[mask]
    else:
        out.T[justified_mask.T] = a.T[mask.T]
    return out

Output:

                            Hareket Adı  \
0                   Smith Machine Shrug   
1                        Leverage Shrug   
2         Standing Dumbbell Upright Row   
3             Kettlebell Sumo High Pull   
4                        Dumbbell Shrug   
5           Calf-Machine Shoulder Shrug   
6                         Barbell Shrug   
7         Barbell Shrug Behind The Back   
8                     Upright Cable Row   
9                          Cable Shrugs   
10             Upright Row - With Bands   
11  Smith Machine Behind the Back Shrug   
12            Smith Machine Upright Row   
13                          Clean Shrug   
14                     Scapular Pull-Up   
15                         Snatch Shrug   

                                                  Url                Düzey  \
0   https://www.bodybuilding.com/exercises/smith-m...      Level: Beginner   
1   https://www.bodybuilding.com/exercises/leverag...      Level: Beginner   
2   https://www.bodybuilding.com/exercises/standin...      Level: Beginner   
3   https://www.bodybuilding.com/exercises/kettleb...  Level: Intermediate   
4   https://www.bodybuilding.com/exercises/dumbbel...      Level: Beginner   
5   https://www.bodybuilding.com/exercises/calf-ma...      Level: Beginner   
6   https://www.bodybuilding.com/exercises/barbell...                        
7   https://www.bodybuilding.com/exercises/barbell...      Level: Beginner   
8   https://www.bodybuilding.com/exercises/upright...  Level: Intermediate   
9   https://www.bodybuilding.com/exercises/cable-s...      Level: Beginner   
10  https://www.bodybuilding.com/exercises/upright...      Level: Beginner   
11  https://www.bodybuilding.com/exercises/smith-m...      Level: Beginner   
12  https://www.bodybuilding.com/exercises/smith-m...      Level: Beginner   
13  https://www.bodybuilding.com/exercises/clean-s...      Level: Beginner   
14  https://www.bodybuilding.com/exercises/scapula...      Level: Beginner   
15  https://www.bodybuilding.com/exercises/snatch-...  Level: Intermediate   

   Kas Grubu   Ekipmanlar  
0      Traps      Machine  
1      Traps      Machine  
2      Traps     Dumbbell  
3      Traps  Kettlebells  
4      Traps     Dumbbell  
5      Traps      Machine  
6      Traps      Barbell  
7      Traps      Barbell  
8      Traps        Cable  
9      Traps        Cable  
10     Traps        Bands  
11     Traps      Machine  
12     Traps      Machine  
13     Traps      Barbell  
14     Traps         None  
15     Traps      Barbell

Is that first snippet serious?! Would that even work? I’m having trouble imagining what the output would look like. — AMC, Nov 29 '19 at 06:04
@AlexanderCécile - Sure, because if use `dropna` get no data — jezrael, Nov 29 '19 at 06:05
Are you certain that’s what OP wants though? His question is unclear. Did you run the first snippet? How does it work if the columns have different/uneven amounts of NaN values? Won’t you end up with columns of different length? — AMC, Nov 29 '19 at 06:09
Also the docs recommend against using `values`. It’s unnecessary in the first snippet anyway. — AMC, Nov 29 '19 at 06:10
@AlexanderCécile - It handle `apply` - add NaNs by column with mosts non missing values — jezrael, Nov 29 '19 at 06:10

how to delete nan values in pandas?

2 Answers2