import pandas as pd df_1 = pd.read_csv('new_sample.csv') df_1

OG_IDobject

PFB/2e1887a14bc74c3ef4a769070d324c5b0%

PFB/6fced07160d31e6632d159dcb6e9a0e10%

22510 others100%

CHANNELobject

facebook88.4%

instagram6.2%

twitter5.4%

PFB/2e1887a14bc74c3ef4a769070d324c5b

facebook

PFB/6fced07160d31e6632d159dcb6e9a0e1

facebook

PFB/856469627c3d448691183b1ba89b8db8

facebook

PTW/df406ee3465100b91cdb45568633f7be

twitter

PIG/95ff63d13e38abc6e070299b4e03d405

instagram

PFB/6261bd7ffa41d5637bead4bba718ba31

facebook

PFB/b8c5266f5a6a429594bf998dc83c66c7

facebook

PFB/bdb3294c23a2f8c4732f88fdc5f666c1

facebook

PFB/aba21251c16a06a25cd499908812b9d2

facebook

PFB/177d377baa5877d92d7b1f0969a8aaed

facebook

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

OG_IDobject

PFB/7a0c075e43fc428f02752d3a20bd3dce0%

PFB/e8e640ad4754767671cdde96d785198b0%

19909 others100%

CHANNELobject

facebook100%

377

PFB/7a0c075e43fc428f02752d3a20bd3dce

facebook

8100

PFB/e8e640ad4754767671cdde96d785198b

facebook

8193

PFB/9bbca36a44e5e83e56fc89b265ce66c4

facebook

4273

PFB/13bfe197cb2d03e5d14d3756d37aaed5

facebook

12734

PFB/981367f314245a92972cd878ae2860f4

facebook

6904

PFB/4e9f0710f2f14da6db0767c5154ada3f

facebook

729

PFB/b2fc62f4f0d19c4cd005948ad15ba2ee

facebook

20976

PFB/009539cc8711965f7cafbbe38cdba62a

facebook

15919

PFB/ca0de385a00619de7bcde3d2da460abc

facebook

6963

PFB/747db33c44c377bf50c09009bda69692

facebook

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

OG_IDobject

PIG/6a99265a5033250f2350e5bbbc5947bc0.1%

PIG/50cabde0a40e0d888a5b27adbb5248c00.1%

1389 others99.9%

CHANNELobject

instagram100%

10435

PIG/6a99265a5033250f2350e5bbbc5947bc

instagram

11425

PIG/50cabde0a40e0d888a5b27adbb5248c0

instagram

13060

PIG/4e93d39d44ea07863f0abf4518db8fc2

instagram

17885

PIG/2d4c0fbb34ad816c6fee6dc7a6e85ae1

instagram

11683

PIG/1b77fc2a32a08474f7d86215030ab58c

instagram

17603

PIG/6801a6f3935b26603d890b3e85acfe45

instagram

14675

PIG/d20b22b366082bd16076a0e0f9aee019

instagram

16412

PIG/891b4a1bb1e8102043bada310b78199d

instagram

17733

PIG/261a3b1ee139cfaa82a3c42387a77b69

instagram

5334

PIG/9ab6d15bcae2d83c7c9baee68994c583

instagram

twitter_records_spend = df_1.loc[df_1['CHANNEL'] == 'twitter'] twitter_records_spend=twitter_records_spend.sort_values(by=['SPEND']) twitter_records_spend

OG_IDobject

PTW/52520725e68e0fa821f9e5c51fba274a0.1%

PTW/7da0e76a835a28712240b222a69b71750.1%

1208 others99.8%

CHANNELobject

twitter100%

14387

PTW/52520725e68e0fa821f9e5c51fba274a

twitter

7096

PTW/7da0e76a835a28712240b222a69b7175

twitter

22472

PTW/bca0849cf121d655c911f2ccfe4be654

twitter

2539

PTW/2b525f60d225a113dab1fcace931f151

twitter

5637

PTW/6ff11233d22a20d70204b002016889b3

twitter

7651

PTW/c61eede48526c408c64d9f9a8a15a60b

twitter

9008

PTW/1d405b8ed2cf34268facfc4b58c6348f

twitter

12164

PTW/762b72aa842b219b8e7adcc395656c85

twitter

12472

PTW/310d2a5d2107bd173f0f111c1cc74481

twitter

15903

PTW/91f1d045ea7fe995791eb1a80b80c715

twitter

