Можно ли программно очистить эти данные с помощью Python?
Я ищу более быстрый способ очистки данных в электронной таблице приличного размера (между строками 5-15 тыс.), Проблема, с которой я продолжаю сталкиваться, заключается в том, что данные находятся в такой плохой форме, что мне потребовались годы, чтобы фактически очистить их, используя только Excel/SQL. Честно говоря, я мало что знаю о python, но мне очень интересно учиться, чтобы упростить себе работу.
Это пример данных, которые мне нужно очистить. Даже если бы я прошел 50% пути, это сэкономило бы мне часы.
| Place | Address | City | State | Zip |
|Burger King Hartford, CT | 123 Apple St W|--|--|--|
|McDonald's| 5 Big Mac dr | New York, NY 10001|--|--|
|Arby's|--| Sturgis, KY |--|--|
|Taco Bell|--| Grand Rapids MI 49501 |--|--|
|Jimmy Johns|--| Fort Wayne, IN. 46774 |--|--|
Я думал о двух разных подходах к этой проблеме, но я не уверен, что любой из них возможен. Если вы считаете, что любой из них возможен, или если у вас есть идея, которая будет работать лучше, я был бы очень благодарен.
Моей первой мыслью было бы найти в каждой строке в фрейме данных определенную подстроку, которая будет списком всех сокращений состояний (MI, IN, KY и т. д.). Если одна из этих подстрок найдена, перейдите к столбцу состояния. То же самое будет сделано для почтовых индексов, но, поскольку их около 47 000, я не знаю, насколько это будет легко.
Моя вторая мысль заключается в использовании функции split() в python и разделении столбцов сначала разделителем , что оставило бы мне что-то вроде этого
| Place | Address | City | State | Zip |
|Burger King Hartford | 123 Apple St W|--|CT|--|
|McDonald's| 5 Big Mac dr | New York |NY 10001|--|
|Arby's|--| Sturgis |KY|--|
|Taco Bell|--| Grand Rapids MI 49501 |--|--|
|Jimmy Johns|--| Fort Wayne |IN. 46774|--|
А затем снова используйте функцию разделения, чтобы разделить пустое пространство во вновь заполненном поле состояния. Это не помогло бы мне с Grand Rapids MI 49015, потому что он разделен только пробелами, гранд и пороги также были бы разделены, но это приблизило бы меня к идеалу.
Любые предложения по функциям, которые я должен прочитать, или мысли о том, как подойти к этому по-другому?
Ответы 2
Вот несколько советов по очистке данных этой электронной таблицы в Python:
Загрузите данные в фрейм данных Pandas. Это облегчит манипулирование и обработку данных.
import pandas as pd
df = pd.read_csv('data.csv') # or whatever source
Используйте строковые методы, такие как Series.str.extract(), чтобы вывести аббревиатуры штатов и почтовые индексы в новые столбцы:
df['State'] = df['Address'].str.extract(r'([A-Z]{2})')
df['Zip'] = df['Address'].str.extract(r'(\d{5})')
Используйте Series.str.split(), чтобы разделить столбец Address на запятые. Затем возьмите второй элемент, чтобы извлечь название города:
df['City'] = df['Address'].str.split(',').str[1]
Очистите Place имена, удалив лишний текст с помощью Series.str.replace():
df['Place'] = df['Place'].str.replace('[^A-Za-z\s]+', '')
Заполните пропущенные значения и обрежьте лишние пробелы с помощью Series.fillna() и Series.str.strip():
df = df.fillna('').applymap(str.strip)
Вы можете использовать регулярное выражение, если хотите очистить с помощью строковых методов.
с вашими образцами данных я смог несколько нормализовать их до этого list:
[['Burger King Hartford', '123 Apple St W', '--', 'CT', '--'],
["McDonald's", '5 Big Mac dr', 'New York', 'NY', '10001'],
["McDonald's", '5 Big Mac dr', 'New York', 'NY', '10001'],
["Arby's", '--', 'Sturgis', 'KY', '--'],
['Taco Bell', '--', 'Grand Rapids', 'MI', '49501'],
['Taco Bell', '--', 'Grand Rapids', 'MI', '49501'],
['Jimmy Johns', '--', 'Fort Wayne', 'IN', '46774'],
['Jimmy Johns', '--', 'Fort Wayne', 'IN', '46774']]
код:
import io, pprint
csv_file = """\
| Place | Address | City | State | Zip |
|Burger King Hartford, CT | 123 Apple St W|--|--|--|
|McDonald's| 5 Big Mac dr | New York, NY 10001|--|--|
|McDonald's| 5 Big Mac dr | New York, NY|--|10001|
|Arby's|--| Sturgis, KY |--|--|
|Taco Bell|--| Grand Rapids MI 49501 |--|--|
|Taco Bell|--| Grand Rapids | MI 49501 |--|
|Jimmy Johns|--| Fort Wayne, IN. 46774 |--|--|
|Jimmy Johns|--| Fort Wayne | IN | 46774 |"""
states = [ 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA',
'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME',
'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM',
'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX',
'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY']
with io.StringIO(csv_file) as file:
parsed_data = []
headers = file.readline() # skip headers
for line in file:
remainder = ''
place, address, city, state, zipcode = \
line.replace('.', '').replace(',', '').strip('\n|').split('|')
if state == '--':
for i in states:
if i in place:
place, state, remainder = place.partition(i)
break
elif i in address:
address, state, remainder = address.partition(i)
break
elif i in city:
city, state, remainder = city.partition(i)
break
if zipcode == '--' and remainder.strip(): zipcode = remainder
else:
try: state, zipcode = state.split()
except ValueError: pass
row = list(map(str.strip, (place, address, city, state, zipcode)))
parsed_data.append(row)
pprint.pp(parsed_data)
Парсинг адресных данных - НЕПЛОХОЕ дело даже для эксперта. Ваш подход звучит как хороший шаг и может помочь вам пройти 80-90% пути. Оставшиеся 10% доведут вас до слез. При этом я бы определенно выбрал Python, его инструменты и бесконечное количество модулей, а не формулы Excel, sql и молитвы.