python - 无法将字符串转换为字典/json

Question

我正在将 Excel 文件读入 Pandas 数据框。

在“segment_efforts”列中有字典列表的字符串表示形式，我想将它们变成这样的字典：

for cell in range(len(df)):
    d = ast.literal_eval(df['segment_efforts'].values[cell]

但是，我得到

扫描字符串文字时 EOL

literal_eval但是，对于复制到编辑器和三引号的单个单元格，它可以正常工作。

如何修复上述代码以实现与在字符串周围加上三引号相同的效果？

示例字符串：

[{'elapsed_time': 628, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 1, 'id': 4679807677, 'average_watts': 143.0, 'average_speed': 7.5, 'average_cadence': 114.4, 'start_date_local': '2018-03-20T19:45:40Z', 'distance': 4708.72, 'split': 1, 'start_index': 0, 'name': 'Lap 1', 'max_speed': 8.8, 'average_heartrate': 126.3, 'end_index': 628, 'moving_time': 628, 'start_date': '2018-03-20T18:45:40Z', 'max_heartrate': 143.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 309, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 2, 'id': 4679807680, 'average_watts': 209.2, 'average_speed': 8.09, 'average_cadence': 119.2, 'start_date_local': '2018-03-20T19:56:09Z', 'distance': 2499.13, 'split': 2, 'start_index': 629, 'name': 'Lap 2', 'max_speed': 8.3, 'average_heartrate': 150.6, 'end_index': 937, 'moving_time': 309, 'start_date': '2018-03-20T18:56:09Z', 'max_heartrate': 155.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 96, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 3, 'id': 4679807683, 'average_watts': 137.2, 'average_speed': 5.03, 'average_cadence': 84.6, 'start_date_local': '2018-03-20T20:01:18Z', 'distance': 482.84, 'split': 3, 'start_index': 938, 'name': 'Lap 3', 'max_speed': 7.5, 'average_heartrate': 131.3, 'end_index': 1034, 'moving_time': 96, 'start_date': '2018-03-20T19:01:18Z', 'max_heartrate': 151.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 306, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 4, 'id': 4679807685, 'average_watts': 209.3, 'average_speed': 8.06, 'average_cadence': 119.1, 'start_date_local': '2018-03-20T20:02:55Z', 'distance': 2467.17, 'split': 4, 'start_index': 1035, 'name': 'Lap 4', 'max_speed': 8.3, 'average_heartrate': 148.6, 'end_index': 1340, 'moving_time': 306, 'start_date': '2018-03-20T19:02:55Z', 'max_heartrate': 157.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 94, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 5, 'id': 4679807688, 'average_watts': 149.6, 'average_speed': 6.09, 'average_cadence': 91.5, 'start_date_local': '2018-03-20T20:08:01Z', 'distance': 572.04, 'split': 5, 'start_index': 1341, 'name': 'Lap 5', 'max_speed': 8.1, 'average_heartrate': 136.6, 'end_index': 1435, 'moving_time': 94, 'start_date': '2018-03-20T19:08:01Z', 'max_heartrate': 147.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 323, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 6, 'id': 4679807691, 'average_watts': 209.0, 'average_speed': 8.09, 'average_cadence': 119.1, 'start_date_local': '2018-03-20T20:09:36Z', 'distance': 2612.63, 'split': 6, 'start_index': 1436, 'name': 'Lap 6', 'max_speed': 8.3, 'average_heartrate': 148.9, 'end_index': 1759, 'moving_time': 323, 'start_date': '2018-03-20T19:09:36Z', 'max_heartrate': 157.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 103, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 7, 'id': 4679807693, 'average_watts': 145.2, 'average_speed': 5.97, 'average_cadence': 91.5, 'start_date_local': '2018-03-20T20:15:00Z', 'distance': 614.71, 'split': 7, 'start_index': 1760, 'name': 'Lap 7', 'max_speed': 7.7, 'average_heartrate': 134.1, 'end_index': 1862, 'moving_time': 103, 'start_date': '2018-03-20T19:15:00Z', 'max_heartrate': 153.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 308, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 8, 'id': 4679807695, 'average_watts': 209.0, 'average_speed': 7.97, 'average_cadence': 117.9, 'start_date_local': '2018-03-20T20:16:43Z', 'distance': 2454.1, 'split': 8, 'start_index': 1863, 'name': 'Lap 8', 'max_speed': 8.3, 'average_heartrate': 147.7, 'end_index': 2170, 'moving_time': 308, 'start_date': '2018-03-20T19:16:43Z', 'max_heartrate': 154.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 96, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 9, 'id': 4679807697, 'average_watts': 142.5, 'average_speed': 5.25, 'average_cadence': 80.9, 'start_date_local': '2018-03-20T20:21:51Z', 'distance': 503.87, 'split': 9, 'start_index': 2171, 'name': 'Lap 9', 'max_speed': 7.8, 'average_heartrate': 133.8, 'end_index': 2266, 'moving_time': 96, 'start_date': '2018-03-20T19:21:51Z', 'max_heartrate': 152.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 306, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 10, 'id': 4679807701, 'average_watts': 218.6, 'average_speed': 8.14, 'average_cadence': 120.0, 'start_date_local': '2018-03-20T20:23:27Z', 'distance': 2491.13, 'split': 10, 'start_index': 2267, 'name': 'Lap 10', 'max_speed': 8.4, 'average_heartrate': 151.5, 'end_index': 2573, 'moving_time': 306, 'start_date': '2018-03-20T19:23:27Z', 'max_heartrate': 158.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 265, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 11, 'id': 4679807703, 'average_watts': 115.7, 'average_speed': 6.56, 'average_cadence': 102.1, 'start_date_local': '2018-03-20T20:28:34Z', 'distance': 1739.46, 'split': 11, 'start_index': 2574, 'name': 'Lap 11', 'max_speed': 7.9, 'average_heartrate': 125.3, 'end_index': 2839, 'moving_time': 265, 'start_date': '2018-03-20T19:28:34Z', 'max_heartrate': 154.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}]

标签： pythonpandasdataframe

Answer 1

我假设您的数据最初直接来自 Python，而没有显式转换为 JSON 格式，因此在尝试将示例字符串解析为 JSON 时会出现一些错误。

这些问题是：

1. 使用单引号代替双引号
2. 使用 PythonTrue代替 JavaScripttrue
3. 使用 PythonFalse代替 JavaScriptfalse
4. 使用 PythonNone代替 JavaScriptnull

如果您在字符串中替换这四种有问题的样式，则可以将其解析为 JSON 而无需引发JSONDecodeError.

例子：

import json


s = """[{'elapsed_time': 628, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 1, 'id': 4679807677, 'average_watts': 143.0, 'average_speed': 7.5, 'average_cadence': 114.4, 'start_date_local': '2018-03-20T19:45:40Z', 'distance': 4708.72, 'split': 1, 'start_index': 0, 'name': 'Lap 1', 'max_speed': 8.8, 'average_heartrate': 126.3, 'end_index': 628, 'moving_time': 628, 'start_date': '2018-03-20T18:45:40Z', 'max_heartrate': 143.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 309, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 2, 'id': 4679807680, 'average_watts': 209.2, 'average_speed': 8.09, 'average_cadence': 119.2, 'start_date_local': '2018-03-20T19:56:09Z', 'distance': 2499.13, 'split': 2, 'start_index': 629, 'name': 'Lap 2', 'max_speed': 8.3, 'average_heartrate': 150.6, 'end_index': 937, 'moving_time': 309, 'start_date': '2018-03-20T18:56:09Z', 'max_heartrate': 155.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 96, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 3, 'id': 4679807683, 'average_watts': 137.2, 'average_speed': 5.03, 'average_cadence': 84.6, 'start_date_local': '2018-03-20T20:01:18Z', 'distance': 482.84, 'split': 3, 'start_index': 938, 'name': 'Lap 3', 'max_speed': 7.5, 'average_heartrate': 131.3, 'end_index': 1034, 'moving_time': 96, 'start_date': '2018-03-20T19:01:18Z', 'max_heartrate': 151.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 306, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 4, 'id': 4679807685, 'average_watts': 209.3, 'average_speed': 8.06, 'average_cadence': 119.1, 'start_date_local': '2018-03-20T20:02:55Z', 'distance': 2467.17, 'split': 4, 'start_index': 1035, 'name': 'Lap 4', 'max_speed': 8.3, 'average_heartrate': 148.6, 'end_index': 1340, 'moving_time': 306, 'start_date': '2018-03-20T19:02:55Z', 'max_heartrate': 157.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 94, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 5, 'id': 4679807688, 'average_watts': 149.6, 'average_speed': 6.09, 'average_cadence': 91.5, 'start_date_local': '2018-03-20T20:08:01Z', 'distance': 572.04, 'split': 5, 'start_index': 1341, 'name': 'Lap 5', 'max_speed': 8.1, 'average_heartrate': 136.6, 'end_index': 1435, 'moving_time': 94, 'start_date': '2018-03-20T19:08:01Z', 'max_heartrate': 147.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 323, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 6, 'id': 4679807691, 'average_watts': 209.0, 'average_speed': 8.09, 'average_cadence': 119.1, 'start_date_local': '2018-03-20T20:09:36Z', 'distance': 2612.63, 'split': 6, 'start_index': 1436, 'name': 'Lap 6', 'max_speed': 8.3, 'average_heartrate': 148.9, 'end_index': 1759, 'moving_time': 323, 'start_date': '2018-03-20T19:09:36Z', 'max_heartrate': 157.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 103, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 7, 'id': 4679807693, 'average_watts': 145.2, 'average_speed': 5.97, 'average_cadence': 91.5, 'start_date_local': '2018-03-20T20:15:00Z', 'distance': 614.71, 'split': 7, 'start_index': 1760, 'name': 'Lap 7', 'max_speed': 7.7, 'average_heartrate': 134.1, 'end_index': 1862, 'moving_time': 103, 'start_date': '2018-03-20T19:15:00Z', 'max_heartrate': 153.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 308, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 8, 'id': 4679807695, 'average_watts': 209.0, 'average_speed': 7.97, 'average_cadence': 117.9, 'start_date_local': '2018-03-20T20:16:43Z', 'distance': 2454.1, 'split': 8, 'start_index': 1863, 'name': 'Lap 8', 'max_speed': 8.3, 'average_heartrate': 147.7, 'end_index': 2170, 'moving_time': 308, 'start_date': '2018-03-20T19:16:43Z', 'max_heartrate': 154.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 96, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 9, 'id': 4679807697, 'average_watts': 142.5, 'average_speed': 5.25, 'average_cadence': 80.9, 'start_date_local': '2018-03-20T20:21:51Z', 'distance': 503.87, 'split': 9, 'start_index': 2171, 'name': 'Lap 9', 'max_speed': 7.8, 'average_heartrate': 133.8, 'end_index': 2266, 'moving_time': 96, 'start_date': '2018-03-20T19:21:51Z', 'max_heartrate': 152.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 306, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 10, 'id': 4679807701, 'average_watts': 218.6, 'average_speed': 8.14, 'average_cadence': 120.0, 'start_date_local': '2018-03-20T20:23:27Z', 'distance': 2491.13, 'split': 10, 'start_index': 2267, 'name': 'Lap 10', 'max_speed': 8.4, 'average_heartrate': 151.5, 'end_index': 2573, 'moving_time': 306, 'start_date': '2018-03-20T19:23:27Z', 'max_heartrate': 158.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}, {'elapsed_time': 265, 'device_watts': True, 'activity': {'resource_state': 1, 'id': 1462917682}, 'lap_index': 11, 'id': 4679807703, 'average_watts': 115.7, 'average_speed': 6.56, 'average_cadence': 102.1, 'start_date_local': '2018-03-20T20:28:34Z', 'distance': 1739.46, 'split': 11, 'start_index': 2574, 'name': 'Lap 11', 'max_speed': 7.9, 'average_heartrate': 125.3, 'end_index': 2839, 'moving_time': 265, 'start_date': '2018-03-20T19:28:34Z', 'max_heartrate': 154.0, 'resource_state': 2, 'athlete': {'resource_state': 1, 'id': 3255732}, 'total_elevation_gain': 0.0}]"""

s = s.replace("'", '"')
s = s.replace("True", "true")
s = s.replace("False", "false")
s = s.replace("None", "null")

d = json.loads(s)