0

Recently I have been working on a time series data set and have written a script to automate some plotting. Using the pd.to_datetime function (provided with a specific format), I assumed would automatically convert every time entry to the appropriate format.

The raw data follows this format:
%d/%m/%YYYY HH:MM (HH:MM is irrelevant in this case so don't worry about it as we are only interested in the daily average)

However, it seems Python intermittently changes the 'raw timestamps' and changes the format to: %d-%m-%YYYY

Why is this the case and how can I make sure Python doesn't do this?

I have received the below error and struggle to work out why this is the case. I have looked at the following SO but I don't have the same issue. time data does not match format

The data itself is provided in the following CSV and is all in the %d/%m/%Y format.

Error message

My code for my function is attached in case there are any errors with how I've converted the timestamps.

def plotFunction(dataframe):
    for i in wellNames:
        my_list = dataframe["Date"].values
        DatesRev = []

        for j in my_list:
            a=j[0:10]
            DatesRev.append(a)
        #We now need to re-add the dates to our data frame
        df2 = pd.DataFrame(data= DatesRev)
        df2.columns = ["DatesRev"]
        dataframe["DatesRev"] = df2["DatesRev"]
#         print (dataframe)
#         #df2= pd.DataFrame(DatesRev) 
#         #df2.columns = ['DatesRev']    
#         #dataframe['DatesRev'] = df2['DatesRev']
        wellID = dataframe[dataframe['Well']==i]
        wellID['DatesRev'] = pd.to_datetime(wellID['DatesRev'], format='%d/%m/%Y')
        print (i)
#         ax = wellID.set_index('DatesRev').plot()
#         xfmt = mdates.DateFormatter('%d-%m-%Y')
#         ax.xaxis.set_major_formatter(xfmt)
#         plt.xticks(rotation=90)
#         ax.legend(bbox_to_anchor=(1.04,1), loc="upper left")
#         plt.title(i)
#         plt.show()
#         plt.savefig(i + ".jpg", bbox_inches='tight')
IronKirby
  • 708
  • 1
  • 7
  • 24
  • the problem is that python does not recognize `/` very well. I came across this problem myself. Dashes are what it recognizes the best. If I may suggest, try to keep the `-` formatting. – Julian Rachman Jan 17 '18 at 03:51
  • @JulianRachman - Interesting! I wasn't aware of that. The data will always be coming in this type of format so I will have to get used to this regardless. Python's pretty robust at handling special 'characters' so '/' shouldn't really be a hindrance. There has to be a robust solution for this - I'm certain of it. but I'll take your point into consideration. :) – IronKirby Jan 17 '18 at 03:54
  • Ya it is an interesting thing but it's just Python doing Python things. Let me know if it works. I will post an answer. – Julian Rachman Jan 17 '18 at 03:55
  • Try “%s/%s/%s” %(d, m, Y) –  Jan 17 '18 at 04:28
  • @mikey - this did not work either unfortunately. – IronKirby Jan 17 '18 at 04:35

1 Answers1

0

The problem is that python does not recognize / very well. I came across this problem myself. Dashes are what it recognizes the best. If I may suggest, try to keep the - formatting. It is just Python being Python. :P

In wellID['DatesRev'], change format='%d/%m/%Y' to format='%d-%m-%Y' and that should possibly fix your problem.

Julian Rachman
  • 575
  • 3
  • 15