Splitting a column based on character transition in a dataframe

Question

I want to make a basic split (see image below) with Python/Pandas.

For people who use Excel/PowerQuery, there is a nice function that allow them to do that, Splitter.SplitTextByCharacterTransition.

I tried to make an equivalent in Python using itertools but unfortunately I get a copy of the column "Col1":

import pandas as pd
import itertools

df = pd.DataFrame({'Col1': ['304', '321A', '14', '319B', '315', '88', '242C', '243']})

df['Col2'] = [''.join(a) for b, a in itertools.groupby(df['Col1'])]

`>>> df`

    Col1    Col2
0   304     304
1   321A    321A
2   14      14
3   319B    319B
4   315     315
5   88      88
6   242C    242C
7   243     243

Do you have any suggestions/propositions to fix that, please ?

So the column value is always `number+character` or `number` and doesn't have `char+num+char` or `num+char+num`? — Ynjxsjmh, Aug 21 '22 at 13:41

score 3 · Answer 1 · answered Aug 21 '22 at 14:01

3

Let us do extract

df['Col1'].str.extract(r'^(\d+)(.*)').fillna('')

answered Aug 21 '22 at 14:01

Shubham Sharma

68,127
6
24
53

I love when the code is short but gives the expected result. Thank you so much Shubham. What if we need to rename the columns `0` and `1` to `Col1` and `Col2` ? I think we need to specify a kind of `<>` string inside the `str.extract()` function but I don't know where exactly. – Timeless Aug 21 '22 at 14:11
1

@L'Artiste Yes, we can also use named capturing groups. Please check `df['Col1'].str.extract(r'^(?P\d+)(?P.*)').fillna('')` – Shubham Sharma Aug 21 '22 at 14:13

Ynjxsjmh · Accepted Answer · 2022-08-21T13:58:46.017

2

Let's try .str.extractall to extract both the number and character then get the first non value in group

out = df.join(df.pop('Col1')
              .str.extractall('(\d+)|([a-zA-Z]+)')
              .groupby(level=0).first()
              .fillna('')
              .set_axis(['Col1', 'Col2'], axis=1))

print(out)

  Col1 Col2
0  304
1  321    A
2   14
3  319    B
4  315
5   88
6  242    C
7  243

edited Aug 21 '22 at 13:58

answered Aug 21 '22 at 13:47

Ynjxsjmh

28,441
6
34
52

Hi @Ynjxsjmh, is it possible to make empty values instead of `None` ? – Timeless Aug 21 '22 at 13:56
@L'Artiste, do you mean empty strings? https://stackoverflow.com/q/31295740/14627505 – Vladimir Fokow Aug 21 '22 at 13:57
Yes, I mean empty strings, like in the `Col2` of @dn_alekseev's answer/output. – Timeless Aug 21 '22 at 13:58

score 1 · Answer 3 · answered Aug 21 '22 at 13:46

try Regular Expressions for work with text

Code that worked for me:

import pandas as pd
import itertools
import re

df = pd.DataFrame({'Col1': ['304', '321A', '14', '319B', '315', '88', '242C', '243']})

df['Col2'] = [re.sub("[^A-Za-z]", "", b) for b, a in itertools.groupby(df['Col1'])]
df['Col1'] = [re.sub("[^0-9]", "", b) for b, a in itertools.groupby(df['Col1'])]

print(df)

Output:

  Col1 Col2
0  304     
1  321    A
2   14     
3  319    B
4  315     
5   88     
6  242    C
7  243

score 0 · Answer 4 · answered Aug 21 '22 at 16:16

I'm adding a slightly modified version of the accepted answer and a deep dive into how it works hoping it will benefit some users who are still learning Pandas - like myself. :)

The accepted answer can me simplified because the df.join() is not needed:

out = (df.pop('Col1')
        .str.extractall('(\d+)|([a-zA-Z]+)')
        .groupby(level=0).first()
        .fillna('')
        .set_axis(['Col1', 'Col2'], axis=1))

Continue reading if you want to see a breakdown of how the solution works.

Self-contained runnable code:

import pandas as pd
import itertools

df = pd.DataFrame({'Col1': ['304', '321A', '14', '319B', '315', '88', '242C', '243']})
# print(df)
# Col1
# 0   304
# 1  321A
# 2    14
# 3  319B
# 4   315
# 5    88
# 6  242C
# 7   243

# Start with the given given data column
col1 = df['Col1']  # Equivalent to df.pop('Col1')
# print(col1)
# 0     304
# 1    321A
# 2      14
# 3    319B
# 4     315
# 5      88
# 6    242C
# 7     243

# Split the data into two columns based on the groups of
# the given regular expression.
# Docs:
# https://pandas.pydata.org/docs/reference/api/pandas.Series.str.extractall.html
split = col1.str.extractall('(\d+)|([a-zA-Z]+)')
# print(split)
#            0    1
#   split
# e 0      304  NaN
# 1 0      321  NaN
#   1      NaN    A
# 2 0       14  NaN
# 3 0      319  NaN
#   1      NaN    B
# 4 0      315  NaN
# 5 0       88  NaN
# 6 0      242  NaN
#   1      NaN    C
# 7 0      243  NaN

# split, above, has a multi-level index so we
# group by the first level (0) and specify that
# we wnat the first() (and ONLY) entry from
# each group. We could have used last().
# Docs:
# https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html
grouping = split.groupby(level=0).first()
# print(grouping)
# 0     1
# 0  304  None
# 1  321     A
# 2   14  None
# 3  319     B
# 4  315  None
# 5   88  None
# 6  242     C
# 7  243  None

# Here we change the None values to empty strings.
noNones = grouping.fillna('')
#print(noNones)
#      0  1
# 0  304
# 1  321  A
# 2   14
# 3  319  B
# 4  315
# 5   88
# 6  242  C
# 7  243

# Finally we label axis 1 (the df columns)
out = noNones.set_axis(['Col1', 'Col2'], axis=1)
# print(out)
#   Col1 Col2
# 0  304
# 1  321    A
# 2   14
# 3  319    B
# 4  315
# 5   88
# 6  242    C
# 7  243

Splitting a column based on character transition in a dataframe

>>> df

4 Answers4

`>>> df`