Classic case of Pandas data analysis

Posted by Matty999555 on Thu, 13 Jan 2022 01:43:31 +0100

Author: Peter Editor: Peter

Hello, I'm Peter~

I have written many articles about Pandas. This paper carries out a simple comprehensive use, which is mainly divided into:

  • How to simulate data by yourself
  • Multiple data processing methods
  • Data statistics and visualization
  • User RFM model
  • User repurchase cycle


Build data

The data used in this case is self simulated by Xiaobian, which mainly includes two data: order data and fruit information data, and the two data will be combined

import pandas as pd
import numpy as np
import random
from datetime import *
import time

import as px
import plotly.graph_objects as go
import plotly as py

# Draw subgraph
from plotly.subplots import make_subplots

1. Time field

2. Fruits and users

3. Generate order data

order = pd.DataFrame({
    "time":time_range,  # Order time 
    "fruit":fruit_list,  # Fruit name
    "name":name_list,  # Customer name
    # Purchase volume
    "kilogram":np.random.choice(list(range(50,100)), size=len(time_range),replace=True) 


4. Generate fruit information data

infortmation = pd.DataFrame({
    "price":[3.8, 8.9, 12.8, 6.8, 15.8, 4.9, 5.8, 7],
    "region":["south China","North China","northwest","Central China","northwest","south China","North China","Central China"]


5. Data merging

The order information and fruit information are directly combined into a complete DataFrame, and this df is the data to be processed next

6. Generate new field: order amount

Here you can learn:

  • How to generate time related data
  • How to generate random data from a list (iteratable objects)
  • The DataFrame of Pandas is created by itself, including generating new fields
  • Pandas data merge

Analysis dimension 1: time

Monthly sales trend from 2019 to 2021

1. First extract the year and month:

df["year"] = df["time"].dt.year
df["month"] = df["time"].dt.month
# Extract year and month at the same time
df["year_month"] = df["time"].dt.strftime('%Y%m')


2. View field types:

3. Statistics and display by month and year:

# Statistics of sales volume by month and year
df1 = df.groupby(["year_month"])["kilogram"].sum().reset_index()

fig =,x="year_month",y="kilogram",color="kilogram")
fig.update_layout(xaxis_tickangle=45)   # Tilt angle

2019-2021 sales trend

df2 = df.groupby(["year_month"])["amount"].sum().reset_index()

df2["amount"] = df2["amount"].apply(lambda x:round(x,2))

fig = go.Figure()
fig.add_trace(go.Scatter(  #
    mode='lines+markers', # Mode mode selection
    name='lines')) # name

fig.update_layout(xaxis_tickangle=45)   # Tilt angle

Annual sales, sales and average sales

Analysis dimension 2: goods

Proportion of annual fruit sales

df4 = df.groupby(["year","fruit"]).agg({"kilogram":"sum","amount":"sum"}).reset_index()
df4["year"] = df4["year"].astype(str)
df4["amount"] = df4["amount"].apply(lambda x: round(x,2))

from plotly.subplots import make_subplots
import plotly.graph_objects as go

fig = make_subplots(
    subplot_titles=["2019 year","2020 year","2021 year"],
    specs=[[{"type": "domain"},   # Specify the type by type
           {"type": "domain"},
           {"type": "domain"}]]

years = df4["year"].unique().tolist()

for i, year in enumerate(years):
    name = df4[df4["year"] == year].fruit
    value = df4[df4["year"] == year].kilogram

    textposition='inside',   # 'inside','outside','auto','none'
    insidetextorientation='radial',   # horizontal,radial,tangential

# fig.update_layout(title_text = "making multi row and multi column subgraphs")

Comparison of annual sales amount of each fruit

years = df4["year"].unique().tolist()

for _, year in enumerate(years):
    df5 = df4[df4["year"]==year]
    fig = go.Figure(go.Treemap( 
        labels = df5["fruit"].tolist(),
        parents = df5["year"].tolist(),
        values = df5["amount"].tolist(),
        textinfo = "label+value+percent root"

Monthly sales volume change of commodities

fig =,x="year_month",y="amount",color="fruit")
fig.update_layout(xaxis_tickangle=45)   # Tilt angle

Changes in line chart display:

Analysis dimension 3: Region

Sales by Region

Average annual sales in different regions

df7 = df.groupby(["year","region"])["amount"].mean().reset_index()

Analysis dimension 4: user

User order quantity and amount comparison

df8 = df.groupby(["name"]).agg({"time":"count","amount":"sum"}).reset_index().rename(columns={"time":"order_number"})"Spectral_r")

User fruit preferences

Analyze according to the order quantity and order amount of each fruit by each user:

df9 = df.groupby(["name","fruit"]).agg({"time":"count","amount":"sum"}).reset_index().rename(columns={"time":"number"})

df10 = df9.sort_values(["name","number","amount"],ascending=[True,False,False])["number","amount"],color="#a97fcf"),
#            color="number",

User hierarchy - RFM model

RFM model is an important tool and means to measure customer value and profitability.

This model can reflect three indicators of a user's delivery transaction behavior, overall transaction frequency and total transaction amount, and describe the customer's value status through three indicators; At the same time, customers are divided into 8 types of customer value according to these three indicators:

  • Recency (R) is the number of days from the latest purchase date of the customer to the present. This indicator is related to the time point of analysis, so it changes. In theory, the more recent a customer purchases, the more likely it is to repurchase
  • Frequency (F) refers to the number of times customers purchase -- consumers who buy most often have higher loyalty. Increasing the number of customers' purchases means that they can occupy more time share.
  • Monetary value (M) is the total amount spent by the customer on the purchase.

The following three indicators are solved by Pandas's multiple methods. The first is F and M: the number of orders and total amount of each customer

How to solve R index?

1. First solve the difference between each order and the current time

2. The data in the first place is his latest purchase record: Taking xiaoming user as an example, the last time is December 15, and the difference from the current time is 25 days

3. According to the user's de duplication, the first data is retained, so as to obtain the R index of each user:

4. Three indicators are obtained by data consolidation:

When the amount of data is large enough and there are enough users, users can be divided into 8 types only by RFM model

User repurchase analysis

The re purchase cycle is the time interval between two purchases: Taking xiaoming users as an example, the first two re purchase cycles are 4 days and 22 days respectively

The following is the process of solving the repurchase cycle of each user:

1. The purchase time of each user is in ascending order

2. Move time one unit:

3. Combined difference:

The occurrence of null value is the first record of each user. There is no data before it, and the null value part is directly deleted later

Directly extract the numerical part of the number of days:

5. Re purchase cycle comparison,
       color_continuous_scale="spectral"   # purples

The narrower the rectangle in the figure above, the smaller the interval; The whole re purchase cycle of each user is determined by the length of the whole rectangle. View the sum of the overall re purchase cycle and the average re purchase cycle of each user:

A conclusion is drawn: the overall repurchase cycle of Michk and Mike users is relatively long, and they are loyal users in the long run; Moreover, the average repurchase cycle is relatively low, indicating that the repurchase is active in a short time.

It can also be observed from the following violin that the re purchase cycle distribution of Michk and Mike is the most concentrated.