0

I have a dataframe like below:

       score           pvpdate
0      {2: 29, 4: 24}  2018-05-23
1      {2: 23, 4: 24}  2018-05-29
2      {2: 36, 4: 25}  2018-05-23

and I want to filter the score column and get a result like

     score1      score2     pvpdate
0        29          24     2018-05-23
1        23          24     2018-05-29
2        36          25     2018-05-23

How can I do this? I have tried the ways that score column like a json column, and it doesn't work.

Bram Vanroy
  • 27,032
  • 24
  • 137
  • 239
张羽白
  • 55
  • 1
  • 6

2 Answers2

0

If want columns names from keys of dictionaries extract column by pop, use DataFrame contructor and change columns names by add_prefix, last join all columns without score:

df = pd.DataFrame(df.pop('score').values.tolist()).add_prefix('score').join(df)
print (df)
   score2  score4     pvpdate
0      29      24  2018-05-23
1      23      24  2018-05-29
2      36      25  2018-05-23

Anf if want columns names created by length of parsed DataFrame use f-strings:

#if stored dictionary like strings
df['score']=df['score'].map(ast.literal_eval) 

df1 = pd.DataFrame(df.pop('score').values.tolist())
df1.columns = [f'score{i}' for i in range(1, len(df1.columns) + 1)]
df = df1.join(df)
print (df)
   score1  score2     pvpdate
0      29      24  2018-05-23
1      23      24  2018-05-29
2      36      25  2018-05-23
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Wouldn't `df.pop('score').tolist()` work too? I.e. Unless for performance (marginal?) no need to drop down to NumPy. – jpp Oct 22 '18 at 14:04
  • 1
    @jpp - I think yes, sure. I add `.values` for better performance if big df. – jezrael Oct 22 '18 at 14:05
  • I have already tried your way, but it still doesn't work, it still shows as before.I tried df[['score']] = df[['score',]].astype(str) before apply your way, still doesn't work. – 张羽白 Oct 23 '18 at 01:47
  • I found an answer import ast df['score']=df['score'].map(lambda d:ast.literal_eval(d)) dfNew=df.join(pd.DataFrame(df['score'].to_dict()).T) – 张羽白 Oct 23 '18 at 02:21
  • But still thank you so much though I still can't figure out why yours doesn't work – 张羽白 Oct 23 '18 at 02:21
  • @张羽白 - Problem is my solution working with dictionaries in column `score`, in your real data are strings. So necessary convert them to dictionaries by `df['score']=df['score'].map(ast.literal_eval)` – jezrael Oct 23 '18 at 05:11
0

Dictionary column in pandas dataframe I found answer from this,by using import ast df['score']=df['score'].map(lambda d:ast.literal_eval(d)) dfNew=df.join(pd.DataFrame(df['score'].to_dict()).T)

张羽白
  • 55
  • 1
  • 6