How to call the month from dataframe's date if the date format is DD/MM/YYYY-CodePudding

How to call the month from dataframe's date if the date format is DD/MM/YYYY

Currently, I'm using the df['month'] = pd.DatetimeIndex(df['Date']).month to get the month number and covert it to month name. When review the output, only realize it is getting the day as month.

Data:
Date format: DD/MM/YYYY
01/01/2022, 15/01/2022, 03/02/2022, 20/02/2022, 06/03/2022, 18/03/2022

So, how can I correct it?

Code:

month_labels = {1: 'Jan', 2: 'Feb', 3: 'March', 4: 'April', 5: 'May', 6: 'June', 7: 'July', 8: 'August',9: 'Sept', 10: 'Oct', 11: 'Nov', 12: 'Dec'}


df['month'] = pd.DatetimeIndex(df['Settlement_Date']).month
x= df['month'].apply(lambda x: month_labels[x])


print(x)

Result:
Jan, Jan, Mar, Feb, Jun, Mar

The month is in the middle of the date instead of first of the date.

Expected result:
Jan, Jan, Feb, Feb, Mar, Mar

CodePudding user response：

[Solution]
Change to pd.to_datetime and use format = '%d/%m/%Y' to convert from the original format "MM/DD/YYYY" to customize format "DD/MM/YYYY".
Finally, get the expected result.

df['month'] = pd.to_datetime(df['Settlement_Date'], format = '%d/%m/%Y', errors='coerce').dt.month

Data:
01/01/2022, 15/01/2022, 03/02/2022, 20/02/2022, 06/03/2022, 18/03/2022

Result:
Jan, Jan, Feb, Feb, Mar, Mar

CodePudding user response：

Check Below code:

import pandas as pd
df = pd.DataFrame({'date':['01/01/2022']})

df['date'] = pd.to_datetime(df['date'])

df['date'].dt.month_name()

Output:

CodePudding user response：

Since you have a custom date format DD/MM/YYY, you are supposed to pass it as the format that should be used to parse the dates

import pandas as pd

dates = ['1/01/2022', '15/01/2022', '03/02/2022', '20/02/2022', '06/03/2022', '18/03/2022']
df = pd.DataFrame({'date': dates})
df_date = pd.to_datetime(df['date'], format='%d/%m/%Y')
df['month'] = df_date.dt.month_name()

print(df)

Output:

         date     month
0   1/01/2022   January
1  15/01/2022   January
2  03/02/2022  February
3  20/02/2022  February
4  06/03/2022     March
5  18/03/2022     March

CodePudding user response：

Use the dayfirst=True parameter of DatetimeIndex:

df['month'] = pd.DatetimeIndex(df['Settlement_Date'], dayfirst=True).month

Output:

  Settlement_Date  month
0      01/01/2022      1
1      15/01/2022      1
2      03/02/2022      2
3      20/02/2022      2
4      06/03/2022      3
5      18/03/2022      3