Google Playstore Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ef2414cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from sklearn.impute import SimpleImputer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "35b4138c",
   "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>App</th>\n",
       "      <th>Category</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Size</th>\n",
       "      <th>Installs</th>\n",
       "      <th>Type</th>\n",
       "      <th>Price</th>\n",
       "      <th>Content Rating</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Last Updated</th>\n",
       "      <th>Current Ver</th>\n",
       "      <th>Android Ver</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Photo Editor &amp; Candy Camera &amp; Grid &amp; ScrapBook</td>\n",
       "      <td>ART_AND_DESIGN</td>\n",
       "      <td>4.1</td>\n",
       "      <td>159</td>\n",
       "      <td>19M</td>\n",
       "      <td>10,000+</td>\n",
       "      <td>Free</td>\n",
       "      <td>0</td>\n",
       "      <td>Everyone</td>\n",
       "      <td>Art &amp; Design</td>\n",
       "      <td>January 7, 2018</td>\n",
       "      <td>1.0.0</td>\n",
       "      <td>4.0.3 and up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Coloring book moana</td>\n",
       "      <td>ART_AND_DESIGN</td>\n",
       "      <td>3.9</td>\n",
       "      <td>967</td>\n",
       "      <td>14M</td>\n",
       "      <td>500,000+</td>\n",
       "      <td>Free</td>\n",
       "      <td>0</td>\n",
       "      <td>Everyone</td>\n",
       "      <td>Art &amp; Design;Pretend Play</td>\n",
       "      <td>January 15, 2018</td>\n",
       "      <td>2.0.0</td>\n",
       "      <td>4.0.3 and up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U Launcher Lite – FREE Live Cool Themes, Hide ...</td>\n",
       "      <td>ART_AND_DESIGN</td>\n",
       "      <td>4.7</td>\n",
       "      <td>87510</td>\n",
       "      <td>8.7M</td>\n",
       "      <td>5,000,000+</td>\n",
       "      <td>Free</td>\n",
       "      <td>0</td>\n",
       "      <td>Everyone</td>\n",
       "      <td>Art &amp; Design</td>\n",
       "      <td>August 1, 2018</td>\n",
       "      <td>1.2.4</td>\n",
       "      <td>4.0.3 and up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sketch - Draw &amp; Paint</td>\n",
       "      <td>ART_AND_DESIGN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>215644</td>\n",
       "      <td>25M</td>\n",
       "      <td>50,000,000+</td>\n",
       "      <td>Free</td>\n",
       "      <td>0</td>\n",
       "      <td>Teen</td>\n",
       "      <td>Art &amp; Design</td>\n",
       "      <td>June 8, 2018</td>\n",
       "      <td>Varies with device</td>\n",
       "      <td>4.2 and up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pixel Draw - Number Art Coloring Book</td>\n",
       "      <td>ART_AND_DESIGN</td>\n",
       "      <td>4.3</td>\n",
       "      <td>967</td>\n",
       "      <td>2.8M</td>\n",
       "      <td>100,000+</td>\n",
       "      <td>Free</td>\n",
       "      <td>0</td>\n",
       "      <td>Everyone</td>\n",
       "      <td>Art &amp; Design;Creativity</td>\n",
       "      <td>June 20, 2018</td>\n",
       "      <td>1.1</td>\n",
       "      <td>4.4 and up</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 App        Category  Rating  \\\n",
       "0     Photo Editor & Candy Camera & Grid & ScrapBook  ART_AND_DESIGN     4.1   \n",
       "1                                Coloring book moana  ART_AND_DESIGN     3.9   \n",
       "2  U Launcher Lite – FREE Live Cool Themes, Hide ...  ART_AND_DESIGN     4.7   \n",
       "3                              Sketch - Draw & Paint  ART_AND_DESIGN     4.5   \n",
       "4              Pixel Draw - Number Art Coloring Book  ART_AND_DESIGN     4.3   \n",
       "\n",
       "  Reviews  Size     Installs  Type Price Content Rating  \\\n",
       "0     159   19M      10,000+  Free     0       Everyone   \n",
       "1     967   14M     500,000+  Free     0       Everyone   \n",
       "2   87510  8.7M   5,000,000+  Free     0       Everyone   \n",
       "3  215644   25M  50,000,000+  Free     0           Teen   \n",
       "4     967  2.8M     100,000+  Free     0       Everyone   \n",
       "\n",
       "                      Genres      Last Updated         Current Ver  \\\n",
       "0               Art & Design   January 7, 2018               1.0.0   \n",
       "1  Art & Design;Pretend Play  January 15, 2018               2.0.0   \n",
       "2               Art & Design    August 1, 2018               1.2.4   \n",
       "3               Art & Design      June 8, 2018  Varies with device   \n",
       "4    Art & Design;Creativity     June 20, 2018                 1.1   \n",
       "\n",
       "    Android Ver  \n",
       "0  4.0.3 and up  \n",
       "1  4.0.3 and up  \n",
       "2  4.0.3 and up  \n",
       "3    4.2 and up  \n",
       "4    4.4 and up  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(\"googleplaystore.csv\")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1764493a",
   "metadata": {},
   "source": [
    "# First deal with null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fab95c70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "App                  0\n",
       "Category             0\n",
       "Rating            1474\n",
       "Reviews              0\n",
       "Size                 0\n",
       "Installs             0\n",
       "Type                 1\n",
       "Price                0\n",
       "Content Rating       1\n",
       "Genres               0\n",
       "Last Updated         0\n",
       "Current Ver          8\n",
       "Android Ver          3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's check for null values\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc775f5b",
   "metadata": {},
   "source": [
    "* Rating attribute have more null values than other\n",
    "* so we deal with it first using imputer\n",
    "* replace null value with its mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7a4f6d28",
   "metadata": {},
   "outputs": [],
   "source": [
    "imputer = SimpleImputer(missing_values= np.nan, strategy=\"mean\")\n",
    "imputer.fit(data[['Rating']])\n",
    "\n",
    "data['Rating'] = imputer.transform(data[['Rating']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "26ea6b27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "App               0\n",
       "Category          0\n",
       "Rating            0\n",
       "Reviews           0\n",
       "Size              0\n",
       "Installs          0\n",
       "Type              1\n",
       "Price             0\n",
       "Content Rating    1\n",
       "Genres            0\n",
       "Last Updated      0\n",
       "Current Ver       8\n",
       "Android Ver       3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()\n",
    "# we see that we replace all null values in rating column"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "430283a6",
   "metadata": {},
   "source": [
    "* now let's check others\n",
    "* as we see other values are much less compare to size of data\n",
    "* so if we drop that rows it not affect our data as much as Rating do\n",
    "* so we drop rows where null values are present\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "82872ed0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "App               0\n",
       "Category          0\n",
       "Rating            0\n",
       "Reviews           0\n",
       "Size              0\n",
       "Installs          0\n",
       "Type              0\n",
       "Price             0\n",
       "Content Rating    0\n",
       "Genres            0\n",
       "Last Updated      0\n",
       "Current Ver       0\n",
       "Android Ver       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dropna(inplace=True)\n",
    "\n",
    "# check if it work properly or not?\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "063d68bb",
   "metadata": {},
   "source": [
    "# Handling duplicate values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cac4b4ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "483"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a46799cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deleting last occurence of duplicate rows\n",
    "data.drop_duplicates(keep='first', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "33356653",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "584abed8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10346, 13)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0df03b2c",
   "metadata": {},
   "source": [
    "# Q1. How many free apps are there in ART_AND_DESIGN?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "34b22e24",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 61 apps in ART_AND_DESIGN which are free.\n"
     ]
    }
   ],
   "source": [
    "apps = data[(data['Category']=='ART_AND_DESIGN') & (data['Type']=='Free')].shape[0]\n",
    "print(\"There are\",apps,\"apps in ART_AND_DESIGN which are free.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329a0aab",
   "metadata": {},
   "source": [
    "### & How many are paids?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1d835870",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 3 apps in ART_AND_DESIGN which are paid.\n"
     ]
    }
   ],
   "source": [
    "apps = data[(data['Category']=='ART_AND_DESIGN') & (data['Type']=='Paid')].shape[0]\n",
    "print(\"There are\",apps,\"apps in ART_AND_DESIGN which are paid.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0720d30d",
   "metadata": {},
   "source": [
    "## Q2. How many apps are there in ART_AND_DESIGN with rating greater than               4.5?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9afd46e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 22 apps in ART_AND_DESIGN which has more than 4.5 rating.\n"
     ]
    }
   ],
   "source": [
    "apps = data[(data['Category']=='ART_AND_DESIGN') & (data['Rating']>4.5)].shape[0]\n",
    "print(\"There are\",apps,\"apps in ART_AND_DESIGN which has more than 4.5 rating.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84900fa7",
   "metadata": {},
   "source": [
    "## Q3. How many apps are there in FAMILY with rating more than 4.5 and Free?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "676883f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 313 in FAMILY category which are free and have Rating more than 4.5\n"
     ]
    }
   ],
   "source": [
    "apps = data[(data['Category']=='FAMILY') & (data['Type']=='Free') & (data['Rating']>4.5)].shape[0]\n",
    "print(\"There are\",apps,'apps in FAMILY category which are free and have Rating more than 4.5')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b8a5abe",
   "metadata": {},
   "source": [
    "## Q3. List all the apps are which are from FAMILY with rating more than 4.5 and Free?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c05edd53",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020     Super ABC! Learning games for kids! Preschool ...\n",
      "2023                                       Candy Pop Story\n",
      "2029                           Dog Run - Pet Dog Simulator\n",
      "2032       Puzzle Kids - Animals Shapes and Jigsaw Puzzles\n",
      "2040          No. Color - Color by Number, Number Coloring\n",
      "                               ...                        \n",
      "10691                                               Pin-fo\n",
      "10801                                  Fr Ignacio Outreach\n",
      "10809                Castle Clash: RPG War and Strategy FR\n",
      "10820                                      Fr. Daoud Lamei\n",
      "10837                     Fr. Mike Schmitz Audio Teachings\n",
      "Name: App, Length: 313, dtype: object\n"
     ]
    }
   ],
   "source": [
    "apps = data[(data['Category']=='FAMILY') & (data['Type']=='Free') & (data['Rating']>4.5)]['App']\n",
    "print(apps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c9a52f17",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(apps)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5eb5b109",
   "metadata": {},
   "source": [
    "# GroupBY"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8a01762",
   "metadata": {},
   "source": [
    "### Q1. Which category has maximum average rating?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "20aba063",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Kamlesh Baviskar\\AppData\\Local\\Temp\\ipykernel_21320\\2149164317.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.mean is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  data.groupby(by='Category').mean()['Rating']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Category\n",
       "ART_AND_DESIGN         4.368438\n",
       "AUTO_AND_VEHICLES      4.190824\n",
       "BEAUTY                 4.260882\n",
       "BOOKS_AND_REFERENCE    4.312461\n",
       "BUSINESS               4.135958\n",
       "COMICS                 4.156445\n",
       "COMMUNICATION          4.158216\n",
       "DATING                 4.013538\n",
       "EDUCATION              4.374564\n",
       "ENTERTAINMENT          4.136036\n",
       "EVENTS                 4.363647\n",
       "FAMILY                 4.191501\n",
       "FINANCE                4.135315\n",
       "FOOD_AND_DRINK         4.168388\n",
       "GAME                   4.277598\n",
       "HEALTH_AND_FITNESS     4.251656\n",
       "HOUSE_AND_HOME         4.169001\n",
       "LIBRARIES_AND_DEMO     4.182938\n",
       "LIFESTYLE              4.113799\n",
       "MAPS_AND_NAVIGATION    4.065061\n",
       "MEDICAL                4.185279\n",
       "NEWS_AND_MAGAZINES     4.140784\n",
       "PARENTING              4.282223\n",
       "PERSONALIZATION        4.304871\n",
       "PHOTOGRAPHY            4.183479\n",
       "PRODUCTIVITY           4.200279\n",
       "SHOPPING               4.245774\n",
       "SOCIAL                 4.247001\n",
       "SPORTS                 4.219279\n",
       "TOOLS                  4.065970\n",
       "TRAVEL_AND_LOCAL       4.107539\n",
       "VIDEO_PLAYERS          4.074858\n",
       "WEATHER                4.239675\n",
       "Name: Rating, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(by='Category').mean()['Rating']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35a37f3d",
   "metadata": {},
   "source": [
    "### Q2. How many free apps are there in each category?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "36b0b918",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Category\n",
       "FAMILY                 1753\n",
       "GAME                   1038\n",
       "TOOLS                   764\n",
       "BUSINESS                415\n",
       "PRODUCTIVITY            379\n",
       "LIFESTYLE               354\n",
       "FINANCE                 343\n",
       "COMMUNICATION           339\n",
       "SPORTS                  327\n",
       "MEDICAL                 324\n",
       "PERSONALIZATION         305\n",
       "PHOTOGRAPHY             302\n",
       "HEALTH_AND_FITNESS      291\n",
       "SOCIAL                  277\n",
       "NEWS_AND_MAGAZINES      262\n",
       "TRAVEL_AND_LOCAL        225\n",
       "SHOPPING                222\n",
       "BOOKS_AND_REFERENCE     201\n",
       "DATING                  189\n",
       "VIDEO_PLAYERS           171\n",
       "MAPS_AND_NAVIGATION     132\n",
       "EDUCATION               126\n",
       "FOOD_AND_DRINK          122\n",
       "ENTERTAINMENT           109\n",
       "LIBRARIES_AND_DEMO       83\n",
       "AUTO_AND_VEHICLES        82\n",
       "HOUSE_AND_HOME           80\n",
       "WEATHER                  74\n",
       "EVENTS                   63\n",
       "ART_AND_DESIGN           61\n",
       "COMICS                   60\n",
       "PARENTING                58\n",
       "BEAUTY                   53\n",
       "Name: Type, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data = data[data['Type']=='Free']\n",
    "new_data.groupby('Category').count()['Type'].sort_values(ascending=False)"
   ]
  }
 ],
 "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}