1

I have csv file called data.csv which contains full address format separated by commas:

id;address
1;village1, parish1, county1
2;village2, parish2, county2
3;village3, parish3, county3

Using pandas, I would like to export those last 2 (parish and county) into separate columns from the right.

and export them as exported.csv

eg.

import pandas as pd

df = pd.read_csv('data.csv', delimiter=';')

...

df.to_csv('exported.csv', sep=';', encoding='utf8', index=True)

print ("Done!")

Note: those rows are over 100k

How to do that?

tuuni
  • 111
  • 1
  • 2
  • 9
  • Have you looked into [`Series.str.split`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.split.html)? – ALollz Feb 12 '21 at 02:49
  • Yes, I am not sure how to create it. – tuuni Feb 12 '21 at 02:57
  • 3
    Does this answer your question? [How to split a dataframe string column into two columns?](https://stackoverflow.com/questions/14745022/how-to-split-a-dataframe-string-column-into-two-columns) – Leo103 Feb 12 '21 at 04:38

2 Answers2

0

Something like this ought to work.

data = pd.read_csv('data.csv', sep=';')
addresses = data.address.str.split(pat=', ', expand=True)
parish = addresses[1]
county = addresses[2]

df = pd.DataFrame(data={'parish': parish, 'county': county})
df.to_csv('exported.csv', sep=';', encoding='utf8', index=True)

You can convert columns from a DataFrame into Series.str and call split. I am unsure if there is a cleaner way to trim spaces, but in your case the separator has to have a trailing space if you want your fields to be trimmed.

BarricadeX75
  • 96
  • 9
0

Maybe there is a better way to do this but I was able to do it like this

import pandas as pd
df = pd.read_csv('data.csv', delimiter=';')

address = df.address.str.split(',', expand=True)
df = pd.concat([df , address], axis=1)
df.columns = ['id', 'address', 'village', 'parish', 'county']
df.to_csv('exported.csv', sep=';', encoding='utf8', index=True)

print ("Done!")

The file 'exported.csv' using the example data you provided will look like this:

;id;address;village;parish;county
0;1;village1, parish1, county1;village1; parish1; county1
1;2;village2, parish2, county2;village2; parish2; county2
2;3;village3, parish3, county3;village3; parish3; county3
Miguel Almonte
  • 191
  • 7