How to Split Text in a Pandas Column into Multiple Rows?

Splitting Text in a Column into Multiple Rows using Pandas

When handling large CSV files, it becomes necessary to manipulate data efficiently. One common task is to split text in a column into multiple rows. This can be achieved using Pandas, a powerful data manipulation library in Python.

Suppose we have a CSV file with a column named "Seatblocks" containing text values separated by spaces and colons. Our goal is to split each value in this column into individual rows, creating new columns for each colon-separated part.

CustNum  CustomerName     ItemQty  Item   Seatblocks                 ItemExt
32363    McCartney, Paul      3     F04    2:218:10:4,6                   60
31316    Lennon, John        25     F01    1:13:36:1,12 1:13:37:1,13     300

To split the "Seatblocks" column by space and give each part a separate row, we use the following code:

s = df['Seatblocks'].str.split(' ').apply(Series, 1).stack()
s.index = s.index.droplevel(-1)
s.name = 'Seatblocks'
del df['Seatblocks']
df = df.join(s)

This code produces the following output:

   CustNum     CustomerName  ItemQty Item  ItemExt  Seatblocks
0    32363  McCartney, Paul        3  F04       60  2:218:10:4,6
1    31316     Lennon, John       25  F01      300  1:13:36:1,12
1    31316     Lennon, John       25  F01      300  1:13:37:1,13

To split each colon-separated string in its own column, we can use the following code:

df.join(s.apply(lambda x: Series(x.split(':'))))

This results in the following:

   CustNum     CustomerName  ItemQty Item  ItemExt  0    1   2     3
0    32363  McCartney, Paul        3  F04       60  2  218  10   4,6
1    31316     Lennon, John       25  F01      300  1   13  36  1,12
1    31316     Lennon, John       25  F01      300  1   13  37  1,13

These methods provide effective ways to split text in a column into multiple rows, enabling further data manipulation and analysis.

The above is the detailed content of How to Split Text in a Pandas Column into Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!