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.

Mastering SQL for Data Science: Top SQL Interview Questions by Experience Level

Introduction: SQL (Structured Query Language) is a cornerstone of data manipulation and querying in data science. SQL technical rounds are designed to assess a candidate’s ability to work with databases, retrieve, and manipulate data efficiently. This guide provides a comprehensive list of SQL interview questions segmented by experience level—beginner, intermediate, and experienced. For each level, you'll find key questions designed to evaluate the candidate’s proficiency in SQL and their ability to solve data-related problems. The difficulty increases as the experience level rises, and the final section will guide you on how to prepare effectively for these rounds. Beginner (0-2 Years of Experience) At this stage, candidates are expected to know the basics of SQL, common commands, and elementary data manipulation. What is SQL? Explain its importance in data science. Hint: Think about querying, relational databases, and data manipulation. What is the difference between WHERE ...

Spacy errors and their solutions

Introduction: There are a bunch of errors in spacy, which never makes sense until you get to the depth of it. In this post, we will analyze the attribute error E046 and why it occurs. (1) AttributeError: [E046] Can't retrieve unregistered extension attribute 'tag_name'. Did you forget to call the set_extension method? Let's first understand what the error means on superficial level. There is a tag_name extension in your code. i.e. from a doc object, probably you are calling doc._.tag_name. But spacy suggests to you that probably you forgot to call the set_extension method. So what to do from here? The problem in hand is that your extension is not created where it should have been created. Now in general this means that your pipeline is incorrect at some level. So how should you solve it? Look into the pipeline of your spacy language object. Chances are that the pipeline component which creates the extension is not included in the pipeline. To check the pipe eleme...

fundamentals of LLM: A story from history of GPTs to the future

Introduction: So there has been a lot of developments in LLM and I have not gone through any of it. In the coming few parts, I will talk about LLM and its related eco-system that has developed and will try to reach to the cutting or more like bleeding edge. Lets go through the main concepts first. What is LLM? LLM[1] refers to large language models; that refer to mainly deep learning based big transformer models that can perform the natural language understanding and natural language generation tasks much better than the previous versions of the models generated in NLP history. LLM models are generally quite big, in terms of 10-100GBs and they can't fit in even in one machine's ram. So, most LLMs are inferenced using bigger GPU cluster systems and are quite computationally exhaustive. What was the first true LLM? The BERTs Transformers were invented on 2017 by vaswani et al in their revolutionary paper called "attention is all you need". After that we had the BER...

Machine learning and statistics with python

Search This Blog