Convert any Dates in Spreadsheets using Python
Last Updated :
07 Jan, 2022
In this article, we are going to see how to convert any Dates in Spreadsheets using Python.
Used file:
This file comprises a single column entitled ‘Date’ and stores random dates of 2021 in some different forms of format.
Approach:
- We’ll begin by importing the pandas library.
- Let’s have a look at the code that converts the dates.
sample_dates[“Date”] = pd.to_datetime(sample_dates[“Date”]).dt.strftime(“%Y-%m-%d”)
- To perform tasks involving date and time, we must first transform the column into a datetime data type, which this section of the code will do:
sample_dates[“Date”] = pd.to_datetime(sample_dates[“Date”])
- Then we use the dt and strftime methods with a value of “%Y-%m-%d” to inform Python how to format the date. Example here we used is “%Y-%m-%” where %Y is the full year, %m is the month with 2 digits and %d is the date with 2 digits.
Example 1: Convert any Dates in Spreadsheets
Python3
import pandas as pd
sample_dates = pd.read_excel( "sample_dates.xlsx" )
sample_dates[ "Date" ] = pd.to_datetime(
sample_dates[ "Date" ]).dt.strftime( "%Y-%m-%d" )
sample_dates.to_excel( "sample_dates_formated.xlsx" )
|
Output:
Other well-known formats:
Example date – Saturday, 18 December, 2021, 7:00 PM
- “%A, %B %d” -> “Saturday, December 18”
- “%d-%b-%y” -> “18-Dec-21”
- “%d/%m/%Y” -> “18/12/2021”
- “%b %d, %Y” -> “Dec 18, 2021”
Directive |
Meaning |
Example |
%a |
Weekday as locale’s abbreviated name |
Sun, Mon,…..,Sat(en_US);
So, Mo,…..,Sa(de_DE)
|
%A |
Weekday as locale full name |
Sunday, Monday, ….., Saturday |
%w |
Weekday as a decimal number, where 0 is Sunday and 6 is Saturday |
0, 1, 2, 3……,6 |
%d |
Day of the month as a zero-padded decimal number |
01,02,….31 |
Example 2:
We’re going to use the same dataset that we used in the last example.
Format - "%d %b, %Y" -> "18 December, 2021"
Here we will use ‘%#d’ to remove the padding of zero from the day, i.e. 08 to 8. This will not do padding of zero if the date is in single digit. We can use ‘%-d’ on Linux.
Python3
import pandas as pd
sample_dates = pd.read_excel( "sample_dates.xlsx" )
sample_dates[ "Date" ] = pd.to_datetime(
sample_dates[ "Date" ]).dt.strftime( "%#d %B, %Y" )
sample_dates.to_excel( "sample_dates_formated.xlsx" )
|
Output:
Example 3:
Here we will use different format
Format - "%B %d, %Y" -> "December 18, 2021"
Python3
import pandas as pd
sample_dates = pd.read_excel( "sample_dates.xlsx" )
sample_dates[ "Date" ] = pd.to_datetime(
sample_dates[ "Date" ]).dt.strftime( "%B %d, %Y" )
sample_dates.to_excel( "sample_dates_formated.xlsx" )
|
Output:
Share your thoughts in the comments
Please Login to comment...