CODEWITHSUNDEEP
pythonpandasdataframe
felixpradoh
felixpradoh

Reputation: 135

Create a DataFrame without NaN values from another DataFrame

I am working with the following DataFrame:

df1 = pd.DataFrame([
                    [1,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN],
                    [0.5,2,np.NaN,np.NaN,np.NaN,np.NaN],
                    [np.NaN,1.5,3,np.NaN,np.NaN,np.NaN],
                    [np.NaN,np.NaN,2.5,4,np.NaN,np.NaN],
                    [np.NaN,np.NaN,np.NaN,3.5,5,5.5],
                    [np.NaN,np.NaN,np.NaN,np.NaN,6.2,6],
                    ], columns=['AA','BB','CC','DD', 'EE', 'FF'])

And as output I get:

DataFrame1_______
    AA   BB   CC   DD   EE   FF
0  1.0  NaN  NaN  NaN  NaN  NaN
1  0.5  2.0  NaN  NaN  NaN  NaN
2  NaN  1.5  3.0  NaN  NaN  NaN
3  NaN  NaN  2.5  4.0  NaN  NaN
4  NaN  NaN  NaN  3.5  5.0  5.5
5  NaN  NaN  NaN  NaN  6.2  6.0

I would like to know if there is a way to convert this dataframe to another without NaNs values such as:

new_DataFrame1______
    AA   BB   CC   DD   EE   FF
0  1.0  2.0  3.0  4.0  5.0  5.5
1  0.5  1.5  2.5  3.5  6.2  6.0

Basically i would like to move every value that is not NaN to the index=0 of its column.

Thanks in advance

Upvotes: 0

Views: 1182

Answers (2)

jezrael
jezrael

Reputation: 862901

Use justify with remove only missing rows by DataFrame.dropna:

#https://stackoverflow.com/a/44559180/2901002
df = pd.DataFrame(justify(df1.to_numpy(), invalid_val=np.nan, axis=0), 
                  columns=df1.columns).dropna(how='all')
print (df)
    AA   BB   CC   DD   EE   FF
0  1.0  2.0  3.0  4.0  5.0  5.5
1  0.5  1.5  2.5  3.5  6.2  6.0

Another solution:

df = pd.concat([df1[c].dropna().reset_index(drop=True) for c in df1.columns], axis=1)
print (df)
    AA   BB   CC   DD   EE   FF
0  1.0  2.0  3.0  4.0  5.0  5.5
1  0.5  1.5  2.5  3.5  6.2  6.0

Upvotes: 3

Henry Yik
Henry Yik

Reputation: 22503

You can also use stack and groupby with dict comprehension:

print (pd.DataFrame({col:i.tolist() for col, i in df1.stack().groupby(level=1)}))

    AA   BB   CC   DD   EE   FF
0  1.0  2.0  3.0  4.0  5.0  5.5
1  0.5  1.5  2.5  3.5  6.2  6.0

Upvotes: 2

Related Questions