Splitting a column into 2 in a csv file using python-CodePudding

I have a .csv file with 100 rows of data displayed like this

"Jim 1234"

"Sam 1235"

"Mary 1236"

"John 1237"

What I'm trying to achieve is splitting the numbers from the names into 2 columns in python

edit*

Using,

    import pandas as pd
    df = pd.read_csv('test.csv', sep='\s ')
    df.to_csv('result.csv', index=False)

I managed to get it to display like this in excel

However, the numbers still do not show up in column B as I expected.

CodePudding user response：

Your data have only one column and a tab delimiter:

pd.read_csv('test.csv', quoting=1, header=None, squeeze=True) \
  .str.split('\t', expand=True) \
  .to_csv('result.csv', index=False, header=False)

CodePudding user response：

Pandas is quite easy to use:

import pandas as pd
df = pd.read_csv('test.csv', sep='\s ')
df.to_csv('result.csv', index=False)

And if your data includes the ", the following code should work:

import pandas as pd
df = pd.read_csv('test.csv', names=['c1'])
df[['name', 'number']] = df['c1'].str.extract(pat = '"(.*) (.*)"')
df.to_csv('result.csv', columns= ['name', 'number'], index=False)

CodePudding user response：

very simple way,

data=pd.DataFrame(['Jim1234','Sam4546'])
data[0].str.split('(\d )', expand=True)

CodePudding user response：

if your file resemble to the picture below then the next code will work csv file content

import pandas as pd 
df = pd.read_csv('a.csv', header=None, delimiter='\s')
df

code execution