我最初从以下数据框开始:
数据集与用户回答多个问题有关,这些问题有多个答案选择,并且用户有能力回答多个答案。
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
