CODEWITHSUNDEEP
pythonpandaspandas-groupby
Brandy
Brandy

Reputation: 13

How do I convert rows to column by using groupby in pandas?

I have a dataframe which looks like this

   SHIP_ID        LON            LAT        LABEL_ETA     VESSEL_TYPE       TIMESTAMP_UTC_MONTH
0   23445      233.226313      0.363567      21858.0     CONTAINER SHIP            APR
1   32323      100.200100     -0.457523      21857.0     CONTAINER SHIP            JAN
2   55421      191.223145     -0.398676      21855.0     CONTAINER SHIP            FEB
3   45672      112.234589      0.438764      21852.0     CONTAINER SHIP            DEC

However, I want to make it in this way

   SHIP_ID       VESSEL_TYPE        JAN       FEB      MAR     APR .....    DEC
0   23445      CONTAINER SHIP      102.22    102.22    ...        
1   32323      CONTAINER SHIP      102.22    102.22    ...  
2   55421      CONTAINER SHIP      102.22    ..        ...
3   45672      CONTAINER SHIP      102.22    ..        ...

I'd like the TIMESTAMP_UTC_MONTH's data as the column which are the months and it contains the mean value of each month's LABEL_ETA.

LON AND LAT are to be excluded as well. I've tried methods like pivot_table() and it worked! But the requirement says I am only able to do it with groupby() method.

Upvotes: 1

Views: 60

Answers (2)

RichieV
RichieV

Reputation: 5183

df.groupby(['SHIP_ID', 'VESEEL_TYPE', 'TIMESTAMP_UTC_MONTH']).LABEL_ETA.mean().unstack().reset_index()

Read more about grouping in pandas' userguide

Upvotes: 1

hoomant
hoomant

Reputation: 455

If you only want to have "the mean value of each month's LABEL_ETA", you can simply use:

df.groupby('TIMESTAMP_UTC_MONTH').LABEL_ETA.mean()

Upvotes: 0

Related Questions