Login Get started

Data Analysis with Python for Excel Users - Full Course

freeCodeCamp.org・2 minutes read

Frank offers a Python course for Excel users, covering data analysis, automation, and practical applications using three modules. The course includes detailed instructions on downloading Anaconda, using Jupyter Notebook, navigating features, running code, creating data frames in pandas, and visualizing data with line plots, bar plots, and pie charts.

Insights

Python offers Excel users the ability to work with data, create visualizations, and automate tasks, enhancing functionality beyond Excel's capabilities.
The course on Python for data analysis comprises three modules covering core concepts, data analysis using pandas, and practical applications like pivot tables and visualizations.
Anaconda, a tool for Python development, includes Jupyter Notebook, facilitating the creation, editing, and running of Python scripts with various customization options.
Jupyter Notebook provides a versatile interface for executing Python code, transitioning between command and edit modes, and utilizing keyboard shortcuts for efficient coding.
Python encompasses various data types like integers, floats, Booleans, and strings, with functions for type checking, string manipulation, and list operations.
Data structures like lists and dictionaries in Python offer flexible storage options for multiple data types, supporting operations like appending, inserting, sorting, and updating values.
Pandas in Python serves as a robust data analysis tool, akin to Excel but with capabilities for handling larger datasets, complex transformations, and reshaping data through methods like pivot and pivot_table.

Get key ideas from YouTube videos. It’s free

Recent questions

What is Python used for?
Data analysis, automation, and handling large datasets.

Related videos

Summary

00:00

Python Course for Excel Users with Data Analysis

Frank, a data scientist, offers a course to Excel users on using Python for data analysis and automation.
Python allows Excel users to perform tasks like working with data, creating charts, and pivot tables, along with automating tasks and handling large datasets.
The course is divided into three modules: Python core concepts, pandas for data analysis, and practical application through pivot tables and visualizations.
The course materials include code files and a free PDF Python cheat sheet for reference.
To start, users need to download Anaconda from anaconda.com, selecting the appropriate version for their operating system.
The installation process involves clicking through prompts, agreeing to terms, and allowing permissions.
Anaconda includes Jupyter Notebook, a popular tool for data science, which opens a new notebook with Python 3.
Jupyter Notebook's interface allows for creating, editing, and saving Python scripts, with options to insert cells, run code, and manage kernels.
Additional features in Jupyter Notebook include toggling headers, toolbars, and line numbers, as well as installing extensions for customization.
Users can navigate through different tabs in Jupyter Notebook, such as Files, Running, Clusters, and NB Extensions, to manage and personalize their workspace.

13:59

"Jupyter Notebook: Interface, Cell Types, Shortcuts"

Jupyter Notebook interface overview provided in the text.
Explanation of cell types and modes in Jupyter Notebook.
Opening a Jupyter Notebook file named example.py and b.
Command mode in Jupyter Notebook indicated by a blue color.
Tools in the toolbar can be applied in command mode.
Keyboard shortcuts available in command mode, such as pressing H to view shortcuts.
Transition from command mode to edit mode by pressing Enter, indicated by a green color.
Edit mode used for actions within the cell, like writing code or text.
Running a cell in edit mode by clicking the run button.
Different cell types in Jupyter Notebook: code, Markdown, and raw NBConvert.
Changing cell types using the dropdown menu or shortcuts like Y for code and M for Markdown.
Using shortcuts like F for Find and Replace in command mode.
Running cells using shortcuts like Ctrl Enter or Shift Enter.
Converting cell types using shortcuts like M for Markdown and Y for code.
Adjusting heading sizes in Markdown cells by adding hash signs.
Navigating through cells using keyboard arrows or mouse clicks.
Inserting new cells above with A and below with B.
Cutting cells with X, pasting below with V, and above with Shift V.
Deleting cells by pressing D twice and undoing changes with Z.
Saving changes in the Jupyter Notebook file using Ctrl S.
Accessing more keyboard shortcuts by pressing H or going to Help > Keyboard Shortcuts.

27:21

