Search code examples
pythonpandasdataframereshapemelt

Reshaping a Dataframe with repeating column names

I have data that looks like this:

    dataframe_1:                
    week    SITE        LAL SITE     LAL
0   1   BARTON CHAPEL   1.1 PENASCAL I  1
1   2   BARTON CHAPEL   1.1 PENASCAL I  1
2   3   BARTON CHAPEL   1.1 PENASCAL I  1

And, i need the final dataframe to look like this:

    dataframe_2:        
    week    SITE    LAL
0   1   BARTON CHAPEL   1.1
1   2   BARTON CHAPEL   1.1
2   3   BARTON CHAPEL   1.1
3   1   PENASCAL I  1
4   2   PENASCAL I  1
5   3   PENASCAL I  1

I've tried using 'melt' but I cannot get the desire result. Perhaps I'm using the wrong approach? thank you,

Solution

  • Not a very generalizable solution, but will work on your example:

    df.groupby('week').apply( lambda _df : pd.concat((_df.iloc[:,1:3], _df.iloc[:,3:5]))).reset_index('week')

    it groups by week and then reshapes with column selection + concatenation. Removing a superfluous index column in the end.