Concatenating multiple columns into one while copying values of other columns

Question

I have the following data frame:

Date          DV        FA1              FA2           FA3           FA4
22/02/2019   200      Lazard             NaN           NaN           NaN 
2/02/2019    50      Deutsche           Ondra           NaN          NaN         
22/02/2019  120   China Securities      Ballas         Daiwa     Morgan Stanley

I need all the FA columns to concatenate into one column while also copying Date and DV columns. The end result would like below:

Date            DV        FA 
22/02/2019     200      Lazard             
2/02/2019       50      Deutsche           
2/02/2019       50      Ondra           
22/02/2019     120     China Securities
22/02/2019     120      Ballas           
22/02/2019     120      Daiwa     
22/02/2019     120     Morgan Stanley

Could anyone please help me with this?? Thank you.

Answer 1

Using melt with dropna

yourdf=df.melt(['Date','DV']).dropna()
yourdf
          Date   DV variable            value
0   22/02/2019  200      FA1           Lazard
1    2/02/2019   50      FA1         Deutsche
2   22/02/2019  120      FA1  ChinaSecurities
4    2/02/2019   50      FA2            Ondra
5   22/02/2019  120      FA2           Ballas
8   22/02/2019  120      FA3            Daiwa
11  22/02/2019  120      FA4    MorganStanley

Answer 2

Using stack:

df = (df.set_index(['Date','DV']).stack()
        .reset_index(level=[0,1], name='FA')
        .reset_index(drop=True))

print(df)
         Date   DV                FA
0  22/02/2019  200            Lazard
1   2/02/2019   50          Deutsche
2   2/02/2019   50             Ondra
3  22/02/2019  120  China Securities
4  22/02/2019  120            Ballas
5  22/02/2019  120             Daiwa
6  22/02/2019  120    Morgan Stanley