basic_data_parsing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1e30107b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "03708277",
   "metadata": {},
   "outputs": [],
   "source": [
    "filename=\"/data/digbose92/MuSE_Loreal_study/MUSE_LOreal_Metadata_outputs.xlsx\"\n",
    "sheet_name=\"NIELSEN_FINAL_COMBINED_OUTPUT_U\"\n",
    "data=pd.read_excel(filename,sheet_name=sheet_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "29fd647b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting openpyxl\n",
      "  Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)\n",
      "\u001b[2K     \u001b[38;2;114;156;31m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m250.0/250.0 kB\u001b[0m \u001b[31m15.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hCollecting et-xmlfile\n",
      "  Using cached et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)\n",
      "Installing collected packages: et-xmlfile, openpyxl\n",
      "Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2\n"
     ]
    }
   ],
   "source": [
    "!pip install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e80e75fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Filename',\n",
       " 'Advertiser',\n",
       " 'Date',\n",
       " 'Version',\n",
       " 'Country',\n",
       " 'Duration',\n",
       " 'Description',\n",
       " 'Brand/Product',\n",
       " 'ProductAdvertiser',\n",
       " 'Title',\n",
       " 'Creative ID',\n",
       " 'Channel',\n",
       " 'Publication',\n",
       " 'Website',\n",
       " '!!MISSING_KEY!! CreativeLabel_Outlet_PaidSocial',\n",
       " 'Ad Format',\n",
       " 'Endline',\n",
       " 'female_screen_time',\n",
       " 'age_1_screen_time',\n",
       " 'age_2_screen_time',\n",
       " 'age_3_screen_time',\n",
       " 'age_4_screen_time',\n",
       " 'age_5_screen_time',\n",
       " 'age_6_screen_time',\n",
       " 'age_7_screen_time',\n",
       " 'age_8_screen_time',\n",
       " 'light_skintone_screen_time',\n",
       " 'medium_skintone_screen_time',\n",
       " 'dark_skintone_screen_time',\n",
       " 'frames_analyzed',\n",
       " 'faces',\n",
       " 'mst_scale_1',\n",
       " 'mst_scale_2',\n",
       " 'mst_scale_3',\n",
       " 'mst_scale_4',\n",
       " 'mst_scale_5',\n",
       " 'mst_scale_6',\n",
       " 'mst_scale_7',\n",
       " 'mst_scale_8',\n",
       " 'mst_scale_9',\n",
       " 'mst_scale_10',\n",
       " '[0, 15)',\n",
       " '[15, 25)',\n",
       " '[25, 35)',\n",
       " '[35, 45)',\n",
       " '[45, 55)',\n",
       " '[55, 65)',\n",
       " '[65, 75)',\n",
       " '[75, inf)',\n",
       " '(male, [0, 15), [-inf, 1.1))',\n",
       " '(male, [0, 15), [1.1, 2.1))',\n",
       " '(male, [0, 15), [2.1, 3.1))',\n",
       " '(male, [0, 15), [3.1, 4.1))',\n",
       " '(male, [0, 15), [4.1, 5.1))',\n",
       " '(male, [0, 15), [5.1, 6.1))',\n",
       " '(male, [0, 15), [6.1, 7.1))',\n",
       " '(male, [0, 15), [7.1, 8.1))',\n",
       " '(male, [0, 15), [8.1, 9.1))',\n",
       " '(male, [15, 25), [-inf, 1.1))',\n",
       " '(male, [15, 25), [1.1, 2.1))',\n",
       " '(male, [15, 25), [2.1, 3.1))',\n",
       " '(male, [15, 25), [3.1, 4.1))',\n",
       " '(male, [15, 25), [4.1, 5.1))',\n",
       " '(male, [15, 25), [5.1, 6.1))',\n",
       " '(male, [15, 25), [6.1, 7.1))',\n",
       " '(male, [15, 25), [7.1, 8.1))',\n",
       " '(male, [15, 25), [8.1, 9.1))',\n",
       " '(male, [25, 35), [-inf, 1.1))',\n",
       " '(male, [25, 35), [1.1, 2.1))',\n",
       " '(male, [25, 35), [2.1, 3.1))',\n",
       " '(male, [25, 35), [3.1, 4.1))',\n",
       " '(male, [25, 35), [4.1, 5.1))',\n",
       " '(male, [25, 35), [5.1, 6.1))',\n",
       " '(male, [25, 35), [6.1, 7.1))',\n",
       " '(male, [25, 35), [7.1, 8.1))',\n",
       " '(male, [25, 35), [8.1, 9.1))',\n",
       " '(male, [25, 35), [9.1, 10.1))',\n",
       " '(male, [35, 45), [-inf, 1.1))',\n",
       " '(male, [35, 45), [1.1, 2.1))',\n",
       " '(male, [35, 45), [2.1, 3.1))',\n",
       " '(male, [35, 45), [3.1, 4.1))',\n",
       " '(male, [35, 45), [4.1, 5.1))',\n",
       " '(male, [35, 45), [6.1, 7.1))',\n",
       " '(male, [35, 45), [7.1, 8.1))',\n",
       " '(male, [35, 45), [8.1, 9.1))',\n",
       " '(male, [45, 55), [-inf, 1.1))',\n",
       " '(male, [45, 55), [1.1, 2.1))',\n",
       " '(male, [45, 55), [2.1, 3.1))',\n",
       " '(male, [45, 55), [3.1, 4.1))',\n",
       " '(male, [45, 55), [4.1, 5.1))',\n",
       " '(male, [45, 55), [6.1, 7.1))',\n",
       " '(male, [45, 55), [7.1, 8.1))',\n",
       " '(male, [55, 65), [-inf, 1.1))',\n",
       " '(male, [55, 65), [1.1, 2.1))',\n",
       " '(male, [55, 65), [2.1, 3.1))',\n",
       " '(male, [55, 65), [3.1, 4.1))',\n",
       " '(male, [55, 65), [4.1, 5.1))',\n",
       " '(male, [55, 65), [7.1, 8.1))',\n",
       " '(male, [55, 65), [8.1, 9.1))',\n",
       " '(male, [65, 75), [1.1, 2.1))',\n",
       " '(female, [0, 15), [-inf, 1.1))',\n",
       " '(female, [0, 15), [1.1, 2.1))',\n",
       " '(female, [0, 15), [2.1, 3.1))',\n",
       " '(female, [0, 15), [3.1, 4.1))',\n",
       " '(female, [0, 15), [4.1, 5.1))',\n",
       " '(female, [0, 15), [5.1, 6.1))',\n",
       " '(female, [0, 15), [6.1, 7.1))',\n",
       " '(female, [0, 15), [7.1, 8.1))',\n",
       " '(female, [0, 15), [8.1, 9.1))',\n",
       " '(female, [15, 25), [-inf, 1.1))',\n",
       " '(female, [15, 25), [1.1, 2.1))',\n",
       " '(female, [15, 25), [2.1, 3.1))',\n",
       " '(female, [15, 25), [3.1, 4.1))',\n",
       " '(female, [15, 25), [4.1, 5.1))',\n",
       " '(female, [15, 25), [5.1, 6.1))',\n",
       " '(female, [15, 25), [6.1, 7.1))',\n",
       " '(female, [15, 25), [7.1, 8.1))',\n",
       " '(female, [15, 25), [8.1, 9.1))',\n",
       " '(female, [15, 25), [9.1, 10.1))',\n",
       " '(female, [25, 35), [-inf, 1.1))',\n",
       " '(female, [25, 35), [1.1, 2.1))',\n",
       " '(female, [25, 35), [2.1, 3.1))',\n",
       " '(female, [25, 35), [3.1, 4.1))',\n",
       " '(female, [25, 35), [4.1, 5.1))',\n",
       " '(female, [25, 35), [5.1, 6.1))',\n",
       " '(female, [25, 35), [6.1, 7.1))',\n",
       " '(female, [25, 35), [7.1, 8.1))',\n",
       " '(female, [25, 35), [8.1, 9.1))',\n",
       " '(female, [35, 45), [-inf, 1.1))',\n",
       " '(female, [35, 45), [1.1, 2.1))',\n",
       " '(female, [35, 45), [2.1, 3.1))',\n",
       " '(female, [35, 45), [3.1, 4.1))',\n",
       " '(female, [35, 45), [4.1, 5.1))',\n",
       " '(female, [35, 45), [5.1, 6.1))',\n",
       " '(female, [35, 45), [6.1, 7.1))',\n",
       " '(female, [35, 45), [7.1, 8.1))',\n",
       " '(female, [35, 45), [8.1, 9.1))',\n",
       " '(female, [45, 55), [-inf, 1.1))',\n",
       " '(female, [45, 55), [1.1, 2.1))',\n",
       " '(female, [45, 55), [2.1, 3.1))',\n",
       " '(female, [45, 55), [3.1, 4.1))',\n",
       " '(female, [45, 55), [4.1, 5.1))',\n",
       " '(female, [45, 55), [5.1, 6.1))',\n",
       " '(female, [45, 55), [6.1, 7.1))',\n",
       " '(female, [45, 55), [7.1, 8.1))',\n",
       " '(female, [55, 65), [-inf, 1.1))',\n",
       " '(female, [55, 65), [1.1, 2.1))',\n",
       " '(female, [55, 65), [2.1, 3.1))',\n",
       " '(female, [55, 65), [3.1, 4.1))',\n",
       " '(female, [55, 65), [4.1, 5.1))',\n",
       " '(female, [55, 65), [6.1, 7.1))',\n",
       " '(female, [55, 65), [7.1, 8.1))',\n",
       " '(female, [65, 75), [1.1, 2.1))',\n",
       " 'masculine_faces',\n",
       " 'feminine_faces']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b2bda98e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11577, 155)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "42cff118",
   "metadata": {},
   "outputs": [],
   "source": [
    "#sample the data based on files ending with mp4\n",
    "\n",
    "video_data=data[data['Filename'].str.endswith('.mp4')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8a3d3a28",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10472, 155)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "video_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5957f82a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({\"L\\\\'Oreal\": 4839, 'La Roche-Posay Laboratoire Pharmaceutique (LRPLP)': 1123, 'Laboratoires Vichy': 768, 'Lancome': 711, 'CeraVe': 688, 'YSL Beaute': 570, \"Kiehl\\\\'s\": 540, 'Giorgio Armani': 466, 'NYX Cosmetics': 350, 'Yves Saint Laurent': 147, 'Biotherm': 119, 'Prada': 107, 'Redken': 44})\n"
     ]
    }
   ],
   "source": [
    "#sample stats of Advertiser\n",
    "from collections import Counter\n",
    "advertiser=video_data['Advertiser']\n",
    "advertiser_count=Counter(advertiser)\n",
    "print(advertiser_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b5b17ef5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({'Germany': 4297, 'UK': 2566, 'France': 1159, 'Italy': 1112, 'USA': 915, 'Spain': 238, 'Brazil': 58, 'India': 49, 'Mexico': 47, 'Indonesia': 30, 'Canada': 1})\n"
     ]
    }
   ],
   "source": [
    "#sample stats of country\n",
    "country=list(video_data['Country'])\n",
    "country_count=Counter(country)\n",
    "print(country_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f9e8f779",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean 40.111535523300226\n",
      "Median 16.0\n",
      "Max 210\n",
      "Min 0\n",
      "75 percentile 25.0\n",
      "25 percentile 16.0\n",
      "90 percentile 115.0\n",
      "50 percentile 16.0\n"
     ]
    }
   ],
   "source": [
    "#duration mean median mode max\n",
    "from statistics import mean, median\n",
    "import re\n",
    "\n",
    "duration_list=(list(video_data['Duration']))\n",
    "duration = [int(re.findall(r'\\d+', s)[0]) for s in duration_list]\n",
    "\n",
    "print(\"Mean\",mean(duration))\n",
    "print(\"Median\",median(duration))\n",
    "print(\"Max\",max(duration))\n",
    "print(\"Min\",min(duration))\n",
    "print('75 percentile',np.percentile(duration, 75))\n",
    "print('25 percentile',np.percentile(duration, 25))\n",
    "print('90 percentile',np.percentile(duration, 90))\n",
    "print('50 percentile',np.percentile(duration, 50))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "68ee584c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('CeraVe, unisex skincare range', 254), ('Vichy, toiletries range', 238), ('Garnier, Wahre Schatze conditioner', 207), ('Kerastase, haircare range', 197), ('Garnier, Fructis haircare range', 188), ('CeraVe, female skincare range', 177), ('La Roche-Posay, female facecare range', 175), (\"L\\\\'Oreal Professionnel, Metal Detox haircare range\", 172), ('Garnier, micellar water range', 147), ('La Roche-Posay, toiletries range', 141), ('Garnier, Vitamin C Serum female antistain face moisturiser', 138), ('La Roche-Posay, Anthelios UVMune 400 sun protection', 138), ('Lancome, Renergie H.C.F. Triple Serum female antiage face moisturiser', 130), ('Yves Saint Laurent, Nu Lip lip and cheek cream', 124), ('Garnier, Olia hair colourant range', 117), (\"Kiehl\\\\'s, toiletries corporate\", 115), ('Maybelline, makeup range', 112), (\"Kiehl\\\\'s, toiletries range\", 112), (\"L\\\\'Oreal, Men Expert Hydra Energetic Anti-Fatigue 24H male face moisturiser\", 111), ('Garnier, toiletries range', 107), ('La Roche-Posay, unisex skincare range', 103), ('Maybelline, SuperStay Vinyl Ink lip gloss', 98), ('Lancome, Le 8 Hypnose mascara', 98), ('Garnier, Fructis Vitamine &amp; Kraft haircare range', 90), ('NYX, makeup range', 87), ('Kerastase, Blond Absolu haircare range', 87), ('Garnier, toiletries corporate', 85), ('La Roche-Posay, Hyalu B5 female antiage face moisturiser', 84), ('NYX, lipstick range', 84), (\"L\\\\'Oreal, toiletries range\", 81), ('Maybelline, Instant Perfector 4in1 face foundation', 79), ('Vichy, Neovadiol female antiage face moisturiser', 78), ('Giorgio Armani, toiletries range', 77), (\"L\\\\'Oreal, toiletries corporate\", 76), ('CeraVe, unisex facecare range', 76), ('Yves Saint Laurent (Toiletries), Lash Clash Extreme Volume mascara', 75), (\"Kiehl\\\\'s, female facecare range\", 73), ('Garnier, Olia hair colourant', 69), ('Maybelline, Lash Sensational Sky High mascara', 69), ('Giorgio Armani, Armani Code male fragrance', 69), ('Maybelline, Colossal Curl Bounce mascara', 65), ('Lancome, La Vie Est Belle female fragrance', 64), ('Vichy, Mineral 89 female facecare range', 64), ('Lancome, toiletries corporate', 63), (\"L\\\\'Oreal, Men Expert Hydra Energetic Eye Roll-On male face moisturiser\", 63), ('Maybelline, makeup corporate', 62), ('CeraVe, Blemish Control unisex face cleanser', 62), ('Vichy, LiftActiv Collagen Specialist female facecare range', 61), ('Vichy, Mineral 89 female antiage face moisturiser range', 60), ('Prada, Luna Rossa male fragrance', 60)]\n"
     ]
    }
   ],
   "source": [
    "#brand listing \n",
    "brand_list=list(video_data['Brand/Product'])\n",
    "print(Counter(brand_list).most_common(50))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7f6df193",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({nan: 9570, 'VOX': 52, 'Antena 3': 22, 'ANTENA NOVA TELEVISION': 21, 'Telecinco': 21, 'Goldmines Bollywood': 21, '13 Azteca': 19, 'TF1': 18, 'CStar': 18, 'RTL TELEVISION TELEVISION': 18, 'TF1 Series Film': 17, '6ter': 17, 'GIALLO TV': 16, 'SCTV': 16, 'CIELO': 14, 'LA SEXTA': 14, 'W9': 13, 'SAT1': 13, 'TV8': 13, 'REAL TIME': 13, 'Indosiar': 13, 'M6': 12, '2 Televisa': 11, 'HGTV HOME &amp; GARDEN': 11, 'ANTENA NEOX TELEVISION': 11, 'TFX': 10, 'FOOD NETWORK': 10, 'LA 7D': 10, 'DISCOVERY MAX TELEVISION': 10, 'E!': 9, 'TMC': 9, 'TWENTY SEVEN': 9, 'LA 5': 9, '5 Televisa': 8, 'Bandeirantes': 8, 'Rete 4': 8, 'CUATRO': 8, 'ESPN BRASIL': 7, 'ENERGY TELEVISION': 7, 'BBC America': 6, 'C News': 6, 'MEDIASET EXTRA': 6, 'TOP CRIME': 6, 'DIVINITY TELEVISION': 6, 'C4+1 - ULSTER': 6, 'Discovery Channel': 5, 'MTV': 5, 'Canal +': 5, 'RMC Decouverte': 5, 'Kabel1': 5, 'TNT SERIE': 5, 'PRO7': 5, 'NOVE': 5, 'ASTRA - MORE 4': 5, 'Zee Punjabi': 5, 'Balle Balle': 5, '7 Azteca': 4, 'SBT': 4, 'NRJ 12': 4, 'BFM TV': 4, 'RMC Story': 4, 'France 2': 4, 'TNT COMEDY': 4, 'RAI TV3': 4, 'ITALIA 1': 4, 'ASTRA - BOX HITS': 4, 'Colors': 4, 'Raj TV': 4, 'Ramdhenu': 4, 'GLOBO NEWS': 3, 'RECORD': 3, 'VH1': 3, 'SYFY': 3, 'C8': 3, 'HGTV': 3, 'WELT': 3, 'SKY CINEMA FUN': 3, 'SKY SERIEN + SHOWS HD': 3, 'RAI PREMIUM': 3, 20: 3, 'R101 TV': 3, 'MEGA TELEVISION': 3, 'FDF-T5 TELEVISION': 3, 'ASTRA - 5 USA': 3, 'ASTRA - KERRANG': 3, 'ASTRA - CBS DRAMA': 3, 'C4 - ULSTER': 3, 'ASTRA - CLUBLAND TV': 3, 'ASTRA - MTV MUSIC': 3, 'ASTRA - ITV3': 3, 'Rede TV': 2, 'Globo': 2, 'BET': 2, 'CBS': 2, 'LIFETIME MOVIE NETWORK': 2, 'LIFETIME': 2, 'KGMB': 2, 'ESPN': 2, 'BRAVO': 2, 'ABC (Network)': 2, 'TBS': 2, 'FYI': 2, 'LCI': 2, \"L\\\\'equipe\": 2, 'Cherie 25': 2, 'SKY ONE': 2, 'SKY COMEDY': 2, 'DELUXE MUSIC': 2, 'SKY NATURE': 2, 'DMAX': 2, 'RAI 4': 2, 'RAI TV1': 2, 'RAI MOVIE': 2, 'MOTOR TREND': 2, 'ASTRA - SYFY': 2, 'ASTRA - HGTV': 2, 'ASTRA - TRUE MOVIES +1': 2, 'ASTRA - MOVIES 24': 2, 'ASTRA - HGTV +1': 2, 'C5 +1 - NORTH WEST': 2, 'Star Plus': 2, 'RECORD NEWS': 1, 'HISTORY': 1, 'KABC': 1, 'WCPX': 1, 'KXLF': 1, 'TEL': 1, 'KING': 1, 'WGHP': 1, 'Freeform': 1, 'KOCB': 1, 'WSBK': 1, 'KDNL': 1, 'THE WEATHER CHANNEL': 1, 'CFTM': 1, 'Tennis Channel': 1, 'KNOE': 1, 'KHRR': 1, 'SundanceTV': 1, 'WOIO': 1, 'KDFW': 1, 'KOCO': 1, 'HGTV (National Cable': 1, 'WVEC': 1, 'Kelley Blue Book': 1, 'WSYX': 1, 'OPRAH WINFREY NETWORK': 1, 'HLN (NATIONAL CABLE)': 1, 'KMSB': 1, 'KNTV': 1, 'OXYGEN': 1, 'WPSG': 1, 'WCCO': 1, 'WATL': 1, 'KTXL': 1, 'Discovery Investigation': 1, 'France 3': 1, 'France 5': 1, 'SKY ATLANTIC': 1, 'SKY CINEMA FAMILY': 1, 'RTL PLUS': 1, 'TNT FILM': 1, 'PROSIEBEN MAXX': 1, 'SKY CINEMA PREMIEREN': 1, 'SKY CINEMA BEST OF': 1, 'SIXX TELEVISION': 1, 'CINE34': 1, 'TGCOM': 1, 'RADIO 105 TV': 1, 'RAI TV2 ALTRE IS': 1, '-': 1, 'RAI TV2': 1, 'ASTRA - MTV HITS': 1, 'C4+1 - SCOTLAND': 1, 'ASTRA - ITV2': 1, 'ASTRA - INVESTIGATION DISCOVERY': 1, 'ASTRA - COLORS RISHTEY': 1, 'ASTRA - QUEST +1': 1, 'ASTRA - DMAX+1': 1, 'ASTRA - SKY COMEDY': 1, 'ASTRA - 4SEVEN': 1, 'ASTRA - GOLD': 1, 'ITV - Ulster': 1, 'ASTRA - 5SELECT': 1, 'ASTRA - FILM4+1': 1, 'ASTRA - SKY SPORTS MIX': 1, 'ASTRA - SKY ATLANTIC': 1, 'C4 - MIDLANDS': 1, 'ASTRA - SKY REPLAY': 1, 'ITV+1 - ULSTER': 1, 'C4+1 - WEST': 1, 'ASTRA - SKY CINEMA ANIMATION': 1, 'ASTRA - COLORS GUJARATI': 1, 'ASTRA - MOVIES 4 MEN +1': 1, 'ASTRA - PICK': 1, 'ITV - Anglia': 1, 'ASTRA - PARAMOUNT NETWORK': 1, 'ASTRA - LEGEND': 1, 'ASTRA - DISCOVERY TURBO +1': 1, 'ASTRA - ITV 4 +1': 1, 'ITV - Border': 1, 'ASTRA - B4U MOVIES': 1, 'ASTRA - SONY MAX': 1, 'ASTRA - SKY MAX': 1, 'ASTRA - 5 USA +1': 1, 'ASTRA - DRAMA': 1, 'ASTRA - W +1': 1, 'ASTRA - NOW 90S': 1, 'ASTRA - SONY MOVIE CHANNEL +1': 1, 'ASTRA - DISCOVERY HISTORY': 1, 'ASTRA - THE BOX': 1, 'ASTRA - SONY CHANNEL': 1, 'ASTRA - COLORS TV': 1, 'ASTRA - ANIMAL PLANET': 1, 'TPI': 1, 'Sony Pal': 1, 'Colors Rishtey': 1, 'PTC Punjabi': 1, 'Zee TV HD': 1})\n"
     ]
    }
   ],
   "source": [
    "#channel listing\n",
    "\n",
    "channel_list=list(video_data['Channel'])\n",
    "channel_counter=Counter(channel_list)\n",
    "print(channel_counter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c80f7cb6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({'GARNIER': 2078, 'LRP': 1123, 'OAP': 1057, 'CERAVE': 790, 'VICHY': 768, 'YSL': 717, 'LANCOME': 711, 'KIEHLS': 540, 'KERASTASE': 490, 'MNY': 488, 'ARMANI': 466, 'MAYBELLINE': 383, 'NYX': 350, 'LOP': 241, 'BIOTHERM': 119, 'PRADA': 107, 'REDKEN': 44})\n"
     ]
    }
   ],
   "source": [
    "#Product advertiser \n",
    "\n",
    "product_advertiser_list=list(video_data['ProductAdvertiser'])\n",
    "product_advertiser_list_counter=Counter(product_advertiser_list)\n",
    "print(product_advertiser_list_counter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bba07f54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unexpected exception formatting exception. Falling back to standard exception\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"pandas/_libs/tslib.pyx\", line 605, in pandas._libs.tslib.array_to_datetime\n",
      "  File \"pandas/_libs/tslibs/parsing.pyx\", line 318, in pandas._libs.tslibs.parsing.parse_datetime_string\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/dateutil/parser/_parser.py\", line 1368, in parse\n",
      "    return DEFAULTPARSER.parse(timestr, **kwargs)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/dateutil/parser/_parser.py\", line 643, in parse\n",
      "    raise ParserError(\"Unknown string format: %s\", timestr)\n",
      "dateutil.parser._parser.ParserError: Unknown string format: 31/07/2022 - 12/08/2022\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"pandas/_libs/tslib.pyx\", line 616, in pandas._libs.tslib.array_to_datetime\n",
      "TypeError: invalid string coercion to datetime for \"31/07/2022 - 12/08/2022\" at position 39\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/interactiveshell.py\", line 3460, in run_code\n",
      "    exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "  File \"/tmp/ipykernel_12759/1744046717.py\", line 2, in <module>\n",
      "    video_data['Date']= pd.to_datetime(video_data['Date'],infer_datetime_format=True, dayfirst=True)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/pandas/core/tools/datetimes.py\", line 1064, in to_datetime\n",
      "    cache_array = _maybe_cache(arg, format, cache, convert_listlike)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/pandas/core/tools/datetimes.py\", line 229, in _maybe_cache\n",
      "    cache_dates = convert_listlike(unique_dates, format)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/pandas/core/tools/datetimes.py\", line 438, in _convert_listlike_datetimes\n",
      "    result, tz_parsed = objects_to_datetime64ns(\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/pandas/core/arrays/datetimes.py\", line 2177, in objects_to_datetime64ns\n",
      "    result, tz_parsed = tslib.array_to_datetime(\n",
      "  File \"pandas/_libs/tslib.pyx\", line 427, in pandas._libs.tslib.array_to_datetime\n",
      "  File \"pandas/_libs/tslib.pyx\", line 683, in pandas._libs.tslib.array_to_datetime\n",
      "  File \"pandas/_libs/tslib.pyx\", line 829, in pandas._libs.tslib._array_to_datetime_object\n",
      "  File \"pandas/_libs/tslib.pyx\", line 819, in pandas._libs.tslib._array_to_datetime_object\n",
      "  File \"pandas/_libs/tslibs/parsing.pyx\", line 318, in pandas._libs.tslibs.parsing.parse_datetime_string\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/dateutil/parser/_parser.py\", line 1368, in parse\n",
      "    return DEFAULTPARSER.parse(timestr, **kwargs)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/dateutil/parser/_parser.py\", line 643, in parse\n",
      "    raise ParserError(\"Unknown string format: %s\", timestr)\n",
      "dateutil.parser._parser.ParserError: Unknown string format: 31/07/2022 - 12/08/2022 present at position 39\n",
      "\n",
      "During handling of the above exception, another exception occurred:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/interactiveshell.py\", line 2057, in showtraceback\n",
      "    stb = self.InteractiveTB.structured_traceback(\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/ultratb.py\", line 1288, in structured_traceback\n",
      "    return FormattedTB.structured_traceback(\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/ultratb.py\", line 1177, in structured_traceback\n",
      "    return VerboseTB.structured_traceback(\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/ultratb.py\", line 1049, in structured_traceback\n",
      "    formatted_exceptions += self.format_exception_as_a_whole(etype, evalue, etb, lines_of_context,\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/ultratb.py\", line 935, in format_exception_as_a_whole\n",
      "    self.get_records(etb, number_of_lines_of_context, tb_offset) if etb else []\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/site-packages/IPython/core/ultratb.py\", line 1003, in get_records\n",
      "    lines, first = inspect.getsourcelines(etb.tb_frame)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/inspect.py\", line 979, in getsourcelines\n",
      "    lines, lnum = findsource(object)\n",
      "  File \"/data/digbose92/envs/miniconda3/envs/py38env/lib/python3.8/inspect.py\", line 798, in findsource\n",
      "    raise OSError('could not get source code')\n",
      "OSError: could not get source code\n"
     ]
    }
   ],
   "source": [
    "# year parsing\n",
    "video_data['Date']= pd.to_datetime(video_data['Date'],infer_datetime_format=True, dayfirst=True)\n",
    "video_data['Date'] = video_data['Date'].dt.strftime('%Y-%m-%d')\n",
    "\n",
    "# video_data['Date'] = video_data['Date'].dt.strftime('%Y-%m-%d')\n",
    "date_list=list(video_data['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cae7eb97",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}