Python dataframe datetime format

How to Format Pandas Datetime?

Explain how to format DateTime in Pandas DataFrame? Pandas Library is best known for supporting a diverse type of data which also includes date and time. The Python Pandas Library has extensive support for DateTime datatype and provides core DateTime computation functionalities out of the box.

In some cases, the dates and times are not in the Pandas DateTime format that you wish, and you wanted to store, print, or visualize it in a different format inside a DataFrame. So here is a complete list of possibilities that you can do with a datetime datatype in your dataframe.

Table of contents

Let’s say you want to store the date of birth of a particular person, we will take the date of birth of John (13/04/1999) for the sake of example, we store this date of birth in a DataFrame with type object. See the following code.

 import pandas as pd df = pd.DataFrame(, 'Name':>) print(df.dtypes) # output # DOB object # Name object # dtype: object 

Here, the date of birth (DOB) type is an string object, but we don’t want it to be that way, instead, we want to convert it to a DateTime object. In this case, we need to use pandas.to_datetime() function.

1. Pandas DateTime Format with pandas.to_datetime()

With pandas built-in function pd.to_datetime(), we can convert the date time string object into a datetime64 object. It will automatically change the default format of the string date time into the default format of datetime64.

 import pandas as pd df = pd.DataFrame(, 'Name':>) # converting date to dtype datetime64 pd.to_datetime(df.DOB) # output # 0 1999-11-01 # 1 2001-12-22 # Name: DOB, dtype: datetime64[ns] 

The following picture shows how the pandas.to_datetime() change the format of DateTime in pandas.

2. Pandas Format DateTime from YYYY-MM-DD to DD-MM-YYYY

In the above example, you have seen how we can change the default format string into DateTime format. The default DateTime format for the datetime64 will be YYYY-MM-DD. In most cases the attribute dayfirst attribute of to_datetime() will work but dayfirst=True is not strict, but will prefer to parse with the day first.

To change the datetime format from YYYY-MM-DD to DD-MM-YYYY use the dt.strftime(‘%d-%m-%Y’) function. See the following Example.

 import pandas as pd df = pd.DataFrame(, 'Name':>) df['DOB'] = pd.to_datetime(df.DOB) print(df['DOB']) new_style=df['DOB'].dt.strftime('%d-%m-%Y') print(new_style) # output # 0 1999-11-01 # 1 2001-12-22 # Name: DOB, dtype: datetime64[ns] # 0 01-11-1999 # 1 22-12-2001 # Name: DOB, dtype: object 

Look at the following picture of how the program changes the datetime format in the pandas dataframe. Remember doing so we will lose the data type of the datetime64.

3. Pandas DateTime format with df.style.format

The above method we used was changing the internal representation of the DateTime. With using df.style.format we will only change the style for the printing and displaying but the internal structure will be still the same.

This example will only work on Jupyter notebook and not elsewhere because it is related to the style of the dataframe, not the dataframe itself.

 import pandas as pd df = pd.DataFrame(, 'Name':>) df['DOB'] = pd.to_datetime(df.DOB) print(df.dtypes) df.style.format() # output # DOB datetime64[ns] # Name object # dtype: object # DOB Name # 0 01-11-1999 Ali # 1 22-12-2001 Zahid 

In the above example, you can see the style of the date change while the dtype is still the same. This is just another way of formatting datetime in a dataframe.

4. Change the format of time in pandas datetime

Until now, we have seen how to deal with the format of date in datetime in pandas. We can also change the format of time in pandas datetime. Let’s create a sample dataframe first for dealing with the format of pandas datetime.

 import pandas as pd dt_range= pd.date_range('2022-07-01', periods=5, freq='H') df = pd.DataFrame() print(df) # output # DateWtime # 0 2022-07-01 00:00:00 # 1 2022-07-01 01:00:00 # 2 2022-07-01 02:00:00 # 3 2022-07-01 03:00:00 # 4 2022-07-01 04:00:00 

