Блог

Главная - Вопросы по программированию - Объединить две электронные таблицы на Python с Pandas, по ближайшему времени в столбце «Время», значения в формате XX: XX: XX

Объединить две электронные таблицы на Python с Pandas, по ближайшему времени в столбце «Время», значения в формате XX: XX: XX

#python #excel #pandas #merge #timestamp

#python #excel #pandas #объединить #временная метка

Вопрос:

Пишу свою первую программу для организации некоторых данных, уже несколько месяцев занимаюсь python. Эта небольшая программа будет использоваться в ближайшем будущем с прототипом RFID-считывателя, который я разрабатываю. Я успешно взял .txt и . xls, извлек соответствующую информацию, теперь я хочу сопоставить их на основе метки времени в формате XX: XX: XX. .txt — это данные с моего GPS, .xls содержит информацию о тегах из RFID-меток.

Мне нужно только местоположение GPS в паре с идентификатором тега на основе ближайшей временной метки.

Пожалуйста, взгляните на то, что у меня есть на данный момент, и предоставьте любое направление или совет, который у вас может быть.

 import numpy as np 
import pandas as pd
import xlrd

filename_xls = '11_4_TAG.xls'
df = pd.read_excel(filename_xls)
tag_data=(df[['Time', 'TagID']])
#print(tag_data)

filename = '11_4_GPS.txt'
df_gps = pd.read_csv(filename, delimiter=r"s ", skiprows=17, skipfooter=3, engine='python', encoding="unicode_escape")
gps_data=(df_gps[['Latitude', 'Longitude', 'Time']])
#print(gps_data)

pd.merge_asof(tag_data, gps_data, on='Time', direction='nearest')
#print(pd.merge_asof)

Я перепробовал много вариантов «вкл», «направление» и «по»

Вот результаты для моих двух новых наборов данных:

tag_data

 WARNING *** OLE2 inconsistency: SSCS size is 0 but SSAT size is non-zero
        Time                     TagID
0   10:18:32  E280116060000207A633DAB6
1   10:18:57  A15427AABB00112233445566
2   10:19:07  E280116060000207A6336C96
3   10:19:09  E280116060000207A6341969
4   10:19:34  E280116060000207A633E5B9
5   10:19:40  E280116060000207A633A846
6   10:19:56  A94439112233445566778899
7   10:20:01  E200001D52120136069068C0
8   10:20:05  E280116060000207A633DA16
9   10:20:07  A63367112233445566778899
10  10:20:12  E280116060000207A633A836
11  10:20:15  E280116060000207A633CBD9
12  10:20:18  E200001D5212006106702126
13  10:20:20  A39223112233445566778899
14  10:20:28  E280116060000207A633DCC6
15  10:20:50  A02257AABB00112233445566
16  10:22:24  E280116060000207A633DA26
17  10:22:44  E280116060000207A6336AC6
18  10:23:43  E280116060000207A633DA46
19  10:24:03  E280116060000207A6336CA6
20  10:24:22  E280116060000207A633DC96
21  10:28:01  C10002AABB00112233445566
22  10:28:05  013193AABB00112233445566
23  10:28:12  017072AABB00112233445566
24  10:28:22  023764AABB00112233445566
25  10:28:42  A15800AABB00112233445566
26  10:28:49  E280116060000207A6336CC6
27  10:28:51  E280116060000207A6344236
28  10:29:00  E280116060000207A6336CB6
29  10:29:01  E280116060000207A633CBB9
30  10:29:08  E280116060000207A6341959
31  10:29:11  A72546AABB00112233445566
32  10:29:15  A93853112233445566778899
33  10:29:15  A93853AABB00112233445566
34  10:30:46  A13832AABB00112233445566
35  10:30:52  A02533AABB00112233445566
36  10:30:58  00111160600002078899CBA9
37  10:31:23  A83503AABB00112206906A73
[Finished in 0.8s]

gps_data

 WARNING *** OLE2 inconsistency: SSCS size is 0 but SSAT size is non-zero
         Latitude      Longitude      Time
