CSR Reporting I Using Excel SUMPRODUCT

Example of SUMPRODUCT function in Excel used for an example from Social Sector. 

Let us assume, that as a funding company your donations for a month are as per the table given below. 

Amount in Rupees	Description
25,000	Covid Relief - PPE kits
10,000	Covid Relief - Grocery kit
40,000	Covid Relief - Grocery kit
20,000	General Donation for healthcare activities
44,000	Medical Assistance
3,50,000	Covid relief activities
10,000	General donation
6,000	Monthly aid
89,600	50% advance for supply of medicines
5,00,000	Covid relief activities
4,980	Supply of groceries to tribal children
5,00,000	Relief activities for Covid
49,450	Scholarship
22,166	Scholarship

Your manager wants to know the spend on covid relief related activities for that particular month.

Now let us find out how SUMPRODUCT function can do the conditional sum. The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.

The expression to use is =SUMPRODUCT(Table1[Amount]*--(ISNUMBER((SEARCH("covid",Table1[Purpose],1))))). Where the dataset has a name of Table1.

If the term covid is found, the search function returns a number indicating its position. ISNUMBER function then would return True. The double negative forces True and False to 1 and 0 for doing the multiplication. Then the addition. 

=SUMPRODUCT(Table1[Amount]*(ISNUMBER((SEARCH("COVID",Table1[Purpose],1))))) is similar to the above formula. 

Thus, to recap, wherever the word covid is found, the function returns “True”, and the corresponding amount is multiplied by 1. All such amounts are added. The search term “covid” is not case sensitive. You can use “COVID” as well. The answer is Rs 14,25,000. 

Similarly formula =SUMPRODUCT(Table1[Amount]*--(ISNUMBER((SEARCH("Scholarship",Table1[Purpose],1))))) can be used to calculate donations given as Scholarships. The answer is Rs 71,616.

In my next blog, I will add another dimension of time to calculate the conditional sum.

13:26 01-09-2021

***

Learning Python

I started by journey of learning Python from Eric Matthes. The name of the book is Python Crash Course. It give me the necessary start. I was stuck and asked him a query. He responded immediately.
Later I found Python Data Science Handbook very useful. It is written by Jake VanderPlas.
To learn pandas, I bought a book by Daniel Y. Chen. The name of the book is Pandas for Everyone.
Recently I found the articles by Kimberley Fessel to be very useful. I learned about her excellent articles through a padcast. If I am not mistaken I heard Daniel Y. Chen on the same podcast channel.
All of the above are very well-know. But still I hope this information helps.

Where are IITs located?

With new Indian Institues of Technology (IITs) set up; I was keen to know their locations. 

Here is plot that shows their locations. 

I created the dataset. Using pandas and geopands I plotted this figure. 

It was good learning. I could set their colors differently as I wanted. 

This was intended but the map shows an interesting pattern. Most of the IITs are in Northern India. 

plot showing population of Rajasthan India districtwise using geopandas

A geopandas chart showing population of Rajasthan India district wise

I had the shape file of state of Rajasthan in India. The next step was to create a  virtual environment for geo Pandas on my Anaconda distribution on windows laptop. 

I did that. 

Once I had the set up and data, I plotted the population of the state. Geo Pandas made it really easy to plot the geo data and associated properties. 

I am looking forward to working on more geo Pandas plots. 

World population map with GeoPandas

Today I had decided to plot my first plot using GeoPandas; and I am glad I could do it today.

Below is the plot of world-population. But the plot is not that important.

What is important is how I got to this point. After attaining some momentum with pandas almost six months ago; I could not do much with GeoPandas. The reason was when I downloaded GeoPandas; it also loaded it dependencies and disturbed by existing packages in Anaconda distribution on my Windows laptop. So I had to remove GeoPandas.

The solution, I understood, needed multiple separate environments. That is done by installing virtual environments. Here was where I lost the momentum.

The options to install virtual environments are many; and that needed me to understood what was relevant for me. Finally I figured that out today.

I opted to install the needed virtual environment using Anaconda GUI. I loaded the necessary packages. I was not clear what happens to the IDE. It also installed on its own.

I now hope to build on what I have learned today!

World population map with GeoPandas

Ubuntu

The benefits of Ubuntu Linux distribution

I bought a new laptop with Ubuntu distribution in September of 2015. I had decided to learn good useful software packages available under it.  

I learned, from beginning, Python 2.7 on it in 2nd half of 2017. Then it was not used for a while. The reason is I bought Windows laptop to learn Python3. And use Excel. I added Power Query and Power BI to it.

Back to Ubuntu distribution. 

But I was determined to use my Ubuntu laptop. So during COVID-19 I started using the functionality available at command prompt. 

Soon I could do copying of files, moving files through simple commands at the prompt. 

I had lots of files, photos on internal drive, external drive, on media card and USB pen drives. I moved all my files in to designated folders. The software has a GUI like windows. But moving files using commands at the prompt is much easier. Also you could set some conditions such copy only newer or updated files. 

I had read about it, but now i experienced it. 

As a next step I learned to write shell scripts to do files management through shell scripts. 

The next part was learning the text processing ability of Linux. Text processing becomes easy with grep, sed, and awk. Regular Expressions is also one has to learn to use these three commands. 

With awk one could work on data with columns and rows and make analysis and prepare reports. 

Here also you could write scripts to automate these tasks. I could write scripts in shell for grep, sed, and awk.

I used pdfgrep to read pdf files. I downloaded pdfgrep. 

I had to learn how to download,  update my packages from central servers using command prompt. Learning to read help files is part of it. 

The power and flexibility of these commands is immense. 

The next stage is to use it for the real world applications. In fact I learned only to work on real world applications. 

I am happy with my progress so far and hope to put it to some good use. 

State-wise electricity consumption - India

State-wise electricity consumption - India

I had prepared this report almost a month ago. I am posting it today. It was part of my learning Power BI reports. The data was taken from wiki (internet).

Maharashtra has the highest consumption of electricity followed by Namil Nadu and Gujarat. It is followed by Karnataka and Central India (UP, MP and Rajasthan).

If electricity consumption is proxy to the the progress of a state then the chart clearly tells you the story.