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 ...

What is Bort?

Introduction: Bort, is the new and more optimized version of BERT; which came out this october from amazon science. I came to know about it today while parsing amazon science's news on facebook about bort. So Bort is the newest addition to the long list of great LM models with extra-ordinary achievements. Why is Bort important? Bort, is a model of 5.5% effective and 16% total size of the original BERT model; and is 20x faster than BERT, while being able to surpass the BERT model in 20 out of 23 tasks; to quote the abstract of the paper, ' it obtains performance improvements of between 0 . 3% and 31%, absolute, with respect to BERT-large, on multiple public natural language understanding (NLU) benchmarks. ' So what made this achievement possible? The main idea behind creation of Bort is to go beyond the shallow depth of weight pruning, connection deletion or merely factoring the NN into different matrix factorizations and thus distilling it. While methods like know...

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...

Machine learning and statistics with python

Search This Blog