将两列等长的列合并到

Question

我有一个包含多列的数据帧。我试图实现的是将两列的值合并/叠加到一列中，将每列的值一行一行地叠加（不幸的是，这个要求使我无法使用类似union的解决方案）。其余列的内容可以复制。非常感谢您的帮助

#Current DataFrame
print(df)
Stock Ticker    Index Ticker    Price   Date
AAPL            INDX            100     12/31/2018 8:57  
GOOG            RSL             123     12/31/2018 8:57
GM              COMP            90      12/31/2018 8:57
MMM             NIKK            340     12/31/2018 8:57
INVD            EUR             30      12/31/2018 8:57 

#Desired results
print(df2)
Stock and Bench   Price   Date
AAPL              100     12/31/2018 8:57
INDX              100     12/31/2018 8:57
GOOG              123     12/31/2018 8:57
RSL               123     12/31/2018 8:57
GM                90      12/31/2018 8:57
COMP              90      12/31/2018 8:57
MMM               340     12/31/2018 8:57
NIKK              340     12/31/2018 8:57
INVD              30      12/31/2018 8:57
EUR               30      12/31/2018 8:57

Answer 1

您可以将price和date列设置为索引，并将stock和ticker堆叠起来。最后使用reset\u索引进行一些清理。你知道吗

df.set_index(['Date', 'Price'])[['Stock Ticker','Index Ticker']].stack()\
.reset_index(2,drop = True).reset_index(name = 'Stock and Bench')


    Date    Price   Stock and Bench
0   12/31/2018 8:57 100 AAPL
1   12/31/2018 8:57 100 INDX
2   12/31/2018 8:57 123 GOOG
3   12/31/2018 8:57 123 RSL
4   12/31/2018 8:57 90  GM
5   12/31/2018 8:57 90  COMP
6   12/31/2018 8:57 340 MMM
7   12/31/2018 8:57 340 NIKK
8   12/31/2018 8:57 30  INVD
9   12/31/2018 8:57 30  EUR

Answer 2

您可以使用^{}设置Date和Price作为id_vars：

(df.melt(id_vars=['Date', 'Price'], 
         value_name='Stock and Bench')
         .drop('variable', axis=1))

         Date        Price     Stock and Bench
0  12/31/2018/8:57    100            AAPL
1  12/31/2018/8:57    123            GOOG
2  12/31/2018/8:57     90              GM
3  12/31/2018/8:57    340             MMM
4  12/31/2018/8:57     30            INVD
5  12/31/2018/8:57    100            INDX
6  12/31/2018/8:57    123             RSL
7  12/31/2018/8:57     90            COMP
8  12/31/2018/8:57    340            NIKK
9  12/31/2018/8:57     30             EUR

或使用^{}：

(pd.wide_to_long(df.reset_index(), stubnames='Ticker', i = 'index', 
                j = 'num', suffix='\w+')
                .reset_index(drop=True)
                .rename({'Ticker':'Stock and Bench'}, axis=1))

        Date         Price Stock and Bench
0  12/31/2018-8:57    100   AAPL
1  12/31/2018-8:57    123   GOOG
2  12/31/2018-8:57     90     GM
3  12/31/2018-8:57    340    MMM
4  12/31/2018-8:57     30   INVD
5  12/31/2018-8:57    100   INDX
6  12/31/2018-8:57    123    RSL
7  12/31/2018-8:57     90   COMP
8  12/31/2018-8:57    340   NIKK
9  12/31/2018-8:57     30    EUR