df_2 = pd.read_csv('old_sample.csv') df_2

OG_IDobject

PFB/39c083c184b0b4ab75c05eb7e438506e0%

PFB/825f9855541bc0b4016e874976824cf20%

17721 others100%

CHANNELobject

facebook100%

PFB/39c083c184b0b4ab75c05eb7e438506e

facebook

PFB/825f9855541bc0b4016e874976824cf2

facebook

PFB/8953a69b37ccc87e1ffb12d8a6411304

facebook

PFB/481f31584c44e96fb87d161223f79285

facebook

PFB/55a0b04ab047dbd5dec0076d2e987e36

facebook

PFB/01856d481c7bd86bade511ec77eb58ef

facebook

PFB/eeab0538d3a594fcc33015a78c2e615e

facebook

PFB/be920116460dcbb9a61c2351849db678

facebook

PFB/cbe46d24e5f86545a19eada605aa7528

facebook

PFB/0bc18fd7ca8846cddc7efb5d041cea1d

facebook

#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

COMPANY_NAMEobject

Home Box Office0.2%

Charter Communications0.2%

656 others99.7%

INDUSTRY_NAMEobject

Financial Services39.7%

Insurance19.6%

6 others40.7%

305

Home Box Office

Telecoms

136

Charter Communications

Telecoms

199

Disney Media & Entertainment

Telecoms

195

Discovery+

Telecoms

141

Chime

Financial Services

130

Capital One

Financial Services

193

Discover

Financial Services

621

Walmart

Retail

113

CBS

Telecoms

573

Toyota

Auto

#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

COMPANY_NAMEobject

Home Box Office0.2%

Discovery+0.2%

642 others99.7%

INDUSTRY_NAMEobject

Financial Services40.1%

Travel19.4%

6 others40.5%

302

Home Box Office

Telecoms

192

Discovery+

Telecoms

133

Charter Communications

Telecoms

196

Disney Media & Entertainment

Telecoms

138

Chime

Financial Services

126

Capital One

Financial Services

303

Home Depot

Retail

494

STARZ

Telecoms

610

Walmart

Retail

561

Toyota

Auto

# 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']

COMPANY_NAMEobject

Home Box Office100%

CREATIVE_TYPEobject

SocialLink27.3%

SocialVideo27.3%

4 others45.5%

854

Home Box Office

SocialLink

861

Home Box Office

SocialVideo

863

Home Box Office

SocialVideo

858

Home Box Office

SocialPhoto

862

Home Box Office

SocialVideo

855

Home Box Office

SocialLink

857

Home Box Office

SocialPhoto

859

Home Box Office

SocialStatus

853

Home Box Office

SocialCarousel

860

Home Box Office

SocialStory

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

COMPANY_NAMEobject

Toyota0.7%

Walmart0.7%

642 others98.6%

CREATIVE_TYPEobject

SocialLink36.6%

SocialVideo28.2%

5 others35.2%

1441

T-Mobile

SocialCarousel

Amazon

SocialCarousel

837

Home Depot

SocialCarousel

1123

Nissan

SocialLink

827

Home Box Office

SocialLink

297

CBS

SocialLink

834

Home Box Office

SocialVideo

1521

Toyota

SocialLink

1293

Remitly

SocialLink

1668

Wayfair

SocialCarousel

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

COMPANY_NAMEobject

INDUSTRY_NAMEobject

302

Home Box Office

Telecoms

192

Discovery+

Telecoms

133

Charter Communications

Telecoms

## 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

indexint64

COMPANY_NAMEobject

302

Home Box Office

133

Charter Communications

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

COMPANY_NAMEobject

Home Box Office100%

CREATIVE_TYPEobject

SocialLink27.3%

SocialVideo27.3%

4 others45.5%

854

Home Box Office

SocialLink

861

Home Box Office

SocialVideo

863

Home Box Office

SocialVideo

858

Home Box Office

SocialPhoto

862

Home Box Office

SocialVideo

855

Home Box Office

SocialLink

857

Home Box Office

SocialPhoto

859

Home Box Office

SocialStatus

853

Home Box Office

SocialCarousel

860

Home Box Office

SocialStory

old_spending_homebox = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Home Box Office'] old_spending_homebox

COMPANY_NAMEobject

Home Box Office100%

CREATIVE_TYPEobject

SocialLink27.3%

SocialVideo27.3%

4 others45.5%

827

Home Box Office

SocialLink

834

Home Box Office

