How to import data into excel

This import will not cost you anything except running some code!
The need to productively have an all in one solution to manage your data as your code has become more critical as volumes of data become larger. Do you, as a data analyst, therefore, need to send your data into an excel file? Previously we would have posted a video How do I remove unwanted characters, and here we build on that theme, linking in with Excel.

Two techniques used here to achieve this are  XLSX writer explained, and Openpyxl explained.

The elements we cover off are:

  • Load data from a data frame and populate into an excel file.
  •  Renaming of a sheet.
  • Creating a new sheet and giving it a name.
  • We look at properties, namely changing the colour of a tab to yellow.
  • You may need to put some text in a sheet cell, to act as a header or to show you have a total figure there.
  • And the final piece of functionality covered in this video is how to copy data from one sheet into another one.

There are several benefits to putting all the upfront work in Python:

  1. The benefits of cleansing the data or format will save time further down the road.
  2. After you receive the document, you can quickly review without fixing errors in the data.
  3. If you are distributing the output to several people, it quickly gets them what they want, without manual intervention after the logic has completed.

I have certainly benefited in this data cleansing and importing into excel exercise, as the two are combined now, makes it a more efficient process.

Please remember to subscribe to our channel if you like the work we are doing, thanks!

Data Analytics Ireland

How to check if a file is empty

Ever wondered how to about checking if a file is empty?

A problem you may come across in Data Analytics that when you are importing a file as outlined in this post Python – How to import data from files is how do we know if the files are empty or not before import?

In the world of data, there are several reasons to check :

  • You have an automated process relying on the import not been empty.
  •  A process that preceded you receiving the file did not work.
  • The amount of time and effort to investigate the problem causes undue work to fix.

The nuts and bolts of it all

Here we have a video that looks at different scenarios on how to bring in files. The following functionality appears in this video:

  • os.path.getsize – This looks to get the file size attached to the file. * Please see note below
  • pd.read_csv
  • pd.read_excel

The add on bits

*One note about os.path.getsize, which we found:

  • It only works in the logic provided if the size of the file is zero KB.
  •  CSV and XLSX files even though they where created empty, when saved had a file size greater than zero.
  •  TXT files, when created empty and saved, had a file size of zero.

 

Hope this video helps explain further how empty files can be checked in python before they are processed.

Thanks!

Data Analytics Ireland

How to remove characters from an imported CSV file

Removal of unwanted errors in your data, the easy way.
The process of importing data can take many formats, but have you been looking for a video on how you do this? Even better are you looking for a video that shows you how to import a CSV file and then data cleanse it, effectively removing any unwanted characters?

As a follow up to Python – how do I remove unwanted characters, that video focused on data cleansing the data created within the code, this video runs through several options to open a CSV file, find the unwanted characters, remove the unwanted characters from the dataset and then return the cleansed data.

How to get in amongst the problem data:

The approach here looked at three different scenarios:

(A) Using a variable that is equal to an open function, and then reading the variable to a data frame.

(B)Using a “with statement” and an open function together, and returning the output to a data frame.

(C) Using read_csv to quickly and efficiently read in the CSV file and then cleanse the dataset.

Some minor bumps in the road that need some thought

There where some challenges with this that you will see in the video:

  • Options A & B had to deploy additional code just to get the data frame the way we wanted.
  •  The additional lines of code made for more complexity if you were trying to manage it.

In the end, read_csv was probably the best way to approach this; it required less code and would have been easier to manage in the longer run.

 

As always thanks for watching, please subscribe to our YouTube channel, and follow us on social media!

Data Analytics Ireland

 

Python Tutorial: How to sort lists

Following on from our post on how to use Python lists have you ever wondered how to sort lists for your Python project?

Our latest video on lists will go through some of the techniques available so that you can get an idea of how to structure your data and sort.

Getting to understand how to implement

In this latest video we will look at:

  • sort() method
  • sorted() function
  • sorting a list through a function

 

Adding in those extra bits to help make the process smoother

Have you thought about sorting ascending/descending?

  • There is also a discussion on this topic as well, and while an index is available for the list, which you may feel does not merit sorting, there could be other logical reasons to implement sorting.
  • Leaving out the reverse = True/False in the sorted method can have an impact, though if you require it left out of the list you have created, automatic ascending will be the default.

On this channel, we have discussed a number of different ways to manage your data. In thinking about sorting a list, why would you want to do this?

Some common reasons are:

  • To visually see if there are duplicates, either on the screen or printed out.
  • If other objects are dependant on the list, say a combo box, then having duplicates visible can help to reduce the size of their contents.
  • Iteration – If you are looking to iterate over a list, it will be quicker if it is sorted.

If you want to learn about lists, using them, and how how they can be iterated over, why not visit Data Analytics Ireland YouTube channel, there are lots of videos there that will help explain the concepts discussed here further.

To get some more links on this topic click here python sort method, it is a blog posting from our website that has some useful links and explanations for you.

how to pass data between functions

In this python program, we are learning how to pass data between functions, a tool that will become very handy especially when you are trying to automate certain tasks that are repetitive.

Functions serve a number of benefits:

  • You can pass a number of arguments to them to be processed.
  • They are easily identified by using the def keyword in your code.
  • a return statement can give you the output of the function to show on the screen or pass to another function.

It compliments r-tutorial-how-to-pass-data-between-functions/  and can be seen that this handy bit of functionality is used widely across many different programming languages.

Below is a video that will help to give an understanding of how to pass data between functions when trying to learn python:

 

In many of the Data Analytics Ireland    YouTube channel videos, there is an emphasis on creating content that eliminates duplication of code within the code. We have also started incorporating classes as well and you can see here How to create a class in Python , a tutorial on how to create one.

Classes by their nature have methods, which are called on the objects that created them ( the class), and can alter their state, whereas a function will run and just return a value.

It is important to understand the distinction as while the two will most likely achieve the same outcome, it is the ability to change the class state that will differentiate the two.

Python Tutorial: Add a column to a data frame

You have learnt addition, now learn how to add a column to a data frame!
In our last post on what are Python data frames, we introduced the concept,  but are you now searching how to add a column to a data frame?To start, I was working away and wondering how I could accomplish this, as there were many posts about it. Searching through the jungles of website articles, some topics of interest that gave me ideas where as follows:(A)List comprehension.(B) Lambda.(C) Numpy.

Having tested the waters to see how you can approach:

After working through the above to:

  • Figure out how to use them.
  • Write some code to see how it all comes together.

The best thing to do was to put code into action!

Python for beginners or advanced programmers does not have to be hard!

Remember to subscribe to our channel if you like what we do!

Support is on the way:

TutorialsPoint is an excellent resource if looking to understand some other examples see this post here: TutorialsPoint: Add Column

To see a related post how to hide a column from a data frame look no further How to hide a column from a data frame

Data Analytics Ireland