4.1 Change time format from HH:MM:SS to SS:MM:HH in Pandas

The default format of the datetime which is generated using the pd.date_range() function is HH:MM:SS. To change the time format use the dt.strftime() function.

See the following example to change datetime format in pandas.

 df['DateTime'] = df.Date.dt.strftime('%Y-%m-%d %S:%M:%H') print(df['DateTime'] # output # 0 2022-07-01 00:00:00 # 1 2022-07-01 00:00:01 # 2 2022-07-01 00:00:02 # 3 2022-07-01 00:00:03 # 4 2022-07-01 00:00:04 # Name: DateTime, dtype: object 

5. Month to String Format in Pandas Datetime

Let’s say you want to make your datetime format more readable for the readers. In this case, you might want to change the date time format to a string name corresponding to that month. Let’s say for the 1st month you want to convert it to January and for the 12th month to be December.

See the following example of how we have changed the format of the month to a string name.

 import pandas as pd dt_range= pd.date_range('2022-07-01', periods=5, freq='M') df = pd.DataFrame() new_df=df.Date.dt.strftime('%Y-%b-%d %S:%M:%H') print(new_df) # output # 0 2022-Jul-31 00:00:00 # 1 2022-Aug-31 00:00:00 # 2 2022-Sep-30 00:00:00 # 3 2022-Oct-31 00:00:00 # 4 2022-Nov-30 00:00:00 # Name: Date, dtype: object 

The following picture shows the before and after the status of the datetime.

6. Conclusion

In this article, you have learned the different ways to format pandas datetime. If you still face any problems please let us know in the comments.

Related Articles

You may also like reading:

Источник

Working with datetime in Pandas DataFrame

Some Pandas tricks to help you get started with data analysis

Datetime is a common data type in data science projects. Often, you’ll work with it and run into problems. I found Pandas is an amazing library that contains extensive capabilities and features for working with date and time.

In this article, we will cover the following common datetime problems and should help you get started with data analysis.

1. Convert strings to datetime
2. Assemble a datetime from multiple columns
3. Get year, month and day
4. Get the week of year, the day of week, and leap year
5. Get the age from the date of birth
6. Improve performance by setting date column as the index
7. Select data with a specific year and perform aggregation
8. Select data with a specific month and a specific day of the month
9. Select data between two dates
10. Handle missing values

Please check out my Github repo for the source code.

1. Convert strings to datetime

Pandas has a built-in function called to_datetime() that can be used to convert strings to datetime. Let’s take a look at some examples

With default arguments

Pandas to_datetime() is able to parse any valid date string to datetime without any additional arguments. For example:

df = pd.DataFrame('date': ['3/10/2000', '3/11/2000', '3/12/2000'], 
'value': [2, 3, 4]>)df['date'] = pd.to_datetime(df['date'])
df

Day first format

By default, to_datetime() will parse string with month first (MM/DD, MM DD, or MM-DD) format, and this arrangement is relatively unique in the United State.

In most of the rest of the world, the day is written first (DD/MM, DD MM, or DD-MM). If…

Источник

DateTime in Pandas and Python

In this tutorial, you’ll learn how to work with dates, times, and DateTime in Pandas and Python. Working with DateTime in Python and Pandas can be a complicated thing. This guide aims to make the complicated, simple, by focusing on what you need to know to get started and to know enough to discover more on your own. Dates and times are critical forms of data in many domains, including finance, economics, science, and more.

By the end of this tutorial, you’ll have learned how to:

• Load DateTimes effectively in Pandas
• Access DateTime attributes in Pandas
• Filter a Pandas DataFrame based on DateTime filters
• Resample Pandas DataFrames based on DateTimes

Importing DateTimes in Pandas DataFrames

Pandas intelligently handles DateTime values when you import a dataset into a DataFrame. The library will try to infer the data types of your columns when you first import a dataset. For example, let’s take a look at a very basic dataset that looks like this:

# A very simple .csv file Date,Amount 01-Jan-22,100 02-Jan-22,125 03-Jan-22,150

You can find the file here. Let’s try to import the dataset into a Pandas DataFrame and check the column data types.

# Loading a Small DataSet df = pd.read_csv('https://raw.githubusercontent.com/datagy/data/main/sample_dates.csv') print(df) # Returns # Date Amount # 0 01-Jan-22 100 # 1 02-Jan-22 125 # 2 03-Jan-22 150

This is great! It looks like everything worked fine. Not so fast – let’s check the data types of the columns in the dataset. We can do this using the .info() method.

# Checking column data types print(df.info()) # Returns # Data columns (total 2 columns): # # Column Non-Null Count Dtype # --- ------ -------------- ----- # 0 Date 3 non-null object # 1 Amount 3 non-null int64 # dtypes: int64(1), object(1) # memory usage: 176.0+ bytes

We can see that the data type of the Date column is object . This means that the data are stored as strings, meaning that you can’t access the slew of DateTime functionality available in Pandas.

Using Pandas parse_dates to Import DateTimes

One easy way to import data as DateTime is to use the parse_dates= argument. The argument takes a list of columns that Pandas should attempt to infer to read. Let’s try adding this parameter to our import statement and then re-print out the info about our DataFrame:

# Parsing Dates in .read_csv() df = pd.read_csv('https://raw.githubusercontent.com/datagy/data/main/sample_dates.csv', parse_dates=['Date']) print(df.info()) # Returns # Data columns (total 2 columns): # # Column Non-Null Count Dtype # --- ------ -------------- ----- # 0 Date 3 non-null datetime64[ns] # 1 Amount 3 non-null int64 # dtypes: datetime64[ns](1), int64(1) # memory usage: 176.0 bytes

We can see that our column is now correctly imported as a DateTime format.

Using to_datetime to Convert Columns to DateTime

The example above worked quite well when we imported a straightforward date format. Now let’s take a look at a more complicated example. We’ll load data from here, that looks like this:

# More complex datetime formats Date,Close Price,High Price,Low Price,Open Price,Volume Date,Close Price,High Price,Low Price,Open Price,Volume 2021-12-10 05AM,48246.57,48359.35,48051.08,48170.66,827.39761 2021-12-10 06AM,47847.59,48430,47810.81,48249.78,1296.18883 2021-12-10 07AM,47694.62,48037.48,47550,47847.59,2299.85298 2021-12-10 08AM,48090.35,48169.06,47587.39,47694.62,1371.25447

When we pass in the Date column as we did earlier, Pandas can’t interpret the date format. Let’s see what this looks like. The code below shows that the date wasn’t actually read as a DateTime format, but rather continues to exist as a string.

import pandas as pd df = pd.read_csv('https://raw.githubusercontent.com/datagy/data/main/time_data.csv', parse_dates=['Date']) print(df.info()) # Returns: # # RangeIndex: 337 entries, 0 to 336 # Data columns (total 6 columns): # # Column Non-Null Count Dtype # --- ------ -------------- ----- # 0 Date 337 non-null object # 1 Close Price 337 non-null float64 # 2 High Price 337 non-null float64 # 3 Low Price 337 non-null float64 # 4 Open Price 337 non-null float64 # 5 Volume 337 non-null float64 # dtypes: float64(5), object(1) # memory usage: 15.9+ KB

One of the ways we can resolve this is by using the pd.to_datetime() function. The function takes a Series of data and converts it into a DateTime format. We can customize this tremendously by passing in a format specification of how the dates are structured.

The format= parameter can be used to pass in this format. The format codes follow the 1989 C standard. Of course, chances are you don’t actually know the C standard for dates off by hard. The full list can be found here, but the table below breaks down a few of the most important ones.

Источник