How To Split A Column Into Alphabetic Values And Numeric Values From A Column In A Pandas Dataframe?
I have a dataframe: Name Section 1 James P3 2 Sam 2.5C 3 Billy T35 4 Sarah A85 5 Felix 5I How do I split numeric values into a separate column called
Solution 1:
Use str.replace
with str.extract
by [A-Z]+
for all uppercase strings:
df['Section_Number'] = df['Section'].str.replace('([A-Z]+)', '')
df['Section_Letter'] = df['Section'].str.extract('([A-Z]+)')
print (df)
Name Section Section_Number Section_Letter
1 James P3 3 P
2 Sam 2.5C 2.5 C
3 Billy T35 35 T
4 Sarah A85 85 A
5 Felix 5I 5 I
For seelct also lowercase values:
df['Section_Number'] = df['Section'].str.replace('([A-Za-z]+)', '')
df['Section_Letter'] = df['Section'].str.extract('([A-Za-z]+)')
print (df)
Name Section Section_Number Section_Letter
1 James P3 3 P
2 Sam 2.5C 2.5 C
3 Billy T35 35 T
4 Sarah A85 85 A
5 Felix 5I 5 I
Solution 2:
It'll no doubt be slower but throwing out an alternative for completeness you can use str.extractall
to get named groups matching the patterns and consolidate the matches and join back to your DF...
new = df.join(
df.Section.str.extractall(r'(?i)(?P<Section_Letter>[A-Z]+)|(?P<Section_Number>[\d.]+)')
.groupby(level=0).first()
)
Result:
Name Section Section_Letter Section_Number
1 James P3 P32 Sam 2.5C C 2.53 Billy T35 T 354 Sarah A85 A855 Felix 5I I5
Solution 3:
If, as in your example, you have one letter in each name, you can sort and then slice:
defget_vals(x):
return''.join(sorted(x, key=str.isalpha))
# apply ordering
vals = df['Section'].apply(get_vals)
# split numbers from letter
df['num'] = vals.str[:-1].astype(float)
df['letter'] = vals.str[-1]
print(df)
Name Section num letter
1 James P3 3.0 P
2 Sam 2.5C 2.5 C
3 Billy T35 35.0 T
4 Sarah A85 85.0 A
5 Felix 5I 5.0 I
Solution 4:
We can use itertools.groupby
to group the contiguous alpha and non-alpha
from itertools import groupby
[sorted([''.join(x) for _, x in groupby(s, key=str.isalpha)]) for s in df.Section]
[['3', 'P'], ['2.5', 'C'], ['35', 'T'], ['85', 'A'], ['5', 'I']]
We can manipulate this into new columns
from itertools import groupby
N, L = zip(
*[sorted([''.join(x) for _, x in groupby(s, key=str.isalpha)]) for s in df.Section]
)
df.assign(Selection_Number=N, Selection_Letter=L)
Name Section Selection_Number Selection_Letter
1 James P3 3 P
2 Sam 2.5C 2.5 C
3 Billy T35 35 T
4 Sarah A85 85 A
5 Felix 5I 5 I
Post a Comment for "How To Split A Column Into Alphabetic Values And Numeric Values From A Column In A Pandas Dataframe?"