python to excel task conversion: how to do something in excel which you did in python?

Lot of us start our career as a data analyst and use a lot of excel before using programming language to write custom codes. As my career has grown, I didn't cross excel as my primary working tool ever yet. That has proven bad in some cases and has made me a python freak in its own way. 

Now, I had to present something using excel and perform all the calculations in excel. But I had all the tasks done using simple python dict, list and math functions. Now, I had to translate all these python things into small small excel calculations. Here comes the motivation for this post; to point out simple tasks from python into excel.

Caution: all though this will be a small collage of tasks at first, I will keep adding more tasks later on.

First task:

(1) I have two lists. One contain ratings of restaurants, while the other one contains their gross rating i.e. good, bad and average; encoded in 0,1,2.

In python, I used a dictionary with keys 0,1,2 and looped through the list of rating to include group the restaurant ratings under bad,average and good. And then I summed the rating for each of the group.

How will I do the same in excel?

There are several ways to do this. But I will accomplish it using the most simple function; SUMIFS.

SUMIFS has a general notation as below:

SUMIFS(range1, range2,condition_for_range2, range3, condition_for_range3...)

So in this notation the variable in first range gets summed over while range2 and range3 are matched. 

This is the same as pandas conditional like the following:

data_condition = data[(data[range2_variable in condition_for_range2]) & (data[range3_variable in condition_for_range3]) & ...]

then doing something like sum(data_condition[range1_var].tolist()) gives you the sum. 

So I used the sumifs to sum up the ratings for each of 0,1 and 2 gross rated restaurants.

Second task:

(2) find unique elements in a list.

For solving it in python; there are so many ways. Starting from taking a set(list_of_elements) to taking unique() from a dataframe column using pandas; finding the set of unique elements from a list is very easy task in python.

For solving it in excel, you can use the latest Unique function. Unique takes the following notation:

unique(range1) 

where unique provides the unique elements from the values in range1.

But sadly, if you are using an older version of excel , or you are using linux (libreoffice) then unique may not work. In such cases, unique in excel doesn't work and gives #NAME? error. For understanding why and how the #NAME error occurs; read this microsoft guide.

So in such a case, you need to use the data option available in your excel. What you will do is,

(a) first select the whole data range.

(b) Go to DATA--> More filtering --> standard filter

(c) Now if you click the standard filter option, you will see a box that opens up with standard filter options. 

Important thing to do here is to select -none- as the column name. And then open up the "options" trianglular toggle and select "no duplicates".

Finally apply this filter. 

This will select the unique entries in the range. 

Possible issues: probably you have done this, and it's ending up producing no rows at all. How is it happening? the answer is: probably you have not used -none- option for the column name. Although I don't know why it works with none; but I know that it doesn't without it. A brief search doesn't reveal anything; but if you know the reason please comment below the reference or the reason itself.

Third task:

(3) I used math.floor in python to round up some approximate digits to their nearest number. How to do the same in excel?

For this, in excel, there is a function called floor function. In floor, the tricky part is that you have to mention upto which decimal you want the precision. For this precision stuff; there is a second parameter which sets upto which number you want precision.

For example: for 3.321, floor(3.321,1) = 3; floor(3.321,0.1) = 3.3 and so on.

As I needed to round to nearest whole number, I used floor(digit,1) option and it worked smoothly.

For my current work I have used these three tasks, i.e. sumif based on a grouping condition, unique selecting and finally using floor function. I will update this for further excel to python logic transformation.