Concatenating multiple columns into one while copying values of other columns
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
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