Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Easiest way to create variables by substracting elements from group? e.g., value of each row of each group – first row of group value

In the following example, what would be the best to group so that there could be a new column that is formed by taking first year in each group and subtracting by current year. For example in in row with index 0 it would be NaN, row with index 1 , it would = 1, row with index 2 it would = 3, row with index 4 = 1 and so forth.

>>> import pandas as pd
>>> df = pd.DataFrame({'id': ['1', '1', '1', '2', '2', '3', '4', '4'],
...                    'Year': [2000, 2001, 2003, 2004, 2005, 2002, 2001, 2003]})
>>> print(df)
  id  Year
0  1  2000
1  1  2001
2  1  2003
3  2  2004
4  2  2005
5  3  2002
6  4  2001
7  4  2003

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Transform Year with first to get the first year per id, then subtract this from Year column to get difference, finally mask the values where difference is 0:

s = df['Year'] - df.groupby('id')['Year'].transform('first')
df['col'] = s.mask(s == 0)

  id  Year  col
0  1  2000  NaN
1  1  2001  1.0
2  1  2003  3.0
3  2  2004  NaN
4  2  2005  1.0
5  3  2002  NaN
6  4  2001  NaN
7  4  2003  2.0
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading