python - Python pandas: groupby and devide by the first value of each group

Question

I have a pandas dataframe like this.

>data

ID Distance   Speed
1  100        40
1  200        20
1  200        10
2  400        20
2  500        30
2  100        40
2  600        20
2  700        90
3  800        80
3  700        10
3  400        20

I want to groupby the table by ID, and create a new column time by dividing each value in the Distance column by the first row of the Speed column of each ID group. So the result should look like this.

>data

ID Distance   Speed   Time
1  100        40       2.5
1  200        20         5
1  200        10         5
2  400        20        20
2  500        30        25
2  100        40         5
2  600        20        30
2  700        90        35
3  800        80        10
3  700        10      8.75
3  400        20         5

My attempt:

data['Time'] = data['Distance'] / data.loc[data.groupby('ID')['Speed'].head(1).index, 'Speed']

But the result seems to be not good. How do you do it?

标签： pythonpandasdataframepandas-groupby

Answer 1

使用transformwithfirst返回与Seriesoriginal相同的长度df：

data['Time'] = data['Distance'] /data.groupby('ID')['Speed'].transform('first')

或drop_duplicates使用map：

s = data.drop_duplicates('ID').set_index('ID')['Speed']
data['Time'] = data['Distance'] / data['ID'].map(s)

---

print (data)
    ID  Distance  Speed   Time
0    1       100     40   2.50
1    1       200     20   5.00
2    1       200     10   5.00
3    2       400     20  20.00
4    2       500     30  25.00
5    2       100     40   5.00
6    2       600     20  30.00
7    2       700     90  35.00
8    3       800     80  10.00
9    3       700     10   8.75
10   3       400     20   5.00