How to split text in a column into multiple rows

Question

I'm working with a large csv file and the next to last column has a string of text that I want to split by a specific delimiter. I was wondering if there is a simple way to do this using pandas or python?

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

I want to split by the space (' ') and then the colon (':') in the Seatblocks column, but each cell would result in a different number of columns. I have a function to rearrange the columns so the Seatblocks column is at the end of the sheet, but I'm not sure what to do from there. I can do it in excel with the built in text-to-columns function and a quick macro, but my dataset has too many records for excel to handle.

Ultimately, I want to take records such John Lennon's and create multiple lines, with the info from each set of seats on a separate line.

this great question relates to FlatMap in pandas, which currently not exists — cdarlint, Nov 08 '17 at 02:13

Dan Allan · Accepted Answer · 2014-04-25T14:54:40.533

242

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.

edited Apr 25 '14 at 14:54

answered Jun 14 '13 at 20:44

Dan Allan

34,073
6
70
63

7

@DanAllan give an index to the Series when you apply; they will become column names – Jeff Jun 14 '13 at 21:48
This is a great answer. However `.str.split(' ').apply(Series, 1).stack()` may be problematic if the split() call does not return a list (i.e. when the string does not contain any spaces), the dtype of the column object will be a Series, and not a string. – tmarthal Nov 03 '15 at 04:18
5

While this answers the question, it is worth mentioning that (probably) split() creates a list for each row, which blows up the size of the `DataFrame` very quickly. In my case, running the code on a ~200M table resulted in ~10G memory (+swap...) usage. – David Nemeskey Mar 24 '16 at 16:13
1

Though I am not sure it is because of `split()`, because simply `reduce()`'ing through the column works like a charm. The problem then may lie in `stack()`... – David Nemeskey Mar 24 '16 at 16:32
9

I am getting the error `NameError: name 'Series' is not defined` for this. where is `Series` supposed to come from? EDIT: nevermind, it should be `pandas.Series` since it is referring to the item from `pandas` – user5359531 Aug 16 '16 at 19:07
3

Yep, @user5359531. I `from pandas import Series` for convenience/brevity. – Dan Allan Aug 17 '16 at 19:48
tried to do this in a for loop so I could split up a bunch of files and it ended up freezing on me. That says to me that calling del on a column is a dangerous thing to do. – bgenchel Nov 27 '18 at 00:15
@Dan Allan, any idea why I am getting a memory error when trying to replicate this (the first part) and what I can do to fix that? – bernando_vialli Dec 19 '18 at 18:44
@David Nemeskey, do you know what is the way to bypass it? I got a MemoryError doing this and it took hours to get my computer up and running again – bernando_vialli Dec 20 '18 at 16:10
What does the `1` do in `.apply(pd.Series, 1)`? It can't be the `convert_dtype` argument from https://pandas.pydata.org/docs/reference/api/pandas.Series.apply.html and I've observed that replacing it with `0` or `2` gives the same result. So does omitting it at all. – gothicVI Aug 22 '23 at 06:12

Pietro Battiston · Answer 2 · 2017-10-18T09:29:22.760

Differently from Dan, I consider his answer quite elegant... but unfortunately it is also very very inefficient. So, since the question mentioned "a large csv file", let me suggest to try in a shell Dan's solution:

time python -c "import pandas as pd;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print df['col'].apply(lambda x : pd.Series(x.split(' '))).head()"

... compared to this alternative:

time python -c "import pandas as pd;
from scipy import array, concatenate;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print pd.DataFrame(concatenate(df['col'].apply( lambda x : [x.split(' ')]))).head()"

... and this:

time python -c "import pandas as pd;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print pd.DataFrame(dict(zip(range(3), [df['col'].apply(lambda x : x.split(' ')[i]) for i in range(3)]))).head()"

The second simply refrains from allocating 100 000 Series, and this is enough to make it around 10 times faster. But the third solution, which somewhat ironically wastes a lot of calls to str.split() (it is called once per column per row, so three times more than for the others two solutions), is around 40 times faster than the first, because it even avoids to instance the 100 000 lists. And yes, it is certainly a little ugly...

EDIT: this answer suggests how to use "to_list()" and to avoid the need for a lambda. The result is something like

time python -c "import pandas as pd;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print pd.DataFrame(df.col.str.split().tolist()).head()"

which is even more efficient than the third solution, and certainly much more elegant.

EDIT: the even simpler

time python -c "import pandas as pd;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print pd.DataFrame(list(df.col.str.split())).head()"

works too, and is almost as efficient.

EDIT: even simpler! And handles NaNs (but less efficient):

time python -c "import pandas as pd;
df = pd.DataFrame(['a b c']*100000, columns=['col']);
print df.col.str.split(expand=True).head()"

