pandas pivot or groupby multiple columns and control columns

user_7886466 0
Viewed 234

Need to modify the following df

gears   milesbefore milesafter  model_car   safety_car  gears   milesbefore milesafter  model_truck safety_truck
1       10          20          honda       NTSB        5       100         200         volvo       NTSB
1       10          20          honda       NTFD        5       100         200         volvo       NTFD
1       10          20          honda       NRTB        5       100         200         volvo       NRTB
1       10          20          toyota      NTFD        5       100         200         merc        NTFD
1       10          20          toyota      NTFD        5       100         200         merc        NTFD
1       10          20          toyota      NRTB        5       100         200         merc        NRTB
1       10          20          jeep        NTSB        5       100         200         jaguar      NTSB
1       10          20          jeep        NTFD        5       100         200         jaguar      NTFD
1       10          20          jeep        NRTB        5       100         200         jaguar      NRTB
1       10          20          jeep        NRTB        6       1000        2000        jaguar      NTFB

to this

model_car   model_truck NTSB_car    NTFD_car    NRTB_car    NTSB_truck  NTFD_truck  NRTB_truck
honda       volvo       1:10:20     1:10:20     1:10:20     5:100:200   5:100:200   5:100:200
toyota      merc        1:10:20     1:10:20     1:10:20     5:100:200   5:100:200   5:100:200
jeep        jaguar      1:10:20     1:10:20     1:10:20     5:100:200   5:100:200   5:100:200

This involves three conditions one group by model_car and safety_car two is to avoid rows which look like this

1   10  20  jeep    NRTB    6   1000    2000    jaguar  NTFB

where the safety monitoring organization does not match. ideally i would live to save them in a different df.

and third is string concatenation which i can do it myself.

I really could not get beyond df.groupby()

pandas-groupby pandas pivot-table python-3.x
1 Answers

Your raw dataframe has some duplicate columns and appears to really be a "cars" dataframe and "trucks" dataframe. You can start by splitting the raw dataframe and working on each one separately, then merging them at the end. You can do it without groupby.

Split raw data into two similar dataframes

import pandas as pd
df = pd.read_csv('rawdata.csv')

car_cols = [
    'gears', 'milesbefore', 'milesafter', 
    'model_car', 'safety_car'
]
df_cars = df[car_cols].copy()


truck_cols = [
    'gears.1', 'milesbefore.1', 'milesafter.1', 
    'model_truck', 'safety_truck'
]
df_trucks = df[truck_cols].copy()

### Rename fields for compatibility
df_cars.rename(
    columns={
        'model_car': 'model',
        'safety_car': 'safety'
    }, inplace=True
)

df_trucks.rename(
    columns={
        'model_truck': 'model',
        'safety_truck': 'safety',
        'gears.1': 'gears',
        'milesbefore.1': 'milesbefore',
        'milesafter.1': 'milesafter'
    }, inplace=True
)

Here is df_cars, and df_trucks looks similar.

   gears  milesbefore  milesafter   model safety
0      1           10          20   honda   NTSB
1      1           10          20   honda   NTFD
2      1           10          20   honda   NRTB
3      1           10          20  toyota   NTFD
4      1           10          20  toyota   NTFD
5      1           10          20  toyota   NRTB
6      1           10          20    jeep   NTSB
7      1           10          20    jeep   NTFD
8      1           10          20    jeep   NRTB
9      1           10          20    jeep   NRTB

Then concatenate your columns and do pivot on each dataframe

### Do work for cars table
df_cars_final = df_cars.copy().drop_duplicates()
df_cars_final['val'] = df_cars_final['gears'].astype(str)\
                        + ':' + df_cars_final['milesbefore'].astype(str)\
                        + ':' + df_cars_final['milesafter'].astype(str)

df_cars_final = df_cars_final.pivot(
        index='model', columns='safety', values='val'
        ).reset_index().rename_axis(None, axis=1)
        

### Do work for trucks table
df_trucks_final = df_trucks.copy().drop_duplicates()
df_trucks_final['val'] = df_trucks_final['gears'].astype(str)\
                        + ':' + df_trucks_final['milesbefore'].astype(str)\
                        + ':' + df_trucks_final['milesafter'].astype(str)

df_trucks_final = df_trucks_final.pivot(
        index='model', columns='safety', values='val'
        ).reset_index().rename_axis(None, axis=1)

Here is df_cars_final, and df_trucks_final looks similar.

    model     NRTB     NTFD     NTSB
0   honda  1:10:20  1:10:20  1:10:20
1    jeep  1:10:20  1:10:20  1:10:20
2  toyota  1:10:20  1:10:20      NaN

Then merge the two dataframes together to get your desired output.

df_final = df_cars_final.merge(
            df_trucks_final, left_index=True, 
            right_index=True,suffixes=('_car', '_truck')
)

print(df_final)


 model_car NRTB_car NTFD_car NTSB_car model_truck NRTB_truck         NTFB NTFD_truck NTSB_truck
0     honda  1:10:20  1:10:20  1:10:20      jaguar  5:100:200  6:1000:2000  5:100:200  5:100:200
1      jeep  1:10:20  1:10:20  1:10:20        merc  5:100:200          NaN  5:100:200        NaN
2    toyota  1:10:20  1:10:20      NaN       volvo  5:100:200          NaN  5:100:200  5:100:200
user_109766540
Related