我是一個(gè)在 2.7 版上工作的 python 新手。以下是我正在使用的數(shù)據(jù)框示例。還有其他與問(wèn)題無(wú)關(guān)的列，因此它們不包含在下面。df = pd.DataFrame( { "Name" : ["BROD", "BROD", "BROD", "BROD", "SSBD" , "SSBD","SSBD","SSBD"] , "Digit" : ["F", "F", "T", "T", "F", "F", "T", "T"], "ID": ["A","A","A","A","B","B","B","B"], "Date": ["2/3/2010","2/3/2010","2/3/2010","2/3/2010","3/4/2007","3/4/2007","3/4/2007","3/4/2007"], "Base" : ["CAD","CAD","CAD","CAD","CAD","CAD","CAD","CAD"], "Term" : ["USD","USD","JPY","JPY","EUR","EUR","JPY","JPY"], "Amt": [100.00,100.00,9082.00,9082.00,60.00,60.00,7387.80,7387.80]})有多個(gè)重復(fù)值。每行代表交易的一個(gè)組成部分，ID 列將它們分組為一個(gè)交易。我想創(chuàng)建一個(gè)新的數(shù)據(jù)框，每筆交易只包含一行。數(shù)據(jù)框如下所示：ID Date Name Buy Sell Buy Amt Sell AmtA 2/3/2010 BROD USD JPY 100.00 9082.00B 3/4/2007 SSBD EUR JPY 60.00 7387.80對(duì)于每個(gè) ID，如果 Digit = F，則 Term 列中的值位于 Buy 列中，Amt 列中的值位于 Buy Amt 列中。如果數(shù)字 = T，則 Term 列中的值位于 Sell 列中，Amt 列中的值位于 Sell Amount 列中。請(qǐng)為我指出正確的方向，以最有效的方式解決這個(gè)問(wèn)題。謝謝。
2 回答
RISEBY
TA貢獻(xiàn)1856條經(jīng)驗(yàn) 獲得超5個(gè)贊
你可以使用np.where然后groupby
df['Buy'] = np.where((df['Digit'] == 'F'), df['Term'], np.nan)
df['Sell'] = np.where((df['Digit'] == 'T'), df['Term'], np.nan)
df['BuyAmt'] = np.where((df['Digit'] == 'F'), df['Amt'], np.nan)
df['SellAmt'] = np.where((df['Digit'] == 'T'), df['Amt'], np.nan)
df.drop(['Digit','Base','Term','Amt'], axis=1, inplace= True)
df = df.groupby('ID').first()
print(df)
Name Date Buy Sell BuyAmt SellAmt
ID
A BROD 2/3/2010 USD JPY 100.0 9082.0
B SSBD 3/4/2007 EUR JPY 60.0 7387.8
此外，如果您需要像您發(fā)布的那樣按順序排列您的專欄，您可以使用 pandas reindex
達(dá)令說(shuō)
TA貢獻(xiàn)1821條經(jīng)驗(yàn) 獲得超6個(gè)贊
我認(rèn)為應(yīng)該刪除重復(fù)項(xiàng)，否則您需要更好地解釋如何處理相同的行：
>>> df2 = df.drop_duplicates().reset_index(drop=True)
然后我們創(chuàng)建兩個(gè)數(shù)據(jù)框，一個(gè)用于“F”，一個(gè)用于“T”，為每個(gè)創(chuàng)建Buy/Sell和Buy Amt/ Sell Amt，并刪除未使用的列：
>>> df_F = df2[df2.Digit == 'F'].assign(**{'Buy': lambda x: x.Term, 'Buy Amt': lambda x: x.Amt})
... .drop(['Digit', 'Base', 'Term', 'Amt'], axis=1)
>>> df_T = df2[df2.Digit == 'T'].assign(**{'Sell': lambda x: x.Term, 'Sell Amt': lambda x: x.Amt})
... .drop(['Digit', 'Base', 'Term', 'Amt'], axis=1)
最后，我們合并兩個(gè)數(shù)據(jù)框，并重新排列列順序：
>>> merged = df_F.merge(df_T, on=['ID', 'Name', 'Date'])
>>> merged[['ID', 'Date', 'Name', 'Buy', 'Sell', 'Buy Amt', 'Sell Amt']]
ID Date Name Buy Sell Buy Amt Sell Amt
0 A 2/3/2010 BROD USD JPY 100.0 9082.0
1 B 3/4/2007 SSBD EUR JPY 60.0 7387.8
就是這樣。如果“ID”應(yīng)該是索引，則可以使用merged.set_index('ID')
添加回答
舉報(bào)
0/150
提交
取消