数据框中的目录列表，保留其索引

Question

我有一个带有列值的dataframe字典列表，如下所示：

id         comments
1          [{review:{review_id: 8987, review_text: 'wonderful'}, {review:{review_id: 8988, review_text: 'good'}]
2          [{review:{review_id: 9098, review_text: 'not good'}, {review:{review_id: 9895, review_text: 'terrible'}]

我通过以下操作找到了如何展平特定的comments：

pd.io.json.json_normalize(json.loads(df['comments'].iloc[0].replace("'", '"')))

它从列值生成一个新的数据帧。这很好，但我实际上需要做的是id扩展，就像这样：

id      review_id      review_text
1       8987           wonderful
1       8988           good
2       9098           not good
2       9895           terrible

请注意id随评论一起扩展。如何实现此问题的解决方案

作为参考，这里是数据集的一个小样本：https://aimedu-my.sharepoint.com/:x:/g/personal/matthewromero_msds2021_aim_edu/EfhdrrlYJy1KmGWhECf91goB7jpHuPFKyz8L3UTfyCSDiA?e=pYcap3

Answer 1

根据您提供的文件和您希望得到的结果，您可以尝试以下代码：

import pandas as pd
import ast 

#import data
df = pd.read_excel('./restaurants_reviews_sample.xlsx', usecols = [1,2])
#change column to list of dictionaries
df.user_reviews = df.user_reviews.apply(lambda x: list(ast.literal_eval(x)))
#explode the reviews 
df = df.explode('user_reviews')
#resetting index
df.reset_index(inplace = True, drop = True)
#unnesting the review dictionary
df.user_reviews = df.user_reviews.apply(lambda x: x['review'])
#creating new columns (only the ones we need)
df = df.assign(id='', review_text='')
#populate the columns from dictionary in user_reviews
cols = list(df.columns[2:4])
for i in list(range(0, len(df))):
    for c in cols:
        df[c][i] = df.user_reviews[i][c]
#cleaning columns
df.drop(columns = 'user_reviews' , inplace = True)
df.rename(columns = {'id':'review_id',
                     'index':'id'}, inplace = True)

新的数据帧如下所示：

      id        review_id   review_text
0   6301456     46743270    
1   6301456     41974132    A yuppies place, meaning for the young urban poor the place is packed with the young crowd of the early 20’s and mid 20’s to early 30’s that can still take a loud music pumping on the background with open space where you can check out the girls for a possible get to know and possible pick up. Quite affordable for the combo bucket with pulutan for the limited budget crowd but is there to look for a hook up.
2   6301456     38482279    I celebrated my birthday here and it was awesome! My team enjoyed the place, foods and drinks. *tip: if you will be in a group, consider getting the package with cocktail tower and beers plus the platter. It is worth your penny! Kudos to Dylan and JP for the wonderful service that they have provided us and for making sure that my celebration will be a really good one, and it was! Thank you guys! See you again soon! ðŸ˜ÂðŸ˜Â
3   6301456     35971612    Sa lahat nang Central na napuntahan ko, dito ko mas bet! Unang una sa lahat, masarap yung foods and yung pagka gawa ng drinks nila. Hindi pa masyado pala away yung mga customers dito. 😂
4   6301456     35714330    Good place to chill and hang out. Not to mention the comfort room is clean. The staff are quite busy to attend us immediately but they are polite and courteous. Would definitely comeback! Cheers! ðŸÂºðŸ˜Š
5   6301475     47379863    Underrated chocolate cake under 500 pesos! I definitely recommend this Cloud 9 cake!!! I’m not into chocolate but this one is good. This cake has a four layers, i loved the creamy white moose part. I ordered it via Grab Food and it was hassle free! 😀 The packaging was bad, its just white plastic container, Better handle it with care.
6   6301475     42413329    We loved the Cloud9 cake, its just right taste. We ordered it for our office celebration. However, we went back there to try other food. We get to try a chocolate cake that's too sweet, a cheese cake that's just right, and sansrival that's kind weird and i didnt expect that taste it's sweet and have a lot of nuts and.. i don't know i just didnt feel it. We also hand a lasagna, which is too saucey for is, it's like a soup of tomato. it's a bit disappointing, honestly. Other ordered from our next table looks good, and a lot of serving. They ordered rice meal, maybe you should try that .
7   6301475     42372938    Best cake i’ve eaten vs cakes from known brands such as Caramia and the like. Lots of white chocolate on top, not so sweet and similar to brazo de mercedes texture and, the merengue is the best!
8   6301475     41699036    This freaking piece of chicken costs 220Php. Chicken Cacciatore. Remember the name. DO NOT ORDER! This was my first time ordering something from your resto and I can tell you I AM NOT HAPPY!
9   6301475     40973213    Heard a lot about their famous chocolate cake. Bought a slice to try but found it quite sweet for my taste. Hope to try their other cakes though.