Printing Messages and Data Types in Python

To print a message using the print function in Jupyter Notebook, open parentheses and write the message inside.
Execute the code by pressing Ctrl + Enter or Command + Enter on Mac.
Another way to run the code is by clicking on the run button in Jupyter Notebook.
Jupyter Notebook allows printing the last object in a code cell without specifying the print function.
Common data types in Python include integers, floats, Booleans, and strings.
Integers are whole numbers without a fractional component, while floats contain decimal points.
Use the type function to check the data type of a value in Python.
Strings are series of characters enclosed in single or double quotes.
String methods in Python include upper, lower, title, count, and replace.
Variables in Python help store data values and are assigned using the equal sign.

42:46

Python String Concatenation, F-strings, Lists Basics

String concatenation involves combining two messages using the plus operator.
To add a space between two concatenated messages, use the plus operator with a space enclosed in single or double quotes.
The F string method allows for easy joining of strings with variables included within curly braces.
Lists in Python store multiple items and are mutable containers.
To create a list, enclose elements in square brackets separated by commas.
Indexing in lists starts at zero, with negative indexing starting from the end.
Slicing in lists involves accessing parts of the list using the start and stop indices.
The append method adds a new element at the end of a list.
The insert method allows adding an element at a specific position in the list.
Lists can contain elements of different types and can have duplicate elements.

58:39

Python Lists and Dictionaries: Essential Operations

In Python, the `append` method adds a new element to a list at the last position, while the `insert` method allows specifying the position for the new element.
To join two lists in Python, the `+` operator can be used to concatenate them, creating a new list with elements from both original lists.
Nested lists, where lists are placed inside another list, can be created in Python by enclosing the original lists within square brackets.
Elements can be removed from a list in Python using methods like `remove`, `pop`, or `del`, either by specifying the element or its index.
Sorting a list in Python can be done using the `sort` method, which arranges elements from smallest to largest by default, with the option to reverse the order.
Updating values in a list involves using indexing to locate the element to be updated and assigning a new value to it.
Creating copies of lists in Python can be achieved through slicing, where the entire list is copied, or by using the `copy` method explicitly.
Dictionaries in Python store data values using key-value pairs, allowing for the creation of unordered collections of items.
Keys and values in a dictionary are separated by a colon, with different data types like strings and integers being able to coexist within a dictionary.
Methods like `keys` and `values` can be used to extract the keys and values from a dictionary, providing access to the stored data.

01:14:17

Python Dictionary Manipulation and Conditional Statements

The process involves obtaining items, with the first item being a pair of key and value, followed by the second item.
Items are accessed using the "items" method instead of "dot values."
The dictionary initially contains key-value pairs for name and age, with the name being "Frank" and age being 26.
To add a new key-value pair like height, square brackets are used to set the value, such as 1.7 meters.
Updating a value in the dictionary involves using the "update" method, specifying the key and the new value.
Creating a copy of a dictionary is achieved by using the "copy" method, ensuring changes in the original dictionary do not affect the copied one.
Removing elements from a dictionary can be done using the "pop" method to remove a specific key-value pair, the "del" function to delete a key and its value, or the "clear" method to empty the dictionary.
The "if" statement in Python is a conditional statement that executes a block of code based on whether a condition is true or false.
Examples demonstrate how the "if" statement works, with messages printed based on the age being equal to or greater than 18, less than 18, or between 13 and 17.
The "for" loop in Python is used to iterate through an iterable object like a list, performing the same action for each element in the list.

01:30:35

Python Functions, Enumerate, Modules, Pandas Overview

