Guide To MitoSheets: Harnessing Power Of Spreadsheets In Python

The simple concept of MitoSheets is to equip you with spreadsheet functionality in python. It may sound a bit different, but it has a lot of stuff which comes in handy if you are used to Excel but still want to work on python.

Python won’t be the optimum solution for every task. There are many things that can be done quickly in Excel rather than python. One solution would be to alternatively switch between the two and save your work and then get back on the other tool, but this is time-consuming too. Having said that, folks at MitoSheets gave deep thought to it and took the onus to solve this problem. 

They have designed an amazing library which will let you work with spreadsheets without having to switch between two things. Just pass your dataset through one line of code and you’ll have all the functionality you could ever want. Being in its initial phase, as of now we can only perform pivoting, merging and some excel formulas. For the best experience, I and MitoSheets recommend using Jupyter Notebook.

Firstly, let’s install the library using the command “pip install mitosheet”.

Now let’s understand how to work with the library and make life easy. 

Download our Mobile App

Start by loading the required libraries and importing your datasets. In my case, the name of the CSV files is user_accounts.csv and website_visits.csv. Now we are all set to begin working on spreadsheets in python. 

The above line of code will inject your dataset into a spreadsheet and will show up as two different sheets. The output would look like the below image. 

In this article we’ll perform 3 activities:

1. Let’s look at some basic excel function. Click on add new column and then select any cell in that column. Here we’ll try to convert the User_ID to upper case using the =UPPER function. After adding the column, double click on the column name of the recently added column and change it to UpperCaseUserID. Now double click any cell in the column and write the following command. 

=UPPER(User_ID). It should look something like this.

Having done that, hit enter and you’ll have a column with all the User_ID entries in an upper-case format. 

The magic doesn’t end here if you are using Jupyter Notebook a new cell will be automatically created for you where you can see the python code for the executed operations. This not only saves time but also help you to show someone the equivalent operation in python code. The cell will look something like this.

2. Merging the two datasets

Like joins, here we will merge the two imported datasets. To merge we need to have one common key, so just as we did in the previous example, create one more mitosheet in the next cell, add a column to df1, rename it to Cleaned_User_ID and then click on any cell in the column and execute the command =LOWER(User_ID). This will fill the new column with user_id in lower-case. This step is essential to perform the join between the two tables. Now that we have the new column let’s join the two datasets.

From the MitoSheets toolbar click on the Venn diagram icon. This should open a dialogue box as below.

After this, change the merge key for df1 to Cleaned_User_ID to ensure seamless merging of the datasets. 

Now click on merge and you’ll have your new merged dataset with the python code generated by MitoSheets as below. 

After doing the operations, you can also save the analysis for future use by clicking on the save icon present on the MitoSheets toolbar. Saving the analysis doesn’t mean that it’s downloaded on your local machine; for that, you’ll have to click on the download button. 

3. Pivots

Now in the merged dataset, add a new column and name it Weekend_Visits. After that click on any cell in the column and then write =(Total_Visits – Weekday_Visits). Now let’s move to our pivot table. Click on the group like looking icon on the MitoSheets toolbar. This should open a window from which you should select the rows to be Account_Type, columns can be left as it is, values will be Weekend_Visits and select sum. It should look like the following. 

Performing this action will generate the below pivot table. 

Note: MitoSheets is still not available for public use but you can ask them for a trial, and they’ll be happy to help. 

Conclusion:

Currently, it’s in development stages but I believe soon it will become a very handy tool and will be able to provide us with more functionality.