0    N43°03.6205'  W085°57.5513'  10:17:46
1    N43°03.6205'  W085°57.5512'  10:17:49
2    N43°03.6203'  W085°57.5514'  10:17:51
3    N43°03.6202'  W085°57.5511'  10:17:54
4    N43°03.6199'  W085°57.5518'  10:17:57
..            ...            ...       ...
342  N43°03.6162'  W085°57.5477'  10:33:03
343  N43°03.6163'  W085°57.5472'  10:33:06
344  N43°03.6168'  W085°57.5477'  10:33:09
345  N43°03.6167'  W085°57.5477'  10:33:11
346  N43°03.6163'  W085°57.5486'  10:33:14

[347 rows x 3 columns]
[Finished in 0.8s]

Код ошибки:

 WARNING *** OLE2 inconsistency: SSCS size is 0 but SSAT size is non-zero
Traceback (most recent call last):
  File "C:UsersOwnerDocumentsSoftwarePython ProgramsData_parsedata_parse_xls.py", line 15, in <module>
    pd.merge_asof(tag_data, gps_data, on='Time', direction='nearest')
  File "C:UsersOwnerAppDataLocalPackagesPythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0LocalCachelocal-packagesPython38site-packagespandascorereshapemerge.py", line 563, in merge_asof
    return op.get_result()
  File "C:UsersOwnerAppDataLocalPackagesPythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0LocalCachelocal-packagesPython38site-packagespandascorereshapemerge.py", line 1483, in get_result
    join_index, left_indexer, right_indexer = self._get_join_info()
  File "C:UsersOwnerAppDataLocalPackagesPythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0LocalCachelocal-packagesPython38site-packagespandascorereshapemerge.py", line 884, in _get_join_info
    (left_indexer, right_indexer) = self._get_join_indexers()
  File "C:UsersOwnerAppDataLocalPackagesPythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0LocalCachelocal-packagesPython38site-packagespandascorereshapemerge.py", line 1789, in _get_join_indexers
    return func(left_values, right_values, self.allow_exact_matches, tolerance)
  File "pandas_libsjoin.pyx", line 966, in pandas._libs.join.__pyx_fused_cpdef
TypeError: No matching signature found
[Finished in 0.8s]

Комментарии:

1. Вы уверены, что хотите объединить вовремя? потому что это время должно точно совпадать для объединения

2. В настоящее время это единственные две точки данных, которые у меня есть общего между двумя электронными таблицами. Я собираю данные GPS, а также собираю данные тегов, прототип, который я разрабатываю, будет фиксировать информацию GPS во время чтения тега. Думаю, на этом этапе мне больше не понадобится это программное обеспечение. Читая «Pandas», они говорят о поиске ближайшего целого числа, я полагаю, что это может быть datetime, timestamp или intiger. Однако я не могу заставить его работать.

Ответ №1:

Как сказал @MhDG7, вы можете объединить только те значения, которые точно совпадают. Итак, ваша первая задача — для каждой временной метки в gps_data найти ближайшее совпадение между временными метками в tag_data. Ключевая строка ниже — это

 tag_secs_for_gps_secs = [min(tag_data['Secs'], key = lambda tag_secs: abs(tag_secs - gps_secs))  for gps_secs in gps_data['Secs'] ]

это делает именно это. Здесь для каждого времени gps, выраженного в секундах (см. Ниже), мы находим элемент среди временных меток tag_data (также выраженных в секундах), который минимизирует абсолютное расстояние между двумя

Вот полный сценарий Сначала мы создаем игрушечные данные

 import sys
import pandas as pd
from io import StringIO
from datetime import datetime

gps_data_raw = StringIO(
    """
Latitude,Longitude,Time
N43°03.6162',W085°57.5477',10:33:03
N43°03.6163',W085°57.5472',10:33:06
N43°03.6168',W085°57.5477',10:33:09
N43°03.6167',W085°57.5477',10:33:11
N43°03.6163',W085°57.5486',10:33:14
    """)
gps_data = pd.read_csv(gps_data_raw)

tag_data_raw = StringIO(
    """
Time,TagID
10:33:01,C10002AABB00112233445566
10:33:05,013193AABB00112233445566
10:33:12,017072AABB00112233445566
10:33:22,023764AABB00112233445566
10:33:42,A15800AABB00112233445566
10:33:49,E280116060000207A6336CC6
10:33:51,E280116060000207A6344236
    """)
