My 10 computer skills learning during COVID-19

What skills I learned during COVID-19. 

I am restricting only to computer related skills. 

1. VBA. I had learned it already. Starting using it again and improved my VBA skills a bit.

2. Python 2.7. I learned it on Ubuntu with its built-in all purpose editor. 

3. Linux and writing shell scripts on Ubuntu. Also learned to upgrade Ubuntu version. 

4. Python3. I bought a new Windows laptop and learned the entire Data Science Stack. Key components were pandas, Matplotlib. 

5. Excel power query. Learned "M" language. 

6. Power BI. Learned DAX expression language. 

7. Power Shell and its scripts. 

8. MacOS. Learning the new MacOS. 

9. 2FA. Setting up two factor authentication, on all important web accounts, which is must these days. 

10. Google App scripts. I started it in 2015, but couldn't take it forward. I started it again. I wrote a script yesterday to create calendar entries from Google Sheets. 

The real challenge is what do I do with it? I am working on finding the answer. 

A quick way of CSR Reporting II Using Excel SUMPRODUCT

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

Continuing from my previous blog post. I have added a date column. 

Now your manager wants to know the spend on covid relief related activities for January to March of 2021.

You could easily answer the question using SUMPRODUCT function. The expression will look like =SUMPRODUCT(Table1[Amount]*--(Table1[Date]<DATE(2021,4,1))*--(Table1[Date]>DATE(2020,12,31))*--(ISNUMBER((SEARCH("covid",Table1[Purpose],1)))))

The answer is Rs 4,25,000.

I had converted the range in to Excel Table before using it.  

To check if your answer is correct, filter the Date and Purpose column as shown below. Our answer matches and it is correct.

10:59 03-09-2021

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