python - 尝试加载多个json文件并合并为一个 Pandas 数据框

Question

我正在尝试从Google云端硬盘中的目录中将多个json文件加载到一个熊猫数据框中。

我已经尝试了很多解决方案，但是似乎没有什么能产生积极的结果。

到目前为止，这是我尝试过的

path_to_json = '/path/'
json_files = [pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')]
jsons_data = pd.DataFrame(columns=['participants','messages','active','threadtype','thread path'])
for index, js in enumerate(json_files):
    with open(os.path.join(path_to_json, js)) as json_file:
        json_text = json.load(json_file)
        participants = json_text['participants']
        messages = json_text['messages']
        active = json_text['is_still_participant']
        threadtype = json_text['thread_type']
        threadpath = json_text['thread_path']
        jsons_data.loc[index]=[participants,messages,active,threadtype,threadpath]
jsons_data

这是我收到的错误消息的完整回溯：

---------------------------------------------------------------------------
JSONDecodeError                           Traceback (most recent call last)
<ipython-input-30-8385abf6a3a7> in <module>()
      1 for index, js in enumerate(json_files):
      2     with open(os.path.join(path_to_json, js)) as json_file:
----> 3         json_text = json.load(json_file)
      4         participants = json_text['participants']
      5         messages = json_text['messages']

/usr/lib/python3.6/json/__init__.py in load(fp, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)
    297         cls=cls, object_hook=object_hook,
    298         parse_float=parse_float, parse_int=parse_int,
--> 299         parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
    300 
    301 

/usr/lib/python3.6/json/__init__.py in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)
    352             parse_int is None and parse_float is None and
    353             parse_constant is None and object_pairs_hook is None and not kw):
--> 354         return _default_decoder.decode(s)
    355     if cls is None:
    356         cls = JSONDecoder

/usr/lib/python3.6/json/decoder.py in decode(self, s, _w)
    337 
    338         """
--> 339         obj, end = self.raw_decode(s, idx=_w(s, 0).end())
    340         end = _w(s, end).end()
    341         if end != len(s):

/usr/lib/python3.6/json/decoder.py in raw_decode(self, s, idx)
    355             obj, end = self.scan_once(s, idx)
    356         except StopIteration as err:
--> 357             raise JSONDecodeError("Expecting value", s, err.value) from None
    358         return obj, end

JSONDecodeError: Expecting value: line 1 column 1 (char 0) 

我添加了我尝试读取的json文件的示例

Link to Jsons

json的示例：

{
participants: [
{
name: "Test 1"
},
{
name: "Person"
}
],
messages: [
{
sender_name: "Person",
timestamp_ms: 1485467319139,
content: "Hie",
type: "Generic"
}
],
title: "Test 1",
is_still_participant: true,
thread_type: "Regular",
thread_path: "inbox/xyz"
}
#second example
{
participants: [
{
name: "Clearance"
},
{
name: "Person"
}
],
messages: [
{
sender_name: "Emmanuel Sibanda",
timestamp_ms: 1212242073308,
content: "Dear",
share: {
link: "http://www.example.com/"
},
type: "Share"
}
],
title: "Clearance",
is_still_participant: true,
thread_type: "Regular",
thread_path: "inbox/Clearance"
}

Answer 1

我检查了您的json文件，发现document1.json，document2.json和document3.json中存在相同的问题：属性名称未用双引号引起来。

例如，document1.json应该更正为：

{
"participants": [
{
"name": "Clothing"
},
{
"name": "Person"
}
],
"messages": [
{
"sender_name": "Person",
"timestamp_ms": 1210107456233,
"content": "Good day",
"type": "Generic"
}
],
"title": "Clothing",
"is_still_participant": true,
"thread_type": "Regular",
"thread_path": "inbox/Clothing"
}

编辑：您可以使用以下行将双引号添加到json文件的键中：

re.sub("([^\s^\"]+):(.+)", '"\\1":\\2', s)