Sort category.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "d16a527c",
      "metadata": {
        "id": "d16a527c"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "import openpyxl\n",
        "import pandas as pd\n",
        "import numpy as np"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5072cb4e",
      "metadata": {
        "id": "5072cb4e"
      },
      "outputs": [],
      "source": [
        "workbook = openpyxl.load_workbook('/content/TH Read0203_0517-0818_TH_concate_translate_process_minus.xlsx')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "676a4e70",
      "metadata": {
        "id": "676a4e70"
      },
      "outputs": [],
      "source": [
        "sheet_names = workbook.sheetnames\n",
        "for sheet_name in sheet_names:\n",
        "    print(sheet_name)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "98d78634",
      "metadata": {
        "id": "98d78634"
      },
      "outputs": [],
      "source": [
        "worksheet = workbook['05_2017-2022_TH_concate_transla']"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "yJYawPfYmU_6"
      },
      "id": "yJYawPfYmU_6"
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "32cecb1e",
      "metadata": {
        "id": "32cecb1e"
      },
      "outputs": [],
      "source": [
        "data = worksheet.values\n",
        "header_row = next(data)\n",
        "df = pd.DataFrame(data, columns=header_row)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "80878219",
      "metadata": {
        "id": "80878219"
      },
      "outputs": [],
      "source": [
        "df.to_csv('sample_tweets.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.shape"
      ],
      "metadata": {
        "id": "JKAnHzEpk6mE"
      },
      "id": "JKAnHzEpk6mE",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "cd502105",
      "metadata": {
        "id": "cd502105"
      },
      "outputs": [],
      "source": [
        "df.columns"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = df.dropna(subset=['tweet'])"
      ],
      "metadata": {
        "id": "gtjwegKjlBT7"
      },
      "id": "gtjwegKjlBT7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.shape"
      ],
      "metadata": {
        "id": "VZ0hWCwFlPEc"
      },
      "id": "VZ0hWCwFlPEc",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "34ae747c",
      "metadata": {
        "id": "34ae747c"
      },
      "outputs": [],
      "source": [
        "unique = df['Topic'].unique()\n",
        "print(unique)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f869ac37",
      "metadata": {
        "id": "f869ac37"
      },
      "outputs": [],
      "source": [
        "Topic_counts = df['Topic'].value_counts()\n",
        "for Topic, count in Topic_counts.items():\n",
        "    print(Topic, count)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "50e6769d",
      "metadata": {
        "id": "50e6769d"
      },
      "outputs": [],
      "source": [
        "df['Topic'] = df['Topic'].fillna('NA')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "36688cbb",
      "metadata": {
        "id": "36688cbb"
      },
      "outputs": [],
      "source": [
        "Topic_counts = df['Topic'].value_counts()\n",
        "for Topic, count in Topic_counts.items():\n",
        "    print(Topic, count)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "553bf7df",
      "metadata": {
        "id": "553bf7df"
      },
      "outputs": [],
      "source": [
        "df.to_csv('sample_tweets_fill_NA.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1ab4e58c",
      "metadata": {
        "collapsed": true,
        "id": "1ab4e58c"
      },
      "outputs": [],
      "source": [
        "unique = df['Topic'].unique()\n",
        "print(unique)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "74a95a97",
      "metadata": {
        "id": "74a95a97"
      },
      "outputs": [],
      "source": [
        "random_df = df.groupby('Topic', group_keys=False).apply(lambda x: x.sample(min(len(x), 30), random_state=42))\n",
        "print(random_df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "d339650f",
      "metadata": {
        "id": "d339650f"
      },
      "outputs": [],
      "source": [
        "random_df.to_csv('sample_tweets_random.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9d6b5dde",
      "metadata": {
        "id": "9d6b5dde"
      },
      "outputs": [],
      "source": [
        "Topic_counts = random_df['Topic'].value_counts()\n",
        "for Topic, count in Topic_counts.items():\n",
        "    print(Topic, count)"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python [conda env:root] *",
      "language": "python",
      "name": "conda-root-py"
    },
    "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.5"
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}