SocialVideo

836

Home Box Office

SocialVideo

831

Home Box Office

SocialPhoto

835

Home Box Office

SocialVideo

828

Home Box Office

SocialLink

830

Home Box Office

SocialPhoto

832

Home Box Office

SocialStatus

826

Home Box Office

SocialCarousel

829

Home Box Office

SocialLink

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

indexint64

826 - 836

COMPANY_NAMEobject

Home Box Office100%

827

Home Box Office

834

Home Box Office

836

Home Box Office

831

Home Box Office

835

Home Box Office

828

Home Box Office

830

Home Box Office

832

Home Box Office

826

Home Box Office

829

Home Box Office

new_spending_CharterCommunications = new_creative_count_per_spend.loc[new_creative_count_per_spend['COMPANY_NAME'] == 'Charter Communications'] new_spending_CharterCommunications

COMPANY_NAMEobject

Charter Communications100%

CREATIVE_TYPEobject

SocialVideo33.3%

SocialLink22.2%

3 others44.4%

378

Charter Communications

SocialVideo

373

Charter Communications

SocialLink

380

Charter Communications

SocialVideo

374

Charter Communications

SocialLink

376

Charter Communications

SocialPhoto

379

Charter Communications

SocialVideo

377

Charter Communications

SocialUnknown

375

Charter Communications

SocialPhoto

372

Charter Communications

SocialCarousel

old_spending_CharterCommunications = old_creative_count_per_spend.loc[old_creative_count_per_spend['COMPANY_NAME'] == 'Charter Communications'] old_spending_CharterCommunications

COMPANY_NAMEobject

Charter Communications100%

CREATIVE_TYPEobject

SocialVideo22.2%

SocialLink22.2%

4 others55.6%

363

Charter Communications

SocialVideo

357

Charter Communications

SocialLink

358

Charter Communications

SocialLink

359

Charter Communications

SocialPhoto

364

Charter Communications

SocialVideo

356

Charter Communications

SocialCarousel

360

Charter Communications

SocialPhoto

361

Charter Communications

SocialStatus

362

Charter Communications

SocialUnknown

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

indexint64

COMPANY_NAMEobject

363

Charter Communications

357

Charter Communications

358

Charter Communications

359

Charter Communications

364

Charter Communications

356

Charter Communications

360

Charter Communications

362

Charter Communications

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

indexint64

COMPANY_NAMEobject

533

Discovery+

536

Discovery+

532

Discovery+

537

Discovery+

534

Discovery+

535

Discovery+

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

indexint64

549 - 557

COMPANY_NAMEobject

Disney Media & Entertainment100%

555

Disney Media & Entertainment

550

Disney Media & Entertainment

557

Disney Media & Entertainment

556

Disney Media & Entertainment

554

Disney Media & Entertainment

551

Disney Media & Entertainment

553

Disney Media & Entertainment

549

Disney Media & Entertainment

552

Disney Media & Entertainment

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

indexint64

COMPANY_NAMEobject

138

Chime

126

Capital One

American Express

276

Goldman Sachs

190

Discover

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

indexint64

COMPANY_NAMEobject

381

Chime

385

Chime

382

Chime

384

Chime

383

Chime

386

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

indexint64

336 - 344

COMPANY_NAMEobject

Capital One100%

338

Capital One

336

Capital One

342

Capital One

340

Capital One

339

Capital One

344

Capital One

343

Capital One

337

Capital One

341

Capital One

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

indexint64

COMPANY_NAMEobject

116

American Express

117

American Express

115

American Express

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

indexint64

COMPANY_NAMEobject

528

Discover

529

Discover

530

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

COMPANY_NAMEobject

INDUSTRY_NAMEobject

303

Home Depot

Retail

610

Walmart

Retail

445

Petco

Retail

## 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

COMPANY_NAMEobject

INDUSTRY_NAMEobject

621

Walmart

Retail

306

Home Depot

Retail

454

Petco

Retail

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

indexint64

COMPANY_NAMEobject

303

Home Depot

610

Walmart

445

Petco

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

COMPANY_NAMEobject

Walmart100%

CREATIVE_TYPEobject

SocialLink25%

SocialVideo25%

3 others50%

1717

Walmart

SocialCarousel

1719

Walmart

SocialLink

1726

Walmart

SocialVideo

1722

Walmart

SocialPhoto

1725

Walmart

SocialStatus

1723

Walmart

SocialPhoto

1727

Walmart