CODEWITHSUNDEEP
pythonpandaspython-datetime
elksie5000
elksie5000

Reputation: 7762

Combining quarter and financial year columns into date column in pandas

I've got a pandas dataframe in which financial years are listed in a column, and the quarter is listed in another.

I'd like to combine them into a single column.

The format is:

Financial Year   Financial Quarter
2015/16          1
2015/16          1

I was planning to create a date column based on the Financial Year column, and then offset it by Financial Quarter.

My first step was:

df['date'] = pd.to_datetime(df['Financial Year'], format="%Y/%y")

But I got a bit stuck with the second step.

Is there a better way to combine string data from multiple columns in one pass?

Upvotes: 2

Views: 2669

Answers (1)

jezrael
jezrael

Reputation: 862741

IIUC you can first extract years from column Financial Year and then use BQuarterBegin and apply it with columns year1 and year2:

from pandas.tseries.offsets import *

print df
  Financial Year  Financial Quarter
0        2015/16                  1
1        2015/16                  1

df[['year1', 'year2']] = pd.DataFrame([ x.split('/') for x in df['Financial Year'].tolist()])
df['year1'] = pd.to_datetime(df['year1'], format="%Y") 
df['year2'] = pd.to_datetime(df['year2'], format="%y") 
print df
  Financial Year  Financial Quarter      year1      year2
0        2015/16                  1 2015-01-01 2016-01-01
1        2015/16                  1 2015-01-01 2016-01-01

df['date1'] = df.apply(lambda x:(x['year1'] + BQuarterBegin(x['Financial Quarter'])), axis=1)
df['date2'] = df.apply(lambda x:(x['year2'] + BQuarterBegin(x['Financial Quarter'])), axis=1)
print df
  Financial Year  Financial Quarter      year1      year2      date1  \
0        2015/16                  1 2015-01-01 2016-01-01 2015-03-02   
1        2015/16                  1 2015-01-01 2016-01-01 2015-03-02   

       date2  
0 2016-03-01  
1 2016-03-01

Upvotes: 1

Related Questions