concatenating multiple columns include NaN in dataframe

Question

i want to concatenate/join many columns include Nan value to one new column. how to avoid/pass the NaN in join result? below just to show my try i used both .agg and .apply.

import pandas as pd
import numpy as np
df = pd.DataFrame({'foo':['a',np.nan,'c'], 'bar':[1, 2, 3], 'new':['apple', 'banana', 'pear']})
subcat_names=["foo","new"]

df["result"] = df[subcat_names].astype(str).agg(','.join, axis=1)

df=df.fillna("")

df["result_2"] =df[subcat_names].apply(lambda x : '{},{}'.format(x[0],x[1]), axis=1)

print(df)
    
  foo  bar     new      result result_2
0   a    1   apple     a,apple  a,apple
1        2  banana  nan,banana  ,banana
2   c    3    pear      c,pear   c,pear

at result the nan, is unwanted at result_2 , is unwanted

thanks

Jamiu S. · Accepted Answer · 2023-01-09 11:23:16Z

1

You can try pd.notnull()

subcat_names = ["foo", "new"]
df["result"] = df[subcat_names].apply(lambda x: ",".join(x[pd.notnull(x)]), axis=1)
print(df)

Output:

   foo  bar     new   result
0    a    1   apple  a,apple
1         2  banana   banana
2    c    3    pear   c,pear

answered Jan 9, 2023 at 11:23

Jamiu S.

5,7715 gold badges21 silver badges40 bronze badges

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

2 Comments

Ninja Over a year ago

thanks, the exact reason of using lambda to filter

Jamiu S. Over a year ago

That's right, @Ninja I realized because you didn't use the right approach on result that was the main reason why you introduced result_2 and now trying to solve both problems. But basically result_2 is just a duplicate of result.

CaptainTrunky · Accepted Answer · 2023-01-09 11:22:44Z

1

I think that the second option is almost correct, you just have to implement your lambda in a bit more involved way. The following is pseudocode and it's not tested:

def process(row):
    filtered = list()

    for item in row:
        if np.isnan(item).any():
            continue

        filtered.append(item)

    return ",".join(filtered)

df["result_2"] =df[subcat_names].apply(process, axis=1)

Most likely you could rely on not_na pandas function to collect valid values out of current row

answered Jan 9, 2023 at 11:22

CaptainTrunky

1,7172 gold badges17 silver badges26 bronze badges

1 Comment

Ninja Over a year ago

thanks and this work with replacing if np.isnan(item).any(): with if pd.isnull(item): .

Collectives™ on Stack Overflow

concatenating multiple columns include NaN in dataframe

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related