Enumerate function in Python allows for looping through a list and returning both the loop number and the element itself.
The enumerate function returns two elements: the loop number and the element being iterated over.
Demonstrated example of using the enumerate function to print the iteration number and the corresponding element from a list.
Looping through elements in a dictionary involves using the items method to access key-value pairs.
A function in Python is defined using the keyword "def" followed by the function name and parameters.
Example of creating a basic function to sum two values passed as parameters and return the result.
Built-in functions in Python include len for calculating the length of an iterable object, max for finding the maximum value, min for the minimum value, type for determining the type of an object, and range for generating a sequence of numbers.
Modules in Python are files containing Python code, including classes, functions, and variables.
Demonstrated the use of the os module in Python to access functionalities like getting the current directory, listing elements in a folder, and creating a new folder.
Pandas in Python is a powerful tool for data analysis, comparable to Excel but with benefits like handling larger datasets and complex data transformations.

01:46:30

Python excels in data frame creation

Python allows handling complex computations easily
Excel is limited in automation, but macros or VBA can help
Python surpasses Excel with numerous free libraries
Python is cross-platform, ensuring code consistency
Pandas core concepts involve arrays, series, and data frames
Data frames in pandas are akin to Excel spreadsheets
Data frames have rows, columns, and data values
Excel terminology translates to pandas terminology
Ways to create data frames include arrays, dictionaries, and CSV files
Creating data frames involves importing pandas and NumPy, using arrays or dictionaries, and renaming columns and rows

02:02:46

Creating and Displaying Data Frames in Python

The first key and value in creating a dictionary are "states" and "population."
To create a dictionary, run the two keys and values.
A data frame can be easily created using the data frame method with the dictionary's name.
An error occurred due to incorrect capitalization when creating the data frame.
The data frame displays columns for "states" and "population."
A data frame can also be created from a CSV file using the read_CSV method.
The CSV file should be in the same directory as the Jupyter Notebook script.
The head method displays the first five rows of a data frame.
The tail method shows the last five rows of a data frame.
To display all rows, use the set_option method with the Max_rows argument set to the total number of rows.

02:18:07

Data frame indexing and manipulation essentials.

To get the index of a data frame, use the range function with three arguments, where the start is zero and the top is 1000, representing the range from 0 to 999.
Access the column attribute by writing the data frame name followed by the attribute name, such as "column" in plural, to view and potentially modify column names.
Obtain the data types of each column using the "D types" attribute, revealing object types for gender to test preparation course and integer types for math, reading, and writing scores.
Utilize the "head" method to display the first five rows of a data frame and the "info" method to obtain information about the data frame, including non-null rows and data types.
Use the "describe" method to generate basic statistics like count, mean, standard deviation, minimum and maximum values, and percentiles for numerical data in the data frame.
Determine the length of a data frame with the "len" function, find the maximum and minimum index values, and verify the data frame type using the "type" function.
Employ the "round" function to round numerical values in a data frame to a specified number of decimal points.
Select a column from a data frame by using square brackets with the data frame name and column name enclosed in quotes, or by directly referencing the column name after the data frame name with a dot.
Understand the limitations of using the dot notation for column selection, especially when column names contain spaces or special characters, and opt for square brackets to avoid errors.
To select multiple columns from a data frame, use double square brackets with the data frame name and the names of the desired columns enclosed in quotes, allowing for the selection of two or more columns simultaneously.

02:33:44

Creating Data Frames with Multiple Columns

To create a data frame with two columns, specify the order of columns within square brackets.
The first column should be gender, and the second should be math score.
Running the code will display the gender column followed by the math score column in a data frame with 999 rows.
Verify that the created data frame is indeed a data frame using the type function.
Using two pairs of square brackets always results in a data frame, while a single pair yields a series.
Selecting two or more columns using dot notation is not possible; square brackets are preferred for such selections.
To select multiple columns, use two pairs of square brackets and list the desired columns within them.
Adding a new column with a scalar value involves selecting the data frame, specifying the new column name, and assigning a single value to it.
Adding a new column with an array requires matching the array length to the data frame's rows and using NumPy to create the array.
To add random integer or float numbers to a new column, utilize NumPy's random methods for generating the desired values.

02:48:28

Analyzing Data Frame F_exams: Statistics & Insights

