如何正确地从CSV读取JSON

Question

我有一个带有自由格式地址的数据帧，我用googlemaps地理编码器API规范化了它。结果以JSON格式返回，写入列并保存到csv。你知道吗

但是，当我阅读这个csv时，反斜杠出现在一个带有JSON的列中，因为该列是以字符串形式从csv读取的，单引号出现在开头和结尾，JSON中的单引号用反斜杠转义。你知道吗

所以我明白了

json_address
'{\'status\': \'OK\'}'

当我需要的时候

json_address
{'status': 'OK'}

解决方案here对我不起作用，因为我的csv是用单引号读取的，我得到了错误Expecting property name enclosed in double quotes

除此之外，我不想规范化JSON并将其放在单独的列中，我希望输出作为上面指定的字典

我试着用双引号代替单引号

df['json_double'] = df['json_address'].apply(lambda x: x.replace("\'", "\""))

成功了，所以我

''{\''status\'': \'OK\''}''

但是，当我尝试df['json'] = df['json_double'].apply(json.loads)时，我得到了错误Expecting ',' delimiter: line 1 column 609 (char 608)

所以我的问题是

1. 今后如何正确处理JSON以避免此类问题？你知道吗
2. 如何解决我的问题？我对阅读阶段和将字符串列转换为json阶段的解决方案都感兴趣。你知道吗

PS作为json的整个字符串的例子是

'{\'results\': [{\'address_components\': [{\'long_name\': \'4\', \'short_name\': \'4\', \'types\': [\'street_number\']}, {\'long_name\': \'Ulitsa Marshala Rokossovskogo\', \'short_name\': \'Ulitsa Marshala Rokossovskogo\', \'types\': [\'route\']}, {\'long_name\': \'Nizhnij Novgorod\', \'short_name\': \'Nizhnij Novgorod\', \'types\': [\'locality\', \'political\']}, {\'long_name\': \'Sovetskiy\', \'short_name\': \'Sovetskiy\', \'types\': [\'administrative_area_level_3\', \'political\']}, {\'long_name\': \'Gorod Nizhniy Novgorod\', \'short_name\': \'Gorod Nizhniy Novgorod\', \'types\': [\'administrative_area_level_2\', \'political\']}, {\'long_name\': "Nizhegorodskaya oblast\'", \'short_name\': "Nizhegorodskaya oblast\'", \'types\': [\'administrative_area_level_1\', \'political\']}, {\'long_name\': \'Russia\', \'short_name\': \'RU\', \'types\': [\'country\', \'political\']}, {\'long_name\': \'603162\', \'short_name\': \'603162\', \'types\': [\'postal_code\']}], \'formatted_address\': "Ulitsa Marshala Rokossovskogo, 4, Nizhnij Novgorod, Nizhegorodskaya oblast\', Russia, 603162", \'geometry\': {\'location\': {\'lat\': 56.28278, \'lng\': 44.0456111}, \'location_type\': \'ROOFTOP\', \'viewport\': {\'northeast\': {\'lat\': 56.2841289802915, \'lng\': 44.0469600802915}, \'southwest\': {\'lat\': 56.2814310197085, \'lng\': 44.0442621197085}}}, \'place_id\': \'ChIJMeknJyXVUUERI9A8HCXBznI\', \'plus_code\': {\'compound_code\': \'72MW+46 Nizhny Novgorod, Nizhny Novgorod Oblast, Russia\', \'global_code\': \'9H8672MW+46\'}, \'types\': [\'street_address\']}], \'status\': \'OK\'}'

对它应用json.loads会导致Expecting ',' delimiter: line 1 column 609 (char 608)

在建议的解决方案中出现以下错误

Answer 1

您可以使用ast模块的literal_eval方法，例如：

import ast
mystr = '{\'results\': [{\'address_components\': [{\'long_name\': \'4\', \'short_name\': \'4\', \'types\': [\'street_number\']}, {\'long_name\': \'Ulitsa Marshala Rokossovskogo\', \'short_name\': \'Ulitsa Marshala Rokossovskogo\', \'types\': [\'route\']}, {\'long_name\': \'Nizhnij Novgorod\', \'short_name\': \'Nizhnij Novgorod\', \'types\': [\'locality\', \'political\']}, {\'long_name\': \'Sovetskiy\', \'short_name\': \'Sovetskiy\', \'types\': [\'administrative_area_level_3\', \'political\']}, {\'long_name\': \'Gorod Nizhniy Novgorod\', \'short_name\': \'Gorod Nizhniy Novgorod\', \'types\': [\'administrative_area_level_2\', \'political\']}, {\'long_name\': "Nizhegorodskaya oblast\'", \'short_name\': "Nizhegorodskaya oblast\'", \'types\': [\'administrative_area_level_1\', \'political\']}, {\'long_name\': \'Russia\', \'short_name\': \'RU\', \'types\': [\'country\', \'political\']}, {\'long_name\': \'603162\', \'short_name\': \'603162\', \'types\': [\'postal_code\']}], \'formatted_address\': "Ulitsa Marshala Rokossovskogo, 4, Nizhnij Novgorod, Nizhegorodskaya oblast\', Russia, 603162", \'geometry\': {\'location\': {\'lat\': 56.28278, \'lng\': 44.0456111}, \'location_type\': \'ROOFTOP\', \'viewport\': {\'northeast\': {\'lat\': 56.2841289802915, \'lng\': 44.0469600802915}, \'southwest\': {\'lat\': 56.2814310197085, \'lng\': 44.0442621197085}}}, \'place_id\': \'ChIJMeknJyXVUUERI9A8HCXBznI\', \'plus_code\': {\'compound_code\': \'72MW+46 Nizhny Novgorod, Nizhny Novgorod Oblast, Russia\', \'global_code\': \'9H8672MW+46\'}, \'types\': [\'street_address\']}], \'status\': \'OK\'}'
mydic = ast.literal_eval(mystr)