CODEWITHSUNDEEP
pythonpandaspivot
Stefano Potter
Stefano Potter

Reputation: 3577

Pivoting based on unique values

I have a dataframe like so:

Allotment  NDWI   DEM    TWI    Land_Cover
Annex      10     1.2    4      PHP
Annex      10     1.2    4      PHP
Annex      10     1.2    4      WMTGP
Annex      10     1.2    4      SP
Berg       5      1.7    5      BNW
Berg       5      1.7    5      BNW
Berg       5      1.7    5      SP
Berg       5      1.7    5      WMTGP

and I want to pivot it so that all the unique values in the rows for a particular Allotment become their own column.

My desired output is:

Allotment  NDWI    DEM  TWI  Land_Cover1   Land_Cover2   Land_Cover3
Annex      10      1.2  4    PHP           WMTGP         SP
Berg       5       1.7  5    BNW           SP            WMTGP

Is there a way to incorporate .unique() into a pivot table or a reshape?

Upvotes: 1

Views: 448

Answers (1)

Stefan
Stefan

Reputation: 42875

You can use .unique() via .groupby() and .apply():

land_cover = df.groupby('Allotment')['Land_Cover'].apply(lambda x: pd.DataFrame(x.unique()).T).reset_index(level=1, drop=True)
land_cover.columns = ['Land_Cover{}'.format(c) for c in land_cover.columns]

to get:

          Land_Cover0 Land_Cover1 Land_Cover2
Allotment                                    
Annex             PHP       WMTGP          SP
Berg              BNW          SP       WMTGP

which you can merge with a de-duped version of the original DataFrame:

pd.concat([df.set_index('Allotment').loc[:, ['NDWI', 'DEM', 'TWI']].drop_duplicates(), land_cover], axis=1)

           NDWI  DEM  TWI Land_Cover0 Land_Cover1 Land_Cover2
Allotment                                                    
Annex        10  1.2    4         PHP       WMTGP          SP
Berg          5  1.7    5         BNW          SP       WMTGP

Upvotes: 2

Related Questions