To calculate the total sum of a column, select the math score column from the data frame F_exams and use the sum method, resulting in a total sum of 66,000.
To determine the number of rows in the data frame, use the count method on the math score column, revealing 1000 rows.
Calculate the mean of the math score column by using the mean method, which averages the values by summing all rows and dividing by the total number of rows (1000).
Obtain the standard deviation of the math score column by using the STD method, resulting in a standard deviation of 15.
Determine the maximum and minimum values of the math score column using the max and min methods, showcasing a minimum value of 0 and a maximum value of 100.
Utilize the describe method on the data frame to obtain a summary table with statistical values like count, mean, standard deviation, minimum, and maximum values.
Calculate the sum of the math score, reading score, and writing score columns by selecting each column independently and using the plus operator to sum them, resulting in the total sum of the scores column.
Calculate the average score by summing the three columns and dividing by three, then assign this result to a new column named "average" in the data frame.
Use the value_counts method to count the elements in the gender column by category, revealing 518 females and 482 males.
Employ the value_counts method on the parent table level of education column to count elements by category, showcasing the distribution of education levels and their respective percentages.

03:04:06

"Sorting and Reshaping Data in Pandas"

To sort by multiple columns, add quotes and run the command.
The priorities for sorting are set by the columns, with math score being the first priority.
Changes made using the sort_values method do not update the original data frame unless the inplace argument is set to true.
Setting inplace to true updates the data frame with sorted values.
Another option to update values without inplace is overwriting the data frame.
Sorting can also be done with text data, using the lambda function to sort by race ethnicity.
The pivot method reshapes data based on column values without data aggregation.
The pivot method requires specifying index, columns, and values to reshape the data frame.
The pivot_table method creates a pivot table similar to Excel, supporting data aggregation.
To use the pivot method, import pandas, read a CSV file, and specify index, columns, and values to reshape the data frame.

03:19:34

GDP per capita evolution and supermarket spending

The goal is to observe the GDP per capita evolution over the years for listed countries.
Executing the code reveals a more readable data frame displaying GDP evolution.
Verification confirms correct indexing by year and country columns.
The values represent GDP per capita for each country in each year.
The pivot method in pandas facilitates this data organization.
A different dataset, "supermarket_sales.xlsx," is read using the "pd.read_excel" method.
The goal is to analyze spending by gender in the supermarket.
The pivot_table method allows for aggregate functions like summing values.
The resulting pivot table displays spending by gender and quantity of products bought.
Only numerical data columns are shown in the pivot table due to the summing function.

03:35:39

Population trends visualized through data frame reshaping.

The process involves reshaping the original data frame by selecting three arguments: index, columns, and values.
The data frame displays countries in columns and years from 1955 to 2020, showcasing population evolution.
To simplify visualizations, specific countries are selected by creating a new data frame named DF_pivot.
The selected countries include the United States, India, China, Indonesia, and Brazil, with population data from 1955 to 2020.
The content inside the data frame DF_pivot is overwritten to reflect the selected countries.
The data is now ready for visualizations using pandas, with the next step being to create line plots.
Line plots are generated by using the plot method with the kind argument set to line, showcasing population trends over the years for different countries.
Customizations like changing x and y labels, adding a title, and adjusting figure size can be made to enhance the line plot.
Bar plots are then created by selecting a specific year (2020) and customizing the plot with color, labels, and a title.
Grouped bar plots are produced by selecting multiple years (1980, 1990, 2000, 2010, 2020) to display population data for different years in a grouped manner.

03:51:27

Creating Pie Chart with Pandas: Step-by-Step

To create a pie chart using Pandas, copy the data frame DF_people_2020, ensuring the format has countries as the index and the column as 2020.
Modify the column name from an integer to a string using the rename method, changing 2020 to "2020" to align with best practices.
Generate the pie chart by using the plot method with kind="pie" and specifying the data column (2020) in the Y argument.
Export plots created with Pandas by importing matplotlib.pyplot as plt, using plt.savefig("my_test.png") to save the plot as a PNG file, and removing unnecessary words with plt.show() before exporting. Additionally, export pivot tables using the to_excel method, naming the file pivot_table.xlsx.

Try it yourself — It’s free.