Skip to main content
 首页 » 编程设计

python之Pandas 支点 : pivot with multiple columns

2024年12月31日8TianFang

我最初从以下数据框开始:

数据集与用户回答多个问题有关,这些问题有多个答案选择,并且用户有能力回答多个答案。

movie_id, user_id, rated_value, question_id, answer_id, genre, user_gender, user_ethnicity 
101, 345, 3.5, 1, 1, comedy, male, white 
101, 345, 3.5, 1, 2, comedy, male, white 
101, 345, 3.5, 2, 1, comedy, male, white 
125, 345, 4.5, 1, 4, drama, male, white 
101, 233, 4.0, 1, 3, comedy, female, black 
101, 233, 4.0, 2, 2, comedy, female, black 
125, 233, 3.0, 1, 1, drama, female, black 
125, 233, 3.0, 2, 2, drama, female, black 
125, 333, 3.0, 1, 1, comedy, male, asian 
125, 333, 3.0, 2, 2, comedy, male, asian  

我想通过旋转来压平这张 table 。我可以在不引入 genre, user_gender, user_ethnicity 的情况下成功完成,如下所示:

pivoted_df = df_to_pivot.assign(val=1).pivot_table( 
    index=['movie_id', 
           'user_id', 
           'rated_value'], 
    columns=['question_id', 
             'answer_id'], 
    values=['question_id', 'answer_id'], 
    fill_value=0) 

然后合并问题和答案 ID,这样列将反射(reflect)为 1_1, 1_2

pivoted_df.columns = pivoted_df.columns.droplevel() 
pivoted_df.columns = ['{}_{}'.format(l1, l2).strip() for l1, l2 in pivoted_df.columns.values] 
pivoted_df = pivoted_df.reset_index() 

movie_id user_id rating_value 1_1 1_2 1_3 1_4...

但是当尝试添加 genre, user_gender, user_ethnicity

pivoted_df = df_to_pivot.assign(val=1).pivot_table( 
    index=['movie_id', 
           'user_id', 
           'rated_value'], 
    columns=['question_id', 
             'answer_id', 'genre', 'user_gender','user_ethnicity'], 
    values=['question_id', 'answer_id', 'genre', 'user_gender','user_ethnicity'], 
    fill_value=0) 

这真的不行。

我的目标是像其他一样调整 genre, user_gender, user_ethnicity 列 movie_id user_id rated_value 1_1 1_2 1_3 1_4...喜剧、戏剧...、男性、女性、黑人、白人、亚洲人

output:  
movie_id, user_id, rated_value , 1_1, 1_2, 1_3, 1_4, comedy, drama, male, female, white, black, asian 
101, 345, 3.5, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0 

目标是获取每行的 movie_id、user_id 对,其他所有内容均由 1 和 0 反射(reflect)。

请您参考如下方法:

将 question_id 和 answer_id 合并到一列中,然后使用 pd.get_dummies

df['QandA'] = df['question_id'].astype(str) + '_' + df['answer_id'].astype(str) 
 
pd.get_dummies(df, columns=['QandA','genre','user_gender','user_ethnicity']) 

输出:

   movie_id  user_id  rated_value  question_id  answer_id  QandA_1_1  QandA_1_2  QandA_1_3  QandA_1_4  QandA_2_1  QandA_2_2  genre_comedy  genre_drama  user_gender_female  \ 
0       101      345          3.5            1          1          1          0          0          0          0          0             1            0                   0    
1       101      345          3.5            1          2          0          1          0          0          0          0             1            0                   0    
2       101      345          3.5            2          1          0          0          0          0          1          0             1            0                   0    
3       125      345          4.5            1          4          0          0          0          1          0          0             0            1                   0    
4       101      233          4.0            1          3          0          0          1          0          0          0             1            0                   1    
5       101      233          4.0            2          2          0          0          0          0          0          1             1            0                   1    
6       125      233          3.0            1          1          1          0          0          0          0          0             0            1                   1    
7       125      233          3.0            2          2          0          0          0          0          0          1             0            1                   1    
8       125      333          3.0            1          1          1          0          0          0          0          0             1            0                   0    
9       125      333          3.0            2          2          0          0          0          0          0          1             1            0                   0    
 
   user_gender_male  user_ethnicity_asian  user_ethnicity_black  user_ethnicity_white   
0                 1                     0                     0                     1   
1                 1                     0                     0                     1   
2                 1                     0                     0                     1   
3                 1                     0                     0                     1   
4                 0                     0                     1                     0   
5                 0                     0                     1                     0   
6                 0                     0                     1                     0   
7                 0                     0                     1                     0   
8                 1                     1                     0                     0   
9                 1                     1                     0                     0   

我认为你需要pd.get_dummies:

pd.get_dummies(df, columns=['genre','user_gender','user_ethnicity']) 

输出:

   movie_id  user_id  rated_value  question_id  answer_id  genre_comedy  genre_drama  user_gender_female  user_gender_male  user_ethnicity_asian  user_ethnicity_black  \ 
0       101      345          3.5            1          1             1            0                   0                 1                     0                     0    
1       101      345          3.5            1          2             1            0                   0                 1                     0                     0    
2       101      345          3.5            2          1             1            0                   0                 1                     0                     0    
3       125      345          4.5            1          4             0            1                   0                 1                     0                     0    
4       101      233          4.0            1          3             1            0                   1                 0                     0                     1    
5       101      233          4.0            2          2             1            0                   1                 0                     0                     1    
6       125      233          3.0            1          1             0            1                   1                 0                     0                     1    
7       125      233          3.0            2          2             0            1                   1                 0                     0                     1    
8       125      333          3.0            1          1             1            0                   0                 1                     1                     0    
9       125      333          3.0            2          2             1            0                   0                 1                     1                     0    
 
   user_ethnicity_white   
0                     1   
1                     1   
2                     1   
3                     1   
4                     0   
5                     0   
6                     0   
7                     0   
8                     0   
9                     0