Concatenating multiple dataframe columns into a single array under a dataframe column

Question

I am trying to concatenate multiple columns(all are numeric number) in a dataframe into an array under one single column of a dataframe. Caveat is if the concatenating columns have NAN, then should not be concatenated.

Input dataframe:

userid | p1 | p2 |p3 | p4 | p5
 1     | NAN| NAN| 20| 30 | 40
 1     | NAN| 30 | 60| 80 | 100
 2     | NAN| NAN|NAN| NAN| 45

I have tried this solution but it doesnt drop the nan:

df['combined'] = df[['p5','p4','p3','p2','p1']].apply(tuple,axis=1).apply(np.array)

Final Output dataframe must look like this(also the order of concatenation is p5,p4,p3,p2,p1) while also excluding nan while concatenating:

userid | p1 | p2 |p3 | p4 | p5 | combined
 1     | NAN| NAN| 20| 30 | 40 | [40,30,20]
 1     | NAN| 30 | 60| 80 | 100| [100,80,60,30]
 2     | NAN| NAN|NAN| NAN| 45 | [45]

So any solution for the above output will be really appreciated.

Dani Mesejo · Accepted Answer · 2019-10-15 14:41:15Z

1

You could use pd.isna to filter out the NaN values with boolean mask:

df['combined'] = df[['p5', 'p4', 'p3', 'p2', 'p1']].apply(lambda x: x[~pd.isna(x)].values, axis=1)
print(df)

Output

   userid  p1    p2    p3    p4   p5                   combined
0       1 NaN   NaN  20.0  30.0   40         [40.0, 30.0, 20.0]
1       1 NaN  30.0  60.0  80.0  100  [100.0, 80.0, 60.0, 30.0]
2       2 NaN   NaN   NaN   NaN   45                     [45.0]

As mentioned by @jpp, you could also drop them, by using dropna:

lambda x: x.dropna().values

edited Oct 15, 2019 at 14:41

answered Oct 15, 2019 at 14:35

Dani Mesejo

62.2k6 gold badges56 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jpp Over a year ago

Or df.iloc[:, 1:].apply(lambda x: x.dropna().tolist(), axis=1) ?

Datta Over a year ago

@Daniel - Thanks you for your solution provided, this worked for me

Dani Mesejo Over a year ago

@Datta glad I could help. Please consider marking this answer as accepted as it will signal others that your problem was solved.

Collectives™ on Stack Overflow

Concatenating multiple dataframe columns into a single array under a dataframe column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related