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

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.

20 Must-Know Math Puzzles for Data Science Interviews: Test Your Problem-Solving Skills

Introduction: When preparing for a data science interview, brushing up on your coding and statistical knowledge is crucial—but math puzzles also play a significant role. Many interviewers use puzzles to assess how candidates approach complex problems, test their logical reasoning, and gauge their problem-solving efficiency. These puzzles are often designed to test not only your knowledge of math but also your ability to think critically and creatively. Here, we've compiled 20 challenging yet exciting math puzzles to help you prepare for data science interviews. We’ll walk you through each puzzle, followed by an explanation of the solution. 1. The Missing Dollar Puzzle Puzzle: Three friends check into a hotel room that costs $30. They each contribute $10. Later, the hotel realizes there was an error and the room actually costs $25. The hotel gives $5 back to the bellboy to return to the friends, but the bellboy, being dishonest, pockets $2 and gives $1 back to each friend. No...

GAM model : PyGAM package details Analysis and possible issue resolving

Introduction: picture credit to peter laurinec. I have been studying about PyGAM package for last couple of days. Now, I am planning to thoroughly analyze the code of PyGAM package with necessary description of GAM model and sources whenever necessary. This is going to be a long post and very much technical in nature. Pre-requisites: For understanding the coding part of PyGAM package, first you have to learn what is a GAM model. GAM stands for generalized additive model, i.e. it is a type of statistical modeling where a target variable Y is roughly represented by additive combination of set of different functions. In formula it can be written as: g(E[Y]) = f 1 (x 1 ) + f 2 (x 2 ) + f 3 (x 3 ,x 4 )+...etc where g is called a link function and f are different types of functions. In technical terms, in GAM model, theoretically expectation of the link transformed target variable is assume...

Pyarabic: python package for Arabic language

Introduction: In languages which are non-english and non-european as well, NLP work has progressed slowly in the last few decades because of the lesser number of scholars working on them as well as a lack of global interest in them. But now the time has changed and people from all over the world are collaborating on these lesser explored libraries and they are building resources for working on these languages with the same ease with that of english. Pyarabic is a package created from such a similar effort which deals with the intricate details of the arabic language and helps processing all kinds of arabic texts. While trying to learn it, being from a non-arab background, I couldn't read lots of parts of the main readthedocs site and had to work my around it. So in this blog post, I will summarize my learnings in english language, so that you can learn it and use the package with much more ease than me. [Credit where credit is due: this article heavily uses the ac...

Machine learning and statistics with python

Search This Blog