如何通过拆分上的现有列来创建新列

Question

我有一只熊猫，看起来像：

Keyword |              ranks              | search_type |   search_volume
kw1     |[{'rank': 1, 'url': example.com}]|  1          |   500
kw1     |[{'rank': 1, 'url': example.com}]|  2          |   500
kw2     |[{'rank': 2, 'url': example.com}]|  1          |   1500
kw2     |[{'rank': 2, 'url': example.com}]|  2          |   1500
kw3     |[{'rank': 1, 'url': example.com}]|  1          |   60
kw3     |[{'rank': 1, 'url': example.com}]|  2          |   60

我想把ranks分成两列：ranks包含排名，一个名为url的新列包含url，因此生成的df如下所示：

Keyword |   ranks    |        url          | search_type |   search_volume
kw1     |[{'rank': 1 | 'url': example.com}]|  1          |   500
kw1     |[{'rank': 1 | 'url': example.com}]|  2          |   500
kw2     |[{'rank': 2 | 'url': example.com}]|  1          |   1500
kw2     |[{'rank': 2 | 'url': example.com}]|  2          |   1500
kw3     |[{'rank': 1 | 'url': example.com}]|  1          |   60
kw3     |[{'rank': 1 | 'url': example.com}]|  2          |   60

到目前为止我试过：

df.ranks = df.ranks.str.split(',',1).tolist()这带来了一个Nan的列表，我也尝试了df['ranks'].str.split(',', expand=True)但没有用。我试过：

df = pd.DataFrame(df.ranks.str.split(' ',1).tolist(),columns = ['ranks','url'])

但我得到了ValueError: Shape of passed values is (1, 400), indices imply (2, 400)的回报。你知道吗

编辑：df.ranks.dtype返回dtype('0')type(df.ranks)返回pandas.core.series.Series

Answer 1

strip和split，expand参数设置为true，即

df[['rank','url']] = df['ranks'].str.strip('{[]}').str.split(',',expand=True).values

  Keyword                              ranks  search_type  search_volume       rank                  url
0  kw1       [{'rank': 1, 'url': example.com}]            1            500  'rank': 1   'url': example.com
1  kw1       [{'rank': 1, 'url': example.com}]            2            500  'rank': 1   'url': example.com
2  kw2       [{'rank': 2, 'url': example.com}]            1           1500  'rank': 2   'url': example.com
3  kw2       [{'rank': 2, 'url': example.com}]            2           1500  'rank': 2   'url': example.com
4  kw3       [{'rank': 1, 'url': example.com}]            1             60  'rank': 1   'url': example.com
5  kw3       [{'rank': 1, 'url': example.com}]            2             60  'rank': 1   'url': example.com

如果没有，也可以将字符串转换为dict

df[['rank','url']] =pd.concat(df['ranks'].apply(literal_eval).apply(pd.io.json.json_normalize).tolist()).values

# df[['rank','url']] =pd.concat(df['ranks'].apply(pd.io.json.json_normalize).tolist()).values # If you have lists with dict 

  Keyword                                ranks  search_type  search_volume  rank          url
0  kw1       [{'rank': 1, 'url': "example.com"}]            1            500     1  example.com
1  kw1       [{'rank': 1, 'url': "example.com"}]            2            500     1  example.com
2  kw2       [{'rank': 2, 'url': "example.com"}]            1           1500     2  example.com
3  kw2       [{'rank': 2, 'url': "example.com"}]            2           1500     2  example.com
4  kw3       [{'rank': 1, 'url': "example.com"}]            1             60     1  example.com
5  kw3       [{'rank': 1, 'url': "example.com"}]            2             60     1  example.com

Answer 2

我认为有list和dicts，所以建议使用列表理解和select first dictionary of list和select by key：

df['r'] = [x[0]['rank'] for x in df['ranks']]
df['u'] = [x[0]['url'] for x in df['ranks']]
print (df)
  Keyword                                ranks  search_type  search_volume  r  \
0     kw1  [{'rank': 1, 'url': 'example.com'}]            1            500  1   
1     kw1  [{'rank': 1, 'url': 'example.com'}]            2            500  1   
2     kw2  [{'rank': 2, 'url': 'example.com'}]            1           1500  2   
3     kw2  [{'rank': 2, 'url': 'example.com'}]            2           1500  2   
4     kw3  [{'rank': 1, 'url': 'example.com'}]            1             60  1   
5     kw3  [{'rank': 1, 'url': 'example.com'}]            2             60  1   

             u  
0  example.com  
1  example.com  
2  example.com  
3  example.com  
4  example.com  
5  example.com 

或：

df['r'] = [{'rank': x[0]['rank']} for x in df['ranks']]
df['u'] = [{'url': x[0]['url']} for x in df['ranks']]
print (df)
  Keyword                                ranks  search_type  search_volume  \
0     kw1  [{'rank': 1, 'url': 'example.com'}]            1            500   
1     kw1  [{'rank': 1, 'url': 'example.com'}]            2            500   
2     kw2  [{'rank': 2, 'url': 'example.com'}]            1           1500   
3     kw2  [{'rank': 2, 'url': 'example.com'}]            2           1500   
4     kw3  [{'rank': 1, 'url': 'example.com'}]            1             60   
5     kw3  [{'rank': 1, 'url': 'example.com'}]            2             60   

             r                       u  
0  {'rank': 1}  {'url': 'example.com'}  
1  {'rank': 1}  {'url': 'example.com'}  
2  {'rank': 2}  {'url': 'example.com'}  
3  {'rank': 2}  {'url': 'example.com'}  
4  {'rank': 1}  {'url': 'example.com'}  
5  {'rank': 1}  {'url': 'example.com'}  

Answer 3

试试这个

df['ranks'].str.split(', ', expand=True).rename(columns={0:'ranks',1:'url'}）

   Keyword                               ranks   search_type   search_volume
0  kw1       [{'rank': 1, 'url': example.com}]              1            500
1  kw1       [{'rank': 1, 'url': example.com}]              2            500
2  kw2       [{'rank': 2, 'url': example.com}]              1           1500
3  kw2       [{'rank': 2, 'url': example.com}]              2           1500
4  kw3       [{'rank': 1, 'url': example.com}]              1             60
5  kw3       [{'rank': 1, 'url': example.com}]              2             60
         ranks                   url
0  [{'rank': 1  'url': example.com}]
1  [{'rank': 1  'url': example.com}]
2  [{'rank': 2  'url': example.com}]
3  [{'rank': 2  'url': example.com}]
4  [{'rank': 1  'url': example.com}]
5  [{'rank': 1  'url': example.com}]