I'm having a little trouble with the amount of memory that this method consumes and I'm wondering if you could give me a little advice. I have a DataFrame that contains about 8000 rows, each with a string containing 9216 space delimited 8-bit integers. This is roughly 75MB, but when I apply the last solution verbatim, Python eats 2GB of my memory. Can you point me in the direction of some source that would tell me why this is, and what I can do to get around it? Thanks. — castle-bravo, Jul 03 '14 at 04:59
You have a lot of lists and very small strings, which is more or less the worst case for memory usage in python (and the intermediate step ".split().tolist()" produces pure python objects). What I would probably do in your place would be to dump the DataFrame to a file, and then open it as csv with read_csv(..., sep=' '). But to stay on topic: the first solution (together with the third, which however should be awfully slow) may be the one offering you the lowest memory usage among the 4, since you have a relatively small number of relatively long rows. — Pietro Battiston, Jul 03 '14 at 12:42
Hey Pietro, I tried your suggestion of saving to a file and re-loading, an it worked quite well. I ran into some trouble when I tried to do this in a StringIO object, and a nice solution to my problem has been posted [here](http://stackoverflow.com/questions/24562869/pandas-unable-to-read-from-large-stringio-object). — castle-bravo, Jul 03 '14 at 23:09
Your last suggestion of `tolist()` is perfect. In my case I only wanted one of the pieces of data in the list and was able to directly add a single column to my existing df by using .ix: `df['newCol'] = pd.DataFrame(df.col.str.split().tolist()).ix[:,2]` — fantabolous, Jul 21 '14 at 08:37
Ahh, I was having trouble getting this to work at first - something about `obect of type 'float' has no len()` which was baffling, until I realized *some* of my rows had `NaN` in them, as opposed to `str`. — dwanderson, May 21 '16 at 17:25

jezrael · Answer 3 · 2016-09-14T08:47:08.010

import pandas as pd
import numpy as np

df = pd.DataFrame({'ItemQty': {0: 3, 1: 25}, 
                   'Seatblocks': {0: '2:218:10:4,6', 1: '1:13:36:1,12 1:13:37:1,13'}, 
                   'ItemExt': {0: 60, 1: 300}, 
                   'CustomerName': {0: 'McCartney, Paul', 1: 'Lennon, John'}, 
                   'CustNum': {0: 32363, 1: 31316}, 
                   'Item': {0: 'F04', 1: 'F01'}}, 
                    columns=['CustNum','CustomerName','ItemQty','Item','Seatblocks','ItemExt'])

print (df)
   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

Another similar solution with chaining is use reset_index and rename:

print (df.drop('Seatblocks', axis=1)
             .join
             (
             df.Seatblocks
             .str
             .split(expand=True)
             .stack()
             .reset_index(drop=True, level=1)
             .rename('Seatblocks')           
             ))

   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

If in column are NOT NaN values, the fastest solution is use list comprehension with DataFrame constructor:

df = pd.DataFrame(['a b c']*100000, columns=['col'])

In [141]: %timeit (pd.DataFrame(dict(zip(range(3), [df['col'].apply(lambda x : x.split(' ')[i]) for i in range(3)]))))
1 loop, best of 3: 211 ms per loop

In [142]: %timeit (pd.DataFrame(df.col.str.split().tolist()))
10 loops, best of 3: 87.8 ms per loop

In [143]: %timeit (pd.DataFrame(list(df.col.str.split())))
10 loops, best of 3: 86.1 ms per loop

In [144]: %timeit (df.col.str.split(expand=True))
10 loops, best of 3: 156 ms per loop

In [145]: %timeit (pd.DataFrame([ x.split() for x in df['col'].tolist()]))
10 loops, best of 3: 54.1 ms per loop

But if column contains NaN only works str.split with parameter expand=True which return DataFrame (documentation), and it explain why it is slowier:

df = pd.DataFrame(['a b c']*10, columns=['col'])
df.loc[0] = np.nan
print (df.head())
     col
0    NaN
1  a b c
2  a b c
3  a b c
4  a b c

print (df.col.str.split(expand=True))
     0     1     2
0  NaN  None  None
1    a     b     c
2    a     b     c
3    a     b     c
4    a     b     c
5    a     b     c
6    a     b     c
7    a     b     c
8    a     b     c
9    a     b     c

Maybe it's worth mentioning that you necessarily need the `expand=True` option working with `pandas.DataFrames` while using `.str.split()` for example. — holzkohlengrill, Sep 14 '16 at 08:44
@holzkohlengrill - thank you for comment, I add it to answer. — jezrael, Sep 14 '16 at 08:47
@jezrael, it is taking me very long to execute this code, is that expected. How exactly do I make it faster? IF I put it in a for loop like: for x in df[Seablocks][:100] to only do it on a subset and then concatenate on these subsets, will that work? — bernando_vialli, Dec 20 '18 at 16:36

score 12 · Answer 4 · answered Nov 14 '20 at 18:23

It may be late to answer this question but I hope to document 2 good features from Pandas: pandas.Series.str.split() with regular expression and pandas.Series.explode().

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {'CustNum': [32363, 31316],
     'CustomerName': ['McCartney, Paul', 'Lennon, John'],
     'ItemQty': [3, 25],
     'Item': ['F04', 'F01'],
     'Seatblocks': ['2:218:10:4,6', '1:13:36:1,12 1:13:37:1,13'],
     'ItemExt': [60, 360]
    }
)

