Pandas Get Non-Null Values From Row Into One Cell

Question

Given the following data frame:

a = pd.DataFrame({'A': [1,2], 'B': [4,0], 'C': [1,2]})
a
    A   B   C
0   1   4   1
1   2   0   2

I would like to create a new column D containing the non-null values (per row) separated by columns. Like this:

    A   B   C    D
0   1   4   1    1,4,1
1   2   0   2    1,0,2

In reality, I will have many columns. Thanks in advance!

seems like you need `df.apply(lambda x :','.join(x.astype(str)),axis=1)` — BENY, Aug 22 '17 at 20:18

Brad Solomon · Accepted Answer · 2017-11-19T02:35:28.467

1

An alternative:

a['D'] = a.apply(lambda row: ','.join(row.dropna()
          .astype(int).astype(str)), axis=1)

print(a)
   A  B  C      D
0  1  4  1  1,4,1
1  2  0  2  2,0,2

edited Nov 19 '17 at 02:35

answered Aug 22 '17 at 20:19

Brad Solomon

38,521
31
149
235

2

`apply` with a lambda _and_ a loop? – cs95 Aug 22 '17 at 20:42

score 1 · Answer 2 · answered Aug 22 '17 at 20:22

1

# example data with NaN values
a = pd.DataFrame({'A': [np.nan,2], 'B': [4,np.nan], 'C': [1,2]})
a
     A    B  C
0  NaN  4.0  1
1  2.0  NaN  2

# make new column with non-null values
a['D'] = a.apply(lambda x: [val for val in x if not np.isnan(val)], axis=1)
a
     A    B  C           D
0  NaN  4.0  1  [4.0, 1.0]
1  2.0  NaN  2  [2.0, 2.0]

answered Aug 22 '17 at 20:22

andrew_reece

20,390
3
33
58

Having trouble with this in pandas 0.20.3 and I'm not sure why, frankly. – Brad Solomon Nov 17 '17 at 19:51
I just tested again, also on 0.20.3, no issues. What's the trouble you're having? – andrew_reece Nov 17 '17 at 19:56
Very strange. Mind trying with `a = pd.DataFrame({'A': [1,2], 'B': [4,0], 'C': [1,2]}, dtype=float)`? – Brad Solomon Nov 17 '17 at 19:57
I get an error then: `ValueError: Wrong number of items passed 3, placement implies 1`. I knocked it around a bit just now but couldn't figure out why it barfs when all values aren't `NaN`. I'll have a look later. Good observation, any ideas? – andrew_reece Nov 17 '17 at 20:07
No, like I said I'm a bit stumped because your solution is pretty straightforward. And [this](https://stackoverflow.com/a/33098470/7954504) solution works for both cases. – Brad Solomon Nov 17 '17 at 20:08

score 1 · Answer 3 · answered Aug 22 '17 at 20:24

You can do something along the lines of the following:

combVals = []
a = a.T
for col in a.columns:
    combVals.append(str(a[col].dropna().astype(int).tolist())[1:-1])
a = a.T
a['D'] = combVals
print(a)
   A  B  C        D
0  1  4  1  1, 4, 1
1  2  0  2  2, 0, 2

You can remove the spaces in column D by doing: a['D'] = a['D'].str.replace(' ','')

Pandas Get Non-Null Values From Row Into One Cell

3 Answers3

Linked