CODEWITHSUNDEEP
pythonpandasdataframedatetime
Abhishek Kulkarni
Abhishek Kulkarni

Reputation: 225

Converting string to date-time pandas

I am fetching data from an API into a pandas dataframe whose index values are as follows:-

df.index=['Q1-2013',
 'Q1-2014',
 'Q1-2015',
 'Q1-2016',
 'Q1-2017',
 'Q1-2018',
 'Q2-2013',
 'Q2-2014',
 'Q2-2015',
 'Q2-2016',
 'Q2-2017',
 'Q2-2018',
 'Q3-2013',
 'Q3-2014',
 'Q3-2015',
 'Q3-2016',
 'Q3-2017',
 'Q3-2018',
 'Q4-2013',
 'Q4-2014',
 'Q4-2015',
 'Q4-2016',
 'Q4-2017',
 'Q4-2018']

It is a list of string values. Is there a way to convert this to pandas datetime? I explored few Q&A and they are about using pd.to_datetime which works when the index is of object type. In this example, index values are strings. Expected output:

new_df=magic_function(df.index)
print(new_df.index[0])
01-2013

Wondering how to build "magic_function". Thanks in advance. Q1 is quarter1 which is January, Q2 is quarter2 which is April and Q3 is quarter3 which is July, Q4 is quarter4 which is October

Upvotes: 1

Views: 234

Answers (3)

ralf htp
ralf htp

Reputation: 9432

to_datetime() function https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

It is a datetime64 object when applying to_datetime(), to_period() turns it into a period object, further modifications like to_timestamp().strftime('%m-%Y') turn the index items into strings :

import pandas as pd

df = pd.DataFrame(index=['Q1-2013',
 'Q1-2014',
 'Q1-2015',
 'Q1-2016',
 'Q1-2017',
 'Q1-2018',
 'Q2-2013',
 'Q2-2014',
 'Q2-2015',
 'Q2-2016',
 'Q2-2017',
 'Q2-2018',
 'Q3-2013',
 'Q3-2014',
 'Q3-2015',
 'Q3-2016',
 'Q3-2017',
 'Q3-2018',
 'Q4-2013',
 'Q4-2014',
 'Q4-2015',
 'Q4-2016',
 'Q4-2017',
 'Q4-2018'])

#    df_new = pd.DataFrame(index=pd.to_datetime(['-'.join(s.split('-')[::-1]) for s in df.index]))    
    df_new = pd.DataFrame(index=pd.to_datetime(['-'.join(s.split('-')[::-1]) for s in df.index]).to_period('M'))
#    df_new = pd.DataFrame(index=pd.to_datetime(['-'.join(s.split('-')[::-1]) for s in df.index]).to_period('M').to_timestamp().strftime('m-%Y'))


print(df_new.index)

PeriodIndex(['2013-01', '2014-01', '2015-01', '2016-01', '2017-01', '2018-01',
             '2013-04', '2014-04', '2015-04', '2016-04', '2017-04', '2018-04',
             '2013-07', '2014-07', '2015-07', '2016-07', '2017-07', '2018-07',
             '2013-10', '2014-10', '2015-10', '2016-10', '2017-10', '2018-10'],
            dtype='period[M]', freq='M')

Upvotes: 0

Arvind
Arvind

Reputation: 1016

You can map a function to index: pandas.Index.map

quarter_months = {
    'Q1': 1,
    'Q2': 4,
    'Q3': 7,
    'Q4': 10,
}

def quarter_to_month_year(quarter_year):
    quarter, year = quarter_year.split('-')
    month_year = '%s-%s'%(quarter_months[quarter], year)
    return pd.to_datetime(month_year, format='%m-%Y')

df.index = df.index.map(quarter_to_month_year)

This would produce following result:

DatetimeIndex(['2013-01-01', '2014-01-01', '2015-01-01', '2016-01-01',
               '2017-01-01', '2018-01-01', '2013-04-01', '2014-04-01',
               '2015-04-01', '2016-04-01', '2017-04-01', '2018-04-01',
               '2013-07-01', '2014-07-01', '2015-07-01', '2016-07-01',
               '2017-07-01', '2018-07-01', '2013-10-01', '2014-10-01',
               '2015-10-01', '2016-10-01', '2017-10-01', '2018-10-01'],
              dtype='datetime64[ns]', name='index', freq=None)

Upvotes: 0

yatu
yatu

Reputation: 88305

With a bit of manipulation for the parsing to work, you can use pd.PeriodIndex and format as wanted (reason being that the format %Y%q is expected):

df.index = [''.join(s.split('-')[::-1]) for s in df.index]
df.index = pd.PeriodIndex(df.index, freq='Q').to_timestamp().strftime('%m-%Y')
print(df.index)

Index(['01-2013', '01-2014', '01-2015', '01-2016', '01-2017', '01-2018',
       '04-2013', '04-2014', '04-2015', '04-2016', '04-2017', '04-2018',
       '07-2013', '07-2014', '07-2015', '07-2016', '07-2017', '07-2018',
       '10-2013', '10-2014', '10-2015', '10-2016', '10-2017', '10-2018'],
      dtype='object')

We could also get the required format using str.replace:

df.index = df.index.str.replace(r'(Q\d)-(\d+)', r'\2\1')
df.index = pd.PeriodIndex(df.index, freq='Q').to_timestamp().strftime('%m-%Y')

Upvotes: 3

Related Questions