multi-index (column) report using pandas

<!-- Custom stylesheet, it must be in the same directory as the html file

In [1]:

import pandas as pd
import numpy as np


#1 input file
input_file = r"D:\Python Scripts\multi_index_3.xlsx"

df1=pd.read_excel(input_file)
df1['Focus_area']=df1['Focus_area'].astype('category')
table0=df1
table0
#table0 is shown below.

Out[1]:

	Focus_area	City	As_on	Spend
0	Education	Mumbai	2019-01-01	1000000
1	Healthcare	Pune	2019-03-31	20000
2	Environment	Chennai	2018-03-31	100000
3	Disaster Relief	Bengaluru	2017-12-01	220000
4	Vocational Skills	Varanasi	2018-09-30	540000
5	Education	Pune	2019-06-30	3000000
6	Environment	Pune	2019-03-31	666000

In [67]:

df1['As_on']=pd.to_datetime(df1.As_on)

In [68]:

df1.dtypes

Out[68]:

Focus_area          category
City                  object
As_on         datetime64[ns]
Spend                  int64
dtype: object

In [69]:

df1 = df1.set_index(['Focus_area','City'])

In [70]:

df1.dtypes

Out[70]:

As_on    datetime64[ns]
Spend             int64
dtype: object

In [4]:

df1.groupby(df1['As_on'])['Spend'].sum()

Out[4]:

As_on
2017-12-01     220000
2018-03-31     100000
2018-09-30     540000
2019-01-01    1000000
2019-03-31     686000
2019-06-30    3000000
Name: Spend, dtype: int64

In [5]:

df1['Quarter'] = pd.PeriodIndex(df1['As_on'], freq='Q-MAR').strftime('Q%q')#Adding quarter to the dataframe.

Q-DEC specifies quarterly periods whose last quarter ends on the last day in December.¶

Q-MAR specifies quarterly periods whose last quarter ends on the last day in March.¶

In [ ]:

df1

In [7]:

df1['Year']=df1.As_on.dt.year#Adding Year to the dataframe.
df1

Out[7]:

	Focus_area	City	As_on	Spend	Quarter	Year
0	Education	Mumbai	2019-01-01	1000000	Q4	2019
1	Healthcare	Pune	2019-03-31	20000	Q4	2019
2	Environment	Chennai	2018-03-31	100000	Q4	2018
3	Disaster Relief	Bengaluru	2017-12-01	220000	Q3	2017
4	Vocational Skills	Varanasi	2018-09-30	540000	Q2	2018
5	Education	Pune	2019-06-30	3000000	Q1	2019
6	Environment	Pune	2019-03-31	666000	Q4	2019

In [12]:

df2 = pd.pivot_table(df1,index=['City','Focus_area'],columns=['Year','Quarter'],values=['Spend'],fill_value=0)#works
#df2.query('City==["Pune","Mumbai"]')#works

In [13]:

df2

Out[13]:

		Spend
	Year	2017	2018	2019
	Quarter	Q3	Q2	Q4	Q1	Q4
City	Focus_area
Bengaluru	Disaster Relief	220000	0	0	0	0
Chennai	Environment	0	0	100000	0	0
Mumbai	Education	0	0	0	0	1000000
Pune	Education	0	0	0	3000000	0
Environment	0	0	0	0	666000
Healthcare	0	0	0	0	20000
Varanasi	Vocational Skills	0	540000	0	0	0

In [14]:

df2 = pd.pivot_table(df1,index=['Year','Quarter'],columns=['City','Focus_area'],values=['Spend'],fill_value=0)#works
df2

Out[14]:

		Spend
	City	Bengaluru	Chennai	Mumbai	Pune	Varanasi
	Focus_area	Disaster Relief	Environment	Education	Education	Environment	Healthcare	Vocational Skills
Year	Quarter
2017	Q3	220000	0	0	0	0	0	0
2018	Q2	0	0	0	0	0	0	540000
Q4	0	100000	0	0	0	0	0
2019	Q1	0	0	0	3000000	0	0	0
Q4	0	0	1000000	0	666000	20000	0

In [ ]:

df2.stack(level=0)#works
#level=0 is the outermost level i.e. spend
#level=1 is the next level i.e. city
#level=2 is the innermost level i.e. Focus_area

In [22]:

df2.unstack(level=1)
#level=0 is the outermost level i.e. Year
#level=1 is the next level i.e. quarter

Out[22]:

	Spend
City	Bengaluru	Chennai	Mumbai	...	Pune	Varanasi
Focus_area	Disaster Relief	Environment	Education	...	Environment	Healthcare	Vocational Skills
Quarter	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	...	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
Year
2017	NaN	NaN	220000.0	NaN	NaN	NaN	0.0	NaN	NaN	NaN	...	0.0	NaN	NaN	NaN	0.0	NaN	NaN	NaN	0.0	NaN
2018	NaN	0.0	NaN	0.0	NaN	0.0	NaN	100000.0	NaN	0.0	...	NaN	0.0	NaN	0.0	NaN	0.0	NaN	540000.0	NaN	0.0
2019	0.0	NaN	NaN	0.0	0.0	NaN	NaN	0.0	0.0	NaN	...	NaN	666000.0	0.0	NaN	NaN	20000.0	0.0	NaN	NaN	0.0

3 rows × 28 columns

In [ ]:

-->

CSR Multi-index report in python using pandas

I wanted to make a report with multi-index columns and rows using python and pandas. 

The starting point was data in Excel file. The data was brought in pandas data frame.

I wanted an output report that looked like the report shown below.

But there was one condition. The condition was the quarters of the year has to be as per Indian financial year that starts on 1st April and end on 31st March the following year. 

What helped me was the below syntax.

Q-DEC specifies quarterly periods whose last quarter ends on the last day in December. 

Q-MAR specifies quarterly periods whose last quarter ends on the last day in March.

So, I chose Q-MAR. 

The data shown is about spend on Social and Environmental efforts of a company in India. A similar report has to be published by the company, as applicable, as per Indian laws. 

The report format can also be extended to report on financial numbers. The data itself is imaginary.