part6.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "12c9b7b3",
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   survived  pclass     sex   age  sibsp  parch     fare embarked  class  \\\n",
       "0         0       3    male  22.0      1      0   7.2500        S  Third   \n",
       "1         1       1  female  38.0      1      0  71.2833        C  First   \n",
       "2         1       3  female  26.0      0      0   7.9250        S  Third   \n",
       "3         1       1  female  35.0      1      0  53.1000        S  First   \n",
       "4         0       3    male  35.0      0      0   8.0500        S  Third   \n",
       "\n",
       "     who  adult_male deck  embark_town alive  alone  \n",
       "0    man        True  NaN  Southampton    no  False  \n",
       "1  woman       False    C    Cherbourg   yes  False  \n",
       "2  woman       False  NaN  Southampton   yes   True  \n",
       "3  woman       False    C  Southampton   yes  False  \n",
       "4    man        True  NaN  Southampton    no   True  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# titanic data load\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "\n",
    "df = sns.load_dataset('titanic')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af0a17bd",
   "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>age</th>\n",
       "      <th>fare</th>\n",
       "      <th>ten</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22.0</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38.0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26.0</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35.0</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35.0</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age     fare  ten\n",
       "0  22.0   7.2500   10\n",
       "1  38.0  71.2833   10\n",
       "2  26.0   7.9250   10\n",
       "3  35.0  53.1000   10\n",
       "4  35.0   8.0500   10"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#age와 fare 컬럼만 추출\n",
    "df= df.loc[:,['age','fare']]\n",
    "df.head()\n",
    "df['ten'] = 10\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f2b2fe01",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30\n",
      "20\n"
     ]
    }
   ],
   "source": [
    "def add_10(n):\n",
    "    return n+10\n",
    "def add_two_obj(a,b):\n",
    "    return a+b\n",
    "print(add_10(20)) #30\n",
    "# print(n) #n은 add_10 함수가 종료되면 사라짐\n",
    "print(add_two_obj(10,10)) # 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5d927b9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    22.0\n",
      "1    38.0\n",
      "2    26.0\n",
      "Name: age, dtype: float64\n",
      "\n",
      "0    32.0\n",
      "1    48.0\n",
      "2    36.0\n",
      "Name: age, dtype: float64\n",
      "0    32.0\n",
      "1    48.0\n",
      "2    36.0\n",
      "Name: age, dtype: float64\n",
      "0    32.0\n",
      "1    48.0\n",
      "2    36.0\n",
      "Name: age, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 시리즈 객체의 각 원소에 함수를 매핑 .apply(매핑함수)\n",
    "sr1 = df['age'].apply(add_10) #add_10(n), n <- df.age\n",
    "print(df['age'].head(3));print()\n",
    "print(sr1.head(3))\n",
    "\n",
    "#시리즈 객체와 숫자를 적용한 함수 add_two obj(a,b)\n",
    "sr2 = df['age'].apply(add_two_obj, b=10) # a <- df.age\n",
    "print(sr2.head(3))\n",
    "\n",
    "#매핑함수를 lamda 함수로 정의해서 사용\n",
    "sr3 = df['age'].apply(lambda x: add_10(x))#x <- df['age']각각의 원소\n",
    "print(sr3.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ff7302e8",
   "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>age</th>\n",
       "      <th>fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32.0</td>\n",
       "      <td>17.2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48.0</td>\n",
       "      <td>81.2833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36.0</td>\n",
       "      <td>17.9250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age     fare\n",
       "0  32.0  17.2500\n",
       "1  48.0  81.2833\n",
       "2  36.0  17.9250"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 데이터프레임의 각 원소에 함수 매핑 .applymap(매핑함수)\n",
    "df = sns.load_dataset('titanic').loc[:,['age','fare']]\n",
    "\n",
    "df_map = df.applymap(add_10) #age, fare 각각에 add_10 함수 적용\n",
    "df_map.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "579cc93d",
   "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>age</th>\n",
       "      <th>fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>891 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       age   fare\n",
       "0    False  False\n",
       "1    False  False\n",
       "2    False  False\n",
       "3    False  False\n",
       "4    False  False\n",
       "..     ...    ...\n",
       "886  False  False\n",
       "887  False  False\n",
       "888   True  False\n",
       "889  False  False\n",
       "890  False  False\n",
       "\n",
       "[891 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#apply 함수를 데이터 프레임에 적용\n",
    "#.apply(매핑함수, axis=0)->컬럼을 시리즈로\n",
    "\n",
    "def missing_value(series):\n",
    "    return series.isnull()\n",
    "result = df.apply(missing_value)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ce76047e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age      79.5800\n",
       "fare    512.3292\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 최대값 - 최소값\n",
    "def max_min(x):\n",
    "    return x.max() - x.min()\n",
    "\n",
    "result = df.apply(max_min)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e511919",
   "metadata": {},
   "outputs": [],
   "source": [
    "#데이터프레임의 행 단위로 원소에 함수를 적용\n",
    "df['ten']=10\n",
    "df.head(3)\n",
    "\n",
    "df['result'] = df.apply(lambda x: add_two_obj(x['age'],x['ten']),\n",
    "                        axis=1)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "39ff0d31",
   "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>age</th>\n",
       "      <th>fare</th>\n",
       "      <th>ten</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22.0</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>10</td>\n",
       "      <td>17.2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38.0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>10</td>\n",
       "      <td>81.2833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26.0</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>10</td>\n",
       "      <td>17.9250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age     fare  ten   result\n",
       "0  22.0   7.2500   10  17.2500\n",
       "1  38.0  71.2833   10  81.2833\n",
       "2  26.0   7.9250   10  17.9250"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#fare 컬럼에 10을 더해서 fare_add 컬럼으로 추가\n",
    "df['result'] = df.apply(lambda x:x['fare']+x['ten'],axis=1)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "cc6bc2ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     age   fare\n",
      "0  False  False\n",
      "1  False  False\n",
      "2  False  False\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "age     177\n",
      "fare      0\n",
      "dtype: int64\n",
      "<class 'pandas.core.series.Series'>\n",
      "177 <class 'numpy.int64'>\n"
     ]
    }
   ],
   "source": [
    "#pipe() : 데이터프레임 객체를 함수에 매칭\n",
    "df= df.loc[:,['age','fare']]\n",
    "df.head(3)\n",
    "\n",
    "def missing_value(x):\n",
    "    return x.isnull()\n",
    "def missing_count(x):\n",
    "    return missing_value(x).sum()\n",
    "def total_missing_number(x):\n",
    "    return missing_count(x).sum()\n",
    "\n",
    "result_df = df.pipe(missing_value)\n",
    "print(result_df.head(3))\n",
    "print(type(result_df))\n",
    "\n",
    "result_sr = df.pipe(missing_count)\n",
    "print(result_sr.head(3))\n",
    "print(type(result_sr)) #series\n",
    "\n",
    "result_total = df.pipe(total_missing_number)\n",
    "print(result_total,type(result_total))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f7a563db",
   "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>age</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>survived</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22.0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26.0</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35.0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age  pclass     sex  survived\n",
       "0  22.0       3    male         0\n",
       "1  38.0       1  female         1\n",
       "2  26.0       3  female         1\n",
       "3  35.0       1  female         1\n",
       "4  35.0       3    male         0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#titanic 데이터를 load, 0:4행,survived 컬럼부터 'age'컬럼까지만 추출\n",
    "titanic = sns.load_dataset('titanic')\n",
    "titanic.head(3)\n",
    "\n",
    "df = titanic.loc[0:4,'survived':'age']\n",
    "df\n",
    "\n",
    "#열의 순서를 정렬해서 변경\n",
    "columns = list(df.columns.values)\n",
    "columns\n",
    "columns_sorted = sorted(columns)\n",
    "df = df[columns_sorted]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "96b56f5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['age', 'pclass', 'sex', 'survived']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = titanic.loc[0:4, 'survived':'age']\n",
    "df = df[sorted(list(df.columns.values))]\n",
    "df\n",
    "list(df.columns.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "baeea0d5",
   "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>survived</th>\n",
       "      <th>sex</th>\n",
       "      <th>pclass</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   survived     sex  pclass   age\n",
       "0         0    male       3  22.0\n",
       "1         1  female       1  38.0\n",
       "2         1  female       3  26.0\n",
       "3         1  female       1  35.0\n",
       "4         0    male       3  35.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#열의 순서를 역순으로 정렬해서 변경\n",
    "df_r = df[reversed(df.columns.values)] \n",
    "# df_r = df[sorted(df.columns.values, reverse= True)]\n",
    "df_r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ef91fdd1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "연월일     datetime64[ns]\n",
       "당일종가             int64\n",
       "전일종가             int64\n",
       "시가               int64\n",
       "고가               int64\n",
       "저가               int64\n",
       "거래량              int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 열 분리\n",
    "df = pd.read_excel('./dataset/주가데이터.xlsx')\n",
    "df.head()\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b24eaa33",
   "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>연월일</th>\n",
       "      <th>당일종가</th>\n",
       "      <th>전일종가</th>\n",
       "      <th>시가</th>\n",
       "      <th>고가</th>\n",
       "      <th>저가</th>\n",
       "      <th>거래량</th>\n",
       "      <th>연</th>\n",
       "      <th>월</th>\n",
       "      <th>일</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-07-02</td>\n",
       "      <td>10100</td>\n",
       "      <td>600</td>\n",
       "      <td>10850</td>\n",
       "      <td>10900</td>\n",
       "      <td>10000</td>\n",
       "      <td>137977</td>\n",
       "      <td>2018</td>\n",
       "      <td>07</td>\n",
       "      <td>02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-06-29</td>\n",
       "      <td>10700</td>\n",
       "      <td>300</td>\n",
       "      <td>10550</td>\n",
       "      <td>10900</td>\n",
       "      <td>9990</td>\n",
       "      <td>170253</td>\n",
       "      <td>2018</td>\n",
       "      <td>06</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-06-28</td>\n",
       "      <td>10400</td>\n",
       "      <td>500</td>\n",
       "      <td>10900</td>\n",
       "      <td>10950</td>\n",
       "      <td>10150</td>\n",
       "      <td>155769</td>\n",
       "      <td>2018</td>\n",
       "      <td>06</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>10900</td>\n",
       "      <td>100</td>\n",
       "      <td>10800</td>\n",
       "      <td>11050</td>\n",
       "      <td>10500</td>\n",
       "      <td>133548</td>\n",
       "      <td>2018</td>\n",
       "      <td>06</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-06-26</td>\n",
       "      <td>10800</td>\n",
       "      <td>350</td>\n",
       "      <td>10900</td>\n",
       "      <td>11000</td>\n",
       "      <td>10700</td>\n",
       "      <td>63039</td>\n",
       "      <td>2018</td>\n",
       "      <td>06</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          연월일   당일종가  전일종가     시가     고가     저가     거래량     연   월   일\n",
       "0  2018-07-02  10100   600  10850  10900  10000  137977  2018  07  02\n",
       "1  2018-06-29  10700   300  10550  10900   9990  170253  2018  06  29\n",
       "2  2018-06-28  10400   500  10900  10950  10150  155769  2018  06  28\n",
       "3  2018-06-27  10900   100  10800  11050  10500  133548  2018  06  27\n",
       "4  2018-06-26  10800   350  10900  11000  10700   63039  2018  06  26"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#년월일 컬럼의 데이터타입이 datatime -> 문자로 변경\n",
    "#2018-09-01 -> '-'기준으로 분리, 년,월,일로 분리\n",
    "df['연월일'] = df['연월일'].astype('str')\n",
    "df.dtypes\n",
    "dates = df['연월일'].str.split('-')# 문자열을 '-'기준으로 분리 :리스트\n",
    "dates\n",
    "df['연']=dates.str.get(0)\n",
    "df['월']=dates.str.get(1)\n",
    "df['일']=dates.str.get(2)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "1bc6b9ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 39 entries, 7 to 850\n",
      "Data columns (total 15 columns):\n",
      " #   Column       Non-Null Count  Dtype   \n",
      "---  ------       --------------  -----   \n",
      " 0   survived     39 non-null     int64   \n",
      " 1   pclass       39 non-null     int64   \n",
      " 2   sex          39 non-null     object  \n",
      " 3   age          35 non-null     float64 \n",
      " 4   sibsp        39 non-null     int64   \n",
      " 5   parch        39 non-null     int64   \n",
      " 6   fare         39 non-null     float64 \n",
      " 7   embarked     39 non-null     object  \n",
      " 8   class        39 non-null     category\n",
      " 9   who          39 non-null     object  \n",
      " 10  adult_male   39 non-null     bool    \n",
      " 11  deck         3 non-null      category\n",
      " 12  embark_town  39 non-null     object  \n",
      " 13  alive        39 non-null     object  \n",
      " 14  alone        39 non-null     bool    \n",
      "dtypes: bool(2), category(2), float64(2), int64(4), object(5)\n",
      "memory usage: 4.3+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "survived       39\n",
       "pclass         39\n",
       "sex            39\n",
       "age            35\n",
       "sibsp          39\n",
       "parch          39\n",
       "fare           39\n",
       "embarked       39\n",
       "class          39\n",
       "who            39\n",
       "adult_male     39\n",
       "deck            3\n",
       "embark_town    39\n",
       "alive          39\n",
       "alone          39\n",
       "dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 프로그램 라이브러리에 다른 옵션과 메소드가 있는 것은 더 효율적 활용을\n",
    "# 위해서 이므로 이를 이해하고 기본적 함수에 앞서 활용해야한다.\n",
    "# 프레임워크의 활용은 유사성을 찾고 변형하여 수고를 덜기 위함으로\n",
    "# 검색방식과 적용방식에 관한 숙달을 요한다.\n",
    "# 가독성이 좋아야하며 유지보수가 쉽고, 레퍼런스가 되기 쉽도록 일반화 시키면\n",
    "# 더욱 좋은 자료로써 활용할 수 있다. 따라서 좋은 프로그램을 만들다 보면\n",
    "# 라이브러리나 프레임워크로 스스로 만들어 놓고 쓰게된다.\n",
    "# 용도별 맵 정리 쉽게 활용 가능하도록!! 차이점과 공통점 그리고 최신기술트렌드\n",
    "# ai와 툴로써의 활용\n",
    "# 위계로써 어느 위치에 있는지 알아야 그 용법에 맞게 쓸 수 있다.\n",
    "\n",
    "#mask = (조건식)&(조건식)\n",
    "#df.loc[mask,[]]\n",
    "#df.loc[(조건식)&(조건식),[]]\n",
    "#(조건식)|(조건식)\n",
    "#isin() 메소드 활용\n",
    "#데이터프레임 열 객체.isin(추출 값의 리스트)\n",
    "#타이타닉 함께 탑습한 형제자매수가 3,4,5인 승객만 추출\n",
    "mask1 = titanic['sibsp'] == 3\n",
    "mask2 = titanic['sibsp'] == 4\n",
    "mask3 = titanic['sibsp'] == 5\n",
    "df_sib = titanic[mask1 | mask2 | mask3]\n",
    "# df_sib = titanic[(titanic['sibsp'] == 3)|(titanic['sibsp'] == 4)\\\n",
    "#                  |(titanic['sibsp'] == 5)]\n",
    "df_sib.info()\n",
    "\n",
    "# => isin() 메소드를 활용하여 변경\n",
    "#데이터프레임 열 객체.isin(추출값의 리스트)\n",
    "isin_filter = titanic['sibsp'].isin([3,4,5])\n",
    "sf_sib = titanic[isin_filter]\n",
    "#또는\n",
    "df_isin = titanic[titanic['sibsp'].isin([3,4,5])]\n",
    "df_isin.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "bf5d4c04",
   "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>age</th>\n",
       "      <th>alive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>80.00</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>803</th>\n",
       "      <td>0.42</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       age alive\n",
       "630  80.00   yes\n",
       "803   0.42   yes"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#승객의 나이가 가장 적거나, 많은 나이와 같은 승객만 추출,age 컬럼만\n",
    "titanic.loc[titanic.age.isin([titanic.age.max(),\n",
    "                              titanic.age.min()]),['age','alive']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ef8b454a",
   "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>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>33.0</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>15.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>23.0</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>263.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>24.0</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>263.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>726</th>\n",
       "      <td>30.0</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>21.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      age     sex  sibsp    fare\n",
       "85   33.0  female      3   15.85\n",
       "88   23.0  female      3  263.00\n",
       "341  24.0  female      3  263.00\n",
       "726  30.0  female      3   21.00"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#승객의 나이가 20대에서 60대 사이, 함께 탑승한 형제가 3,4,5인 승객 중\n",
    "#여성 승객의 나이(age),성별(sex),형제수(sibsp),요금(fare)을 출력\n",
    "mask_age = (titanic.age >= 20) & (titanic.age <= 60)\n",
    "mask_sib = titanic.sibsp.isin([3,4,5])\n",
    "mask_female = (titanic.sex == 'female')\n",
    "titanic.loc[mask_age &mask_sib&mask_female,['age','sex','sibsp','fare']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "d48f6ff9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 데이터프레임 만들기\n",
    "df1 = pd.DataFrame({'a': ['a0', 'a1', 'a2', 'a3'],\n",
    "                    'b': ['b0', 'b1', 'b2', 'b3'],\n",
    "                    'c': ['c0', 'c1', 'c2', 'c3']},\n",
    "                    index=[0, 1, 2, 3])\n",
    " \n",
    "df2 = pd.DataFrame({'a': ['a2', 'a3', 'a4', 'a5'],\n",
    "                    'b': ['b2', 'b3', 'b4', 'b5'],\n",
    "                    'c': ['c2', 'c3', 'c4', 'c5'],\n",
    "                    'd': ['d2', 'd3', 'd4', 'd5']},\n",
    "                    index=[2, 3, 4, 5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "11409b1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    a   b   c    d\n",
      "0  a0  b0  c0  NaN\n",
      "1  a1  b1  c1  NaN\n",
      "2  a2  b2  c2  NaN\n",
      "3  a3  b3  c3  NaN\n",
      "2  a2  b2  c2   d2\n",
      "3  a3  b3  c3   d3\n",
      "4  a4  b4  c4   d4\n",
      "5  a5  b5  c5   d5\n",
      "\n",
      "    a   b   c    d\n",
      "0  a0  b0  c0  NaN\n",
      "1  a1  b1  c1  NaN\n",
      "2  a2  b2  c2  NaN\n",
      "3  a3  b3  c3  NaN\n",
      "4  a2  b2  c2   d2\n",
      "5  a3  b3  c3   d3\n",
      "6  a4  b4  c4   d4\n",
      "7  a5  b5  c5   d5\n",
      "\n",
      "     a    b    c    a    b    c    d\n",
      "0   a0   b0   c0  NaN  NaN  NaN  NaN\n",
      "1   a1   b1   c1  NaN  NaN  NaN  NaN\n",
      "2   a2   b2   c2   a2   b2   c2   d2\n",
      "3   a3   b3   c3   a3   b3   c3   d3\n",
      "4  NaN  NaN  NaN   a4   b4   c4   d4\n",
      "5  NaN  NaN  NaN   a5   b5   c5   d5\n",
      "    a   b   c   a   b   c   d\n",
      "2  a2  b2  c2  a2  b2  c2  d2\n",
      "3  a3  b3  c3  a3  b3  c3  d3\n"
     ]
    }
   ],
   "source": [
    "#두 데이터프레임을 합치기 pd.concat(데이터프레임 리스트)\n",
    "print(pd.concat([df1, df2])) #df1 행으로 df2 붙이기\n",
    "print()\n",
    "\n",
    "#기존 인덱스를 무시하고 새로 인덱스 정리 ignore_index=True\n",
    "print(pd.concat([df1, df2],ignore_index=True))\n",
    "print()\n",
    "\n",
    "#데이터프레임을 컬럼으로 붙이기\n",
    "print(pd.concat([df1, df2],axis=1))\n",
    "\n",
    "#데이터프레임을 컬럼으로 붙이기 , join ='inner',인덱스기준으로 '='만 추출\n",
    "print(pd.concat([df1, df2],axis=1,join='inner'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "5fbd4e79",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 시리즈 만들기\n",
    "sr1 = pd.Series(['e0', 'e1', 'e2', 'e3'], name='e')\n",
    "sr2 = pd.Series(['f0', 'f1', 'f2'], name='f', index=[3, 4, 5])\n",
    "sr3 = pd.Series(['g0', 'g1', 'g2', 'g3'], name='g')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "a3870f86",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    a   b   c   e\n",
      "0  a0  b0  c0  e0\n",
      "1  a1  b1  c1  e1\n",
      "2  a2  b2  c2  e2\n",
      "3  a3  b3  c3  e3\n",
      "\n",
      "    a   b   c   d    f\n",
      "2  a2  b2  c2  d2  NaN\n",
      "3  a3  b3  c3  d3   f0\n",
      "4  a4  b4  c4  d4   f1\n",
      "5  a5  b5  c5  d5   f2\n",
      "\n",
      "    e   g\n",
      "0  e0  g0\n",
      "1  e1  g1\n",
      "2  e2  g2\n",
      "3  e3  g3\n",
      "\n",
      "0    e0\n",
      "1    e1\n",
      "2    e2\n",
      "3    e3\n",
      "4    g0\n",
      "5    g1\n",
      "6    g2\n",
      "7    g3\n",
      "dtype: object\n",
      "\n"
     ]
    }
   ],
   "source": [
    " #df1과 sr1을 컬럼 방향으로 연결하기\n",
    "print(pd.concat([df1, sr1],axis=1))\n",
    "print()\n",
    "#df2와 sr2를 컬럼방향으로 연결하기\n",
    "print(pd.concat([df2, sr2],axis=1, sort=True))\n",
    "print()\n",
    "#sr1과 sr3를 컬럼 방향으로 연결하기\n",
    "print(pd.concat([sr1, sr3],axis=1))\n",
    "print()\n",
    "\n",
    "#sr1과 sr3를 행방향으로 연결하기\n",
    "print(pd.concat([sr1, sr3],axis=0,ignore_index=True))\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "4132a774",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['id', 'stock_name', 'value', 'price'], dtype='object')\n",
      "Index(['id', 'name', 'eps', 'bps', 'per', 'pbr'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#merge를 활용하여 데이터프레임 합치기\n",
    "df1 = pd.read_excel('./dataset/stock_price.xlsx')\n",
    "df2 = pd.read_excel('./dataset/stock_valuation.xlsx')\n",
    "\n",
    "print(df1.columns) #<=id,stock_name\n",
    "print(df2.columns) #<=id,name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "9e67c59f",
   "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>id</th>\n",
       "      <th>stock_name</th>\n",
       "      <th>value</th>\n",
       "      <th>price</th>\n",
       "      <th>name</th>\n",
       "      <th>eps</th>\n",
       "      <th>bps</th>\n",
       "      <th>per</th>\n",
       "      <th>pbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>204210</td>\n",
       "      <td>모두투어리츠</td>\n",
       "      <td>3093.333333</td>\n",
       "      <td>3475</td>\n",
       "      <td>모두투어리츠</td>\n",
       "      <td>85.166667</td>\n",
       "      <td>5335</td>\n",
       "      <td>40.802348</td>\n",
       "      <td>0.651359</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id stock_name        value  price    name        eps   bps        per  \\\n",
       "0  204210     모두투어리츠  3093.333333   3475  모두투어리츠  85.166667  5335  40.802348   \n",
       "\n",
       "        pbr  \n",
       "0  0.651359  "
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#기본값 on: 제일 왼쪽칼럼명을 index로써 기준\n",
    "#how='inner' 같은행열만 표현\n",
    "#양쪽의 데이터프레임의 'id'컬럼이 같은 자료만 병합\n",
    "pd.merge(df1,df2) \n",
    "#left인 df1을 기준으로 병합한다.\n",
    "#df1은 모두 출력, df2 없으면 Nan\n",
    "pd.merge(df1,df2,how='left')\n",
    "#right인 df2을 기준으로 병합한다.\n",
    "#df2은 모두 출력, df1 없으면 Nan\n",
    "pd.merge(df1,df2,how='right')\n",
    "#outer : df1, df2모두출력 없으면 Nan\n",
    "pd.merge(df1,df2,how='outer',on='id')# on='key' 양쪽에 있는 컬럼명을 넣음\n",
    "#df1의 'stock_name',df2의 'name' 컬럼의 값이 같은 자료를 병한\n",
    "result=pd.merge(df1,df2,how='left',left_on='stock_name',right_on='name')\n",
    "\n",
    "r_data = result.loc[:,['id_x','id_y','name','value','price','eps']]\n",
    "r_data\n",
    "#df1의 price가 50000미만인 자료와 df2을 결합\n",
    "pd.merge(df1[df1.price < 50000],df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "105cd4bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "result=pd.merge(df1,df2,how='left',left_on='stock_name',right_on='name')\n",
    "result=pd.merge(df1,df2,how='left',on='id') #how뒤 inner,outer,left,right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "a3fb7f6c",
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>eps</th>\n",
       "      <th>bps</th>\n",
       "      <th>per</th>\n",
       "      <th>pbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>204210</td>\n",
       "      <td>모두투어리츠</td>\n",
       "      <td>85.166667</td>\n",
       "      <td>5335</td>\n",
       "      <td>40.802348</td>\n",
       "      <td>0.651359</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id    name        eps   bps        per       pbr\n",
       "8  204210  모두투어리츠  85.166667  5335  40.802348  0.651359"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_name = df1[df1.price < 50000].stock_name\n",
    "df2[df2.name.isin(s_name)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "a359d3a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.merge(df1, df2, how='inner', on = 'key',left_on= '왼쪽자료의 컬럼'\n",
    "#right_on= '왼쪽자료의 컬럼') on : 양쪽의 칼럼명이 같은 경우\n",
    "#left_on= '왼쪽자료의 컬럼' right_on= '오른쪽의 컬럼' : 양쪽의 컬럼명이 다른\n",
    "#how = 'inner' : 교집합 'left' 교집합포함왼쪽 'right' 교집합포함 오른쪽\n",
    "#'outer' = 합집합"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "707b42d8",
   "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>stock_name</th>\n",
       "      <th>value</th>\n",
       "      <th>price</th>\n",
       "      <th>name</th>\n",
       "      <th>eps</th>\n",
       "      <th>bps</th>\n",
       "      <th>per</th>\n",
       "      <th>pbr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130960</th>\n",
       "      <td>CJ E&amp;M</td>\n",
       "      <td>58540.666667</td>\n",
       "      <td>98900</td>\n",
       "      <td>CJ E&amp;M</td>\n",
       "      <td>6301.333333</td>\n",
       "      <td>54068</td>\n",
       "      <td>15.695091</td>\n",
       "      <td>1.829178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139480</th>\n",
       "      <td>이마트</td>\n",
       "      <td>239230.833333</td>\n",
       "      <td>254500</td>\n",
       "      <td>이마트</td>\n",
       "      <td>18268.166667</td>\n",
       "      <td>295780</td>\n",
       "      <td>13.931338</td>\n",
       "      <td>0.860437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145990</th>\n",
       "      <td>삼양사</td>\n",
       "      <td>82750.000000</td>\n",
       "      <td>82000</td>\n",
       "      <td>삼양사</td>\n",
       "      <td>5741.000000</td>\n",
       "      <td>108090</td>\n",
       "      <td>14.283226</td>\n",
       "      <td>0.758627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185750</th>\n",
       "      <td>종근당</td>\n",
       "      <td>40293.666667</td>\n",
       "      <td>100500</td>\n",
       "      <td>종근당</td>\n",
       "      <td>3990.333333</td>\n",
       "      <td>40684</td>\n",
       "      <td>25.185866</td>\n",
       "      <td>2.470259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204210</th>\n",
       "      <td>모두투어리츠</td>\n",
       "      <td>3093.333333</td>\n",
       "      <td>3475</td>\n",
       "      <td>모두투어리츠</td>\n",
       "      <td>85.166667</td>\n",
       "      <td>5335</td>\n",
       "      <td>40.802348</td>\n",
       "      <td>0.651359</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       stock_name          value   price    name           eps     bps  \\\n",
       "id                                                                       \n",
       "130960     CJ E&M   58540.666667   98900  CJ E&M   6301.333333   54068   \n",
       "139480        이마트  239230.833333  254500     이마트  18268.166667  295780   \n",
       "145990        삼양사   82750.000000   82000     삼양사   5741.000000  108090   \n",
       "185750        종근당   40293.666667  100500     종근당   3990.333333   40684   \n",
       "204210     모두투어리츠    3093.333333    3475  모두투어리츠     85.166667    5335   \n",
       "\n",
       "              per       pbr  \n",
       "id                           \n",
       "130960  15.695091  1.829178  \n",
       "139480  13.931338  0.860437  \n",
       "145990  14.283226  0.758627  \n",
       "185750  25.185866  2.470259  \n",
       "204210  40.802348  0.651359  "
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#데이터프레임.join(데이터프레임, how='') : index를 기준으로 병합\n",
    "#데이터프레임의 인덱스가 반드시 설정되어 있어야 함\n",
    "\n",
    "df1 = pd.read_excel('./dataset/stock_price.xlsx',index_col='id')\n",
    "df2 = pd.read_excel('./dataset/stock_valuation.xlsx',index_col='id')\n",
    "\n",
    "df3 = df1.join(df2, how='inner') # how='inner'\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "82073637",
   "metadata": {},
   "outputs": [],
   "source": [
    "#groupby() 그룹연산\n",
    "titanic= sns.load_dataset('titanic')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03658697",
   "metadata": {},
   "outputs": [],
   "source": [
    "#그룹만들기 : df.groupby(기준이 되는 컬럼명): 그룹이 여러개면[,...]\n",
    "#1. 그룹만들기\n",
    "#2. 그룹별 함수 적용\n",
    "#3. 통합한 자료 출력\n",
    "\n",
    "print(\"승객수:\",len(titanic))\n",
    "#선실의 레벨별 승객수를 산출\n",
    "grouped = titanic.groupby('class')\n",
    "\n",
    "#각 그룹별 자료를 출력\n",
    "for key,group in grouped:\n",
    "    print('key : ', key)\n",
    "    print('group의 수 : ',len(group)) # 그룹별 승객 수\n",
    "    print(group.head(3))\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "1ea78041",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "key : First, 승객수 : 216\n",
      "     age     sex  class     fare  survived\n",
      "1   38.0  female  First  71.2833         1\n",
      "3   35.0  female  First  53.1000         1\n",
      "6   54.0    male  First  51.8625         0\n",
      "11  58.0  female  First  26.5500         1\n",
      "23  28.0    male  First  35.5000         1\n",
      "\n",
      "key : Second, 승객수 : 184\n",
      "     age     sex   class     fare  survived\n",
      "9   14.0  female  Second  30.0708         1\n",
      "15  55.0  female  Second  16.0000         1\n",
      "17   NaN    male  Second  13.0000         1\n",
      "20  35.0    male  Second  26.0000         0\n",
      "21  34.0    male  Second  13.0000         1\n",
      "\n",
      "key : Third, 승객수 : 491\n",
      "    age     sex  class     fare  survived\n",
      "0  22.0    male  Third   7.2500         0\n",
      "2  26.0  female  Third   7.9250         1\n",
      "4  35.0    male  Third   8.0500         0\n",
      "5   NaN    male  Third   8.4583         0\n",
      "7   2.0    male  Third  21.0750         0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#['survived','age','sex','class','fare'] : 컬럼만 분리\n",
    "df = titanic.loc[:,['age','sex','class','fare','survived']] \n",
    "# 컬럼명이 나열 순서대로 만들어짐\n",
    "# class를 기준으로 분할\n",
    "grouped = df.groupby('class')\n",
    "\n",
    "for key, group in grouped:\n",
    "    print(\"key : {}, 승객수 : {}\".format(key, len(group)))\n",
    "    print(group.head());print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "865ec89f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 그룹별로 메서드 적용\n",
    "avg = grouped.mean()\n",
    "print(avg)\n",
    "\n",
    "# 그룹 중 한 그룹을 선택하기 : grouped.get_geoup('키')\n",
    "group1 = grouped.get_group('First')\n",
    "group1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "7902a89e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "key : ('First', 'female'), 94명\n",
      "     age     sex  class      fare  survived\n",
      "1   38.0  female  First   71.2833         1\n",
      "3   35.0  female  First   53.1000         1\n",
      "11  58.0  female  First   26.5500         1\n",
      "31   NaN  female  First  146.5208         1\n",
      "52  49.0  female  First   76.7292         1\n",
      "key : ('First', 'male'), 122명\n",
      "     age   sex  class      fare  survived\n",
      "6   54.0  male  First   51.8625         0\n",
      "23  28.0  male  First   35.5000         1\n",
      "27  19.0  male  First  263.0000         0\n",
      "30  40.0  male  First   27.7208         0\n",
      "34  28.0  male  First   82.1708         0\n",
      "key : ('Second', 'female'), 76명\n",
      "     age     sex   class     fare  survived\n",
      "9   14.0  female  Second  30.0708         1\n",
      "15  55.0  female  Second  16.0000         1\n",
      "41  27.0  female  Second  21.0000         0\n",
      "43   3.0  female  Second  41.5792         1\n",
      "53  29.0  female  Second  26.0000         1\n",
      "key : ('Second', 'male'), 108명\n",
      "     age   sex   class  fare  survived\n",
      "17   NaN  male  Second  13.0         1\n",
      "20  35.0  male  Second  26.0         0\n",
      "21  34.0  male  Second  13.0         1\n",
      "33  66.0  male  Second  10.5         0\n",
      "70  32.0  male  Second  10.5         0\n",
      "key : ('Third', 'female'), 144명\n",
      "     age     sex  class     fare  survived\n",
      "2   26.0  female  Third   7.9250         1\n",
      "8   27.0  female  Third  11.1333         1\n",
      "10   4.0  female  Third  16.7000         1\n",
      "14  14.0  female  Third   7.8542         0\n",
      "18  31.0  female  Third  18.0000         0\n",
      "key : ('Third', 'male'), 347명\n",
      "     age   sex  class     fare  survived\n",
      "0   22.0  male  Third   7.2500         0\n",
      "4   35.0  male  Third   8.0500         0\n",
      "5    NaN  male  Third   8.4583         0\n",
      "7    2.0  male  Third  21.0750         0\n",
      "12  20.0  male  Third   8.0500         0\n",
      "      age   sex  class      fare  survived\n",
      "6    54.0  male  First   51.8625         0\n",
      "23   28.0  male  First   35.5000         1\n",
      "27   19.0  male  First  263.0000         0\n",
      "30   40.0  male  First   27.7208         0\n",
      "34   28.0  male  First   82.1708         0\n",
      "..    ...   ...    ...       ...       ...\n",
      "839   NaN  male  First   29.7000         1\n",
      "857  51.0  male  First   26.5500         1\n",
      "867  31.0  male  First   50.4958         0\n",
      "872  33.0  male  First    5.0000         0\n",
      "889  26.0  male  First   30.0000         1\n",
      "\n",
      "[122 rows x 5 columns]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "class   sex   \n",
       "First   female    63.0\n",
       "        male      80.0\n",
       "Second  female    57.0\n",
       "        male      70.0\n",
       "Third   female    63.0\n",
       "        male      74.0\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 그룹을 class, sex로 그룹을 지어\n",
    "group1 = df.groupby(['class','sex'])\n",
    "# 각 그룹의 키와 그룹의 인원 수, 그룹의 자료를 출력\n",
    "for key, group in group1:\n",
    "    print(\"key : {}, {}명\".format(key, len(group)))\n",
    "    print(group.head())\n",
    "# 'First', 'male'의 그룹 자료만 추출\n",
    "group_Fm = group1.get_group(('First','male'))\n",
    "print(group_Fm)\n",
    "# 각 그룹별 가장 큰 값을 출력 -> 그룹별 가장 많은 나이\n",
    "# max_values = group1.max() \n",
    "max_values = group1.age.max()\n",
    "max_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "fe596ba1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "class\n",
      "First     78.38\n",
      "Second    13.42\n",
      "Third     11.78\n",
      "Name: fare, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "class\n",
       "First     78.380373\n",
       "Second    13.417399\n",
       "Third     11.778142\n",
       "Name: fare, dtype: float64"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#그룹연산 메소드 데이터 집계(aggreagation)\n",
    "#class로 그룹을 지어 fare 컬럼만 std()표준편차 구함\n",
    "grouped = df.groupby('class')\n",
    "print(grouped.fare.std().round(2)) #각 그룹의 원하는 컬럼 추출 후 함수적용 \n",
    "#1회만 실행\n",
    "grouped.std().fare# 각 그룹에 함주적요 후 원하는 컬럼추출\n",
    "# 그룹이 3개일 시 3회실행"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "fc7f5f75",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age            fare      survived    \n",
      "         max   min       max  min      max min\n",
      "class                                         \n",
      "First   80.0  0.92  512.3292  0.0        1   0\n",
      "Second  70.0  0.67   73.5000  0.0        1   0\n",
      "Third   74.0  0.42   69.5500  0.0        1   0\n"
     ]
    },
    {
     "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th colspan=\"2\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>First</th>\n",
       "      <td>38.233441</td>\n",
       "      <td>0.0</td>\n",
       "      <td>512.3292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>29.877630</td>\n",
       "      <td>0.0</td>\n",
       "      <td>73.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>25.140620</td>\n",
       "      <td>0.0</td>\n",
       "      <td>69.5500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              age fare          \n",
       "             mean  min       max\n",
       "class                           \n",
       "First   38.233441  0.0  512.3292\n",
       "Second  29.877630  0.0   73.5000\n",
       "Third   25.140620  0.0   69.5500"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#집계연산을 처리 -> 사용자 함수를 그룹객체에 적용\n",
    "df = titanic.loc[:,['age','class','fare','survived']] \n",
    "grouped = df.groupby('class')\n",
    "#group 객체.agg(매핑 함수) 함수가 여러개일 겨우 list\n",
    "def max_min(x):\n",
    "    return x.max()-x.min()\n",
    "#grouped 객체에 max_min함수를 적용\n",
    "grouped.agg(max_min)\n",
    "# grouped.max()\n",
    "# grouped.min()\n",
    "\n",
    "#모든 열에 여러 함수를 매핑 : 그룹객체.agg([함수1, 함수2,...])\n",
    "#각 열마다 다른 함수를 매핑 : 그룹객체.agg({열1:함수1,열2:함수2,...})\n",
    "agg_all = grouped.agg(['max','min']) # 함수 str 값으로\n",
    "print(agg_all)\n",
    "\n",
    "agg_sep = grouped.agg({'age':'mean','fare':['min','max']})\n",
    "agg_sep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "fe0e6779",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "class\n",
      "First     38.233441\n",
      "Second    29.877630\n",
      "Third     25.140620\n",
      "Name: age, dtype: float64\n",
      "------\n",
      "class\n",
      "First     14.802856\n",
      "Second    14.001077\n",
      "Third     12.495398\n",
      "Name: age, dtype: float64\n",
      "------\n",
      "origin :  First\n",
      "------\n",
      "1   -0.015770\n",
      "3   -0.218434\n",
      "6    1.065103\n",
      "Name: age, dtype: float64\n",
      "origin :  Second\n",
      "------\n",
      "9    -1.134029\n",
      "15    1.794317\n",
      "17         NaN\n",
      "Name: age, dtype: float64\n",
      "origin :  Third\n",
      "------\n",
      "0   -0.251342\n",
      "2    0.068776\n",
      "4    0.789041\n",
      "Name: age, dtype: float64\n",
      "---위 로직을 아래처럼 간편하게---\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0   -0.251342\n",
       "1   -0.015770\n",
       "2    0.068776\n",
       "3   -0.218434\n",
       "4    0.789041\n",
       "5         NaN\n",
       "6    1.065103\n",
       "7   -1.851931\n",
       "8    0.148805\n",
       "9   -1.134029\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#agg(),transform() = 각 그룹별 데이터에 함수적용, 각 원소에 함수적용\n",
    "#그룹객체.transform(매핑함수) : 각 그룹의 행에 대한 연산\n",
    "age_mean = grouped.age.mean() # 그룹별 'age'컬럼의 평균 \n",
    "type(age_mean)\n",
    "print(age_mean) #그룹의 key가 인덱스로 설정되어 있음\n",
    "print('------')\n",
    "age_std = grouped.age.std() # 그룹별 'age'컬럼의 표준편차\n",
    "print(age_std)\n",
    "print('------')\n",
    "\n",
    "# 그룹 객체의 age 컬럼에 대해 zscore 계산하여 출력\n",
    "for key, group in grouped.age:\n",
    "    group_zscore = (group - age_mean.loc[key]) / age_std.loc[key]\n",
    "    print(\"origin : \", key)\n",
    "    print('------')\n",
    "    print(group_zscore.head(3))\n",
    "print('---위 로직을 아래처럼 간편하게---')  \n",
    "def z_score(x):\n",
    "    return((x-x.mean())/x.std())\n",
    "age_zscore = grouped.age.transform(z_score)\n",
    "age_zscore.loc[0:9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20e6d343",
   "metadata": {},
   "outputs": [],
   "source": [
    "#그룹 객체 필터링: 그룹객체.filter(조건식 함수) map(일반함수) filter(조건함수)\n",
    "#class로 그룹을 지은 후 그룹별 데이터의 개수가 200이상인 그룹만 추출\n",
    "grouped_filter = grouped.filter(lambda x: len(x) >= 200)\n",
    "print(grouped_filter)\n",
    "grouped_filter['class'].unique()\n",
    "\n",
    "# 'age'컬럼의 평균값이 30세보다 적은 그룹만 선택\n",
    "age_filter = grouped.filter(lambda x: x.age.mean() < 30)\n",
    "age_filter['class'].unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "33f363da",
   "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></th>\n",
       "      <th>age</th>\n",
       "      <th>fare</th>\n",
       "      <th>survived</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">First</th>\n",
       "      <th>count</th>\n",
       "      <td>186.000000</td>\n",
       "      <td>216.000000</td>\n",
       "      <td>216.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>38.233441</td>\n",
       "      <td>84.154687</td>\n",
       "      <td>0.629630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>14.802856</td>\n",
       "      <td>78.380373</td>\n",
       "      <td>0.484026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.920000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>27.000000</td>\n",
       "      <td>30.923950</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>37.000000</td>\n",
       "      <td>60.287500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>49.000000</td>\n",
       "      <td>93.500000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>80.000000</td>\n",
       "      <td>512.329200</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Second</th>\n",
       "      <th>count</th>\n",
       "      <td>173.000000</td>\n",
       "      <td>184.000000</td>\n",
       "      <td>184.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>29.877630</td>\n",
       "      <td>20.662183</td>\n",
       "      <td>0.472826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>14.001077</td>\n",
       "      <td>13.417399</td>\n",
       "      <td>0.500623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.670000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>23.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>29.000000</td>\n",
       "      <td>14.250000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>36.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>70.000000</td>\n",
       "      <td>73.500000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Third</th>\n",
       "      <th>count</th>\n",
       "      <td>355.000000</td>\n",
       "      <td>491.000000</td>\n",
       "      <td>491.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>25.140620</td>\n",
       "      <td>13.675550</td>\n",
       "      <td>0.242363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.495398</td>\n",
       "      <td>11.778142</td>\n",
       "      <td>0.428949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.420000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>18.000000</td>\n",
       "      <td>7.750000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>24.000000</td>\n",
       "      <td>8.050000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>32.000000</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>74.000000</td>\n",
       "      <td>69.550000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     age        fare    survived\n",
       "class                                           \n",
       "First  count  186.000000  216.000000  216.000000\n",
       "       mean    38.233441   84.154687    0.629630\n",
       "       std     14.802856   78.380373    0.484026\n",
       "       min      0.920000    0.000000    0.000000\n",
       "       25%     27.000000   30.923950    0.000000\n",
       "       50%     37.000000   60.287500    1.000000\n",
       "       75%     49.000000   93.500000    1.000000\n",
       "       max     80.000000  512.329200    1.000000\n",
       "Second count  173.000000  184.000000  184.000000\n",
       "       mean    29.877630   20.662183    0.472826\n",
       "       std     14.001077   13.417399    0.500623\n",
       "       min      0.670000    0.000000    0.000000\n",
       "       25%     23.000000   13.000000    0.000000\n",
       "       50%     29.000000   14.250000    0.000000\n",
       "       75%     36.000000   26.000000    1.000000\n",
       "       max     70.000000   73.500000    1.000000\n",
       "Third  count  355.000000  491.000000  491.000000\n",
       "       mean    25.140620   13.675550    0.242363\n",
       "       std     12.495398   11.778142    0.428949\n",
       "       min      0.420000    0.000000    0.000000\n",
       "       25%     18.000000    7.750000    0.000000\n",
       "       50%     24.000000    8.050000    0.000000\n",
       "       75%     32.000000   15.500000    0.000000\n",
       "       max     74.000000   69.550000    1.000000"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#그룹 객체에 함수 매핑 : 그룹객체.apply(매핑함수)\n",
    "agg_grouped =grouped.apply(lambda x:x.describe())\n",
    "agg_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cef61a0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#그룹함수, min(), max(),std(),var(),mean(),describe(),...\n",
    "# grouped = df.groupby(컬럼명)\n",
    "# grouped.그룹함수()\n",
    "# grouped.컬럼.그룹함수\n",
    "# grouped.agg([함수1,...])\n",
    "# grouped.agg({컬럼:함수,컬럼1:함수1:,...})\n",
    "# grouped.컬럼.transform(매핑함수) #\n",
    "# grouped.filter(조건식 함수)\n",
    "# grouped.apply(함수 매핑)\n",
    "# grouped.컬럼.apply(함수 매핑)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "a20161a1",
   "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></th>\n",
       "      <th>age</th>\n",
       "      <th>fare</th>\n",
       "      <th>survived</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">First</th>\n",
       "      <th>female</th>\n",
       "      <td>34.611765</td>\n",
       "      <td>106.125798</td>\n",
       "      <td>0.968085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>41.281386</td>\n",
       "      <td>67.226127</td>\n",
       "      <td>0.368852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Second</th>\n",
       "      <th>female</th>\n",
       "      <td>28.722973</td>\n",
       "      <td>21.970121</td>\n",
       "      <td>0.921053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>30.740707</td>\n",
       "      <td>19.741782</td>\n",
       "      <td>0.157407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Third</th>\n",
       "      <th>female</th>\n",
       "      <td>21.750000</td>\n",
       "      <td>16.118810</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>26.507589</td>\n",
       "      <td>12.661633</td>\n",
       "      <td>0.135447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     age        fare  survived\n",
       "class  sex                                    \n",
       "First  female  34.611765  106.125798  0.968085\n",
       "       male    41.281386   67.226127  0.368852\n",
       "Second female  28.722973   21.970121  0.921053\n",
       "       male    30.740707   19.741782  0.157407\n",
       "Third  female  21.750000   16.118810  0.500000\n",
       "       male    26.507589   12.661633  0.135447"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#class와 sex컬럼으로 그룹을 지어 데이터를 가져옴\n",
    "#['age','class','sex','fare','survived']\n",
    "df = titanic.loc[:,['age','class','sex','fare','survived']]\n",
    "df.head()\n",
    "\n",
    "grouped = df.groupby(['class','sex'])\n",
    "gdf = grouped.mean()\n",
    "gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "2ef04d0b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'> MultiIndex([( 'First', 'female'),\n",
      "            ( 'First',   'male'),\n",
      "            ('Second', 'female'),\n",
      "            ('Second',   'male'),\n",
      "            ( 'Third', 'female'),\n",
      "            ( 'Third',   'male')],\n",
      "           names=['class', 'sex'])\n"
     ]
    },
    {
     "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>age</th>\n",
       "      <th>fare</th>\n",
       "      <th>survived</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>First</th>\n",
       "      <td>41.281386</td>\n",
       "      <td>67.226127</td>\n",
       "      <td>0.368852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>30.740707</td>\n",
       "      <td>19.741782</td>\n",
       "      <td>0.157407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>26.507589</td>\n",
       "      <td>12.661633</td>\n",
       "      <td>0.135447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              age       fare  survived\n",
       "class                                 \n",
       "First   41.281386  67.226127  0.368852\n",
       "Second  30.740707  19.741782  0.157407\n",
       "Third   26.507589  12.661633  0.135447"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#멀티 인덱스로 자료를 선택\n",
    "print(type(gdf),gdf.index)\n",
    "#First와 male의 자료만 검색 list[수정가능]와 tuple(수정불가)\n",
    "gdf.loc[('First','male')]\n",
    "#df.xs() 함수를 활용하여 데이터선택\n",
    "gdf.loc['First'] # 처음 인덱스의 자료 검색\n",
    "gdf.loc[[('First','male'),('Second','male'),('Third','male')]]\n",
    "# 처음 인덱스가 아닌 다른 인덱스를 활용하여 데이터 검색\n",
    "gdf.xs('male', level ='sex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "336c618e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf1 = pd.pivot_table(df,\n",
    "                     index = 'class', #행 위치에 들어갈 컬럼\n",
    "                      columns = 'sex', #열 위치에\n",
    "                      values = 'age', #데이터로 사용할 컬럼\n",
    "                      aggfunc = 'mean' # 사용할 함수명\n",
    "                     )\n",
    "pdf1\n",
    "print(pdf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "58870631",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1], dtype=int64)"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.survived.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "61dbd855",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     age                   fare            \n",
      "survived               0          1           0           1\n",
      "class  sex                                                 \n",
      "First  female  25.666667  34.939024  110.604167  105.978159\n",
      "       male    44.581967  36.248000   62.894910   74.637320\n",
      "Second female  36.000000  28.080882   18.250000   22.288989\n",
      "       male    33.369048  16.022000   19.488965   21.095100\n",
      "Third  female  23.818182  19.329787   19.773093   12.464526\n",
      "       male    27.255814  22.274211   12.204469   15.579696\n",
      "------\n",
      "                  mean                                 sum                     \\\n",
      "                   age               fare              age               fare   \n",
      "survived             0       1          0        1       0       1          0   \n",
      "class  sex                                                                      \n",
      "Second male  33.369048  16.022  19.488965  21.0951  2803.0  240.33  1773.4958   \n",
      "\n",
      "                       \n",
      "                       \n",
      "survived            1  \n",
      "class  sex             \n",
      "Second male  358.6167  \n",
      "------\n",
      "survived               0          1\n",
      "class  sex                         \n",
      "First  female  25.666667  34.939024\n",
      "       male    44.581967  36.248000\n",
      "Second female  36.000000  28.080882\n",
      "       male    33.369048  16.022000\n",
      "Third  female  23.818182  19.329787\n",
      "       male    27.255814  22.274211\n"
     ]
    }
   ],
   "source": [
    "# values : 'age', 'fare'\n",
    "# aggfunc : 'mean','sum'\n",
    "pdf2 = pd.pivot_table(df,\n",
    "                     index = ['class','sex'],#행 위치에 들어갈 컬럼\n",
    "                      columns = 'survived', #열 위치에\n",
    "                      values = ['age','fare'], #데이터로 사용할 컬럼\n",
    "                      aggfunc = ['mean','sum'] # 사용할 함수명\n",
    "                     )\n",
    "pdf2\n",
    "print(pdf2.xs('mean',axis=1)) \n",
    "print('------')\n",
    "print(pdf2.xs(('Second','male'),level=[0,'sex'])) #여러개 일때 tuple,[]형식\n",
    "print('------')\n",
    "print(pdf2.xs(('mean','age'),axis=1))"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}