Create a DataFrame without NaN values from another DataFrame

Question

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

I have edited the question since it was an example and I dont really have a diagonal dataframe. — felixpradoh, Sep 10 '20 at 06:56
@FélixdelPradoHurtado - Then use another solution with `bfill` — jezrael, Sep 10 '20 at 07:01

jezrael · Answer 1 · 2020-09-10T07:20:18.303

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

score 2 · Accepted Answer · answered Sep 10 '20 at 07:16

2

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

answered Sep 10 '20 at 07:16

Henry Yik

22,275
4
18
40

Create a DataFrame without NaN values from another DataFrame

2 Answers2