Echo Dot (3rd Gen) - Smart speaker with Alexa - Charcoal

Use your voice to play a song, artist, or genre through Amazon Music, Apple Music, Spotify, Pandora, and others. With compatible Echo devices in different rooms, you can fill your whole home with music.

Buy Now

Wireless Rechargeable Battery Powered WiFi Camera.

Wireless Rechargeable Battery Powered WiFi Camera is home security camera system lets you listen in and talk back through the built in speaker and microphone that work directly through your iPhone or Android Mic.

Buy Now

One way to fill Excel formula with Python

0
62


One way to fill Excel formula with Python 1
Source

While I was working as a data scientist at Facilis, there was once I needed to fill a formula for more than 100 companies. But these formulas need to be filled based on the company’s own positions on the Excel sheet, and it was a very wide excel sheet, how should I complete this task?

First way : Brute force

https://medium.com/media/0ee4f078f57c15c34d024a1884b1384e/href

Well…this is one way to solve this problem, by doing it manually. But after trying to fill a few cells, I became very dizzy. So it’s time to do it in a smarter way.

Second way : Fill with Python

This problem actually reminds me of a LeetCode problem, called Excel Sheet Column Title. Check out this problem here.

Excel Sheet Column Title – LeetCode

So basically, what I need to do is to turn numbers into the Excel column title.

For example, 1 → A, 3 →C, 26 →Z, etc. So in my case, I know the location of the cells I need, they are at the 58th rows, and I need to fill the formula like this:

For company 1, write formula “=E58”;

For company 2, write formula “=I58”;

For company 3, write formula “=M58”;

Trending Chatbot Articles:

1. Building a Discord Bot for ChatOps, Pentesting or Server Automation

2. 8 Best IT Workflow Automation Practices to follow for Chatbots

3. How to prevent chatbot attacks?

4. Adding a conversational interface to your app with Dialogflow

From E →I (F G H), from I →M (J K L), there is exactly the same number of letters between these pairs. So if I know the index of this column, (for Column A, the index is 1), and I know the index of the next needed column (current_index = previous_index + interval), the interval between these two indexes is consistent, what I need to do next is to write an algorithm to turn numbers into Excel column titles.

Here is my Algorithm.

def convertToTitle(n: int):
if n <= 0:
return ""
        A = ord('A')
res = []
while n > 0:
if n > 26:
i = n % 26
if i == 0:
res.append('Z')
n -= 26
else:
res.append(chr(A + i - 1))
n //= 26
else:
res.append(chr(A + n - 1))
break
res.reverse()
return ''.join(res)

So if you are given number 28, you need to return “AB”, this is what this algorithm complete.

The next thing I’m gonna do is, I need to find a way to make sure after I output the excel from Jupyter Notebook, it can be recognized as a formula not as a String.

So in Jupyter, it looks like this: “=” is not read as an operator, but a string.

One way to fill Excel formula with Python 2

After I output the data frame to “xlsx”, and open it in Excel, I get this. “=” is recognized as an operator in Excel. Bingo!

One way to fill Excel formula with Python 3

This problem actually gave me a new thought about how to utilize the difference between different platforms, with smart handling, we can do more with limited time!

There should be other smarter ways to handle this kind of problem, but so far I have only come up with this method, welcome to any inspiration and comments.

Don’t forget to give us your 👏 !

One way to fill Excel formula with Python 4

https://medium.com/media/7078d8ad19192c4c53d3bf199468e4ab/href

One way to fill Excel formula with Python 5
One way to fill Excel formula with Python 6
One way to fill Excel formula with Python 7

One way to fill Excel formula with Python 8


One way to fill Excel formula with Python was originally published in Chatbots Life on Medium, where people are continuing the conversation by highlighting and responding to this story.



Read More

LEAVE A REPLY

Please enter your comment!
Please enter your name here