Python: Pandas Concatenate each row into a string

Question

I have the following Pandas Dataframe:

df=pd.DataFrame({0:["a","b","c","d"], 1:["e","f","g",None], 2:["h",None,None,None]})

   0     1     2
0  a     e     h
1  b     f  None
2  c     g  None
3  d  None  None

I like to create a new DataFrame with one column where each row is a concatenated string, with a seperator ",":

       0
0  a,e,h
1    b,f
2    c,g
3      d

For a single row I could use

df.iloc[0,:].str.cat(sep=",")

but how can I apply this to the whole DataFrame, without using a for-loop (if possible)

piRSquared · Accepted Answer · 2018-07-10 13:28:40Z

5

Stacking removes nulls by default. Follow-up with a groupby on level=0

df.stack().groupby(level=0).apply(','.join)

0    a,e,h
1      b,f
2      c,g
3        d
dtype: object

To duplicate OP's output, use to_frame

df.stack().groupby(level=0).apply(','.join).to_frame(0)

       0
0  a,e,h
1    b,f
2    c,g
3      d

answered Jul 10, 2018 at 13:28

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Comments

hootnot · Accepted Answer · 2018-07-10 13:16:51Z

4

for i, r in df.iterrows():
    print(r.str.cat(sep=","))

as a new dataframe:

ndf = pd.DataFrame([r.str.cat(sep=",") for i, r in df.iterrows()])
print(ndf)

       0
0  a,e,h
1    b,f
2    c,g
3      d

answered Jul 10, 2018 at 13:16

hootnot

1,0148 silver badges14 bronze badges

Comments

Scott Boston · Accepted Answer · 2018-07-10 13:31:10Z

4

You could use:

df.apply(lambda x: ','.join(x.dropna()), axis=1)

Output:

0    a,e,h
1      b,f
2      c,g
3        d
dtype: object

edited Jul 10, 2018 at 13:31

answered Jul 10, 2018 at 13:24

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Collectives™ on Stack Overflow

Python: Pandas Concatenate each row into a string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related