How to do groupby and then replace values of a column with baseline value-CodePudding

I have a data frame as follows, which columns are Car#,measurement_type and measurement _value.

Car#     measurement_type      measurement_value 
1         back to back1                10.3
1         back to back2                10.2
1         baseline                     10.4
2         back to back1                11.5
2         back to back2                10.8
2         baseline                     10.9

I would like to create a new column called (Baseline) in which the values are the baseline value for each car. So the output will look like this:

Car#     measurement_type      measurement_value      Baseline
1         back to back1                10.3              10.4
1         back to back2                10.2              10.4  
1         baseline                     10.4              10.4
2         back to back1                11.5              10.9
2         back to back2                10.8              10.9
2         baseline                     10.9              10.9

I tried this code but it doesn't work:

grouped = df.groupby('car#')
for i in grouped:
    if df['measurement_type'] == 'baseline':
        df['Baseline'] = df.loc[df['measurement_type'] == 'baseline','measurement _value']

Can someone help please.

CodePudding user response：

Here is an approach using df.loc() and map()

Baseline = (df.loc[df["measurement_type"] == "baseline"]
              .groupby("Car#")["measurement_value"].first())
df["Baseline"] = df["Car#"].map(Baseline)
print(df)

   Car# measurement_type  measurement_value  Baseline
0     1    back to back1               10.3      10.4
1     1    back to back2               10.2      10.4
2     1         baseline               10.4      10.4
3     2    back to back1               11.5      10.9
4     2    back to back2               10.8      10.9
5     2         baseline               10.9      10.9

CodePudding user response：

Some other ways:

df["Baseline"] = df['Car#'].map(df.loc[df['measurement_type'].eq("baseline"),
                      ['Car#','measurement_value']]
               .set_index('Car#').squeeze())

df["Baseline"] = df['Car#'].map(dict(df.loc[df['measurement_type'].eq("baseline"),
                           ['Car#','measurement_value']]
                    .to_numpy() )
                                )

print(df)

   Car# measurement_type  measurement_value  Baseline
0     1    back to back1               10.3      10.4
1     1    back to back2               10.2      10.4
2     1         baseline               10.4      10.4
3     2    back to back1               11.5      10.9
4     2    back to back2               10.8      10.9
5     2         baseline               10.9      10.9