Extraction and Text Anlaysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cca19f64",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ab5c85bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Article 123.0 extracted and saved successfully.\n",
      "Article 321.0 extracted and saved successfully.\n",
      "Failed to extract article 2345.0.\n",
      "Article 4321.0 extracted and saved successfully.\n",
      "Article 432.0 extracted and saved successfully.\n",
      "Article 2893.8 extracted and saved successfully.\n",
      "Article 3355.6 extracted and saved successfully.\n",
      "Article 3817.4 extracted and saved successfully.\n",
      "Failed to extract article 4279.2.\n",
      "Article 4741.0 extracted and saved successfully.\n",
      "Article 5202.8 extracted and saved successfully.\n",
      "Article 5664.6 extracted and saved successfully.\n",
      "Article 6126.4 extracted and saved successfully.\n",
      "Article 6588.2 extracted and saved successfully.\n",
      "Article 7050.0 extracted and saved successfully.\n",
      "Article 7511.8 extracted and saved successfully.\n",
      "Article 7973.6 extracted and saved successfully.\n",
      "Failed to extract article 8435.4.\n",
      "Article 8897.2 extracted and saved successfully.\n",
      "Article 9359.0 extracted and saved successfully.\n",
      "Article 9820.8 extracted and saved successfully.\n",
      "Article 10282.6 extracted and saved successfully.\n",
      "Article 10744.4 extracted and saved successfully.\n",
      "Article 11206.2 extracted and saved successfully.\n",
      "Failed to extract article 11668.0.\n",
      "Article 12129.8 extracted and saved successfully.\n",
      "Article 12591.6 extracted and saved successfully.\n",
      "Article 13053.4 extracted and saved successfully.\n",
      "Article 13515.2 extracted and saved successfully.\n",
      "Article 13977.0 extracted and saved successfully.\n",
      "Article 14438.8 extracted and saved successfully.\n",
      "Failed to extract article 14900.6.\n",
      "Article 15362.4 extracted and saved successfully.\n",
      "Article 15824.2 extracted and saved successfully.\n",
      "Article 16286.0 extracted and saved successfully.\n",
      "Article 16747.8 extracted and saved successfully.\n",
      "Article 17209.6 extracted and saved successfully.\n",
      "Failed to extract article 17671.4.\n",
      "Article 18133.2 extracted and saved successfully.\n",
      "Article 18595.0 extracted and saved successfully.\n",
      "Article 19056.8 extracted and saved successfully.\n",
      "Article 19518.6 extracted and saved successfully.\n",
      "Article 19980.4 extracted and saved successfully.\n",
      "Article 20442.2 extracted and saved successfully.\n",
      "Article 20904.0 extracted and saved successfully.\n",
      "Article 21365.8 extracted and saved successfully.\n",
      "Article 21827.6 extracted and saved successfully.\n",
      "Article 22289.4 extracted and saved successfully.\n",
      "Article 22751.2 extracted and saved successfully.\n",
      "Article 23213.0 extracted and saved successfully.\n",
      "Article 23674.8 extracted and saved successfully.\n",
      "Article 24136.6 extracted and saved successfully.\n",
      "Article 24598.4 extracted and saved successfully.\n",
      "Article 25060.2 extracted and saved successfully.\n",
      "Article 25522.0 extracted and saved successfully.\n",
      "Article 25983.8 extracted and saved successfully.\n",
      "Article 26445.6 extracted and saved successfully.\n",
      "Article 26907.4 extracted and saved successfully.\n",
      "Article 27369.2 extracted and saved successfully.\n",
      "Article 27831.0 extracted and saved successfully.\n",
      "Article 28292.8 extracted and saved successfully.\n",
      "Article 28754.6 extracted and saved successfully.\n",
      "Article 29216.4 extracted and saved successfully.\n",
      "Article 29678.2 extracted and saved successfully.\n",
      "Article 30140.0 extracted and saved successfully.\n",
      "Article 30601.8 extracted and saved successfully.\n",
      "Article 31063.6 extracted and saved successfully.\n",
      "Article 31525.4 extracted and saved successfully.\n",
      "Article 31987.2 extracted and saved successfully.\n",
      "Article 32449.0 extracted and saved successfully.\n",
      "Article 32910.8 extracted and saved successfully.\n",
      "Failed to extract article 33372.6.\n",
      "Failed to extract article 33834.4.\n",
      "Article 34296.2 extracted and saved successfully.\n",
      "Article 34758.0 extracted and saved successfully.\n",
      "Article 35219.8 extracted and saved successfully.\n",
      "Article 35681.6 extracted and saved successfully.\n",
      "Article 36143.4 extracted and saved successfully.\n",
      "Article 36605.2 extracted and saved successfully.\n",
      "Article 37067.0 extracted and saved successfully.\n",
      "Failed to extract article 37528.8.\n",
      "Article 37990.6 extracted and saved successfully.\n",
      "Article 38452.4 extracted and saved successfully.\n",
      "Article 38914.2 extracted and saved successfully.\n",
      "Article 39376.0 extracted and saved successfully.\n",
      "Article 39837.8 extracted and saved successfully.\n",
      "Article 40299.6 extracted and saved successfully.\n",
      "Failed to extract article 40761.4.\n",
      "Failed to extract article 41223.2.\n",
      "Article 41685.0 extracted and saved successfully.\n",
      "Article 42146.8 extracted and saved successfully.\n",
      "Article 42608.6 extracted and saved successfully.\n",
      "Failed to extract article 43070.4.\n",
      "Article 43532.2 extracted and saved successfully.\n",
      "Article 43994.0 extracted and saved successfully.\n",
      "Article 44455.8 extracted and saved successfully.\n",
      "Article 44917.6 extracted and saved successfully.\n",
      "Article 45379.4 extracted and saved successfully.\n",
      "Article 45841.2 extracted and saved successfully.\n",
      "Article 46303.0 extracted and saved successfully.\n",
      "Article 46764.8 extracted and saved successfully.\n",
      "Article 47226.6 extracted and saved successfully.\n",
      "Article 47688.4 extracted and saved successfully.\n",
      "Article 48150.2 extracted and saved successfully.\n",
      "Article 48612.0 extracted and saved successfully.\n",
      "Article 49073.8 extracted and saved successfully.\n",
      "Article 49535.6 extracted and saved successfully.\n",
      "Article 49997.4 extracted and saved successfully.\n",
      "Article 50459.2 extracted and saved successfully.\n",
      "Article 50921.0 extracted and saved successfully.\n",
      "Article 51382.8 extracted and saved successfully.\n",
      "Article 51844.6 extracted and saved successfully.\n",
      "Article 52306.4 extracted and saved successfully.\n",
      "Article 52768.2 extracted and saved successfully.\n",
      "Wall time: 4min 16s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Function to extract the article title and text from a URLs given in Input.xlxs file and extracting each articles into txt file\n",
    "\n",
    "def extract_article_text(url):\n",
    "    try:\n",
    "        response = requests.get(url)\n",
    "        soup = BeautifulSoup(response.text, 'html.parser')\n",
    "        \n",
    "        # Find and remove unwanted elements (e.g., header, footer, etc.)\n",
    "        for element in soup([\"header\", \"footer\"]):\n",
    "            element.decompose()\n",
    "        \n",
    "        # Extract article title and text\n",
    "        article_title = soup.find('title').text.strip()\n",
    "        article_text = \"\"\n",
    "        \n",
    "        # Extract text from <div class=\"td-post-content tagdiv-type\">\n",
    "        article_div = soup.find('div', class_='td-post-content tagdiv-type')\n",
    "        if article_div:\n",
    "            article_text = article_div.get_text()\n",
    "        return article_title, article_text\n",
    "    \n",
    "    except Exception:\n",
    "        print(f\"Error while extracting article from {url}: {Exception}\")\n",
    "        return None, None\n",
    "\n",
    "# Function to save the article title and text to a text file\n",
    "    \n",
    "def save_article_to_file(url_id, article_title, article_text):\n",
    "    if not os.path.exists(\"articles\"):\n",
    "        os.mkdir(\"articles\")\n",
    "    \n",
    "    with open(f\"articles/{url_id}.txt\", \"w\", encoding=\"utf-8\") as file:\n",
    "        file.write(f\"Title: {article_title}\\n\\n\")\n",
    "        file.write(article_text)\n",
    "\n",
    "def main():\n",
    "    input_file = \"input.xlsx\"\n",
    "    df = pd.read_excel(input_file)\n",
    "    \n",
    "    for index, row in df.iterrows():\n",
    "        url_id = row[\"URL_ID\"]\n",
    "        url = row[\"URL\"]\n",
    "        \n",
    "        # Extract article title and text\n",
    "        article_title, article_text = extract_article_text(url)\n",
    "        \n",
    "        # Check if extraction was successful\n",
    "        if article_title and article_text:\n",
    "            save_article_to_file(url_id, article_title, article_text)\n",
    "            print(f\"Article {url_id} extracted and saved successfully.\")\n",
    "        else:\n",
    "            print(f\"Failed to extract article {url_id}.\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3d8b5010",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[nltk_data] Downloading package punkt to\n",
      "[nltk_data]     C:\\Users\\lenovo\\AppData\\Roaming\\nltk_data...\n",
      "[nltk_data]   Package punkt is already up-to-date!\n",
      "[nltk_data] Downloading package vader_lexicon to\n",
      "[nltk_data]     C:\\Users\\lenovo\\AppData\\Roaming\\nltk_data...\n",
      "[nltk_data] Downloading package stopwords to\n",
      "[nltk_data]     C:\\Users\\lenovo\\AppData\\Roaming\\nltk_data...\n",
      "[nltk_data]   Package stopwords is already up-to-date!\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import nltk\n",
    "from nltk.tokenize import word_tokenize\n",
    "from nltk.sentiment import SentimentIntensityAnalyzer\n",
    "from nltk.corpus import stopwords\n",
    "\n",
    "# Load NLTK resources (you might need to download NLTK resources)\n",
    "nltk.download('punkt')\n",
    "nltk.download('vader_lexicon')\n",
    "nltk.download('stopwords')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "460c3172",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to load positive and negative dictionaries from files\n",
    "def load_dictionaries(positive_dict_file, negative_dict_file):\n",
    "    with open(positive_dict_file, 'r') as file:\n",
    "        positive_words = set(file.read().splitlines())\n",
    "    with open(negative_dict_file, 'r') as file:\n",
    "        negative_words = set(file.read().splitlines())\n",
    "    return positive_words, negative_words\n",
    "\n",
    "# Function to perform sentiment analysis and calculate scores\n",
    "def calculate_sentiment_scores(text, positive_words, negative_words):\n",
    "    sia = SentimentIntensityAnalyzer()\n",
    "    tokens = word_tokenize(text)\n",
    "    \n",
    "    positive_score = 0\n",
    "    negative_score = 0\n",
    "    \n",
    "    for word in tokens:\n",
    "        # Remove punctuation and convert to lowercase\n",
    "        word = word.lower()\n",
    "        if word.isalpha():\n",
    "            # Check if the word is in the positive dictionary\n",
    "            if word in positive_words:\n",
    "                positive_score += 1\n",
    "            # Check if the word is in the negative dictionary\n",
    "            if word in negative_words:\n",
    "                negative_score += 1\n",
    "    \n",
    "    # Calculate sentiment analysis metrics\n",
    "    polarity_score = (positive_score - negative_score) / ((positive_score + negative_score) + 0.000001)\n",
    "    subjectivity_score = (positive_score + negative_score) / (len(tokens) + 0.000001)\n",
    "    \n",
    "    return positive_score, negative_score, polarity_score, subjectivity_score\n",
    "\n",
    "def main():\n",
    "    input_data_file = \"Output Data Structure.xlsx\"\n",
    "    positive_dict_file = \"positive-words.txt\"\n",
    "    negative_dict_file = \"negative-words.txt\"\n",
    "    articles_dir = \"articles\"\n",
    "    \n",
    "    # Load dictionaries\n",
    "    positive_words, negative_words = load_dictionaries(positive_dict_file, negative_dict_file)\n",
    "\n",
    "    # Read output data structure Excel file\n",
    "    output_data = pd.read_excel(input_data_file)\n",
    "    \n",
    "    results = []\n",
    "    for index, row in output_data.iterrows():\n",
    "        url_id = row[\"URL_ID\"]\n",
    "        url = row[\"URL\"]\n",
    "        article_file = os.path.join(articles_dir, f\"{url_id}.txt\")\n",
    "        \n",
    "        if os.path.exists(article_file):\n",
    "            # Read article text from file\n",
    "            with open(article_file, 'r', encoding='utf-8') as article:\n",
    "                article_text = article.read()\n",
    "            \n",
    "            # Perform sentiment analysis\n",
    "            positive_score, negative_score, polarity_score, subjectivity_score = calculate_sentiment_scores(article_text, positive_words, negative_words)\n",
    "            \n",
    "            results.append({\n",
    "                \"URL_ID\": url_id,\n",
    "                \"URL\": url,\n",
    "                \"Positive_Score\": positive_score,\n",
    "                \"Negative_Score\": negative_score,\n",
    "                \"Polarity_Score\": polarity_score,\n",
    "                \"Subjectivity_Score\": subjectivity_score\n",
    "            })\n",
    "    \n",
    "    # Create DataFrame from results\n",
    "    result_df = pd.DataFrame(results)\n",
    "    \n",
    "    # Save results to Excel\n",
    "    result_df.to_excel(\"sentiment_analysis_results.xlsx\", index=False)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b5cbe945",
   "metadata": {},
   "outputs": [],
   "source": [
    "sentiment_analysis = pd.read_excel(\"sentiment_analysis_results.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "acfa62c0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>URL_ID</th>\n",
       "      <th>URL</th>\n",
       "      <th>Positive_Score</th>\n",
       "      <th>Negative_Score</th>\n",
       "      <th>Polarity_Score</th>\n",
       "      <th>Subjectivity_Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>88</td>\n",
       "      <td>24</td>\n",
       "      <td>0.571429</td>\n",
       "      <td>0.060054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>321.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-e-hea...</td>\n",
       "      <td>41</td>\n",
       "      <td>13</td>\n",
       "      <td>0.518519</td>\n",
       "      <td>0.079295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4321.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>44</td>\n",
       "      <td>27</td>\n",
       "      <td>0.239437</td>\n",
       "      <td>0.051116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>432.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>44</td>\n",
       "      <td>27</td>\n",
       "      <td>0.239437</td>\n",
       "      <td>0.051116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2893.8</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-chatb...</td>\n",
       "      <td>56</td>\n",
       "      <td>12</td>\n",
       "      <td>0.647059</td>\n",
       "      <td>0.051515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>50921.0</td>\n",
       "      <td>https://insights.blackcoffer.com/coronavirus-i...</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>-0.567568</td>\n",
       "      <td>0.047619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>51382.8</td>\n",
       "      <td>https://insights.blackcoffer.com/coronavirus-i...</td>\n",
       "      <td>27</td>\n",
       "      <td>66</td>\n",
       "      <td>-0.419355</td>\n",
       "      <td>0.049025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>51844.6</td>\n",
       "      <td>https://insights.blackcoffer.com/what-are-the-...</td>\n",
       "      <td>100</td>\n",
       "      <td>33</td>\n",
       "      <td>0.503759</td>\n",
       "      <td>0.067036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>52306.4</td>\n",
       "      <td>https://insights.blackcoffer.com/marketing-dri...</td>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "      <td>0.185185</td>\n",
       "      <td>0.033898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>52768.2</td>\n",
       "      <td>https://insights.blackcoffer.com/continued-dem...</td>\n",
       "      <td>58</td>\n",
       "      <td>34</td>\n",
       "      <td>0.260870</td>\n",
       "      <td>0.080986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      URL_ID                                                URL  \\\n",
       "0      123.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "1      321.0  https://insights.blackcoffer.com/rise-of-e-hea...   \n",
       "2     4321.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "3      432.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "4     2893.8  https://insights.blackcoffer.com/rise-of-chatb...   \n",
       "..       ...                                                ...   \n",
       "97   50921.0  https://insights.blackcoffer.com/coronavirus-i...   \n",
       "98   51382.8  https://insights.blackcoffer.com/coronavirus-i...   \n",
       "99   51844.6  https://insights.blackcoffer.com/what-are-the-...   \n",
       "100  52306.4  https://insights.blackcoffer.com/marketing-dri...   \n",
       "101  52768.2  https://insights.blackcoffer.com/continued-dem...   \n",
       "\n",
       "     Positive_Score  Negative_Score  Polarity_Score  Subjectivity_Score  \n",
       "0                88              24        0.571429            0.060054  \n",
       "1                41              13        0.518519            0.079295  \n",
       "2                44              27        0.239437            0.051116  \n",
       "3                44              27        0.239437            0.051116  \n",
       "4                56              12        0.647059            0.051515  \n",
       "..              ...             ...             ...                 ...  \n",
       "97                8              29       -0.567568            0.047619  \n",
       "98               27              66       -0.419355            0.049025  \n",
       "99              100              33        0.503759            0.067036  \n",
       "100              32              22        0.185185            0.033898  \n",
       "101              58              34        0.260870            0.080986  \n",
       "\n",
       "[102 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentiment_analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5f07823c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "from nltk.tokenize import sent_tokenize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e49d9941",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to calculate average sentence length\n",
    "def calculate_avg_sentence_length(sentences):\n",
    "    total_words = sum(len(word_tokenize(sentence)) for sentence in sentences)\n",
    "    total_sentences = len(sentences)\n",
    "    return total_words / total_sentences\n",
    "\n",
    "# Function to calculate percentage of complex words\n",
    "def calculate_percentage_complex_words(text):\n",
    "    words = word_tokenize(text)\n",
    "    complex_words = [word for word in words if len(word) > 2]\n",
    "    return len(complex_words) / len(words)\n",
    "\n",
    "# Function to calculate fog index\n",
    "def calculate_fog_index(avg_sentence_length, percentage_complex_words):\n",
    "    return 0.4 * (avg_sentence_length + percentage_complex_words)\n",
    "\n",
    "# Function to calculate average number of words per sentence\n",
    "def calculate_avg_words_per_sentence(words, sentences):\n",
    "    return len(words) / len(sentences)\n",
    "\n",
    "# Function to calculate complex word count\n",
    "def calculate_complex_word_count(text):\n",
    "    words = word_tokenize(text)\n",
    "    complex_words = [word for word in words if len(word) > 2]\n",
    "    return len(complex_words)\n",
    "\n",
    "# Function to calculate word count\n",
    "def calculate_word_count(text):\n",
    "    words = word_tokenize(text)\n",
    "    stop_words = set(stopwords.words(\"english\"))\n",
    "    cleaned_words = [word for word in words if word not in stop_words and word.isalpha()]\n",
    "    return len(cleaned_words)\n",
    "\n",
    "# Function to count syllables in a word\n",
    "def count_syllables(word):\n",
    "    vowels = \"aeiouAEIOU\"\n",
    "    count = 0\n",
    "    if word[-1] in ['e', 'E'] and word[-2:] != 'le' and word[-2:] != 'LE':\n",
    "        word = word[:-1]\n",
    "    for index, letter in enumerate(word):\n",
    "        if index == 0 and letter in vowels:\n",
    "            count += 1\n",
    "        elif letter in vowels and word[index-1] not in vowels:\n",
    "            count += 1\n",
    "    return count\n",
    "\n",
    "# Function to calculate syllable count per word\n",
    "def calculate_syllable_count_per_word(text):\n",
    "    words = word_tokenize(text)\n",
    "    syllable_count = sum(count_syllables(word) for word in words)\n",
    "    return syllable_count / len(words)\n",
    "\n",
    "# Function to calculate personal pronoun count\n",
    "def calculate_personal_pronouns(text):\n",
    "    pronouns = [\"I\", \"we\", \"my\", \"ours\", \"us\"]\n",
    "    pattern = r'\\b(?:' + '|'.join(pronouns) + r')\\b'\n",
    "    matches = re.findall(pattern, text)\n",
    "    return len(matches)\n",
    "\n",
    "# Function to calculate average word length\n",
    "def calculate_avg_word_length(text):\n",
    "    words = word_tokenize(text)\n",
    "    total_characters = sum(len(word) for word in words)\n",
    "    return total_characters / len(words)\n",
    "\n",
    "def main():\n",
    "    output_data_file = \"Output Data Structure.xlsx\"\n",
    "    articles_dir = \"articles\"\n",
    "    \n",
    "    # Read output data structure Excel file\n",
    "    output_data = pd.read_excel(output_data_file)\n",
    "    \n",
    "    results_ = []\n",
    "    for index, row in output_data.iterrows():\n",
    "        url_id = row[\"URL_ID\"]\n",
    "        article_file = os.path.join(articles_dir, f\"{url_id}.txt\")\n",
    "        \n",
    "        if os.path.exists(article_file):\n",
    "            # Read article text from file\n",
    "            with open(article_file, 'r', encoding='utf-8') as article:\n",
    "                article_text = article.read()\n",
    "            \n",
    "            # Tokenize sentences for text analysis\n",
    "            sentences = sent_tokenize(article_text)\n",
    "            words = word_tokenize(article_text)\n",
    "            \n",
    "            # Calculate text analysis metrics\n",
    "            avg_sentence_length = calculate_avg_sentence_length(sentences)\n",
    "            percentage_complex_words = calculate_percentage_complex_words(article_text)\n",
    "            fog_index = calculate_fog_index(avg_sentence_length, percentage_complex_words)\n",
    "            avg_words_per_sentence = calculate_avg_words_per_sentence(words, sentences)\n",
    "            complex_word_count = calculate_complex_word_count(article_text)\n",
    "            word_count = calculate_word_count(article_text)\n",
    "            syllable_count_per_word = calculate_syllable_count_per_word(article_text)\n",
    "            personal_pronoun_count = calculate_personal_pronouns(article_text)\n",
    "            avg_word_length = calculate_avg_word_length(article_text)\n",
    "            \n",
    "            results_.append({\n",
    "                \"URL_ID\": url_id,\n",
    "                \"Avg_Sentence_Length\": avg_sentence_length,\n",
    "                \"Percentage_Complex_Words\": percentage_complex_words,\n",
    "                \"Fog_Index\": fog_index,\n",
    "                \"Avg_Words_Per_Sentence\": avg_words_per_sentence,\n",
    "                \"Complex_Word_Count\": complex_word_count,\n",
    "                \"Word_Count\": word_count,\n",
    "                \"Syllable_Count_Per_Word\": syllable_count_per_word,\n",
    "                \"Personal_Pronoun_Count\": personal_pronoun_count,\n",
    "                \"Avg_Word_Length\": avg_word_length\n",
    "            })\n",
    "    \n",
    "    # Create DataFrame from results\n",
    "    result_df2 = pd.DataFrame(results_)\n",
    "    \n",
    "    # Save results to Excel\n",
    "    result_df2.to_excel(\"text_analysis_results.xlsx\", index=False)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ada74847",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>URL_ID</th>\n",
       "      <th>Avg_Sentence_Length</th>\n",
       "      <th>Percentage_Complex_Words</th>\n",
       "      <th>Fog_Index</th>\n",
       "      <th>Avg_Words_Per_Sentence</th>\n",
       "      <th>Complex_Word_Count</th>\n",
       "      <th>Word_Count</th>\n",
       "      <th>Syllable_Count_Per_Word</th>\n",
       "      <th>Personal_Pronoun_Count</th>\n",
       "      <th>Avg_Word_Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123.0</td>\n",
       "      <td>23.312500</td>\n",
       "      <td>0.758177</td>\n",
       "      <td>9.628271</td>\n",
       "      <td>23.312500</td>\n",
       "      <td>1414</td>\n",
       "      <td>1040</td>\n",
       "      <td>1.597319</td>\n",
       "      <td>2</td>\n",
       "      <td>5.139410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>321.0</td>\n",
       "      <td>27.240000</td>\n",
       "      <td>0.738620</td>\n",
       "      <td>11.191448</td>\n",
       "      <td>27.240000</td>\n",
       "      <td>503</td>\n",
       "      <td>343</td>\n",
       "      <td>1.544787</td>\n",
       "      <td>3</td>\n",
       "      <td>5.098385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4321.0</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>0.750900</td>\n",
       "      <td>9.560360</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>1043</td>\n",
       "      <td>761</td>\n",
       "      <td>1.529158</td>\n",
       "      <td>6</td>\n",
       "      <td>5.051836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>432.0</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>0.750900</td>\n",
       "      <td>9.560360</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>1043</td>\n",
       "      <td>761</td>\n",
       "      <td>1.529158</td>\n",
       "      <td>6</td>\n",
       "      <td>5.051836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2893.8</td>\n",
       "      <td>20.307692</td>\n",
       "      <td>0.750758</td>\n",
       "      <td>8.423380</td>\n",
       "      <td>20.307692</td>\n",
       "      <td>991</td>\n",
       "      <td>715</td>\n",
       "      <td>1.519697</td>\n",
       "      <td>5</td>\n",
       "      <td>4.990152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>50921.0</td>\n",
       "      <td>25.900000</td>\n",
       "      <td>0.736165</td>\n",
       "      <td>10.654466</td>\n",
       "      <td>25.900000</td>\n",
       "      <td>572</td>\n",
       "      <td>414</td>\n",
       "      <td>1.435006</td>\n",
       "      <td>1</td>\n",
       "      <td>4.841699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>51382.8</td>\n",
       "      <td>37.940000</td>\n",
       "      <td>0.721666</td>\n",
       "      <td>15.464666</td>\n",
       "      <td>37.940000</td>\n",
       "      <td>1369</td>\n",
       "      <td>1085</td>\n",
       "      <td>1.465472</td>\n",
       "      <td>2</td>\n",
       "      <td>4.732736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>51844.6</td>\n",
       "      <td>27.943662</td>\n",
       "      <td>0.725806</td>\n",
       "      <td>11.467787</td>\n",
       "      <td>27.943662</td>\n",
       "      <td>1440</td>\n",
       "      <td>1028</td>\n",
       "      <td>1.440524</td>\n",
       "      <td>0</td>\n",
       "      <td>4.768145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>52306.4</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>0.696171</td>\n",
       "      <td>11.078468</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>1109</td>\n",
       "      <td>780</td>\n",
       "      <td>1.381042</td>\n",
       "      <td>6</td>\n",
       "      <td>4.644068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>52768.2</td>\n",
       "      <td>27.707317</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>11.382927</td>\n",
       "      <td>27.707317</td>\n",
       "      <td>852</td>\n",
       "      <td>629</td>\n",
       "      <td>1.645246</td>\n",
       "      <td>0</td>\n",
       "      <td>5.271127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      URL_ID  Avg_Sentence_Length  Percentage_Complex_Words  Fog_Index  \\\n",
       "0      123.0            23.312500                  0.758177   9.628271   \n",
       "1      321.0            27.240000                  0.738620  11.191448   \n",
       "2     4321.0            23.150000                  0.750900   9.560360   \n",
       "3      432.0            23.150000                  0.750900   9.560360   \n",
       "4     2893.8            20.307692                  0.750758   8.423380   \n",
       "..       ...                  ...                       ...        ...   \n",
       "97   50921.0            25.900000                  0.736165  10.654466   \n",
       "98   51382.8            37.940000                  0.721666  15.464666   \n",
       "99   51844.6            27.943662                  0.725806  11.467787   \n",
       "100  52306.4            27.000000                  0.696171  11.078468   \n",
       "101  52768.2            27.707317                  0.750000  11.382927   \n",
       "\n",
       "     Avg_Words_Per_Sentence  Complex_Word_Count  Word_Count  \\\n",
       "0                 23.312500                1414        1040   \n",
       "1                 27.240000                 503         343   \n",
       "2                 23.150000                1043         761   \n",
       "3                 23.150000                1043         761   \n",
       "4                 20.307692                 991         715   \n",
       "..                      ...                 ...         ...   \n",
       "97                25.900000                 572         414   \n",
       "98                37.940000                1369        1085   \n",
       "99                27.943662                1440        1028   \n",
       "100               27.000000                1109         780   \n",
       "101               27.707317                 852         629   \n",
       "\n",
       "     Syllable_Count_Per_Word  Personal_Pronoun_Count  Avg_Word_Length  \n",
       "0                   1.597319                       2         5.139410  \n",
       "1                   1.544787                       3         5.098385  \n",
       "2                   1.529158                       6         5.051836  \n",
       "3                   1.529158                       6         5.051836  \n",
       "4                   1.519697                       5         4.990152  \n",
       "..                       ...                     ...              ...  \n",
       "97                  1.435006                       1         4.841699  \n",
       "98                  1.465472                       2         4.732736  \n",
       "99                  1.440524                       0         4.768145  \n",
       "100                 1.381042                       6         4.644068  \n",
       "101                 1.645246                       0         5.271127  \n",
       "\n",
       "[102 rows x 10 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "text_analysis = pd.read_excel(\"text_analysis_results.xlsx\")\n",
    "text_analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2ef08700",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>URL_ID</th>\n",
       "      <th>URL</th>\n",
       "      <th>Positive_Score</th>\n",
       "      <th>Negative_Score</th>\n",
       "      <th>Polarity_Score</th>\n",
       "      <th>Subjectivity_Score</th>\n",
       "      <th>Avg_Sentence_Length</th>\n",
       "      <th>Percentage_Complex_Words</th>\n",
       "      <th>Fog_Index</th>\n",
       "      <th>Avg_Words_Per_Sentence</th>\n",
       "      <th>Complex_Word_Count</th>\n",
       "      <th>Word_Count</th>\n",
       "      <th>Syllable_Count_Per_Word</th>\n",
       "      <th>Personal_Pronoun_Count</th>\n",
       "      <th>Avg_Word_Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>88</td>\n",
       "      <td>24</td>\n",
       "      <td>0.571429</td>\n",
       "      <td>0.060054</td>\n",
       "      <td>23.312500</td>\n",
       "      <td>0.758177</td>\n",
       "      <td>9.628271</td>\n",
       "      <td>23.312500</td>\n",
       "      <td>1414</td>\n",
       "      <td>1040</td>\n",
       "      <td>1.597319</td>\n",
       "      <td>2</td>\n",
       "      <td>5.139410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>321.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-e-hea...</td>\n",
       "      <td>41</td>\n",
       "      <td>13</td>\n",
       "      <td>0.518519</td>\n",
       "      <td>0.079295</td>\n",
       "      <td>27.240000</td>\n",
       "      <td>0.738620</td>\n",
       "      <td>11.191448</td>\n",
       "      <td>27.240000</td>\n",
       "      <td>503</td>\n",
       "      <td>343</td>\n",
       "      <td>1.544787</td>\n",
       "      <td>3</td>\n",
       "      <td>5.098385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4321.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>44</td>\n",
       "      <td>27</td>\n",
       "      <td>0.239437</td>\n",
       "      <td>0.051116</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>0.750900</td>\n",
       "      <td>9.560360</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>1043</td>\n",
       "      <td>761</td>\n",
       "      <td>1.529158</td>\n",
       "      <td>6</td>\n",
       "      <td>5.051836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>432.0</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-telem...</td>\n",
       "      <td>44</td>\n",
       "      <td>27</td>\n",
       "      <td>0.239437</td>\n",
       "      <td>0.051116</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>0.750900</td>\n",
       "      <td>9.560360</td>\n",
       "      <td>23.150000</td>\n",
       "      <td>1043</td>\n",
       "      <td>761</td>\n",
       "      <td>1.529158</td>\n",
       "      <td>6</td>\n",
       "      <td>5.051836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2893.8</td>\n",
       "      <td>https://insights.blackcoffer.com/rise-of-chatb...</td>\n",
       "      <td>56</td>\n",
       "      <td>12</td>\n",
       "      <td>0.647059</td>\n",
       "      <td>0.051515</td>\n",
       "      <td>20.307692</td>\n",
       "      <td>0.750758</td>\n",
       "      <td>8.423380</td>\n",
       "      <td>20.307692</td>\n",
       "      <td>991</td>\n",
       "      <td>715</td>\n",
       "      <td>1.519697</td>\n",
       "      <td>5</td>\n",
       "      <td>4.990152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>50921.0</td>\n",
       "      <td>https://insights.blackcoffer.com/coronavirus-i...</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>-0.567568</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>25.900000</td>\n",
       "      <td>0.736165</td>\n",
       "      <td>10.654466</td>\n",
       "      <td>25.900000</td>\n",
       "      <td>572</td>\n",
       "      <td>414</td>\n",
       "      <td>1.435006</td>\n",
       "      <td>1</td>\n",
       "      <td>4.841699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>51382.8</td>\n",
       "      <td>https://insights.blackcoffer.com/coronavirus-i...</td>\n",
       "      <td>27</td>\n",
       "      <td>66</td>\n",
       "      <td>-0.419355</td>\n",
       "      <td>0.049025</td>\n",
       "      <td>37.940000</td>\n",
       "      <td>0.721666</td>\n",
       "      <td>15.464666</td>\n",
       "      <td>37.940000</td>\n",
       "      <td>1369</td>\n",
       "      <td>1085</td>\n",
       "      <td>1.465472</td>\n",
       "      <td>2</td>\n",
       "      <td>4.732736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>51844.6</td>\n",
       "      <td>https://insights.blackcoffer.com/what-are-the-...</td>\n",
       "      <td>100</td>\n",
       "      <td>33</td>\n",
       "      <td>0.503759</td>\n",
       "      <td>0.067036</td>\n",
       "      <td>27.943662</td>\n",
       "      <td>0.725806</td>\n",
       "      <td>11.467787</td>\n",
       "      <td>27.943662</td>\n",
       "      <td>1440</td>\n",
       "      <td>1028</td>\n",
       "      <td>1.440524</td>\n",
       "      <td>0</td>\n",
       "      <td>4.768145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>52306.4</td>\n",
       "      <td>https://insights.blackcoffer.com/marketing-dri...</td>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "      <td>0.185185</td>\n",
       "      <td>0.033898</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>0.696171</td>\n",
       "      <td>11.078468</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>1109</td>\n",
       "      <td>780</td>\n",
       "      <td>1.381042</td>\n",
       "      <td>6</td>\n",
       "      <td>4.644068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>52768.2</td>\n",
       "      <td>https://insights.blackcoffer.com/continued-dem...</td>\n",
       "      <td>58</td>\n",
       "      <td>34</td>\n",
       "      <td>0.260870</td>\n",
       "      <td>0.080986</td>\n",
       "      <td>27.707317</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>11.382927</td>\n",
       "      <td>27.707317</td>\n",
       "      <td>852</td>\n",
       "      <td>629</td>\n",
       "      <td>1.645246</td>\n",
       "      <td>0</td>\n",
       "      <td>5.271127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      URL_ID                                                URL  \\\n",
       "0      123.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "1      321.0  https://insights.blackcoffer.com/rise-of-e-hea...   \n",
       "2     4321.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "3      432.0  https://insights.blackcoffer.com/rise-of-telem...   \n",
       "4     2893.8  https://insights.blackcoffer.com/rise-of-chatb...   \n",
       "..       ...                                                ...   \n",
       "97   50921.0  https://insights.blackcoffer.com/coronavirus-i...   \n",
       "98   51382.8  https://insights.blackcoffer.com/coronavirus-i...   \n",
       "99   51844.6  https://insights.blackcoffer.com/what-are-the-...   \n",
       "100  52306.4  https://insights.blackcoffer.com/marketing-dri...   \n",
       "101  52768.2  https://insights.blackcoffer.com/continued-dem...   \n",
       "\n",
       "     Positive_Score  Negative_Score  Polarity_Score  Subjectivity_Score  \\\n",
       "0                88              24        0.571429            0.060054   \n",
       "1                41              13        0.518519            0.079295   \n",
       "2                44              27        0.239437            0.051116   \n",
       "3                44              27        0.239437            0.051116   \n",
       "4                56              12        0.647059            0.051515   \n",
       "..              ...             ...             ...                 ...   \n",
       "97                8              29       -0.567568            0.047619   \n",
       "98               27              66       -0.419355            0.049025   \n",
       "99              100              33        0.503759            0.067036   \n",
       "100              32              22        0.185185            0.033898   \n",
       "101              58              34        0.260870            0.080986   \n",
       "\n",
       "     Avg_Sentence_Length  Percentage_Complex_Words  Fog_Index  \\\n",
       "0              23.312500                  0.758177   9.628271   \n",
       "1              27.240000                  0.738620  11.191448   \n",
       "2              23.150000                  0.750900   9.560360   \n",
       "3              23.150000                  0.750900   9.560360   \n",
       "4              20.307692                  0.750758   8.423380   \n",
       "..                   ...                       ...        ...   \n",
       "97             25.900000                  0.736165  10.654466   \n",
       "98             37.940000                  0.721666  15.464666   \n",
       "99             27.943662                  0.725806  11.467787   \n",
       "100            27.000000                  0.696171  11.078468   \n",
       "101            27.707317                  0.750000  11.382927   \n",
       "\n",
       "     Avg_Words_Per_Sentence  Complex_Word_Count  Word_Count  \\\n",
       "0                 23.312500                1414        1040   \n",
       "1                 27.240000                 503         343   \n",
       "2                 23.150000                1043         761   \n",
       "3                 23.150000                1043         761   \n",
       "4                 20.307692                 991         715   \n",
       "..                      ...                 ...         ...   \n",
       "97                25.900000                 572         414   \n",
       "98                37.940000                1369        1085   \n",
       "99                27.943662                1440        1028   \n",
       "100               27.000000                1109         780   \n",
       "101               27.707317                 852         629   \n",
       "\n",
       "     Syllable_Count_Per_Word  Personal_Pronoun_Count  Avg_Word_Length  \n",
       "0                   1.597319                       2         5.139410  \n",
       "1                   1.544787                       3         5.098385  \n",
       "2                   1.529158                       6         5.051836  \n",
       "3                   1.529158                       6         5.051836  \n",
       "4                   1.519697                       5         4.990152  \n",
       "..                       ...                     ...              ...  \n",
       "97                  1.435006                       1         4.841699  \n",
       "98                  1.465472                       2         4.732736  \n",
       "99                  1.440524                       0         4.768145  \n",
       "100                 1.381042                       6         4.644068  \n",
       "101                 1.645246                       0         5.271127  \n",
       "\n",
       "[102 rows x 15 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(sentiment_analysis, text_analysis, on='URL_ID')\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4293fcbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_excel(\"OutputDataStructure.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cd57e33",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}