Pandas - 字典列表的列扩展 - 如何优化? [英] Pandas - Column expansion of List of Dictionary - How to Optimise?

问题描述

我有一个数据框 test 有 3 列 id, name, value 以下列 test['values'].test 外观示例如下:

I have a dataframe test with 3 columns id, name, value the following column test['values']. A sample of how test looks is:

    name                  values
0   impressions           [{'value': 17686, 'end_time': '2018-06-12T07:0...
1   reach                 [{'value': 6294, 'end_time': '2018-06-12T07:00...
2   follower_count        [{'value': 130, 'end_time': '2018-06-12T07:00:...
3   email_contacts        [{'value': 1, 'end_time': '2018-06-12T07:00:00...
4   phone_call_clicks     [{'value': 0, 'end_time': '2018-06-12T07:00:00...
5   text_message_clicks   [{'value': 0, 'end_time': '2018-06-12T07:00:00...
6   get_directions_clicks [{'value': 0, 'end_time': '2018-06

测试值单元格看起来像这样:

The test value cells look something like this:

[{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686},
 {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}]

我可以通过执行以下操作来扩展它:

I can expand it by doing the following:

test[['Values 1', 'Values 2']] = test['values'].apply(pd.Series)
test[['Date 1', 'Values 1']] = test['Values 1'].apply(pd.Series)
test[['Date 2', 'Values 2']] = test['Values 2'].apply(pd.Series)
test.drop(['values'], axis=1, inplace=True)

结果是这样的:

id                          name        Values 1    Values 2    Date 1  Date 2
/insights/impressions/day   impressions 17686   4064    2018-06-12T07:00:00+0000    2018-06-13T07:00:00+0000
/insights/reach/day reach   6294    2085    2018-06-12T07:00:00+0000    2018-06-13T07:00:00+0000
/insights/follower_count/day    follower_count  130 37  2018-06-12T07:00:00+0000    2018-06-13T07:00:00+0000

我想知道是否:

a.有一种更快的方法来扩展字典列表

a. There's a faster way to expand the list of dictionaries

b. 有一种方法可以取消数据透视，使值 1 和值 2 位于一列中.而日期 1 和日期 2 在另一列中

b. There's a way to unpivot the data so that values 1 and values 2 are on one column. And Date 1 and Date 2 are in another column

推荐答案

如果输入数据是 jsons，最好使用 json_normalize.

If input data are jsons, better is use json_normalize.

j = [{'description': 'Total number 1', 'id': 'a', 'name': 'impressions', 'period': 'day', 'title': 'Impressions', 'values': [{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686}, {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}]},
      {'description': 'fn', 'id': 'b', 'name': 'impressions', 'period': 'day', 'title': 'Impressions', 'values': [{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686}, {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}]}]

from pandas.io.json import json_normalize

df = json_normalize(j, 'values')
print (df)
                   end_time  value
0  2018-06-12T07:00:00+0000  17686
1  2018-06-13T07:00:00+0000   4064
2  2018-06-12T07:00:00+0000  17686
3  2018-06-13T07:00:00+0000   4064

但如果还需要添加原始列:

But if need also add original columns:

from pandas.io.json import json_normalize


df = json_normalize(j, 'values', ['description', 'id', 'name', 'period', 'title'])
print (df)
                   end_time  value     description id         name period  \
0  2018-06-12T07:00:00+0000  17686  Total number 1  a  impressions    day   
1  2018-06-13T07:00:00+0000   4064  Total number 1  a  impressions    day   
2  2018-06-12T07:00:00+0000  17686              fn  b  impressions    day   
3  2018-06-13T07:00:00+0000   4064              fn  b  impressions    day   

         title  
0  Impressions  
1  Impressions  
2  Impressions  
3  Impressions  

<小时>

第一个解决方案:

---

First solution:

test = pd.DataFrame({
    'name':['a', 'b', 'n'],
    'values':[[{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686},
 {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}],[{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686},
 {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}],[{'end_time': '2018-06-12T07:00:00+0000', 'value': 17686},
 {'end_time': '2018-06-13T07:00:00+0000', 'value': 4064}]]
})


df =  (pd.concat([pd.DataFrame(x) for x in test['values']], axis=1, keys=(1, 2))
        .stack(0)
        .reset_index(level=1, drop=True))
print (df)
                   end_time  value
0  2018-06-12T07:00:00+0000  17686
0  2018-06-12T07:00:00+0000  17686
1  2018-06-13T07:00:00+0000   4064
1  2018-06-13T07:00:00+0000   4064

df = test.join(df)
print (df)
  name                                             values  \
0    a  [{'end_time': '2018-06-12T07:00:00+0000', 'val...   
0    a  [{'end_time': '2018-06-12T07:00:00+0000', 'val...   
1    b  [{'end_time': '2018-06-12T07:00:00+0000', 'val...   
1    b  [{'end_time': '2018-06-12T07:00:00+0000', 'val...   
2    n  [{'end_time': '2018-06-12T07:00:00+0000', 'val...   

                   end_time    value  
0  2018-06-12T07:00:00+0000  17686.0  
0  2018-06-12T07:00:00+0000  17686.0  
1  2018-06-13T07:00:00+0000   4064.0  
1  2018-06-13T07:00:00+0000   4064.0  
2                       NaN      NaN  

这篇关于Pandas - 字典列表的列扩展 - 如何优化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！