tag_data = pd.read_csv(tag_data_raw)

Затем мы преобразуем временную метку в объект datetime и, в конечном итоге, в (целочисленные) секунды из base_date

 base_date = datetime(1900, 1, 1, 0, 0, 0)
gps_data['Time'] = pd.to_datetime(gps_data['Time'], format='%H:%M:%S')
tag_data['Time'] = pd.to_datetime(tag_data['Time'], format='%H:%M:%S')
gps_data['Secs'] = gps_data['Time'].apply(lambda t: int((t-base_date).total_seconds()))
tag_data['Secs'] = tag_data['Time'].apply(lambda t: int((t-base_date).total_seconds()))

Затем мы сопоставляем временные метки, вставляем их в фрейм данных и объединяем в этом столбце

 tag_secs_for_gps_secs = [min(tag_data['Secs'], key = lambda tag_secs: abs(tag_secs - gps_secs))  for gps_secs in gps_data['Secs'] ]

gps_data['Nearest_tag_secs'] = tag_secs_for_gps_secs
merged_data = gps_data.merge(tag_data, left_on = 'Nearest_tag_secs', right_on = 'Secs')

Фрейм данных resultimg merged_data может потребовать небольшой очистки, но воспроизведен здесь полностью, чтобы вы могли видеть, что произошло

 |    | Latitude     | Longitude     | Time_x              |   Secs_x |   Nearest_tag_secs | Time_y              | TagID                    |   Secs_y |
|---:|:-------------|:--------------|:--------------------|---------:|-------------------:|:--------------------|:-------------------------|---------:|
|  0 | N43°03.6162' | W085°57.5477' | 1900-01-01 10:33:03 |    37983 |              37981 | 1900-01-01 10:33:01 | C10002AABB00112233445566 |    37981 |
|  1 | N43°03.6163' | W085°57.5472' | 1900-01-01 10:33:06 |    37986 |              37985 | 1900-01-01 10:33:05 | 013193AABB00112233445566 |    37985 |
|  2 | N43°03.6168' | W085°57.5477' | 1900-01-01 10:33:09 |    37989 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |
|  3 | N43°03.6167' | W085°57.5477' | 1900-01-01 10:33:11 |    37991 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |
|  4 | N43°03.6163' | W085°57.5486' | 1900-01-01 10:33:14 |    37994 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |

Комментарии:

1. Это большое спасибо. Сегодня я углублюсь в это и поиграю. Я понимаю, что вы оба сейчас говорите о слиянии по одному и тому же значению. Чтение происходит в течение нескольких минут, пока я обходил своих туристов и читал данные тегов. Мне также нужно будет сопоставить минуты, но я определенно вижу путь к тому, чтобы это произошло сейчас. Я никогда не понимал, насколько креативным нужно быть, чтобы писать код, получая от этого истинное удовольствие. Спасибо вам обоим.

2. нет проблем. Чтобы было ясно, что мой код соответствует общему количеству секунд с момента базовой даты (таким образом, уникальный номер для любой метки времени), а не только двум последним цифрам вашей метки времени

3. О, потрясающе, я безуспешно пытался преобразовать «временную метку» в какой-то intiger, рад видеть, что прошлой ночью я был на правильном пути. Я буду обновлять прогресс позже.

4. Спасибо, это отлично сработало! Я буду изучать / искать части этого кода в течение нескольких дней, чтобы полностью понять, но я получаю представление о том, что происходит с высоты птичьего полета. Я скопировал и вставил небольшое подмножество моих исходных данных, чтобы сначала все исправить, теперь я буду работать над тем, чтобы мои две электронные таблицы были заполнены в StringIO, как вы изобразили.

5. @Beasmazter, SrtingIO материал должен иметь только отдельный пример, который не зависит ни от каких файлов, и вам действительно не нужна эта часть в вашем окончательном решении. Ваша df = pd.read_excel(filename_xls) должна работать нормально (ну, вы можете следить за пробелами вокруг ваших значений, но кроме этого проблем быть не должно).