DataFrame 同时包含字典和字典列表的列拆分为字典列

迈不过友情╰ 2022-12-22 11:20 177阅读 0赞

# DataFrame 同时包含字典和字典列表的列 拆分为字典列 #

> 一个DataFrame中，一个同时包含字典和字典列表的列，想要把字典列表中的字典单独形成一行。

示例：

import numpy
    import pandas

df = pandas.DataFrame(
        numpy.array(
            [
                ['a', 'b', { 'name': 'apple', 'price': 10}], 
                ['a', 'b', [{ 'name': 'banana', 'price': 20}, { 'name': 'peach', 'price': 30}]], 
                ['a', 'b', [{ 'name': 'pear', 'price': 30}]]
            ], dtype=object
        ), 
        columns=['A', 'B', 'Fruit']
    )

<table> 
 <thead> 
  <tr> 
   <th align="right"></th> 
   <th align="right">A</th> 
   <th align="right">B</th> 
   <th align="right">Fruit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="right">0</td> 
   <td align="right">a</td> 
   <td align="right">b</td> 
   <td align="right">{‘name’: ‘apple’, ‘price’: 10}</td> 
  </tr> 
  <tr> 
   <td align="right">1</td> 
   <td align="right">a</td> 
   <td align="right">b</td> 
   <td align="right">[{‘name’: ‘banana’, ‘price’: 20}, {‘name’: 'pe…</td> 
  </tr> 
  <tr> 
   <td align="right">2</td> 
   <td align="right">a</td> 
   <td align="right">b</td> 
   <td align="right">[{‘name’: ‘pear’, ‘price’: 30}]</td> 
  </tr> 
 </tbody> 
</table>

先定义一个函数，将字典外层添加一层列表 将Fruit列统一为字典列表。

def into_list(x):
        """ 字典外层添加一层列表 :param x: 字典 :return: 字典列表 """
    
        a_list = list()
        a_list.append(x)
    
        return a_list

# A列 #

# 取出A与Fruit列
    a_df = pandas.DataFrame(df, columns=['A', 'Fruit'])
    
    # 将字典外层添加一层列表 将Fruit列统一为字典列表
    a_df.loc[:, 'Fruit'] = a_df.loc[:, 'Fruit'].apply(
        lambda x: into_list(x) if type(x) == dict else x
    )
    
    # 将列表里面的每一个字典拆分为一行
    a_df_new_values = numpy.dstack(
        (numpy.repeat(a_df.loc[:, 'A'].values, list(map(len, a_df.Fruit.values))),
         numpy.concatenate(a_df.Fruit.values))
    )
    
    a_df = pandas.DataFrame(data=a_df_new_values[0], columns=a_df.columns)

a_df

<table> 
 <thead> 
  <tr> 
   <th align="right"></th> 
   <th align="right">A</th> 
   <th align="right">Fruit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="right">0</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘apple’, ‘price’: 10}</td> 
  </tr> 
  <tr> 
   <td align="right">1</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘banana’, ‘price’: 20}</td> 
  </tr> 
  <tr> 
   <td align="right">2</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘peach’, ‘price’: 30}</td> 
  </tr> 
  <tr> 
   <td align="right">3</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘pear’, ‘price’: 30}</td> 
  </tr> 
 </tbody> 
</table>

# B列 #

# 取出B与Fruit列
    b_df = pandas.DataFrame(df, columns=['B', 'Fruit'])
    
    # 将字典外层添加一层列表 将Fruit列统一为字典列表
    b_df.loc[:, 'Fruit'] = b_df.loc[:, 'Fruit'].apply(
        lambda x: into_list(x) if type(x) == dict else x
    )
    
    # 将列表里面的每一个字典拆分为一行
    b_df_new_values = numpy.dstack(
        (numpy.repeat(b_df.loc[:, 'B'].values, list(map(len, b_df.Fruit.values))),
         numpy.concatenate(b_df.Fruit.values))
    )
    
    b_df = pandas.DataFrame(data=b_df_new_values[0], columns=b_df.columns)

b_df

<table> 
 <thead> 
  <tr> 
   <th align="right"></th> 
   <th align="right">B</th> 
   <th align="right">Fruit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="right">0</td> 
   <td align="right">b</td> 
   <td align="right">{‘name’: ‘apple’, ‘price’: 10}</td> 
  </tr> 
  <tr> 
   <td align="right">1</td> 
   <td align="right">b</td> 
   <td align="right">{‘name’: ‘banana’, ‘price’: 20}</td> 
  </tr> 
  <tr> 
   <td align="right">2</td> 
   <td align="right">b</td> 
   <td align="right">{‘name’: ‘peach’, ‘price’: 30}</td> 
  </tr> 
  <tr> 
   <td align="right">3</td> 
   <td align="right">b</td> 
   <td align="right">{‘name’: ‘pear’, ‘price’: 30}</td> 
  </tr> 
 </tbody> 
</table>

# 将A与B合并 #

*在合并A与B之前,它们有相同列Fruit，需要将它们其中一个的Fruit列删除。*

b_df.drop(columns='Fruit', inplace=True)

*合并*

df = a_df.join(b_df)

<table> 
 <thead> 
  <tr> 
   <th align="right"></th> 
   <th align="right">A</th> 
   <th align="right">Fruit</th> 
   <th align="right">B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="right">0</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘apple’, ‘price’: 10}</td> 
   <td align="right">b</td> 
  </tr> 
  <tr> 
   <td align="right">1</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘banana’, ‘price’: 20}</td> 
   <td align="right">b</td> 
  </tr> 
  <tr> 
   <td align="right">2</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘peach’, ‘price’: 30}</td> 
   <td align="right">b</td> 
  </tr> 
  <tr> 
   <td align="right">3</td> 
   <td align="right">a</td> 
   <td align="right">{‘name’: ‘pear’, ‘price’: 30}</td> 
   <td align="right">b</td> 
  </tr> 
 </tbody> 
</table>