Geek Answers Handbook

Pandas casting step back 12 months

I have a data frame with course names for each year. I need to find the duration in months starting in 2016.

from io import StringIO

import pandas as pd

u_cols = ['page_id','web_id']
audit_trail = StringIO('''
year_id | web_id
2012|efg
2013|abc 
2014| xyz
2015| pqr
2016| mnp
''')

df11 = pd.read_csv(audit_trail, sep="|", names = u_cols  )

How to add months to a new column starting with the highest (e.g. bottom, like bfill?)

The final data frame will look like this:

u_cols = ['page_id','web_id' , 'months']
audit_trail = StringIO('''
year_id | web_id | months
2012|efg | 60
2013|abc | 48
2014| xyz | 36
2015| pqr | 24
2016| mnp | 12
''')

df12 = pd.read_csv(audit_trail, sep="|", names = u_cols  )

Some answers do not take into account that there may be several courses. Updating sample data ...

from io import StringIO

import pandas as pd

u_cols = ['course_name','page_id','web_id']
audit_trail = StringIO('''
course_name| year_id | web_id
a|2012|efg
a|2013|abc 
a|2014| xyz
a|2015| pqr
a|2016| mnp
b|2014| xyz
b|2015| pqr
b|2016| mnp

''')

df11 = pd.read_csv(audit_trail, sep="|", names = u_cols  )
+6
source share
3 answers
>>> df11.assign(months=df11.groupby('course_name').year_id.transform(
        lambda years: range(len(years) * 12, 0, -12)))
  course_name  year_id web_id  months
0           a     2012    efg      60
1           a     2013   abc       48
2           a     2014    xyz      36
3           a     2015    pqr      24
4           a     2016    mnp      12
5           b     2014    xyz      36
6           b     2015    pqr      24
7           b     2016    mnp      12
+5
source 
df11.assign(
    months=df11.groupby('course_name').apply(
        lambda x: pd.Series(np.repeat([12], len(x)).cumsum()[::-1])
    ).values
)

  course_name  year_id web_id  months
0           a     2012    efg      60
1           a     2013    abc      48
2           a     2014    xyz      36
3           a     2015    pqr      24
4           a     2016    mnp      12
5           b     2014    xyz      36
6           b     2015    pqr      24
7           b     2016    mnp      12

All @Alexander and @jezrael credits for reminding us of a cool feature.transform
Given this, I can change my answer to

df11.assign(months=df11.groupby('course_name').year_id.transform(
    lambda x: np.repeat([12], len(x)).cumsum()[::-1]
))

  course_name  year_id web_id  months
0           a     2012    efg      60
1           a     2013    abc      48
2           a     2014    xyz      36
3           a     2015    pqr      24
4           a     2016    mnp      12
5           b     2014    xyz      36
6           b     2015    pqr      24
7           b     2016    mnp      12
+7

transform arange:

df11['months'] = df11.groupby('course_name')['year_id'] \
                     .transform(lambda x: np.arange(len(x)*12, 0, -12))
print (df11)
  course_name  year_id  web_id  months
0           a     2012     efg      60
1           a     2013     abc      48
2           a     2014     xyz      36
3           a     2015     pqr      24
4           a     2016     mnp      12
5           b     2014     xyz      36
6           b     2015     pqr      24
7           b     2016     mnp      12
+4

More articles:


All Articles