CODEWITHSUNDEEP
pythonpandasstringseries
Biomage
Biomage

Reputation: 344

Splitting pandas dataframe column (into two) after the first letter in the cell

The problem

I would like to split a column from a pandas dataframe into 2 columns, in the percentage column (see below), each entry starts with a capitalised alphabet character, I would like to split the 'Percentage' column immediately after this letter, with the new column labelled 'Amino Acid'.

Current Code:

import pandas as pd

df = pd.read_csv('foo.csv')

df['Amino Acid'], df['Percentage'] = zip(*df['Percentage'].map(lambda x: x.split('[^a-zA-Z]')))

df.to_csv('bar.csv',index=False)

Example of input data

+-----------------------------+-------+-----+-----------+---------------------------------------------------------------------------------------------+
|           Species           |  ID   | OGT |    DB     |                                         Percentage                                          |
+-----------------------------+-------+-----+-----------+---------------------------------------------------------------------------------------------+
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | E is 8.333003365670164% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa |
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | R is 6.310991522830762% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa |
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | A is 10.22668778459711% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa |
+-----------------------------+-------+-----+-----------+---------------------------------------------------------------------------------------------+

Example of desired output

+-----------------------------+-------+-----+-----------+------------+--------------------------------------------------------------------------------------------+
|           Species           |  ID   | OGT |    DB     | Amino Acid |                                         Percentage                                         |
+-----------------------------+-------+-----+-----------+------------+--------------------------------------------------------------------------------------------+
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | E          |  is 8.333003365670164% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa |
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | R          | is 6.310991522830762% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa  |
| Halogeometricum borinquense | 60847 |  37 | ATCC/DSMZ | A          | is 10.22668778459711% in ./archaea/GCF_000337855.1/GCF_000337855.1_ASM33785v1_protein.faa  |
+-----------------------------+-------+-----+-----------+------------+--------------------------------------------------------------------------------------------+

Upvotes: 7

Views: 7473

Answers (2)

jezrael
jezrael

Reputation: 863246

Use split be first whitespace:

df[['Amino Acid', 'Percentage']] = df['Percentage'].str.split(n=1, expand=True)

Upvotes: 6

jpp
jpp

Reputation: 164773

You can extract the first letter directly:

df['Amino Acid'] = df['Percentage'].str[0]
df['Percentage'] = df['Percentage'].str[1:]

Upvotes: 10

Related Questions