我有兩個要合并的熊貓數據框。數據框的大小不同，所以我只希望df1保留那些出現在其中的數據框- 有些學生只出現在df1或之一中df2。df1具有標題，['student', 'week1_count', 'week1_mean', ..., 'week11_count', 'week11_mean']并使用除'student'列之外的所有單元格初始化為零。df2具有標題['student', 'week', 'count', 'mean']并填充了相應的'student'. 'week'是一個介于 1-11 之間的整數，并且'count'和'mean'是相應的浮點數。我想要做的是對于給定的學生 in df1and df2，在給定的一周內，取相應的'count'and'mean'值并將其放入df1相應的列中。例如， 的'week'值1意味著 in'count'和'mean'in的值df2將分別放入'week1_count'和'week1_mean'中df1。關于我一直循環range(11)并創建子集數據框的幾周，但想知道是否有更快的方法。IEdf1: student week1_count week1_mean week2_count week2_mean ... '0' 0 0 0 0 ... '2' 0 0 0 0 ... '3' 0 0 0 0 ... . . . '500' 0 0 0 0 ... '541' 0 0 0 0 ... '542' 0 0 0 0 ... 和df2: student week count mean '0' 1 5 6.5 '1' 1 3 7.0 '2' 1 2 8.2 '2' 2 10 15.1 . . . '500' 2 12 4.3 '540' 4 1 3.0 '542' 1 4 1.2 '542' 2 9 5.2所以預期的結果df_result: student week1_count week1_mean week2_count week2_mean ... '0' 5 6.5 0 0 ... '2' 2 8.2 10 15.1 ... '7' 0 0 0 0 ... . . . '500' 0 0 12 4.3 ... '541' 0 0 0 0 ... '542' 4 1.2 9 5.2 ... 我已經嘗試了各種例程 - 這些例程都沒有按預期工作 - 在熊貓中，例如：合并：使用“左”連接，因為我想要df1. 我嘗試重命名列df2以匹配列名。加入連接更新：嘗試將所有單元格初始化為df1tonp.nan而不是0.0，然后使用df1.update(df2)（在將 cols 重命名為 in 之后df2）用預期的值更新所有 nan 值試圖只設置值：即類似df1[rows_in_both][['week1_count','week1_mean']] = df2[rows_in_both][['count','mean']]但也不起作用
1 回答
慕后森
TA貢獻1802條經驗 獲得超5個贊
這更像是一個update問題而不是 merge
s=df2.pivot(index='student',columns='week',values=['count','mean'])# pivot df2 to format it to df1 like .
s.columns.map('week{0[1]}_{0[0]}'.format) # modify the column
Out[645]:
Index(['week1_count', 'week2_count', 'week4_count', 'week1_mean', 'week2_mean',
'week4_mean'],
dtype='object')
s.columns=s.columns.map('week{0[1]}_{0[0]}'.format)
然后我們做 update
df1=df1.set_index('student')
df1=df1.update(s)
添加回答
舉報
0/150
提交
取消