Dataframe reading and formatting with dates in Pandas Python

Question

Is there a pandas or numpy function that recognizes if a new month has come up from looking at the if the Month(MM) is incremented by 1 with the format YYYY-MM-DD-TTTT. So in the sample below I am trying to see if a function can indicate which index the Date goes from October 2015-10-31 23:59:00 to December 2015-11-01 00:00:00.

Code

import pandas as pd

#Getting the input from a csv file 
data= 'input.csv'
#Reverses all the table data values
data1 = data.iloc[::-1].reset_index(drop=True)
#Getting The date column 
Date = np.array(data1['Date'])

Portion of Dates, the 11th index is when it goes from October to November

['2015-10-31 23:50:00' '2015-10-31 23:51:00' '2015-10-31 23:52:00'
 '2015-10-31 23:53:00' '2015-10-31 23:54:00' '2015-10-31 23:55:00'
 '2015-10-31 23:56:00' '2015-10-31 23:57:00' '2015-10-31 23:58:00'
 '2015-10-31 23:59:00' '2015-11-01 00:00:00' '2015-11-01 00:01:00'
 '2015-11-01 00:02:00' '2015-11-01 00:03:00' '2015-11-01 00:04:00'

Expected Output

At the 11th index the Month changes

mechanical_meat · Accepted Answer · 2021-03-09 02:39:59Z

2

Because you're going to likely reach a point where you want to identify where the month changes again, I'm providing a solution for multiple month changes. This uses pandas' .diff(), reference: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.diff.html

# first convert strings to datetime
data1['Date'] = pd.to_datetime(data1['Date'])

month_changes = data1.loc[np.where(data1['Date'].dt.month.diff().gt(0))].index.tolist()

for month_change in month_changes:
    print(f'At the {month_change + 1}th index the month changes')

edited Mar 9, 2021 at 2:39

answered Mar 9, 2021 at 2:27

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

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

5 Comments

ege Selcuk Over a year ago

It is giving me an error AttributeError: Can only use .dt accessor with datetimelike values

mechanical_meat Over a year ago

Please see edited answer for the way to convert the strings into datetimes.

ege Selcuk Over a year ago

Thank you it works, it is a little bit slow would there be anyway I could accelerate the pandas code, since i am working with a very large dataframe?

mechanical_meat Over a year ago

Ah, I'm unable to think of anything to speed it up right now; but I'll keep thinking, and update the answer if I come up with any optimisation(s).

ege Selcuk Over a year ago

Sorry for the trouble, if you cant find anything, thats okay. The function you have given is also more than enough, I appreciate it.

Collectives™ on Stack Overflow

Dataframe reading and formatting with dates in Pandas Python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related