[Pandas] I tried to analyze sales data with Python [For beginners]

I watched a Python lecture video by Keith Galli, an overseas youtuber, and tried the work.

Let's solve real-life data science problems using Python's Pandas library! ] Solving real world data science tasks with Python Pandas! The difficulty level is not high and it is plain English so please have a look.

It's about an hour and a half video. It's a very polite explanation. At stackoverflow (overseas version programming Q & A site) on Google, It also shows the process of finding a solution.

It is a task to analyze using CSV Excel data. The data can be downloaded from his Github.

Click here for Github

I'm running on a jupyter notebook.

I am doing it while adding supplements to the code in Japanese.

If you watch the video and actually touch it, I think it's the best way to practice Pandas.

You can get a feel for the atmosphere just by reading this article.

Create a data frame from reading data

Load pandas, os library

import pandas as pd
import os

First, check what kind of data is included (get file name list)

files = [file for file in os.listdir("Sales_Data")]
for file in files:
    print(file)

Sales_April_2019.csv
Sales_August_2019.csv
Sales_December_2019.csv
Sales_February_2019.csv
Sales_January_2019.csv
Sales_July_2019.csv
Sales_June_2019.csv
Sales_March_2019.csv
Sales_May_2019.csv
Sales_November_2019.csv
Sales_October_2019.csv
Sales_September_2019.csv

It seems that the data from the file name to January-December 2019 is included, I want to combine the data for analysis

First, create an empty data frame to store all the data

all_months_data = pd.DataFrame()

In order to combine all the month data, we will put the csv file of each month into the empty data frame one after another.

for file in files:
    df = pd.read_csv("Sales_Data/" + file)
    all_months_data = pd.concat([all_months_data,df])

Well, the first 5 lines of data look like this

all_months_data.head()

Output the combined data (At this time, since the serial number is not required, add index = False to the argument)

all_months_data.to_csv("all_data.csv",index=False)

After confirming the output data, try reading the combined data again.

all_data = pd.read_csv("all_data.csv")
all_data.head()

Work 1: When was the month with the highest sales and how much did you earn?

Looking at the column, there is a column of Order Date = order date, but there is no data only for the month, so let's create a new one

The first two letters in the order date column are likely to be the month data

all_data['Month'] = all_data['Order Date'].str[0:2]

Since it was converted to a character string, let's return it to the numerical value again

all_data['Month'] = all_data['Month'].astype('int32')
all_data.head()

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

<ipython-input-77-ffc394ccb2ad> in <module>
----> 1 all_data['Month'] = all_data['Month'].astype('int32')
      2 all_data.head()


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors, **kwargs)
   5880             # else, only a single dtype is given
   5881             new_data = self._data.astype(
-> 5882                 dtype=dtype, copy=copy, errors=errors, **kwargs
   5883             )
   5884             return self._constructor(new_data).__finalize__(self)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in astype(self, dtype, **kwargs)
    579 
    580     def astype(self, dtype, **kwargs):
--> 581         return self.apply("astype", dtype=dtype, **kwargs)
    582 
    583     def convert(self, **kwargs):


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
    436                     kwargs[k] = obj.reindex(b_items, axis=axis, copy=align_copy)
    437 
--> 438             applied = getattr(b, f)(**kwargs)
    439             result_blocks = _extend_blocks(applied, result_blocks)
    440 


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in astype(self, dtype, copy, errors, values, **kwargs)
    557 
    558     def astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):
--> 559         return self._astype(dtype, copy=copy, errors=errors, values=values, **kwargs)
    560 
    561     def _astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in _astype(self, dtype, copy, errors, values, **kwargs)
    641                     # _astype_nansafe works fine with 1-d only
    642                     vals1d = values.ravel()
--> 643                     values = astype_nansafe(vals1d, dtype, copy=True, **kwargs)
    644 
    645                 # TODO(extension)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/dtypes/cast.py in astype_nansafe(arr, dtype, copy, skipna)
    705         # work around NumPy brokenness, #1987
    706         if np.issubdtype(dtype.type, np.integer):
--> 707             return lib.astype_intsafe(arr.ravel(), dtype).reshape(arr.shape)
    708 
    709         # if we have a datetime/timedelta array of objects


pandas/_libs/lib.pyx in pandas._libs.lib.astype_intsafe()


ValueError: cannot convert float NaN to integer

I got an error

When I read the error statement, it says that NaN cannot be quantified.

Combining the is_na and any functions will return True if there is at least one NaN.

