Rnaldinho Rnaldinho - 1 month ago 9
Python Question

How to convert to "end-of-month"?

I have several dataframes that currently have all end-of-month data.

I now used following script to import Finance data:

import csv
import pandas as pd
import numpy as np
import urllib.request

urllib.request.urlretrieve(
'http://chart.finance.yahoo.com/table.csv?s=^GSPC&a=4&b=1&c=2013&d=5&e=1&f=2016&g=m&ignore=.csv',
'gspc.csv'
)

table = pd.read_csv('gspc.csv')

Date Open High Low Close Volume Adj Close
49 2012-05-01 1,398 1,415 1,292 1,310 4158095900 1,310
48 2012-06-01 1,310 1,363 1,267 1,362 4103472300 1,362
47 2012-07-02 1,362 1,392 1,325 1,379 3663113300 1,379


As I said, I need to get this data into end-of-month.
I.e.

Date Open High Low Close Volume Adj Close
49 2012-05-31 1,398 1,415 1,292 1,310 4158095900 1,310
48 2012-06-30 1,310 1,363 1,267 1,362 4103472300 1,362
47 2012-07-31 1,362 1,392 1,325 1,379 3663113300 1,379


I tried

table['Date'] = pd.to_datetime(table['Date'])
table.set_index('Date').resample('M')
table


but weren't successful, though "M" should be "month end frequency".

Answer

This should be the thing you're looking for

table['Date'] = table['Date'] - pd.tseries.offsets.MonthEnd()
Comments