Merging a DataFrame with a lot of NaN

Question

Hi I have Pandas data frame that looks like this:

      0            1    2         3            4     5     6
150  NaN          NaN  NaN       NaN   March 1980   NaN   NaN
151  NaN          NaN  NaN       NaN    June 1990   NaN   NaN
152  NaN          NaN  NaN  Sep 2015          NaN   NaN   NaN
153  NaN          NaN  NaN  Jan 1972          NaN   NaN   NaN
154  NaN          NaN  NaN  Mar 1974          NaN   NaN   NaN

I cannot use dropna(), cuz I will have an empty dataframe.

All columns have one data in one column, is there a way to transform it in one column DataFrame?

         0           
150  March 1980
151  June 1990
152  Sep 2015
153  Jan 1972
154  Mar 1974

Thanks.

Possible duplicate of [How to drop rows of Pandas DataFrame whose value in certain columns is NaN](https://stackoverflow.com/q/13413590/1278112) — Shihe Zhang, Oct 30 '17 at 03:25
@ShiheZhang Not that dupe of the linked one but a dupe of some other question. — Bharath M Shetty, Oct 30 '17 at 03:57

Vaishali · Accepted Answer · 2017-10-30T03:08:09.050

4

Try

df = df.fillna('').sum(1)

or

df = df.fillna('').apply(''.join, axis = 1)

You get

150    March 1980
151     June 1990
152      Sep 2015
153      Jan 1972
154      Mar 1974
dtype: object

edited Oct 30 '17 at 03:08

answered Oct 30 '17 at 03:03

Vaishali

37,545
5
58
86

Thanks, It worked perfectly, but what is the .sum(1) is doing?, I want to believe that it is summing the other columns and because they are empty strings it collapses to one? – Guillermo Izquierdo Oct 30 '17 at 03:05
We are using sum here inplace of join as the dtype is clearly object. Its equivalent of saying df.fillna('').apply(''.join, axis = 1) – Vaishali Oct 30 '17 at 03:06

BENY · Answer 2 · 2017-10-30T03:13:12.790

3

Is this what you want ?

df.apply(lambda x : sorted(x,key=pd.isnull),axis=1).dropna(1)
Out[1052]: 
             0
150  March1980
151   June1990
152    Sep2015
153    Jan1972
154    Mar1974

Or

df.bfill(1).iloc[:,0]
Out[1056]: 
150    March1980
151     June1990
152      Sep2015
153      Jan1972
154      Mar1974
Name: 0, dtype: object

Or

df.stack()
Out[1058]: 
150  4    March1980
151  4     June1990
152  3      Sep2015
153  3      Jan1972
154  3      Mar1974
dtype: object

edited Oct 30 '17 at 03:13

answered Oct 30 '17 at 03:02

BENY

317,841
20
164
234

bfill is very interesting, can't upvote again though ;) – Vaishali Oct 30 '17 at 03:07
1

@Bharathshetty using my phone upvoted .. time for sleep ~ – BENY Oct 30 '17 at 04:00

Bharath M Shetty · Answer 3 · 2017-10-30T03:53:03.000

Another solution is with the help of boolean mask and pd.notnull, which is a lot lot faster than sum and sorted i.e

sdf = pd.DataFrame(df.values[pd.notnull(df)],index=df.index)

Output :

            0
150  March1980
151   June1990
152    Sep2015
153    Jan1972
154    Mar1974

ndf = pd.concat([df.reset_index(drop=True)]*1000)

%%timeit
ndf.apply(lambda x : sorted(x,key=pd.isnull),axis=1).dropna(1)
1 loop, best of 3: 1.29 s per loop

%%timeit
ndf.bfill(1).iloc[:,0]
1 loop, best of 3: 773 ms per loop

%%timeit
ndf.fillna('').sum(1)
10 loops, best of 3: 26.4 ms per loop

%%timeit
pd.DataFrame(ndf.values[pd.notnull(ndf)],index=ndf.index)
100 loops, best of 3: 3.11 ms per loop

Merging a DataFrame with a lot of NaN

3 Answers3