Dataframe using str() method on multiple columns

Question

I have a dataframe in which multiple columns contain comma-separated string values. I want to convert this into a list with a comma-separated string. I have a way to achieve this, but I am looking for a better way.

df = pd.DataFrame({"A": ["test1, test2, test3, test4", "check1, check2, check3, check4", "test1, test2, test3, check4", "test1, test2, test3, check5"], "B": ["a,b,c,d", "e,f,g,h", "i,j,k,l", "m,n,o,p"], "C": ["mtest, mtest1, mtest2, mtest3", "c,d,e,f", "g,h,i,j", "k,l,m,n"]})

>>> df
                                A        B                              C
0      test1, test2, test3, test4  a,b,c,d  mtest, mtest1, mtest2, mtest3
1  check1, check2, check3, check4  e,f,g,h                        c,d,e,f
2     test1, test2, test3, check4  i,j,k,l                        g,h,i,j
3     test1, test2, test3, check5  m,n,o,p                        k,l,m,n

The output that I want is

>>> df
                                     A             B                                   C
0      [test1,  test2,  test3,  test4]  [a, b, c, d]  [mtest,  mtest1,  mtest2,  mtest3]
1  [check1,  check2,  check3,  check4]  [e, f, g, h]                        [c, d, e, f]
2     [test1,  test2,  test3,  check4]  [i, j, k, l]                        [g, h, i, j]
3     [test1,  test2,  test3,  check5]  [m, n, o, p]                        [k, l, m, n]

My present method of achieving this is:-

>>> df["A"] = df["A"].str.split(',')
>>> df["B"] = df["B"].str.split(',')
>>> df["C"] = df["C"].str.split(',')

I want some operation on dataframe that can do this in 1 line instead of me going and apply str.split on every column(Since if there are more than 10 columns, I have to write this statement str split for all column). Lambda can be used to achieve this but it might be a slower operation. Is there a better way?

you can also try df.stack().str.split(',').unstack() and see how it goes — anky
– anky, Commented Aug 3, 2020 at 6:57
@anky I think stack + unstack might be slower than using apply. — Shubham Sharma
– Shubham Sharma, Commented Aug 3, 2020 at 7:03
@ShubhamSharma Yeah I think so too but havent tested. OP seems to already tried apply so commented an alternative :) — anky
– anky, Commented Aug 3, 2020 at 7:04

A.B · Accepted Answer · 2020-08-03 06:55:18Z

1

You can apply it on all columns as

df.apply(lambda x: x.str.split(','))

answered Aug 3, 2020 at 6:55

A.B

20.5k3 gold badges43 silver badges74 bronze badges

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

Comments

ilyankou · Accepted Answer · 2020-08-03 06:55:40Z

1

If you want to apply your function to all cells in the dataframe, you can use applymap:

df = df.applymap(lambda x: x.split(','))

or for specific columns:

df[['A', 'B', 'C']] = df[['A', 'B', 'C']].applymap(lambda x: x.split(','))

answered Aug 3, 2020 at 6:55

ilyankou

1,3298 silver badges14 bronze badges

Comments

Praveen Sujanmulk · Accepted Answer · 2020-08-03 07:02:59Z

1

This might be helpful:

Code:

df=pd.DataFrame([df[c].apply(lambda x: x.split(',')) for c in df]).T

or simply:

df=df.applymap(lambda x: x.split(','))

answered Aug 3, 2020 at 7:02

Praveen Sujanmulk

413 bronze badges

Comments

sammywemmy · Accepted Answer · 2020-08-03 07:45:53Z

1

You can also run the computation with a list comprehension and create a new dataframe :

pd.DataFrame(
    [[string.split(",") for string in entry] for entry in df.to_numpy()],
    columns=df.columns,
)

I think this is much faster than the other suggestions. As always, you have to test to be sure.

answered Aug 3, 2020 at 7:45

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Comments

Alvin-Pranata · Accepted Answer · 2020-08-03 06:58:14Z

I Think your code should be like this

print(pd.DataFrame({"A": ["[test1, test2, test3, test4]", "[check1, check2, check3, check4]", "[test1, test2, test3, check4]", "[test1, test2, test3, check5]"], "B": ["[a,b,c,d]", "[e,f,g,h]", "[i,j,k,l]", "[m,n,o,p]"], "C": ["[mtest, mtest1, mtest2, mtest3]", "[c,d,e,f]", "[g,h,i,j]", "[k,l,m,n]"]}))

and the output will be

                                  A          B                                C
   0      [test1, test2, test3, test4]  [a,b,c,d]  [mtest, mtest1, mtest2, mtest3]
   1  [check1, check2, check3, check4]  [e,f,g,h]                         [c,d,e,f]
   2     [test1, test2, test3, check4]  [i,j,k,l]                          [g,h,i,j]
   3     [test1, test2, test3, check5]  [m,n,o,p]                         [k,l,m,n]

Collectives™ on Stack Overflow

Dataframe using str() method on multiple columns

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related