import pandas as pd
df_1 = pd.read_csv('new_sample.csv')
df_1
facebook_records_spend = df_1.loc[df_1['CHANNEL'] == 'facebook']
facebook_records_spend=facebook_records_spend.sort_values(by=['SPEND'], ascending=True)
facebook_records_spend
instagram_records_spend = df_1.loc[df_1['CHANNEL'] == 'instagram']
instagram_records_spend=instagram_records_spend.sort_values(by=['SPEND'], ascending=True)
instagram_records_spend
twitter_records_spend = df_1.loc[df_1['CHANNEL'] == 'twitter']
twitter_records_spend=twitter_records_spend.sort_values(by=['SPEND'])
twitter_records_spend
df_2 = pd.read_csv('old_sample.csv')
df_2
#Takes in top spenders from new data set
df_new_spend = df_1.groupby(["COMPANY_NAME", "INDUSTRY_NAME"])['SPEND'].sum().reset_index().sort_values('SPEND', ascending=False)
final_analysis_new = df_new_spend
final_analysis_new
#takes in the top spenders from old data set
df_old_spend = df_2.groupby(["COMPANY_NAME",'INDUSTRY_NAME'])['SPEND'].sum().reset_index().sort_values('SPEND', ascending=False)
final_analysis_old = df_old_spend
final_analysis_old
# Finding out where the spending is increasing
new_creative_count_per_spend = df_1.groupby(['COMPANY_NAME','CREATIVE_TYPE', 'CHANNEL', 'INDUSTRY_NAME'])['SPEND'].count().reset_index().sort_values('SPEND', ascending=False)
new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Home Box Office']
old_creative_count_per_spend = df_2.groupby(['COMPANY_NAME','CREATIVE_TYPE', 'CHANNEL', 'INDUSTRY_NAME'])['SPEND'].count().reset_index().sort_values('SPEND', ascending=False)
old_creative_count_per_spend
Top 3 Telecom Companies in Spending
## Top 3 companies in the telecoms industry from old data points
top_3_telecoms_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Telecoms']
top_3_telecoms_old=top_3_telecoms_old[0:3]
top_3_telecoms_old
## Top 3 companies in the telecoms industry from new data points
top_3_telecoms_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Telecoms']
top_3_telecoms_new=top_3_telecoms_new[0:3]
old_vs_new_top_tele = pd.merge(top_3_telecoms_old.reset_index(),
top_3_telecoms_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_tele
Looking at creative type and channels differences
new_spending_homebox = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Home Box Office']
new_spending_homebox
old_spending_homebox = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Home Box Office']
old_spending_homebox
Merging
old_vs_new_HBO = pd.merge(old_spending_homebox.reset_index(),
new_spending_homebox,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_HBO
new_spending_CharterCommunications = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Charter Communications']
new_spending_CharterCommunications
old_spending_CharterCommunications = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Charter Communications']
old_spending_CharterCommunications
old_vs_new_CharterCom = pd.merge(old_spending_CharterCommunications.reset_index(),
new_spending_CharterCommunications,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_CharterCom
new_spending_Discoveryplus = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Discovery+']
old_spending_Discoveryplus = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Discovery+']
old_vs_new_Discoveryplus = pd.merge(old_spending_Discoveryplus.reset_index(),
new_spending_Discoveryplus,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Discoveryplus
new_spending_disney = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Disney Media & Entertainment']
old_spending_disney = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Disney Media & Entertainment']
old_vs_new_disney = pd.merge(old_spending_disney.reset_index(),
new_spending_disney,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_disney
Top 3 Financial Companies in Spending
## Top 3 companies in the financial industry from new data points
top_3_finanical_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Financial Services']
top_3_finanical_new=top_3_finanical_new[0:5]
## Top 3 companies in the financial industry from old data points
top_3_finanical_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Financial Services']
top_3_finanical_old=top_3_finanical_old[0:5]
old_vs_new_top_financ = pd.merge(top_3_finanical_old.reset_index(),
top_3_finanical_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_financ
Looking at creative type and channels differences
new_spending_chime = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Chime']
old_spending_chime = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Chime']
old_vs_new_chime = pd.merge(old_spending_chime.reset_index(),
new_spending_chime,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_chime
new_spending_CapitalOne = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Capital One']
old_spending_CapitalOne = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Capital One']
old_vs_new_CapitalOne = pd.merge(old_spending_CapitalOne.reset_index(),
new_spending_CapitalOne,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_CapitalOne
new_spending_AmericanExpress = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'American Express']
old_spending_AmericanExpress = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'American Express']
old_vs_new_AmericanExpress = pd.merge(old_spending_AmericanExpress.reset_index(),
new_spending_AmericanExpress,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_AmericanExpress
new_spending_Discover = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Discover']
old_spending_Discover = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Discover']
old_vs_new_Discover = pd.merge(old_spending_Discover.reset_index(),
new_spending_Discover,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Discover
Top 3 Retail Companies in Spending
## Top 3 companies in the retail industry from old data points
top_3_retail_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Retail']
top_3_retail_old=top_3_retail_old[0:3]
top_3_retail_old
## Top 3 companies in the retail industry from new data points
top_3_retail_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Retail']
top_3_retail_new=top_3_retail_new[0:3]
top_3_retail_new
old_vs_new_top_retail = pd.merge(
top_3_retail_old.reset_index(),
top_3_retail_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_retail
Looking at creative type and channels differences
new_spending_Walmart = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Walmart']
new_spending_Walmart
old_spending_Walmart = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Walmart']
old_spending_Walmart
old_vs_new_Walmart = pd.merge(old_spending_Walmart.reset_index(),
new_spending_Walmart,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Walmart
new_spending_HomeDepot = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Home Depot']
new_spending_HomeDepot
old_spending_HomeDepot = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Home Depot']
old_spending_HomeDepot
old_vs_new_HomeDepot = pd.merge(old_spending_HomeDepot.reset_index(),
new_spending_HomeDepot,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_HomeDepot
old_vs_new_HomeDepot = pd.merge(old_spending_HomeDepot.reset_index(),
new_spending_HomeDepot,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_HomeDepot
new_spending_Petco = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Petco']
new_spending_Petco
old_spending_Petco = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Petco']
old_spending_Petco
old_vs_new_Petco = pd.merge(old_spending_Petco.reset_index(),
new_spending_Petco,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Petco
Top 3 Auto Companies
## Top 3 companies in the auto industry from old data points
top_3_auto_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Auto']
top_3_auto_old=top_3_auto_old[0:6]
## Top 3 companies in the auto industry from new data
top_3_auto_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Auto']
top_3_auto_new=top_3_auto_new[0:6]
old_vs_new_top_auto = pd.merge(
top_3_auto_old.reset_index(),
top_3_auto_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_auto=old_vs_new_top_auto.sort_values('SPEND_y', ascending=False)
old_vs_new_top_auto
Looking at creative type and channels differences
new_spending_Toyota = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Toyota']
new_spending_Toyota
old_spending_Toyota = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Toyota']
old_spending_Toyota
old_vs_new_Toyota = pd.merge(old_spending_Toyota.reset_index(),
new_spending_Toyota,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Toyota
new_spending_Jeep = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Jeep']
new_spending_Jeep
old_spending_Jeep = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Jeep']
old_spending_Jeep
old_vs_new_Jeep = pd.merge(old_spending_Jeep.reset_index(),
new_spending_Jeep,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Jeep
new_spending_Nissan = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Nissan']
new_spending_Nissan
old_spending_Nissan = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Nissan']
old_spending_Nissan
old_vs_new_Nissan = pd.merge(old_spending_Nissan.reset_index(),
new_spending_Nissan,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Nissan
Top 3 Travel Companies Spending
## Top 3 companies in the travel industry from old data points
top_3_travel_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Travel']
top_3_travel_old[0:5]
## Top 3 companies in the travel industry from new data points
top_3_travel_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Travel']
top_3_travel_new=top_3_travel_new[0:5]
top_3_travel_new
old_vs_new_top_travel = pd.merge(
top_3_travel_old.reset_index(),
top_3_travel_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_travel = old_vs_new_top_travel.sort_values('SPEND_y', ascending=False)
old_vs_new_top_travel
Looking at creative type and channels differences
new_spending_SouthwestAirlines = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Southwest Airlines']
new_spending_SouthwestAirlines
old_spending_SouthwestAirlines = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Southwest Airlines']
old_spending_SouthwestAirlines
old_vs_new_SouthwestAirlines = pd.merge(old_spending_SouthwestAirlines.reset_index(),
new_spending_SouthwestAirlines,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_SouthwestAirlines
new_spending_DisneyThemeParks = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Disney Theme Parks & Resorts']
new_spending_DisneyThemeParks
old_spending_DisneyThemeParks = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Disney Theme Parks & Resorts']
old_spending_DisneyThemeParks
old_vs_new_DisneyThemeParks = pd.merge(old_spending_DisneyThemeParks.reset_index(),
new_spending_DisneyThemeParks,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_DisneyThemeParks
new_spending_Marriott = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Marriott']
new_spending_Marriott
old_spending_Marriott = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Marriott']
old_spending_Marriott
old_vs_new_Marriott = pd.merge(old_spending_Marriott.reset_index(),
new_spending_Marriott,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Marriott
new_spending_Lyft = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Lyft']
new_spending_Lyft
old_spending_Lyft = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Lyft']
old_spending_Lyft
old_vs_new_Lyft = pd.merge(old_spending_Lyft.reset_index(),
new_spending_Lyft,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Lyft
Top 3 Insurance Companies Spending
## Top 3 companies in the insurance industry from old data points
top_3_insurance_old = final_analysis_old.loc[final_analysis_old['INDUSTRY_NAME'] == 'Insurance']
top_3_insurance_old=top_3_insurance_old[0:3]
## Top 3 companies in the insurance industry from new data points
top_3_insurance_new = final_analysis_new.loc[final_analysis_new['INDUSTRY_NAME'] == 'Insurance']
top_3_insurance_new=top_3_insurance_new[0:3]
old_vs_new_top_insurance = pd.merge(
top_3_insurance_old.reset_index(),
top_3_insurance_new,
on=['COMPANY_NAME','INDUSTRY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_top_insurance
Looking at creative type and channels differences
new_spending_Progressive = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Progressive']
new_spending_Progressive
old_spending_Progressive = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Progressive']
old_spending_Progressive
old_vs_new_Progressive = pd.merge(old_spending_Progressive.reset_index(),
new_spending_Progressive,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Progressive
new_spending_StateFarm = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'State Farm']
new_spending_StateFarm
old_spending_StateFarm = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'State Farm']
old_spending_StateFarm
old_vs_new_StateFarm = pd.merge(old_spending_StateFarm.reset_index(),
new_spending_StateFarm,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_StateFarm
new_spending_Geico = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Geico']
new_spending_Geico
old_spending_Geico = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Geico']
old_spending_Geico
old_vs_new_Geico = pd.merge(old_spending_Geico.reset_index(),
new_spending_Geico,
on=['CREATIVE_TYPE', 'CHANNEL', 'COMPANY_NAME', 'INDUSTRY_NAME']
)
old_vs_new_Geico