axis = 1 is a function that returns processing line by line

Have all rows containing NaN returned

nan_df = all_data[all_data.isna().any(axis=1)]
nan_df

There were also 545 lines

Erase data containing NaN

all_data = all_data.dropna(how='all')
all_data.head()

Alright, let's go back to the previous month and try again

all_data['Month'] = all_data['Month'].astype('int32')
all_data.head()

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

<ipython-input-80-ffc394ccb2ad> in <module>
----> 1 all_data['Month'] = all_data['Month'].astype('int32')
      2 all_data.head()


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors, **kwargs)
   5880             # else, only a single dtype is given
   5881             new_data = self._data.astype(
-> 5882                 dtype=dtype, copy=copy, errors=errors, **kwargs
   5883             )
   5884             return self._constructor(new_data).__finalize__(self)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in astype(self, dtype, **kwargs)
    579 
    580     def astype(self, dtype, **kwargs):
--> 581         return self.apply("astype", dtype=dtype, **kwargs)
    582 
    583     def convert(self, **kwargs):


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
    436                     kwargs[k] = obj.reindex(b_items, axis=axis, copy=align_copy)
    437 
--> 438             applied = getattr(b, f)(**kwargs)
    439             result_blocks = _extend_blocks(applied, result_blocks)
    440 


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in astype(self, dtype, copy, errors, values, **kwargs)
    557 
    558     def astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):
--> 559         return self._astype(dtype, copy=copy, errors=errors, values=values, **kwargs)
    560 
    561     def _astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in _astype(self, dtype, copy, errors, values, **kwargs)
    641                     # _astype_nansafe works fine with 1-d only
    642                     vals1d = values.ravel()
--> 643                     values = astype_nansafe(vals1d, dtype, copy=True, **kwargs)
    644 
    645                 # TODO(extension)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/dtypes/cast.py in astype_nansafe(arr, dtype, copy, skipna)
    705         # work around NumPy brokenness, #1987
    706         if np.issubdtype(dtype.type, np.integer):
--> 707             return lib.astype_intsafe(arr.ravel(), dtype).reshape(arr.shape)
    708 
    709         # if we have a datetime/timedelta array of objects


pandas/_libs/lib.pyx in pandas._libs.lib.astype_intsafe()


ValueError: invalid literal for int() with base 10: 'Or'

Next, I'm getting a new error again

It seems that the word'Or'was included

Let's look at the line that contains'Or'

temp_df = all_data[all_data['Order Date'].str[0:2] == "Or"]
temp_df.head()

It seems that the identity of "Or" was "Order Date".

Let's refresh the data by extracting the data other than the data containing the word "Order Date"

all_data = all_data[all_data['Order Date'].str[0:2] != "Or"]
all_data.head()

Let's be honest for the third time and perform quantification

all_data['Month'] = all_data['Order Date'].str[0:2]
all_data['Month'] = all_data['Month'].astype('int32')
all_data.head()

I got it

Next, although there are Quantity Ordered and Price Each, there is no essential sales.

Since PQ (sales) = P (price) x Q (quantity), add a column that corresponds to PQ.

all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each'] 

---------------------------------------------------------------------------

TypeError                                 Traceback (most recent call last)

/opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in na_op(x, y)
    967         try:
--> 968             result = expressions.evaluate(op, str_rep, x, y, **eval_kwargs)
    969         except TypeError:


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in evaluate(op, op_str, a, b, use_numexpr, **eval_kwargs)
    220     if use_numexpr:
--> 221         return _evaluate(op, op_str, a, b, **eval_kwargs)
    222     return _evaluate_standard(op, op_str, a, b)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in _evaluate_numexpr(op, op_str, a, b, truediv, reversed, **eval_kwargs)
    126     if result is None:
--> 127         result = _evaluate_standard(op, op_str, a, b)
    128 


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in _evaluate_standard(op, op_str, a, b, **eval_kwargs)
     69     with np.errstate(all="ignore"):
---> 70         return op(a, b)
     71 


TypeError: can't multiply sequence by non-int of type 'str'


During handling of the above exception, another exception occurred:


TypeError                                 Traceback (most recent call last)

<ipython-input-84-7c1e2b69cbe2> in <module>
----> 1 all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each']


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in wrapper(left, right)
   1046 
   1047         with np.errstate(all="ignore"):
