Welcome to Engineering Python.
This is a Python programming course for engineers.
In this video, I'll continue to talk about how to use Pandas for data analysis.
Before we start, we need to import numpy as np, and import pandas as pd.
Next, we create a DataFrame that will be used throughout this video.
The index contains four dates from January 1, 2018 to January 4, 2018.
The name of this index column is DateTime.
The frequency is one day.
This DataFrame has three columns.
The column names are A, B, and C. The values are a NumPy array.
It's 4 rows and 3 columns.
Most of the elements are float numbers.
Two cells are empty so they are denoted by NumPy NaN, not a number.
The DataFrame is created by passing these arguments in the DataFrame function.
The created DataFrame is df.
It's shown here.
Now, let's calculate the mean of each column.
It's done by calling the mean method of the DataFrame with no parameter.
The mean of column A is 0.7.
The mean of column B is 1.
Note that because there are two empty elements, they will not be used to calculate the mean.
So, the mean of these two numbers 1 and 1 is just 1.
The mean of column C is 25.7.
We can also calculate the mean of each row by passing a number 1 to axis.
The mean of the first row is 50.05, the second row is 0.9, and so on.
Again, empty cells do not participate in the calculation.
I show the DataFrame df here again.
We can apply the NumPy cumulative sum function to this DataFrame.
It will calculate the cumulative sum along each column.
So, in column A, this is 0.1.
This is 0.1 plus 1.3, which equals 1.4.
This is 0.1 plus 1.3 plus 0.8, which is 2.2, and so on.
NaN does not participate in the calculation.
We can also apply the cumulative sum function to each row by passing a number 1 to axis.
This is the result.
These calculations are not done in place.
I show the DataFrame df here again.
Now, I'll try to split the DataFrame into pieces and put them back together.
Let's first split it based on rows.
I already talked about this index location function in the previous video.
This statement will get the zeroth row.
The next statement will get the next two rows.
The last statement will get the last row.
We put them back together using Pandas' concatenation function.
It takes a list.
The elements in the list are the zeroth row, the first and second rows, and the last row.
See the output.
It's the whole DataFrame again.
Now, let's split it based on columns.
This index location statement will get all the elements in the zeroth column.
The next statement will get all the remaining columns.
We still put them back together using Pandas' concatenation function.
It takes a list.
The elements in the list are the zeroth column, the first and second columns.
axis equals 1.
So, this time we put these columns side by side instead of on top of another.
See the output.
It's the whole DataFrame again.
Next, let's see some examples of DataFrame grouping.
We create a DataFrame called df3 using this dictionary 3.
This dictionary contains 4 keys, day, fruit type, how many pounds are sold, and the profit.
The values associated with these keys are 4 lists with strings or integer numbers.
The created DataFrame is shown here.
This DataFrame can be explained like this.
Each row can be considered as a single transaction.
On Monday, we sold 10 pounds of apples.
The profit was 20 dollars.
On Tuesday, we sold 15 pounds of apples, and the profit was 30 dollars.
The other three lines can be interpreted in the same way.
In this statement, we try to group the transactions by Day in the DataFrame and apply the sum
function to the resulting groups.
The result shows there are only two groups because all these transactions happened on
either Monday or Tuesday.
We have three transactions on Monday.
So, 10 plus 50 plus 5.
We have sold a total of 65 pounds of apples and bananas combined.
20 plus 25 plus 10.
Our profit is 55 dollars in total.
Similarly, for Tuesday, we sold 55 pounds of fruit and the total profit is 50 dollars.
This statement groups the transactions by Fruit instead.
There are only two types of fruit.
We sold 30 pounds of apples and 90 pounds of bananas on those days.
The profits are 60 dollars and 45 dollars, respectively.
I show this DataFrame df3 again here.
This statement groups the transactions first by Day and then by Fruit.
Because in the zeroth row this is Monday Apple, in the fourth row it is also Monday Apple,
they are exactly the same.
So, these two lines will be combined.
All the other lines will not be combined.
10 plus 5 is 15.
20 plus 10 is 30.
This is the result.
We can stack the DataFrame using the stack method and show the details of each transaction
like they were defined in the dictionary.
The stacked information is shown here.
This part is the zeroth transaction.
This is the first, second, and so on.
We can convert the stacked DataFrame to the unstacked form by passing a number 1 in this
unstack method.
This is the original DataFrame.
If we pass a number 0 in this unstack method, the result is the transpose of the original
DataFrame.
Rows become columns, and columns become rows.
Next, let's see how to apply math operations on DataFrames.
We define a DataFrame df1 using a dictionary.
The DataFrame has 3 rows and 2 columns.
The elements are all integer numbers.
Here in this statement, we calculate the opposite of df1 and give it to another DataFrame df2.
This is element-wise operation.
This is df2.
We change the column names of df2 from A and B to A and C. Then we add df2 to df1.
This is the result.
For column A, 0 plus 0 is 0.
1 plus -1 is 0.
2 plus -2 is 0.
However, because df1 doesn't have a column named C and df2 doesn't have a column named
B, the elements for these two columns are all NaN, not a number.
This is subtraction.
It's still element-wise calculation.
There is no problem for column A.
The values will also be NaN for columns B and C.
Same thing for the multiplication operation and the division operation.
One special thing about the division is this first element here.
This NaN is a result of 0 divided by 0.
The NumPy sine function of df1 is element-wise operation.
This is the result.
In this statement, we try to calculate the square root of df2.
The square root of 0 is still 0.
However, other elements in df2 are all negative numbers.
So, the square roots of these elements are all NaN, not a number.
Next, I'll talk about how to read and write CSV and Excel files using Pandas.
CSV files contain comma separated values.
They are stored in plain text.
So, we can open it using NotePad.
Let's use the DataFrame df we created at the beginning of this video as an example.
We can save this DataFrame directly to a csv file using the to_csv function and give it
a name, mydf.csv.
After it's done, you should be able to find this file in the same folder as this Jupyter
Notebook.
We can open it in NotePad or the Jupyter editor.
Next, we can read the data from this file to a new DataFrame df2.
The index column is DateTime.
An empty number will be denoted by NaN.
Show df2, it is exactly the same as the original DataFrame.
We can save the DataFrame directly to an Excel file using the to_excel function and give
it a name, mydf.xlsx.
We also need to specify the sheet name.
It's Sheet1.
After it's done, you should be able to find this file in the same folder as this Jupyter
Notebook.
We can open it in Microsoft Excel.
Next, we can read the data from Sheet 1 of this file to DataFrame df2.
The index column is DateTime.
An empty number will be denoted by NaN.
Show df2, it is exactly the same as the original DataFrame.
At the end of this video, I'll show how to do simple plotting using Pandas DataFrames.
Before we start, we need to turn on the Jupyter notebook renderer so we can see all the plotting
results in this browser.
We need to import pyplot as plt.
After that, we create a DatetimeIndex.
It goes from zero hours, zero minutes, and zero seconds of January 1, 2018 to the midnight
of the same day.
The frequency is 30 minutes.
This is the DatetimeIndex we created.
Its length is 48.
Now, we generate a DataFrame df using this index.
The DataFrame has 48 rows and 3 columns of random numbers drawn from the standard normal
distribution.
The column names are A, B, and C.
This is the DataFrame we created.
We can directly plot this DataFrame using df dot plot method.
We add a legend and put it in the best position with least overlapping.
Finally, we show this figure.
We can see three curves represented by three different colors, A is blue, B is orange,
and C is green.
Because they all come from the standard normal distribution, their values are not too far
away from 0.
Next, we calculate the cumulative sum of each column and give the result to df2.
This output is df2.
We plot df2 as well.
The cumulative values may show some increasing or decreasing trends.
They look kind of like stock indexes.
Okay, that was the second part of how to use Pandas for data analysis.
The course materials are available on YouTube and GitHub.
You can watch the course videos in sequence.
If you like this video, please subscribe and share.
I'm Yong Wang.
Thanks for watching.