CODEWITHSUNDEEP
pythonpandasdataframe
Milhouse
Milhouse

Reputation: 595

Want the last day of each month for a data frame in pandas

I have a data frame in pandas where the index are business days. I want to create a new data frame using only the last day of each month, along with the corresponding data in the various columns. I have tried a few different ways with little success and the error message I keep getting is: AttributeError: 'DataFrame' object has no attribute 'date'.

The index in my data frame is labeled 'Date'. Other than verifying that, I don't know where to go. Also, the dates in this column include hours, minutes, and seconds...not sure if that matters.

Below is an example of what the data frame looks like:

Date                   A    B    C
11/27/2015 00:00:00    5    2    4
11/30/2015 00:00:00    2    9    1
12/1/2015  00:00:00    6    1    8
12/2/2015  00:00:00    4    7    0

I'd like for the result to show 

11/30/2015 00:00:00    2  9  1

Some of the code I have tried is as follows: Got the same error with both.

prices = prices.resample('M', 'first')
prices = prices.index + pd.offsets.MonthEnd(0)

Upvotes: 2

Views: 6366

Answers (2)

arthur braga
arthur braga

Reputation: 11

my first contribution to STACKOVERFLOW YESSS

the below will work even if the last day of the month is NOT in your original time series.... worked for me

there may be extra coding that i used in my thing, but the spirit is below:

for i in range(df.size):
  if i==0:
    #get the first date whatever that is:
    lastdayofmonthseries = pd.DataFrame(data=[df.ix[0][0]], index=[df.ix[0].name],columns=[df.columns[0]])

  else:
      if i< df.size-1:
        # print SpotCloses.ix[i],SpotCloses.ix[i+1]

        if df.ix[i].name.month!=df.ix[i+1].name.month:
          #this will find the last day of each month in time series
          TempDF=pd.DataFrame(data=[df.ix[i][0]],index=[df.ix[i].name],columns=[df.columns[0]])
          lastdayofmonthseries=lastdayofmonthseries.append(TempDF)
      else:
          #check if its the last date and save it whatever that is
          if i==df.size-1:
              TempDF = pd.DataFrame(data=[df.ix[i][0]], index=[df.ix[i].name],columns=[df.columns[0]])
              lastdayofmonthseries = lastdayofmonthseries.append(TempDF)

Upvotes: 1

chrisb
chrisb

Reputation: 52286

In [1]: df = pd.DataFrame({'a':range(1000)}, index=pd.date_range('2014-1-1', periods=1000))

In [2]: df.index.days_in_month
Out[2]: 
array([31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31,
       31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 28, 28, 28,

If instead the dates are in a column, not the index, you would do df['Date'].dt.days_in_month

Edit:

Above is if you had wanted the last day of the month by itself. Instead, it sounds like you want? prices.index = prices.index + pd.offsets.MonthEnd(0)

Upvotes: 6

Related Questions