CODEWITHSUNDEEP
pandasdatetime
Slartibartfast
Slartibartfast

Reputation: 1190

How to format datetime field in pandas

I have the following dataframe:

xl_file
Out[2]: 
                  Day                     Description
Date                                                 
2011-01-26  Wednesday                    Republic Day
2011-03-02  Wednesday                   Mahashivratri
2011-04-12    Tuesday                       Ram Navmi
2011-04-14   Thursday  Dr. Babasaheb Ambedkar Jayanti
2011-04-22     Friday                     Good Friday
              ...                             ...
2020-05-25     Monday          Id-Ul-Fitr (Ramzan ID)
2020-10-02     Friday          Mahatma Gandhi Jayanti
2020-11-16     Monday            Diwali-Balipratipada
2020-11-30     Monday               Gurunanak Jayanti
2020-12-25     Friday                       Christmas

[144 rows x 2 columns]

To convert Day column to datetime field i used the following code:

xl_file['Day'] = pd.to_datetime(xl_file['Day'], format = '%A')

Where i specified the format however i am getting this:

xl_file
Out[4]: 
                  Day                     Description
Date                                                 
2011-01-26 1900-01-01                    Republic Day
2011-03-02 1900-01-01                   Mahashivratri
2011-04-12 1900-01-01                       Ram Navmi
2011-04-14 1900-01-01  Dr. Babasaheb Ambedkar Jayanti
2011-04-22 1900-01-01                     Good Friday
              ...                             ...
2020-05-25 1900-01-01          Id-Ul-Fitr (Ramzan ID)
2020-10-02 1900-01-01          Mahatma Gandhi Jayanti
2020-11-16 1900-01-01            Diwali-Balipratipada
2020-11-30 1900-01-01               Gurunanak Jayanti
2020-12-25 1900-01-01                       Christmas

[144 rows x 2 columns]

How can i change formatting so the datetime field shows day name.

I tried this:

xl_file['Day'].date.dayofweek

but that gives an error :AttributeError: 'Series' object has no attribute 'date'

Upvotes: 0

Views: 30

Answers (1)

run-out
run-out

Reputation: 3184

You are trying to work with string text to go to date? The date is in the index. I'm not sure if you have that in datetime yet, but let's assume it's just a string. Convert the index to datetime (timestamp). 

df.index = pd.to_datetime(df.index)
print(df)
                  Day
Date                 
2011-01-26  Wednesday
2011-03-02  Wednesday
2011-04-12    Tuesday
2011-04-14   Thursday
2011-04-22     Friday
2020-05-25     Monday
2020-10-02     Friday
2020-11-16     Monday
2020-11-30     Monday
2020-12-25     Friday

But the Day column is still just a string. We can get the name of the day from the date using day_name() and setting it to a column. 

df["Day_Name"] = df.index.day_name()
print(df)
                  Day   Day_Name
Date                            
2011-01-26  Wednesday  Wednesday
2011-03-02  Wednesday  Wednesday
2011-04-12    Tuesday    Tuesday
2011-04-14   Thursday   Thursday
2011-04-22     Friday     Friday
2020-05-25     Monday     Monday
2020-10-02     Friday     Friday
2020-11-16     Monday     Monday
2020-11-30     Monday     Monday
2020-12-25     Friday     Friday

If you want the index to have the days. 

df.index = df.index.day_name()

Upvotes: 1

Related Questions