Readjusting columns after pandas concatenation-CodePudding

I have a dataframe as follows which I generate after Pandas concat operation:

Date      Col1  Col2    Col1    Col2
1/1/2021    1   3       
2/1/2021    2   4       
3/1/2021                5        6
4/1/2021                7        8

I want to get the following:

Date      Col1  Col2    
1/1/2021    1   3       
2/1/2021    2   4       
3/1/2021    5   6
4/1/2021    7   8

I am not sure how to achieve the above.

Edit: The code is grandfathered so I can't concatenate based on axis=0. Essentially this is a post concat question

CodePudding user response：

Let us try stack unstack to remove the NaN values

df.set_index('Date').stack().unstack()

Another approach would be to use groupby first along the columns axis

df.set_index('Date').groupby(level=0, axis=1).first()

          Col1  Col2
Date                
1/1/2021   1.0   3.0
2/1/2021   2.0   4.0
3/1/2021   5.0   6.0
4/1/2021   7.0   8.0

CodePudding user response：

Try using this when doing the concatenation: pd.concat(....,axis=0). You will not need to readjust after the concat operation.

Update: You still can do this post-concatenation. Split the dataframe into two, based on the condition col1/col2 is equal to null. Then do the concat with axis=0.