How to split text in a column into multiple rows

by

This splits the Seatblocks by space and gives each its own row.

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

In [44]: s = df['Seatblocks'].str.split(' ').apply(Series, 1).stack()

In [45]: s.index = s.index.droplevel(-1) # to line up with df's index

In [46]: s.name="Seatblocks" # needs a name to join

In [47]: s
Out[47]: 
0    2:218:10:4,6
1    1:13:36:1,12
1    1:13:37:1,13
Name: Seatblocks, dtype: object

In [48]: del df['Seatblocks']

In [49]: df.join(s)
Out[49]: 
   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

Or, to give each colon-separated string in its own column:

In [50]: df.join(s.apply(lambda x: Series(x.split(':'))))
Out[50]: 
   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

This is a little ugly, but maybe someone will chime in with a prettier solution.

More Related Contents:

  1. How to iterate over rows in a DataFrame in Pandas
  2. Difference between map, applymap and apply methods in Pandas
  3. Pandas filtering for multiple substrings in series
  4. Python pandas insert list into a cell
  5. How to replace text in a string column of a Pandas dataframe?
  6. Combine two pandas Data Frames (join on a common column)
  7. Pandas DataFrame Groupby two columns and get counts
  8. Pandas dataframe get first row of each group
  9. pandas .at versus .loc
  10. How to deal with multi-level column names downloaded with yfinance
  11. Replace all occurrences of a string in a pandas dataframe (Python)
  12. Running get_dummies on several DataFrame columns?
  13. Selection with .loc in python
  14. How to flatten a hierarchical index in columns
  15. Add column to dataframe with constant value
  16. ‘DataFrame’ object has no attribute ‘sort’
  17. python pandas – creating a column which keeps a running count of consecutive values
  18. Turn Pandas Multi-Index into column
  19. What is the fastest and most efficient way to append rows to a DataFrame?
  20. Creating an empty Pandas DataFrame, and then filling it
  21. Check if rows in one dataframe exist in another dataframe
  22. Pretty print a pandas dataframe in VS Code
  23. Pandas – Interleave / Zip two DataFrames by row
  24. Transpose the data in a column every nth rows in PANDAS
  25. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  26. Calculate summary statistics of columns in dataframe
  27. Check if certain value is contained in a dataframe column in pandas [duplicate]
  28. Pandas: Join dataframe with condition
  29. UnicodeDecodeError when reading CSV file in Pandas
  30. how to understand closed and label arguments in pandas resample method?

Leave a Comment