Pandas - How to create new columns derived from the values in two consecutive rows?

user_10487667 0
Viewed 64

I have the following dataframe -

ID    Name   Distance   Time   Base    Height
1     A      10         2      12      2
2     A      20         4      14      3
3     B      30         6      16      4
4     B      40         8      18      5
5     C      50         10     20      6   
6     C      60         12     22      8

I want to create new columns Speed, Tarea, Speed_ratio, Tarea_ratio whose values are derived from the values in rows with Names A,B,C as follows -

ID    Name   Distance   Time   Base    Height   Speed    Tarea    Speed_ratio   Tarea_ratio
1     A      10         2      12      2        5        12       1             0.5714
2     A      20         4      14      3        5        21       NA            NA   
3     B      30         6      16      4        5        32       1             0.7111 
4     B      40         8      18      5        5        45       NA            NA
5     C      50         10     20      6        5        60       1             0.6818    
6     C      60         12     22      8        5        88       NA            NA

Can this be done? Thanks.

pandas dataframe python-3.x python
3 Answers

You can do the below:

In [431]: df['Speed'] = df.Distance.div(df.Time)
In [433]: df['Tarea'] = df.Base.mul(df.Height)/2

In [447]: df['Speed_ratio'] = df.Speed / df.groupby('Name').Speed.shift(-1)
    
In [450]: df['Tarea_ratio'] = df.Tarea / df.groupby('Name').Tarea.shift(-1)

In [451]: df
Out[451]: 
   ID Name  Distance  Time  Base  Height  Speed  Tarea  Speed_ratio  Tarea_ratio
0   1    A        10     2    12       2    5.0   12.0          1.0     0.571429
1   2    A        20     4    14       3    5.0   21.0          NaN          NaN
2   3    B        30     6    16       4    5.0   32.0          1.0     0.711111
3   4    B        40     8    18       5    5.0   45.0          NaN          NaN
4   5    C        50    10    20       6    5.0   60.0          1.0     0.681818
5   6    C        60    12    22       8    5.0   88.0          NaN          NaN
user_58208140

You can simply use operators between columns:

df["Speed"]=5
df["Tarea"]=df["Base"]*df["Height"]/2 # that column will have float values, if you want to have integer values, use astype() method.
user_140415120

You can assign new columns by using df[key] = value, as you would for a native dict object. For your particular case, the following would work:

df["Speed"]       = df["Distance"] / df["Time"]
df["Tarea"]       = df["Base"] * df["Height"] / 2
df["Speed_ratio"] = df["Speed"].iloc[::2] / df["Speed"].iloc[1::2].values
df["Tarea_ratio"] = df["Tarea"].iloc[::2] / df["Tarea"].iloc[1::2].values

Which gives the following pandas.DataFrame:

ID  Name  Distance  Time  Base  Height  Speed  Tarea  Speed_ratio  Tarea_ratio
 1     A        10     2    12       2    5.0   12.0          1.0     0.571429
 2     A        20     4    14       3    5.0   21.0          NaN          NaN
 3     B        30     6    16       4    5.0   32.0          1.0     0.711111
 4     B        40     8    18       5    5.0   45.0          NaN          NaN
 5     C        50    10    20       6    5.0   60.0          1.0     0.681818
 6     C        60    12    22       8    5.0   88.0          NaN          NaN
user_86854230
Related