Menu
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.
975
Join columns using the Merge Cells add-in for Excel With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.
Step 1: Open all files (workbooks) that contain the data you want to consolidate. Step 2: Ensure the data is organized in the same way (see example below). Step 3: On the Data ribbons, select Data Tools and then Consolidate. Step 4: Select the method of consolidation (in our example, it's Sum).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
