0

I have got a lot of column in a csv, and I want to split a column into 2.

My column:

enter image description here

I need this format :

enter image description here

How can I split it? It is a little bit complex for me.

EDIT: I tried this one, but it isn't working.

df = pd.read_csv('/tmp/test.csv')
df[['Animals', 'Animals2']] = df['Animals'].str.split('(^\D+)', expand=True).drop(0,1)
df['Animals'] = df['Animals'].str.strip()
Harley
  • 469
  • 1
  • 6
  • 16

1 Answers1

3

Use regex and str.split with expand i.e 

df = pd.DataFrame({'Animal':['Cat 3:30 pm','Hamster pig 4:30 pm','Cancelled']})
df[['Animal','Time']] = df['Animal'].str.split('(^\D+)',expand=True).drop(0,1)
df['Animal'] = df['Animal'].str.strip()

Output : 

      Animal     Time
0          Cat   3:30 pm
1  Hamster pig   4:30 pm
2     Cancelled
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
Bharath M Shetty
  • 30,075
  • 6
  • 57
  • 108
  • I updated my question with your code, but it isn't work, what am i wrong? – Harley Nov 14 '17 at 13:26
  • Traceback (most recent call last): File "test7.py", line 158, in df[['Animals', 'Animals2']] = df['Animals'].str.split('(^\D+)', expand=True).drop(0,1) File "/home/web/.local/lib/python2.7/site-packages/pandas/core/frame.py", line 1964, in __getitem__ return self._getitem_column(key) – Harley Nov 14 '17 at 13:33
  • File "pandas/_libs/hashtable_class_helper.pxi", line 1218, in pandas._libs.hashtable.PyObjectHashTable.get_item (pandas/_libs/hashtable.c:20477) KeyError: 'Animals' – Harley Nov 14 '17 at 13:34
  • Did you read my edit with my code what i tried? Is it correct, sure? – Harley Nov 14 '17 at 13:42
  • df = pd.read_csv('/tmp/test.csv') df[['Animals', 'Animals2']] = df['Animals'].str.split('(^\D+)', expand=True).drop(0,1) df['Animals'] = df['Animals'].str.strip() – Harley Nov 14 '17 at 13:45
  • Sir its right. Either post sample of `test.csv` or `df.head()`. Key error resolving is done looking at the data not code believe me. – Bharath M Shetty Nov 14 '17 at 13:46
  • I see the problem now! I think i mistaked with the read ! Because there is 340 rows and 1 columns, what is bad! How can i read it with ; separate? – Harley Nov 14 '17 at 13:47
  • And how can I save it? Because the df.to_csv("test.csv", index=False, sep=";") not working well – Harley Nov 14 '17 at 14:05
  • + how can i take it next to the Animals column? – Harley Nov 14 '17 at 14:13
  • Sir I have my assignments to write can't answer now will answer in mean time. – Bharath M Shetty Nov 14 '17 at 14:14