How to index a date column in python

Question

I have a date column in a python dataframe. I want to index these by ordering the date. Is this something possible in python?

date     indexed
2007-02-21  3
2007-02-18  1
2007-02-24  5
2007-02-18  1
2007-02-23  4
2007-02-20  2
2007-02-23  4

I was looking for indexation, but i guess i am using a wrong term to check for. Please guide.

Edit

Actually i want to replace the dates by the equivalent index numbers.

yep, you need to sort them by date, after that index them all using a simple loop — Mohsen_Fatemi
– Mohsen_Fatemi, Commented Sep 16, 2017 at 19:17

MaxU - stand with Ukraine · Accepted Answer · 2017-09-16 20:03:31Z

1

IIUC you want to use pd.factorize() method:

In [190]: df['new'] = pd.factorize(df['date'], sort=True)[0] + 1

In [191]: df
Out[191]:
        date  indexed  new
0 2007-02-21        3    3
1 2007-02-18        1    1
2 2007-02-24        5    5
3 2007-02-18        1    1
4 2007-02-23        4    4
5 2007-02-20        2    2
6 2007-02-23        4    4

PS pd.factorize() starts counting from 0, so i've added 1 in order to meet your desired result

edited Sep 16, 2017 at 20:03

answered Sep 16, 2017 at 19:41

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

3 Comments

Doubt Dhanabalu Over a year ago

Thanks a lot. Why do we add 1 here? kindly clarify

MaxU - stand with Ukraine Over a year ago

@DoubtDhanabalu, pd.factorize() starts from 0. So i've added 1 in order to meet your desired result

Doubt Dhanabalu Over a year ago

ok i got it, Thanks a lot. I am accepting this answer. Thanks again.

ChaosPredictor · Accepted Answer · 2017-09-16 19:45:42Z

1

What you looking for is sort_values by date

df = pd.DataFrame(["2007-02-21","2007-02-18","2007-02-24","2007-02-18","2007-02-23","2007-02-20","2007-02-23"],columns=["date"])

df.sort_values("date", axis=0)

edited Sep 16, 2017 at 19:45

answered Sep 16, 2017 at 19:35

ChaosPredictor

4,1192 gold badges40 silver badges53 bronze badges

Comments

mwweb · Accepted Answer · 2017-09-16 19:58:23Z

1

Using pandas.DataFrame.sort_index

import pandas as pd

df = pd.DataFrame(['2007-02-21','2007-02-18','2007-02-24','2007-02-18','2007-
02-23', '2007-02-20' , '2007-02-23'], index=[3, 1, 5, 1, 4,2,4], columns=
['Date'])

print df
         Date
3  2007-02-21
1  2007-02-18
5  2007-02-24
1  2007-02-18
4  2007-02-23
2  2007-02-20
4  2007-02-23


df2 = df.sort_index(axis=0)
print(df2)

         Date
1  2007-02-18
1  2007-02-18
2  2007-02-20
3  2007-02-21
4  2007-02-23
4  2007-02-23
5  2007-02-24

answered Sep 16, 2017 at 19:58

mwweb

7,9454 gold badges21 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to index a date column in python

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related