I have a pandas column of type date which ranges from 2015-01-01 to 2019-01-01 and I want to create a new column which can normalize my values to single digit
I have a pandas column like below
A
2015-01-01
2015-02-01
2015-03-01
...
...
2019-12-01
and I want a new column like below
A B
2015-01-01 0
2015-02-01 1
2015-03-01 2
2015-05-01. 4
...
2019-11-01. 46
2019-12-01 47
Use Series.asfreq with DatetimeIndex for all values, then crate range column by DataFrame.assign and last use Series.map:
s = df.set_index('A').asfreq('MS').assign(new=lambda x: np.arange(len(x)))['new']
df['B'] = df['A'].map(s)
print (df)
A B
0 2015-01-01 0
1 2015-02-01 1
2 2015-03-01 2
3 2019-12-01 59
Detail:
print (s)
A
2015-01-01 0
2015-02-01 1
2015-03-01 2
2015-04-01 3
2015-05-01 4
2015-06-01 5
2015-07-01 6
...
...
...
2019-06-01 53
2019-07-01 54
2019-08-01 55
2019-09-01 56
2019-10-01 57
2019-11-01 58
2019-12-01 59
Freq: MS, Name: new, dtype: int32
We can using resample
df1=df.copy()
df.index=df.A
s=df.resample('MS').count()
df1['B']=df1.A.map(pd.Series(np.arange(len(s)),index=s.index))
df1
A B
0 2015-01-01 0
1 2015-02-01 1
2 2015-03-01 2
3 2019-12-01 59
Make sure A is a datetime column
df['A'] = pd.to_datetime(df['A'])
Subtract the start date from every subsequent date.
start = df['A'].sort_values('A').iloc[0, 0]
df'B'] = df['A'].apply(lambda x: x - start).dt.days