If you're continuing using the same notebook as in part one, you won't need to import anything extra. If this is a new notebook, you'll need to import the numpy and pandas libraries as well as the Series and DF objects from the pandas library like so.
import numpy as np
import pandas as pd
import seaborn as sb
from numpy.random import randn
import matplotlib.pyplot as plt
from matplotlib import rcParams
from pandas import Series, DataFrame
Don't forget to add inline matplotlib and configure your base grid.
%matplotlib inline
# Figsize is represented in inches.
rcParams['figure.figsize']= 5,4
sb.set_style('whitegrid')
In the previous demonstration, we covered some of the basics of with dataframe objects, basic data munging, and some visualization. In part two, we'll start to try to start using the data and generating new and usefull values from what we have.
Profit Calculator¶
This takes two dataframes and concatinates them based on a common key. This is handy if you have sales data on a platform like Google Analytics while keeping the details of your profit margins offline. In this way you can merge them and do your own offline analytics while keeping the details of your specific margins private.
This would then make it easy to maintain a single list of your products and bump it against a sales report from any time period. Below we read in the test analytics data. Set the sku data type to string, and specify which columns to use.
Sales = pd.read_csv("./Test.csv", parse_dates=True, dtype = {"sku":str}, usecols = ["date", "price", "sku"])
Sales
Here we create our product database which maps a product sku with its cost. I added some spacing in the cost column to make it easier to see which product maps to which SKU, but that's not necessary.
# Cost of goods sold COGS
COGS = DataFrame({'sku':['203','445', '661'],
'cost':[ 46, 53, 89]})
COGS
Once we have our two dataframes, we merge them by calling the merge method and passing in our two dataframes. We merge using "left" or the AD DataFrame as the primary source and we look for matches "on" the SKU column. This produces a new DF that contains all of the data in one spot.
# Create a new frame by joining the original two, merging by matching sku numbers to the main DF
C = pd.merge(Sales, COGS, how = 'left', on='sku')
print(C)
If you want to change the order of the columns to keep certain data next to each other, you can simply set the order in a list and pass it in as a parameter to the DF.
Col_Order = ['date', 'sku','cost', 'price']
C1 = DataFrame(C, columns=Col_Order)
C1
So now that we have the cost and the price listed in one DF, how do we calculate the profit and start making use of this data? Simple, just call the DF using the column indexes and set up an equasion like you might if you were working it out on paper.
# Creates a profit series and calculates the difference between the
# price and the cost.
C['profit']=C['price']-C['cost']
# Sets the column order including new column.
cols = ['date', 'sku', 'cost', 'price', 'profit']
# Creates a new DF and passes in the source DF and desired column order.
Profit = DataFrame(C, columns=cols)
Profit
Now we can take the combined data and start looking a little deeper. Here I want to find what it costs me to do business every day, how much I charged, and how much profit I made. You can do that by grouping your data by a column, in this case the date, and using the sum() method.
sales_by_date = Profit.groupby(Profit['date'])
sales_by_date.sum()
Now you may be interested in visualizing that as a line graph. Now it's a littler easier to see your bottom line. Here you can see the relationship between the price of your product, the cost of goods, and the difference represented as profit on the bottom.
sales_by_date.sum().plot()
You can break it down even further if you're only interested in your profit you can simply use the DF group and sum the profit series and plot that.
sales_by_date['profit'].sum().plot()
Here we can group the our sales by item numbers and find the total cost of goods by item and associated profit.
sales_by_date = Profit.groupby(Profit['sku'])
sales_by_date.sum()
You could then display the visualization of your costs and profit by item type. Here we can see where our margins are tightest and where we can focus our marketing efforts. The return on item 445 is pretty close to that of 661; however, the cost per item is much lower. Perhaps it would make more sense to focuse on selling more 445. I could keep my overhead down, keep more in stock, and still do really well.
sales_by_date.sum().plot(kind='bar')
Vehicle Comparison¶
Here we take a closer look at our vehicle data again.
# Reading in the data
cars = pd.read_csv("/Users/Steglitz/jupyter/mtcars.csv")
# Setting the columns that you want to use.
# NOTE: You can chose the display order by reordering the columns the way you want.
cars.columns = ['car_names','mpg','cyl','disp', 'hp', 'drat', 'wt', 'qsec', 'vs', 'am', 'gear', 'carb']
cars.index = cars.car_names
# Returns the first five rows.
cars.head()
Here we plot the efficiency of vehicles. On the x-axis we have the list of our vehciles. On the y-axis we display where they fall in respect to their miles per gallon.
mpg = cars['mpg']
fig = plt.figure()
ax = fig.add_axes([.1,.1,1,1])
mpg.plot()
ax.set_xticks(range(32))
ax.set_xticklabels(cars.car_names, rotation=60, fontsize='medium')
ax.set_title('Miles per Gallon of Cars in mtcars')
ax.set_xlabel('car names')
ax.set_ylabel('miles/gal')
ax.legend(loc='best')
You can also grab quick details about the dataset like:
# Maximum value
mpg.max()
# Minimum value
mpg.min()
# Record count
mpg.count()
Or you could use the describe method to give a quick all-in-one summary. Describe generates descriptive statistics that summarize the central tendency, dispersion and shape of a dataset’s distribution, excluding NaN values.
mpg.describe()
Therer are many useful ways to visualize your data to see relationships between data. Here we have a histogram plot that shows you the general distribution of the data.
mpg.plot(kind='hist')
You can also use seaborn to plot a histogram with distribution curve. This can be helfpul when determining if your data meets prerequisites for certain analytics techniques.
sb.distplot(mpg)
Here we create a scatterplot from the two column series, "hp" and "mpg". The "s" attribute sets the dot size.
cars.plot(kind='scatter', x='hp', y='mpg', c=['lightblue'], s=50)
Here we have the same scatterplot with trendline and confidence interval region. Here the interval was just randomly selected for demonstration purposes, not calculated.
sb.regplot(x='hp', y='mpg', data=cars, scatter=True, ci=80)
Here you can create a grid matrix which evaluates the relationship between all the listed variables. Here we can see the relationship between automatic and manual cars with MPG, displacement, HP, and weight.
cars_df = pd.DataFrame((cars.ix[:,(1,3,4,6)].values), columns = ['mpg', 'disp', 'hp', 'wt'])
cars_target = cars.ix[:,9].values
target_names = [0,1]
cars_df['group'] = pd.Series(cars_target, dtype="category")
sb.pairplot(cars_df, hue='group', palette='hls')
Math and Statistics with Pandas¶
You can set your desired precision by using the set_printoptions method with the precision attribute.
np.set_printoptions(precision=2)
Creating Arrays¶
You can create arrays by passing lists to the array method.
a = np.array([1,2,3,4,5,6])
a
You can create a multidimensional array by passing in a list of lists.
b = np.array([[10,20,30], [40,50,60]])
b
Creating an array by assignment
np.random.seed(25)
c = 36*np.random.randn(6)
c
d = np.arange(1,35)
d
Arithmetic with arrays¶
Using the arrays we just created, you can do arithmetic operations on entire arrays.
You can do multiplication:
a * 10
Addition:
print('C: ', c);print('A: ', a)
c + a
Subtraction:
print('C: ', c);print('A: ', a)
c - a
Array multiplication:
print('C: ', c);print('A: ', a)
c * a
Division:
print('C: ', c);print('A: ', a)
c /a
Multiplying matrices and basic linear algebra¶
Using a . after a number typecasts it as a float as opposed to an integer. This helps avoid problems associated with integer math in python.
# Setting up the aa matrix.
aa = np.array([[2.,4.,6.],[1.,3.,5.],[10.,20.,30.]])
aa
# Setting up the bb matrix.
bb = np.array([[0.,1.,2.],[3.,4.,5.],[6.,7.,8.]])
bb
Matrix addition:
print('Matrix aa: \n', aa);print('Matrix bb: \n', bb)
aa+bb
Subtraction:
print('Matrix aa: \n', aa);print('Matrix bb: \n', bb)
aa-bb
Matrix multiplication:
print('Matrix aa: \n', aa);print('Matrix bb: \n', bb)
aa*bb
Matrix division:
print('Matrix aa: \n', aa);print('Matrix bb: \n', bb)
aa /bb
You can see that even with a divide by zero error, Pandas is able to complete the calculation and give you a warning.
This concludes part two of our Pandas series. The datasets can be found at PythonTrier Github. Give it a try and see what you can come up with.