Add column of repeating sequential values

Question

I have a dataframe that contains stacked monthly values and looks like:

      Value    Month
0    0.09187    Jan
1    0.72878    Feb
2    0.92052    Mar
3   -1.86845    Apr
4   -1.16489    May
5   -0.61433    Jun
6    0.68008    Jul
7   -1.50555    Aug
8   -0.18985    Sep
9   -1.11380    Oct
10  -0.63838    Nov
11   0.37527    Dec 
12   0.234216   Jan

I would like to add a column of years, using a known range, so that the df looks like:

     Value     Month   Year
0    0.09187    Jan    1950
1    0.72878    Feb    1950
2    0.92052    Mar    1950
3   -1.86845    Apr    1950
4   -1.16489    May    1950
5   -0.61433    Jun    1950
6    0.68008    Jul    1950
7   -1.50555    Aug    1950
8   -0.18985    Sep    1950
9   -1.11380    Oct    1950
10  -0.63838    Nov    1950
11   0.37527    Dec    1950
12   0.234216   Jan    1951

I tried initializing a years list to apply to the column as:

years = list(range(1950, 2000)
df['Year'] = years * 12

But this produced

      Value    Month  Year
0    0.09187    Jan   1950
1    0.72878    Feb   1951
2    0.92052    Mar   1952

And so on. I've been unable to come up with any other approach

sacuL · Accepted Answer · 2018-09-20 22:34:35Z

4

As long as you know that you have Jan data for all your years, you could do:

df['Year'] = df['Month'].eq('Jan').cumsum()+1949
>>> df
       Value Month  Year
0   0.091870   Jan  1950
1   0.728780   Feb  1950
2   0.920520   Mar  1950
3  -1.868450   Apr  1950
4  -1.164890   May  1950
5  -0.614330   Jun  1950
6   0.680080   Jul  1950
7  -1.505550   Aug  1950
8  -0.189850   Sep  1950
9  -1.113800   Oct  1950
10 -0.638380   Nov  1950
11  0.375270   Dec  1950
12  0.234216   Jan  1951

Or, you could follow your original logic, but use np.repeat:

import numpy as np
years = list(range(1950, 2000))
df['Year'] = np.repeat(years,12)

Or another alternative:

df['Year'] = pd.date_range('1950-01-01',periods=len(df),freq='m').year

edited Sep 20, 2018 at 22:34

answered Sep 20, 2018 at 22:22

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brlockwood Over a year ago

Worked perfectly. I did not know about pandas df.cumsum method. Very useful. Thank you!

Collectives™ on Stack Overflow

Add column of repeating sequential values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related