CODEWITHSUNDEEP
excelpandasdataframesplit
HiSoKa
HiSoKa

Reputation: 1

Splitting the data of one excel column into two columns sing python

I have problem of splitting the content of one excel column which contains numbers and letters into two columns the numbers in one column and the letters in the other.

As can you see in the first photo there is no space between the numbers and the letters, but the good thing is the letters are always "ms". I need a method split them as in the second photo.

Before

After

I tried to use the replace but it did not work. it did not split them.

Is there any other method.

Upvotes: 0

Views: 780

Answers (3)

Lukee
Lukee

Reputation: 1

Open the Excel file with this data in Notepad++, change ms for ,ms, save as CSV (comma delimited). Open with Excel.

Upvotes: 0

Parth Rathod
Parth Rathod

Reputation: 16

It is pretty simple.
You need to use pandas.Series.str.split
Attaching the Syntax here :- pandas.Series.str.split

The Code should be

import pandas as pd

data_before = {'data' : ['34ms','56ms','2435ms']}
df = pd.DataFrame(data_before)

result = df['data'].str.split(pat='(\d+)',expand=True)
result = result.loc[:,[1,2]]
result.rename(columns={1:'number', 2:'string'}, inplace=True)

Output : -

print(result)

Output

Upvotes: 0

BehRouz
BehRouz

Reputation: 1364

You can use the extract method. Here is an example:

df = pd.DataFrame({'time': ['34ms', '239ms', '126ms']})

df[['time', 'unit']] = df['time'].str.extract('(\d+)(\D+)')

# convert time column into integer
df['time'] = df['time'].astype(int)

print(df)

# output:
#     time unit
# 0   343   ms
# 1   239   ms
# 2   126   ms

Upvotes: 1

Related Questions