print(df)
print('-'*80+'\n')

df['Seatblocks'] = df['Seatblocks'].str.split('[ :]')
df = df.explode('Seatblocks').reset_index(drop=True)
cols = list(df.columns)
cols.append(cols.pop(cols.index('CustomerName')))
df = df[cols]


print(df)
print('='*80+'\n')
print(df[df['CustomerName'] == 'Lennon, John'])

The output is:

   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      360
--------------------------------------------------------------------------------

    CustNum  ItemQty Item Seatblocks  ItemExt     CustomerName
0     32363        3  F04          2       60  McCartney, Paul
1     32363        3  F04        218       60  McCartney, Paul
2     32363        3  F04         10       60  McCartney, Paul
3     32363        3  F04        4,6       60  McCartney, Paul
4     31316       25  F01          1      360     Lennon, John
5     31316       25  F01         13      360     Lennon, John
6     31316       25  F01         36      360     Lennon, John
7     31316       25  F01       1,12      360     Lennon, John
8     31316       25  F01          1      360     Lennon, John
9     31316       25  F01         13      360     Lennon, John
10    31316       25  F01         37      360     Lennon, John
11    31316       25  F01       1,13      360     Lennon, John
================================================================================

    CustNum  ItemQty Item Seatblocks  ItemExt  CustomerName
4     31316       25  F01          1      360  Lennon, John
5     31316       25  F01         13      360  Lennon, John
6     31316       25  F01         36      360  Lennon, John
7     31316       25  F01       1,12      360  Lennon, John
8     31316       25  F01          1      360  Lennon, John
9     31316       25  F01         13      360  Lennon, John
10    31316       25  F01         37      360  Lennon, John
11    31316       25  F01       1,13      360  Lennon, John

score 6 · Answer 5 · answered Jun 17 '20 at 10:44

6

This seems a far easier method than those suggested elsewhere in this thread.

split rows in pandas dataframe

answered Jun 17 '20 at 10:44

Timbo

61
1
1

score 2 · Answer 6 · edited Jun 11 '20 at 19:10

2

Another approach would be like this:

temp = df['Seatblocks'].str.split(' ')
data = data.reindex(data.index.repeat(temp.apply(len)))
data['new_Seatblocks'] = np.hstack(temp)

edited Jun 11 '20 at 19:10

10 Rep

2,217
7
19
33

answered Jun 11 '20 at 16:16

Bharat Sahu

21
1

score 1 · Answer 7 · edited Jun 21 '19 at 18:31

Can also use groupby() with no need to join and stack().

Use above example data:

import pandas as pd
import numpy as np


df = pd.DataFrame({'ItemQty': {0: 3, 1: 25}, 
                   'Seatblocks': {0: '2:218:10:4,6', 1: '1:13:36:1,12 1:13:37:1,13'}, 
                   'ItemExt': {0: 60, 1: 300}, 
                   'CustomerName': {0: 'McCartney, Paul', 1: 'Lennon, John'}, 
                   'CustNum': {0: 32363, 1: 31316}, 
                   'Item': {0: 'F04', 1: 'F01'}}, 
                    columns=['CustNum','CustomerName','ItemQty','Item','Seatblocks','ItemExt']) 
print(df)

   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  


#first define a function: given a Series of string, split each element into a new series
def split_series(ser,sep):
    return pd.Series(ser.str.cat(sep=sep).split(sep=sep)) 
#test the function, 
split_series(pd.Series(['a b','c']),sep=' ')
0    a
1    b
2    c
dtype: object

df2=(df.groupby(df.columns.drop('Seatblocks').tolist()) #group by all but one column
          ['Seatblocks'] #select the column to be split
          .apply(split_series,sep=' ') # split 'Seatblocks' in each group
         .reset_index(drop=True,level=-1).reset_index()) #remove extra index created

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

Thanks in advance. How I could use the above code by splitting two columns correpsindingly. For Example: 0 31316 Lennon, John 25 F01 300 1:13:36:1,12 1:13:37:1,13 A,B.. The result should be: `0 31316 Lennon, John 25 F01 300 1:13:36:1,12 A` and next line `0 31316 Lennon, John 25 F01 300 1:13:37:1,13 B` — Krithi.S, Feb 14 '20 at 04:48
@Krithi.S, I try to understand the question. Do you mean the two columns must have same number of members after splitting? What is your expected results for 0 31316 Lennon, John 25 F01 300 1:13:36:1,12 1:13:37:1,13 A,B,C ? — Ben2018, Feb 16 '20 at 04:48

How to split text in a column into multiple rows

7 Answers7

Linked

Related