Marketing Analytics|Combine Master/Slave Accounts by Python

Posted on Nov 4, 2019

Before I started working as analyst, I thought master/slave are only words for describing slavery in 18th Century, or for the sexual relationship in modern age. However, my thoughts are proved to be wrong. In the business world, it is a terminology to show the relationship about one main subsciber account to the related accounts under the same subscriber (company/individual.)

For engaging the clients better, company wants to categorize the dataset which contains these types of accounts: Master, Slave and Single Corporate Account. The original dataset we had from CRM system only shows the ID of Slave account, the ID of its master, and the other account type.

Our original dataset
Our Original Spreadsheet

Our goal is to convert the above sheet into one categorized dataset --- it can show master and its slave accounts in one row, and the number of seats under this account.

In this way, we can clearly see the ID and number of Master/Slave Account, and also know those rows with missing data determine the single corporate accounts left.

Output Goal

Normally, if use the functions in excel, it will take few hours to format it. But python can solve this in 2 minutes.

And here is how I write the codes to make it real.

#import module for dataframe and matrix
import pandas as pd
import numpy as np
#change the dataset here
path = 'MaterSlave.xlsx'
#open the sheets in the excel file
df = pd.read_excel(open(path, 'rb'), sheet_name='Master+Slave')
df_Master_seeds = pd.read_excel(open(path, 'rb'), sheet_name='Master')
#identify the size of dataset
#identify the columns
#identify the user status
df = df[(df.Status == 'Master') | (df.Status == 'Slave')]

df_Master = df[df.Status == 'Master'].copy()[['ID User','Seats']]
df_Slave = df[df.Status == 'Slave'].copy()
#convert the datatype of "ID User"
df_Slave['ID User'] = df_Slave['ID User'].astype('str')
df_Master = df_Master.rename(columns={'ID User': 'ID Master'})
df_t = df_Slave.groupby('ID Master', as_index=False).agg({'Seats': 'sum', 'ID User': lambda x: ' '.join(x)})
#count how many slaves in one master account
df_t['Slave_count'] = df_t['ID User'].apply(lambda x: x.count(' ')+1)
#change the datatype of "ID Master"
df_t['ID Master'] = df_t['ID Master'].astype('int64')
#see the new dataset
#see the master seats
df_total_seats = df_t.merge(df_Master, on = ['ID Master'])
#calculate the totle seats
df_total_seats['Seats'] = df_total_seats.Seats_x + df_total_seats.Seats_y
#drop unnecessary columns for seats of different status and only leave the necessary one be in the dataset
df_total_seats.drop(['Seats_x', 'Seats_y'], axis=1,inplace=True)

#Rename the column name
df_Master_seeds = df_Master_seeds.rename(columns={'Master ID': 'ID Master'})
#merge the sheet one and sheet two
df = df_Master_seeds.merge(df_total_seats, on=['ID Master'], how='outer')
#export the dataset
df.to_csv('Master_Slave_result.csv', index=True)

When you open the dataset 'Master_Slave_result.csv', you can see the result is as same as the second picture.

This join method can be used in most of the cases on marketing analytics. As long as you want to organize the information of user account, or identify the subscriber changes, this blog can help to make your life easier as an account manager/analyst.

About Author

Related Articles

Leave a Comment

No comments found.

View Posts by Categories

Our Recent Popular Posts

View Posts by Tags

#python #trainwithnycdsa 2019 airbnb Alex Baransky alumni Alumni Interview Alumni Reviews Alumni Spotlight alumni story Alumnus API Application artist aws beautiful soup Best Bootcamp Best Data Science 2019 Best Data Science Bootcamp Best Data Science Bootcamp 2020 Best Ranked Big Data Book Launch Book-Signing bootcamp Bootcamp Alumni Bootcamp Prep Bundles California Cancer Research capstone Career Career Day citibike clustering Coding Course Demo Course Report D3.js data Data Analyst data science Data Science Academy Data Science Bootcamp Data science jobs Data Science Reviews Data Scientist Data Scientist Jobs data visualization Deep Learning Demo Day Discount dplyr employer networking feature engineering Finance Financial Data Science Flask gbm Get Hired ggplot2 googleVis Hadoop higgs boson Hiring hiring partner events Hiring Partners Industry Experts Instructor Blog Instructor Interview Job Job Placement Jobs Jon Krohn JP Morgan Chase Kaggle Kickstarter lasso regression Lead Data Scienctist Lead Data Scientist leaflet linear regression Logistic Regression machine learning Maps matplotlib Medical Research Meet the team meetup Networking neural network Neural networks New Courses nlp NYC NYC Data Science nyc data science academy NYC Open Data NYCDSA NYCDSA Alumni Online Online Bootcamp Online Training Open Data painter pandas Part-time Portfolio Development prediction Prework Programming PwC python python machine learning python scrapy python web scraping python webscraping Python Workshop R R language R Programming R Shiny r studio R Visualization R Workshop R-bloggers random forest Ranking recommendation recommendation system regression Remote remote data science bootcamp Scrapy scrapy visualization seaborn Selenium sentiment analysis Shiny Shiny Dashboard Spark Special Special Summer Sports statistics streaming Student Interview Student Showcase SVM Switchup Tableau team TensorFlow Testimonial tf-idf Top Data Science Bootcamp twitter visualization web scraping Weekend Course What to expect word cloud word2vec XGBoost yelp