-> 1048             result = na_op(lvalues, rvalues)
   1049         return construct_result(
   1050             left, result, index=left.index, name=res_name, dtype=None


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in na_op(x, y)
    968             result = expressions.evaluate(op, str_rep, x, y, **eval_kwargs)
    969         except TypeError:
--> 970             result = masked_arith_op(x, y, op)
    971 
    972         return missing.dispatch_fill_zeros(op, x, y, result)


/opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in masked_arith_op(x, y, op)
    445         if mask.any():
    446             with np.errstate(all="ignore"):
--> 447                 result[mask] = op(xrav[mask], com.values_from_object(yrav[mask]))
    448 
    449     else:


TypeError: can't multiply sequence by non-int of type 'str'

Once again, it contained a string

I want to rewrite it into a form that can be calculated for the time being, so I will use the "to_numeric" function

all_data['Quantity Ordered'] = pd.to_numeric(all_data['Quantity Ordered'])
all_data['Price Each'] = pd.to_numeric(all_data['Price Each'])

I will try it again

all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each'] 

all_data.head()

It came out. It seems that the answer to the question will finally come out

For the time being, I will repost the question.

Work 1: When was the month with the highest sales and how much did you earn?

all_data.groupby('Month').sum()

I don't know Pat, so let's show it

Install the library for illustration

import matplotlib.pyplot as plt

Graph while deciding the range for the X axis

months = range(1,13)
results = all_data.groupby('Month').sum()
plt.bar(months,results['Sales'])
plt.xticks(months)
plt.xlabel('Month')
plt.ylabel('Sales')
plt.show()

That was work 1.

Work 2: Which city had the highest sales

Now that we have a line of sales, it seems easy work, but let's do it

Before that, let's check again what the data was like.

all_data.head()

It seems like extracting a part of the data of the Purchase Address part and totaling it.

Let's create a City column as we did in Sales.

I have to extract a part of Address, but when I look at the data of Adress, I want to extract only the second city name like "address, city name, zip code".

Let's split by "," in the split function, and use the apply function to apply it to all columns in the same column

all_data['City'] = all_data['Purchase Address'].apply(lambda x: x.split(',')[1])
all_data.head()

Only City was successfully divided!

By the way, there is one problem that must be considered here when totaling in City.

That is the case when a city has the same name but other countries have the same name. If this is not taken into consideration, they will be added up and unintended aggregation results will be obtained. (Example: Portland in both Oregon and Maine)

However, there is no need to create a new column for the country name. Since it's just aggregating, all you have to do is put the State (2 characters) in the cell of the City column. This time, we will use the form City (State). ex: Dallas (TX)

Earlier, I wrote it in one line with the lambda function, but since it's a big deal, let's create a function to get the city name and country name respectively. It is also kind to the reader.

def get_city(address):
    return address.split(',')[1]

def get_state(address):
    return address.split(',')[2].split(' ')[1]
#Purchase address data is a comma(,)Blank after(" ")Because there is, cut with split so that it does not become unnatural when united

all_data['City'] = all_data['Purchase Address'].apply(lambda x: f"{get_city(x)} ({get_state(x)})")
all_data.drop("State",axis=1)

It's OK, all you have to do is add up normally

all_data.groupby('City').sum()

San Francisco is number one. Also, you can see that Portland is also properly divided.

Arrange the code used in the previous month

results = all_data.groupby('City').sum()
cities = [city for city, df in all_data.groupby('City')]
#City for x-axis labels: Numbers and labels will be disjointed unless they are in the same order as when grooved above
plt.bar(cities,results['Sales'])
plt.xticks(cities,rotation="vertical")
#If the cities are displayed as they are, it will be bulky, so display them vertically.
plt.ylabel('Sales')
plt.xlabel('City name')
plt.show()

It's done!

Work 3: When and when should an ad be placed (displayed) to maximize the likelihood that a customer will buy the item?

The work became like data science at once (however, the conclusion is not so strict logic first)

Now, as usual, let's look back at the data

all_data.head()

Perhaps it would be nice to look at the relationship between the Order Data column and the Sales column.

Let's change the data type so that Order Date can be handled easily as date data.

all_data['Order Date'] = pd.to_datetime(all_data['Order Date'])
all_data.head()

The format of the data has changed

Let's make a line of Hour, Minute

When creating a column in Month or Sales, it was a character string, so I used Split etc., but thanks to changing the data type earlier

all_data['Hour'] = all_data['Order Date'].dt.hour
all_data['Minute'] = all_data['Order Date'].dt.minute
all_data.head()

Let's visualize once to grasp the data

hours = [hour for hour, df in all_data.groupby('Hour')]
plt.plot(hours,all_data.groupby(['Hour']).count())
#Number of Orders per hour with time on the X axis(Number of lines)Aggregate
plt.xticks(hours)
plt.grid()
#I want to see a clear tendency for each hour as much as possible, so I will add a grid
plt.xlabel('Hour')
plt.ylabel('Orders')
plt.show()

The peak is around AM: 11 and PM: 7. Therefore, it seems good to display advertisements during this time when the number of orders (customers) is the highest.

Work 4: Which items are most bought together as a set?

all_data.head()

It seems that you can understand if you group by the same Order ID

The values you are wearing can be easily sorted using the duplicated function.

df = all_data[all_data['Order ID'].duplicated(keep=False)]
df.head(20)

The data with the same ID is now lined up in succession

The above data set is the order with the ID (≒ nothing other than the order bought as a set)

After grouping by ID, create a column that stores the orders purchased by that ID. (Example ID: 1111 → Order: Apple, Banana, Orange)

df = all_data[all_data['Order ID'].duplicated(keep=False)]
df['Grouped'] = df.groupby('Order ID')['Product'].transform(lambda x: ','.join(x))
#transform is like an apply function. I will omit the explanation here.
df = df[['Order ID','Grouped']].drop_duplicates()
df.head(10)

/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

It feels good. The data is nicely organized.

In this Grouped column you have to look up "which, which, in what combination, how many times" etc.

Let's aggregate while using a convenient library. See stackoverflow below for a concrete image of the library.

• Looking at the input and output codes, it's OK to do something like this. https://stackoverflow.com/questions/56187042/python-itertools-make-combinations-with-sum/56187946#56187946

from itertools import combinations
from collections import Counter

count = Counter()
for row in df['Grouped']:
    row_list = row.split(',')
    count.update(Counter(combinations(row_list,2)))
    
count.most_common(10)

[(('iPhone', 'Lightning Charging Cable'), 1005),
 (('Google Phone', 'USB-C Charging Cable'), 987),
 (('iPhone', 'Wired Headphones'), 447),
 (('Google Phone', 'Wired Headphones'), 414),
 (('Vareebadd Phone', 'USB-C Charging Cable'), 361),
 (('iPhone', 'Apple Airpods Headphones'), 360),
 (('Google Phone', 'Bose SoundSport Headphones'), 220),
 (('USB-C Charging Cable', 'Wired Headphones'), 160),
 (('Vareebadd Phone', 'Wired Headphones'), 143),
 (('Lightning Charging Cable', 'Wired Headphones'), 92)]

The output is a little dirty, so I will make minor corrections

for key,value in count.most_common(10):
    print(key,value)

('iPhone', 'Lightning Charging Cable') 1005
('Google Phone', 'USB-C Charging Cable') 987
('iPhone', 'Wired Headphones') 447
('Google Phone', 'Wired Headphones') 414
('Vareebadd Phone', 'USB-C Charging Cable') 361
('iPhone', 'Apple Airpods Headphones') 360
('Google Phone', 'Bose SoundSport Headphones') 220
('USB-C Charging Cable', 'Wired Headphones') 160
('Vareebadd Phone', 'Wired Headphones') 143
('Lightning Charging Cable', 'Wired Headphones') 92

iPhone and charge cable are the most

Work 5 (Final Work): Which product is the best selling? Why do you think it sells best?

This is the last work. You have come to think for yourself (hypothesis part). Let's visualize the data.

Think on a quantity basis

product_group = all_data.groupby('Product')
quantity_ordered = product_group.sum()['Quantity Ordered']
products = [product for product,df in product_group]
plt.bar(products,quantity_ordered)
plt.xlabel('Product')
plt.ylabel('Quantity Ordered')
plt.xticks(products,rotation="vertical",size=8)
plt.show()

Is there a lot of batteries and cables?

I guess it's because the price is cheap. Check prices and sales.

prices = all_data.groupby('Product').mean()['Price Each']

fig, ax1 = plt.subplots()

ax2 = ax1.twinx()
#Graph generation that is common to the x-axis and has different y-axis values
ax1.bar(products,quantity_ordered)
ax2.plot(products,prices,'b-')

ax1.set_xlabel('Product Name')
ax1.set_ylabel('Quanity Ordered', color="g")
ax2.set_ylabel('Price', color="b")
ax1.set_xticklabels(products,rotation="vertical",size=8)

plt.show()

That is all.