data_zscore.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "59055e18-a5bc-45cd-a71e-22c6a552bd29",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in /opt/homebrew/Caskroom/miniconda/base/lib/python3.12/site-packages (3.1.5)\n",
      "Requirement already satisfied: et-xmlfile in /opt/homebrew/Caskroom/miniconda/base/lib/python3.12/site-packages (from openpyxl) (2.0.0)\n"
     ]
    }
   ],
   "source": [
    "!pip3 install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 366,
   "id": "e0db4cd7-96a3-4f55-8d1d-d4e5ce08e105",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import requests\n",
    "FIREBASE_URL = \"https://childmonitoring-951c3-default-rtdb.firebaseio.com/Users.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 427,
   "id": "625ba2fe-550e-49f2-ab32-a05693c2f746",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data pushed successfully: {'bharani': {'age': '83', 'gender': 'girl', 'height': '141', 'id': '4', 'mail': 'bharani@gmail.com', 'name': 'Bharani', 'password': 'Bha@210457', 'weight': '28'}, 'goutham': {'age': '78', 'gender': 'boy', 'height': '110', 'id': '2', 'mail': 'goutham@gmail.com', 'name': 'goutham', 'password': 'G@210457', 'weight': '20'}, 'john123': {'age': '65', 'gender': 'boy', 'height': '130', 'id': '3', 'mail': 'john123@gmail.com', 'name': 'John', 'password': 'pass456', 'weight': '19'}}\n"
     ]
    }
   ],
   "source": [
    "data = {\n",
    "    'goutham': {\n",
    "        'age': '78',\n",
    "        'id':'2',\n",
    "        'gender':'boy',\n",
    "        'height': '110',\n",
    "        'mail': 'goutham@gmail.com',\n",
    "        'name': 'goutham',\n",
    "        'password': 'G@210457',\n",
    "        'weight': '20'\n",
    "    },\n",
    "    'john123': {\n",
    "        'age': '65',\n",
    "        'id':'3',\n",
    "        'gender':'boy',\n",
    "        'height': '130',\n",
    "        'mail': 'john123@gmail.com',\n",
    "        'name': 'John',\n",
    "        'password': 'pass456',\n",
    "        'weight': '19'\n",
    "    },\n",
    "    'bharani': {\n",
    "        'age': '83',\n",
    "        'id':'4',\n",
    "        'gender':'girl',\n",
    "        'height': '141',\n",
    "        'mail': 'bharani@gmail.com',\n",
    "        'name': 'Bharani',\n",
    "        'password': 'Bha@210457',\n",
    "        'weight': '28'\n",
    "    }\n",
    "}\n",
    "\n",
    "# Sending the data to Firebase\n",
    "response = requests.put(FIREBASE_URL, json=data)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    print(\"Data pushed successfully:\", response.json())\n",
    "else:\n",
    "    print(\"Error:\", response.status_code, response.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 428,
   "id": "c44682aa-9a72-4d7d-b8bb-503c466e387b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Retrieved data: {'bharani': {'age': '83', 'gender': 'girl', 'height': '141', 'id': '4', 'mail': 'bharani@gmail.com', 'name': 'Bharani', 'password': 'Bha@210457', 'weight': '28'}, 'goutham': {'age': '78', 'gender': 'boy', 'height': '110', 'id': '2', 'mail': 'goutham@gmail.com', 'name': 'goutham', 'password': 'G@210457', 'weight': '20'}, 'john123': {'age': '65', 'gender': 'boy', 'height': '130', 'id': '3', 'mail': 'john123@gmail.com', 'name': 'John', 'password': 'pass456', 'weight': '19'}}\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Your Firebase Realtime Database URL\n",
    "\n",
    "response = requests.get(FIREBASE_URL)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "    print(\"Retrieved data:\", data)\n",
    "else:\n",
    "    print(\"Error:\", response.status_code, response.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "id": "8e35edb8-8f01-4de2-b1c6-11137d2ec170",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "id": "4fcbcd69-1a11-4445-9292-d5124f90ee89",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    bharani            goutham            john123\n",
      "age                      83                 78                 65\n",
      "gender                 girl                boy                boy\n",
      "height                  141                110                130\n",
      "id                        4                  2                  3\n",
      "mail      bharani@gmail.com  goutham@gmail.com  john123@gmail.com\n",
      "name                Bharani            goutham               John\n",
      "password         Bha@210457           G@210457            pass456\n",
      "weight                   28                 20                 19\n"
     ]
    }
   ],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "id": "4b85d00d-d8cd-4604-8bd0-a014c2697d34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age gender  height id               mail     name    password  \\\n",
      "bharani   83   girl     141  4  bharani@gmail.com  Bharani  Bha@210457   \n",
      "goutham   78    boy     110  2  goutham@gmail.com  goutham    G@210457   \n",
      "john123   65    boy     130  3  john123@gmail.com     John     pass456   \n",
      "\n",
      "         weight    BMI  \n",
      "bharani      28  14.08  \n",
      "goutham      20  16.53  \n",
      "john123      19  11.24  \n"
     ]
    }
   ],
   "source": [
    "df = df.T\n",
    "df[\"age\"] = pd.to_numeric(df[\"age\"])\n",
    "df[\"height\"] = pd.to_numeric(df[\"height\"])\n",
    "df[\"weight\"] = pd.to_numeric(df[\"weight\"])\n",
    "df[\"BMI\"] = round((df[\"weight\"]*10000)/(df[\"height\"]**2),2)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 435,
   "id": "006cfdb3-fd83-48c4-8588-58433c5a2729",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_boy = df[df['gender'] == 'boy']\n",
    "df_girl = df[df['gender'] == 'girl']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 436,
   "id": "c6070e28-5baf-469c-b858-fab870ded9f9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "boy_data\n",
      "         age gender  height id               mail     name  password  weight  \\\n",
      "goutham   78    boy     110  2  goutham@gmail.com  goutham  G@210457      20   \n",
      "john123   65    boy     130  3  john123@gmail.com     John   pass456      19   \n",
      "\n",
      "           BMI  \n",
      "goutham  16.53  \n",
      "john123  11.24  \n",
      "girl_data\n",
      "         age gender  height id               mail     name    password  \\\n",
      "bharani   83   girl     141  4  bharani@gmail.com  Bharani  Bha@210457   \n",
      "\n",
      "         weight    BMI  \n",
      "bharani      28  14.08  \n"
     ]
    }
   ],
   "source": [
    "print('boy_data')\n",
    "print(df_boy)\n",
    "print('girl_data')\n",
    "print(df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 437,
   "id": "364efee9-0817-4b4b-ae87-0eb998134161",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     L         M        S   StDev   SD5neg   SD4neg   SD3neg   SD2neg  \\\n",
      "0    1  110.2647  0.04164  4.5914   87.308   91.899   96.490  101.082   \n",
      "1    1  110.8006  0.04172  4.6226   87.688   92.310   96.933  101.555   \n",
      "2    1  111.3338  0.04180  4.6538   88.065   92.719   97.373  102.026   \n",
      "3    1  111.8636  0.04187  4.6837   88.445   93.129   97.812  102.496   \n",
      "4    1  112.3895  0.04195  4.7147   88.816   93.531   98.245  102.960   \n",
      "..  ..       ...      ...     ...      ...      ...      ...      ...   \n",
      "163  1  176.4453  0.04168  7.3542  139.674  147.028  154.383  161.737   \n",
      "164  1  176.4724  0.04159  7.3395  139.775  147.114  154.454  161.793   \n",
      "165  1  176.4976  0.04150  7.3247  139.874  147.199  154.524  161.848   \n",
      "166  1  176.5211  0.04142  7.3115  139.964  147.275  154.587  161.898   \n",
      "167  1  176.5432  0.04134  7.2983  140.052  147.350  154.648  161.947   \n",
      "\n",
      "      SD1neg      SD0      SD1      SD2      SD3      SD4  age  \n",
      "0    105.673  110.265  114.856  119.448  124.039  128.630   61  \n",
      "1    106.178  110.801  115.423  120.046  124.668  129.291   62  \n",
      "2    106.680  111.334  115.988  120.641  125.295  129.949   63  \n",
      "3    107.180  111.864  116.547  121.231  125.915  130.599   64  \n",
      "4    107.675  112.390  117.104  121.819  126.534  131.248   65  \n",
      "..       ...      ...      ...      ...      ...      ...  ...  \n",
      "163  169.091  176.445  183.800  191.154  198.508  205.862  224  \n",
      "164  169.133  176.472  183.812  191.151  198.491  205.830  225  \n",
      "165  169.173  176.498  183.822  191.147  198.472  205.796  226  \n",
      "166  169.210  176.521  183.833  191.144  198.456  205.767  227  \n",
      "167  169.245  176.543  183.841  191.140  198.438  205.736  228  \n",
      "\n",
      "[168 rows x 15 columns]\n"
     ]
    }
   ],
   "source": [
    "bh = pd.read_excel(\"HFA_Boys.xlsx\")\n",
    "bh['age']= bh['Month']\n",
    "bh = bh.drop(\"Month\",axis = 1)\n",
    "print(bh)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 438,
   "id": "12c8c3fc-4bc4-4033-8331-b765676c1517",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age gender  height id               mail     name  password  weight  \\\n",
      "goutham   78    boy     110  2  goutham@gmail.com  goutham  G@210457      20   \n",
      "john123   65    boy     130  3  john123@gmail.com     John   pass456      19   \n",
      "\n",
      "           BMI  \n",
      "goutham  16.53  \n",
      "john123  11.24  \n"
     ]
    }
   ],
   "source": [
    "print(df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 439,
   "id": "640fac8e-f43c-4bc9-ae85-a5792fb44a03",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  height  L         M        S   StDev  SD5neg  SD4neg  \\\n",
      "goutham   78  2     110  1  118.8700  0.04295  5.1055  93.343  98.448   \n",
      "john123   65  3     130  1  112.3895  0.04195  4.7147  88.816  93.531   \n",
      "\n",
      "          SD3neg   SD2neg   SD1neg     SD0      SD1      SD2      SD3  \\\n",
      "goutham  103.554  108.659  113.765  118.87  123.975  129.081  134.186   \n",
      "john123   98.245  102.960  107.675  112.39  117.104  121.819  126.534   \n",
      "\n",
      "             SD4        HZ  \n",
      "goutham  139.292 -1.737353  \n",
      "john123  131.248  3.735201  \n"
     ]
    }
   ],
   "source": [
    "height_df_boy = pd.DataFrame()\n",
    "height_df_boy['age'] = df_boy['age']\n",
    "height_df_boy['id'] = df_boy['id']\n",
    "height_df_boy['height'] = df_boy['height']\n",
    "merged_bh = pd.merge(height_df_boy, bh, left_on='age', right_on='age', how='left')\n",
    "merged_bh.set_index(df_boy.index, inplace=True)\n",
    "# print(merged_bh)\n",
    "# Calculate L * M * S and create a new column in df_boy\n",
    "merged_bh['HZ'] = ((merged_bh['height']/merged_bh['M'])**merged_bh['L'] -1)/(merged_bh['L'] * merged_bh['S'])\n",
    "# print(merged_df_boy)\n",
    "# print(merged_df_boy['HZ'])\n",
    "# Now you can use merged_df_boy to see the updated DataFrame\n",
    "height_df_boy = merged_bh\n",
    "\n",
    "# Show the updated df_boy with the new column\n",
    "print(height_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 440,
   "id": "1dba4920-a653-4e7f-a7b5-d9dd4c3a734d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  weight\n",
      "goutham   78  2      20\n",
      "john123   65  3      19\n"
     ]
    }
   ],
   "source": [
    "weight_df_boy = pd.DataFrame()\n",
    "weight_df_boy[['age','id','weight']] = df_boy[['age','id','weight']]\n",
    "print(weight_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 441,
   "id": "5546b844-98eb-477b-9df0-1e3c882d4c65",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         L        M        S  SD4neg  SD3neg  SD2neg  SD1neg     SD0     SD1  \\\n",
      "0  -0.2026  18.5057  0.12988  11.070  12.718  14.367  16.279  18.506  21.109   \n",
      "1  -0.2130  18.6802  0.13028  11.171  12.833  14.496  16.428  18.680  21.319   \n",
      "2  -0.2234  18.8563  0.13067  11.272  12.950  14.627  16.578  18.856  21.530   \n",
      "3  -0.2338  19.0340  0.13105  11.376  13.067  14.759  16.729  19.034  21.744   \n",
      "4  -0.2443  19.2132  0.13142  11.480  13.186  14.892  16.882  19.213  21.959   \n",
      "5  -0.2548  19.3940  0.13178  11.587  13.307  15.027  17.036  19.394  22.176   \n",
      "6  -0.2653  19.5765  0.13213  11.694  13.429  15.164  17.192  19.576  22.395   \n",
      "7  -0.2758  19.7607  0.13246  11.804  13.553  15.302  17.350  19.761  22.615   \n",
      "8  -0.2864  19.9468  0.13279  11.915  13.679  15.442  17.509  19.947  22.839   \n",
      "9  -0.2969  20.1344  0.13311  12.027  13.805  15.583  17.670  20.134  23.063   \n",
      "10 -0.3075  20.3235  0.13342  12.141  13.934  15.726  17.832  20.324  23.290   \n",
      "11 -0.3180  20.5137  0.13372  12.256  14.063  15.870  17.996  20.514  23.517   \n",
      "12 -0.3285  20.7052  0.13402  12.371  14.193  16.015  18.160  20.705  23.747   \n",
      "13 -0.3390  20.8979  0.13432  12.488  14.324  16.160  18.326  20.898  23.978   \n",
      "14 -0.3494  21.0918  0.13462  12.605  14.456  16.307  18.492  21.092  24.210   \n",
      "15 -0.3598  21.2870  0.13493  12.722  14.588  16.454  18.659  21.287  24.445   \n",
      "16 -0.3701  21.4833  0.13523  12.841  14.721  16.602  18.828  21.483  24.680   \n",
      "17 -0.3804  21.6810  0.13554  12.960  14.855  16.751  18.997  21.681  24.918   \n",
      "18 -0.3906  21.8799  0.13586  13.079  14.990  16.900  19.167  21.880  25.158   \n",
      "19 -0.4007  22.0800  0.13618  13.199  15.125  17.050  19.338  22.080  25.399   \n",
      "20 -0.4107  22.2813  0.13652  13.318  15.259  17.201  19.510  22.281  25.642   \n",
      "21 -0.4207  22.4837  0.13686  13.438  15.395  17.352  19.682  22.484  25.887   \n",
      "22 -0.4305  22.6872  0.13722  13.558  15.531  17.503  19.855  22.687  26.134   \n",
      "23 -0.4402  22.8915  0.13759  13.677  15.666  17.655  20.029  22.892  26.382   \n",
      "24 -0.4499  23.0968  0.13797  13.797  15.802  17.807  20.203  23.097  26.632   \n",
      "25 -0.4594  23.3029  0.13838  13.915  15.937  17.958  20.377  23.303  26.885   \n",
      "26 -0.4688  23.5101  0.13880  14.033  16.072  18.110  20.552  23.510  27.138   \n",
      "27 -0.4781  23.7182  0.13923  14.151  16.207  18.262  20.727  23.718  27.394   \n",
      "28 -0.4873  23.9272  0.13969  14.268  16.341  18.414  20.903  23.927  27.652   \n",
      "29 -0.4964  24.1371  0.14016  14.385  16.475  18.565  21.078  24.137  27.911   \n",
      "30 -0.5053  24.3479  0.14065  14.502  16.609  18.717  21.255  24.348  28.172   \n",
      "31 -0.5142  24.5595  0.14117  14.617  16.742  18.868  21.431  24.560  28.436   \n",
      "32 -0.5229  24.7722  0.14170  14.732  16.875  19.019  21.607  24.772  28.701   \n",
      "33 -0.5315  24.9858  0.14226  14.845  17.008  19.170  21.784  24.986  28.969   \n",
      "34 -0.5399  25.2005  0.14284  14.958  17.140  19.321  21.961  25.200  29.239   \n",
      "35 -0.5482  25.4163  0.14344  15.071  17.271  19.472  22.138  25.416  29.512   \n",
      "36 -0.5564  25.6332  0.14407  15.182  17.402  19.622  22.316  25.633  29.787   \n",
      "37 -0.5644  25.8513  0.14472  15.292  17.532  19.772  22.494  25.851  30.064   \n",
      "38 -0.5722  26.0706  0.14539  15.402  17.662  19.922  22.672  26.071  30.344   \n",
      "39 -0.5799  26.2911  0.14608  15.511  17.792  20.072  22.851  26.291  30.627   \n",
      "40 -0.5873  26.5128  0.14679  15.620  17.921  20.222  23.031  26.513  30.912   \n",
      "41 -0.5946  26.7358  0.14752  15.727  18.050  20.372  23.210  26.736  31.199   \n",
      "42 -0.6017  26.9602  0.14828  15.834  18.178  20.522  23.390  26.960  31.490   \n",
      "43 -0.6085  27.1861  0.14905  15.940  18.306  20.672  23.571  27.186  31.784   \n",
      "44 -0.6152  27.4137  0.14984  16.045  18.433  20.822  23.753  27.414  32.080   \n",
      "45 -0.6216  27.6432  0.15066  16.150  18.561  20.972  23.936  27.643  32.381   \n",
      "46 -0.6278  27.8750  0.15149  16.254  18.689  21.123  24.119  27.875  32.685   \n",
      "47 -0.6337  28.1092  0.15233  16.359  18.817  21.275  24.305  28.109  32.993   \n",
      "48 -0.6393  28.3459  0.15319  16.463  18.945  21.428  24.492  28.346  33.305   \n",
      "49 -0.6446  28.5854  0.15406  16.568  19.075  21.582  24.681  28.585  33.621   \n",
      "50 -0.6496  28.8277  0.15493  16.673  19.205  21.737  24.871  28.828  33.941   \n",
      "51 -0.6543  29.0731  0.15581  16.778  19.336  21.894  25.064  29.073  34.266   \n",
      "52 -0.6585  29.3217  0.15670  16.884  19.468  22.052  25.259  29.322  34.595   \n",
      "53 -0.6624  29.5736  0.15760  16.990  19.601  22.212  25.457  29.574  34.929   \n",
      "54 -0.6659  29.8289  0.15850  17.096  19.735  22.373  25.656  29.829  35.268   \n",
      "55 -0.6689  30.0877  0.15940  17.203  19.870  22.537  25.859  30.088  35.612   \n",
      "56 -0.6714  30.3501  0.16031  17.310  20.006  22.701  26.064  30.350  35.960   \n",
      "57 -0.6735  30.6160  0.16122  17.418  20.143  22.868  26.271  30.616  36.313   \n",
      "58 -0.6752  30.8854  0.16213  17.526  20.281  23.036  26.481  30.885  36.671   \n",
      "59 -0.6764  31.1586  0.16305  17.634  20.420  23.206  26.694  31.159  37.035   \n",
      "\n",
      "       SD2     SD3     SD4  age  \n",
      "0   24.165  27.770  31.375   61  \n",
      "1   24.423  28.093  31.764   62  \n",
      "2   24.683  28.420  32.157   63  \n",
      "3   24.946  28.750  32.554   64  \n",
      "4   25.210  29.083  32.956   65  \n",
      "5   25.477  29.420  33.362   66  \n",
      "6   25.747  29.760  33.772   67  \n",
      "7   26.018  30.102  34.186   68  \n",
      "8   26.293  30.449  34.605   69  \n",
      "9   26.569  30.799  35.029   70  \n",
      "10  26.848  31.153  35.457   71  \n",
      "11  27.129  31.508  35.888   72  \n",
      "12  27.412  31.868  36.324   73  \n",
      "13  27.697  32.231  36.765   74  \n",
      "14  27.984  32.598  37.211   75  \n",
      "15  28.275  32.969  37.664   76  \n",
      "16  28.567  33.343  38.120   77  \n",
      "17  28.862  33.723  38.583   78  \n",
      "18  29.161  34.107  39.054   79  \n",
      "19  29.461  34.495  39.529   80  \n",
      "20  29.765  34.889  40.013   81  \n",
      "21  30.072  35.287  40.503   82  \n",
      "22  30.382  35.692  41.001   83  \n",
      "23  30.695  36.100  41.506   84  \n",
      "24  31.011  36.515  42.019   85  \n",
      "25  31.330  36.936  42.542   86  \n",
      "26  31.653  37.363  43.073   87  \n",
      "27  31.979  37.795  43.612   88  \n",
      "28  32.309  38.236  44.163   89  \n",
      "29  32.642  38.682  44.722   90  \n",
      "30  32.979  39.135  45.291   91  \n",
      "31  33.320  39.597  45.873   92  \n",
      "32  33.665  40.065  46.464   93  \n",
      "33  34.015  40.542  47.069   94  \n",
      "34  34.368  41.027  47.686   95  \n",
      "35  34.727  41.521  48.315   96  \n",
      "36  35.091  42.025  48.959   97  \n",
      "37  35.459  42.538  49.617   98  \n",
      "38  35.832  43.060  50.287   99  \n",
      "39  36.210  43.591  50.972  100  \n",
      "40  36.593  44.131  51.669  101  \n",
      "41  36.981  44.682  52.382  102  \n",
      "42  37.376  45.244  53.113  103  \n",
      "43  37.774  45.815  53.855  104  \n",
      "44  38.179  46.397  54.615  105  \n",
      "45  38.591  46.992  55.394  106  \n",
      "46  39.009  47.598  56.188  107  \n",
      "47  39.433  48.214  56.996  108  \n",
      "48  39.864  48.843  57.822  109  \n",
      "49  40.301  49.483  58.664  110  \n",
      "50  40.744  50.132  59.519  111  \n",
      "51  41.195  50.792  60.389  112  \n",
      "52  41.652  51.463  61.274  113  \n",
      "53  42.116  52.146  62.175  114  \n",
      "54  42.587  52.838  63.089  115  \n",
      "55  43.063  53.538  64.012  116  \n",
      "56  43.547  54.248  64.949  117  \n",
      "57  44.038  54.968  65.898  118  \n",
      "58  44.534  55.695  66.857  119  \n",
      "59  45.038  56.434  67.829  120  \n"
     ]
    }
   ],
   "source": [
    "bw = pd.read_excel(\"WFA_Boys.xlsx\")\n",
    "bw['age']=bw['Month']\n",
    "bw = bw.drop('Month',axis=1)\n",
    "print(bw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 442,
   "id": "1474f562-9026-49d7-a324-a1db9b648edf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  weight       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "goutham   78  2      20 -0.3804  21.6810  0.13554   12.96  14.855  16.751   \n",
      "john123   65  3      19 -0.2443  19.2132  0.13142   11.48  13.186  14.892   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        WZ  \n",
      "goutham  18.997  21.681  24.918  28.862  33.723  38.583 -0.604660  \n",
      "john123  16.882  19.213  21.959  25.210  29.083  32.956 -0.085023  \n"
     ]
    }
   ],
   "source": [
    "merged_bw = pd.DataFrame()\n",
    "# print(merged_bw)\n",
    "merged_bw = pd.merge(weight_df_boy, bw, left_on='age', right_on='age', how='left')\n",
    "merged_bw.set_index(df_boy.index, inplace=True)\n",
    "# print(merged_bw)\n",
    "# Calculate L * M * S and create a new column in df_boy\n",
    "merged_bw['WZ'] = ((merged_bw['weight']/merged_bw['M'])**merged_bw['L'] -1)/(merged_bw['L'] * merged_bw['S'])\n",
    "# print(merged_df_boy)\n",
    "# print(merged_df_boy['WZ'])\n",
    "# Now you can use merged_df_boy to see the updated DataFrame\n",
    "weight_df_boy = merged_bw\n",
    "\n",
    "# Show the updated df_boy with the new column\n",
    "print(weight_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 443,
   "id": "a5e7c58a-de94-4dbe-ac31-1973e0f4ca09",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          L        M        S  SD4neg  SD3neg  SD2neg  SD1neg     SD0     SD1  \\\n",
      "0   -0.7387  15.2641  0.08390  11.204  12.118  13.031  14.071  15.264  16.645   \n",
      "1   -0.7621  15.2616  0.08414  11.204  12.115  13.027  14.066  15.262  16.648   \n",
      "2   -0.7856  15.2604  0.08439  11.204  12.114  13.024  14.063  15.260  16.653   \n",
      "3   -0.8089  15.2605  0.08464  11.205  12.114  13.022  14.061  15.260  16.659   \n",
      "4   -0.8322  15.2619  0.08490  11.207  12.114  13.021  14.060  15.262  16.667   \n",
      "..      ...      ...      ...     ...     ...     ...     ...     ...     ...   \n",
      "163 -0.9048  22.0374  0.12911  14.148  15.811  17.473  19.504  22.037  25.281   \n",
      "164 -0.8892  22.0760  0.12920  14.151  15.823  17.495  19.535  22.076  25.324   \n",
      "165 -0.8735  22.1140  0.12930  14.153  15.834  17.515  19.564  22.114  25.366   \n",
      "166 -0.8578  22.1514  0.12939  14.155  15.845  17.535  19.594  22.151  25.408   \n",
      "167 -0.8419  22.1883  0.12948  14.156  15.855  17.554  19.622  22.188  25.449   \n",
      "\n",
      "        SD2     SD3     SD4  age  \n",
      "0    18.259  20.166  22.072   61  \n",
      "1    18.273  20.200  22.127   62  \n",
      "2    18.290  20.238  22.186   63  \n",
      "3    18.308  20.277  22.247   64  \n",
      "4    18.328  20.320  22.312   65  \n",
      "..      ...     ...     ...  ...  \n",
      "163  29.572  35.503  41.434  224  \n",
      "164  29.609  35.507  41.406  225  \n",
      "165  29.646  35.512  41.378  226  \n",
      "166  29.681  35.515  41.348  227  \n",
      "167  29.716  35.516  41.317  228  \n",
      "\n",
      "[168 rows x 13 columns]\n"
     ]
    }
   ],
   "source": [
    "bb = pd.read_excel(\"BFA_Boys.xlsx\")\n",
    "bb['age']=bb['Month']\n",
    "bb = bb.drop('Month',axis=1)\n",
    "print(bb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 444,
   "id": "4b1155dd-0e94-4b2c-ae51-4778e3d4f26a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id    BMI\n",
      "goutham   78  2  16.53\n",
      "john123   65  3  11.24\n"
     ]
    }
   ],
   "source": [
    "bmi_df_boy = pd.DataFrame()\n",
    "bmi_df_boy[['age','id','BMI']] = df_boy[['age','id','BMI']]\n",
    "print(bmi_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 445,
   "id": "d1bd97d2-58fb-4301-b5e1-90331746918c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id    BMI       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "goutham   78  2  16.53 -1.1230  15.3825  0.08865  11.292  12.189  13.086   \n",
      "john123   65  3  11.24 -0.8322  15.2619  0.08490  11.207  12.114  13.021   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        BZ  \n",
      "goutham  14.136  15.382  16.888  18.745  21.097  23.449  0.779658  \n",
      "john123  14.060  15.262  16.667  18.328  20.320  22.312 -4.102910  \n"
     ]
    }
   ],
   "source": [
    "merged_bb = pd.DataFrame()\n",
    "# print(merged_bb)\n",
    "merged_bb = pd.merge(bmi_df_boy, bb, left_on='age', right_on='age', how='left')\n",
    "merged_bb.set_index(df_boy.index, inplace=True)\n",
    "# print(merged_bb)\n",
    "# Calculate L * M * S and create a new column in df_boy\n",
    "merged_bb['BZ'] = ((merged_bb['BMI']/merged_bb['M'])**merged_bb['L'] -1)/(merged_bb['L'] * merged_bb['S'])\n",
    "# print(merged_bb)\n",
    "# print(merged_bb['BZ'])\n",
    "# Now you can use merged_df_boy to see the updated DataFrame\n",
    "bmi_df_boy = merged_bb\n",
    "\n",
    "# Show the updated df_boy with the new column\n",
    "print(bmi_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 446,
   "id": "46694b53-9da2-4c58-ac04-09601581c47e",
   "metadata": {},
   "outputs": [],
   "source": [
    "## for girls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 447,
   "id": "4bd7447d-99d9-4602-8918-bfaa2601f66a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     L         M        S   StDev   SD5neg   SD4neg   SD3neg   SD2neg  \\\n",
      "0    1  109.6016  0.04355  4.7731   85.736   90.509   95.282  100.055   \n",
      "1    1  110.1258  0.04364  4.8059   86.096   90.902   95.708  100.514   \n",
      "2    1  110.6451  0.04373  4.8385   86.453   91.291   96.130  100.968   \n",
      "3    1  111.1596  0.04382  4.8710   86.805   91.676   96.547  101.418   \n",
      "4    1  111.6696  0.04390  4.9023   87.158   92.060   96.963  101.865   \n",
      "..  ..       ...      ...     ...      ...      ...      ...      ...   \n",
      "163  1  163.1418  0.04023  6.5632  130.326  136.889  143.452  150.015   \n",
      "164  1  163.1469  0.04019  6.5569  130.363  136.919  143.476  150.033   \n",
      "165  1  163.1508  0.04016  6.5521  130.390  136.942  143.494  150.047   \n",
      "166  1  163.1534  0.04012  6.5457  130.425  136.971  143.516  150.062   \n",
      "167  1  163.1548  0.04009  6.5409  130.450  136.991  143.532  150.073   \n",
      "\n",
      "      SD1neg      SD0      SD1      SD2      SD3      SD4  age  \n",
      "0    104.828  109.602  114.375  119.148  123.921  128.694   61  \n",
      "1    105.320  110.126  114.932  119.738  124.543  129.349   62  \n",
      "2    105.807  110.645  115.484  120.322  125.161  129.999   63  \n",
      "3    106.289  111.160  116.031  120.902  125.773  130.644   64  \n",
      "4    106.767  111.670  116.572  121.474  126.376  131.279   65  \n",
      "..       ...      ...      ...      ...      ...      ...  ...  \n",
      "163  156.579  163.142  169.705  176.268  182.831  189.395  224  \n",
      "164  156.590  163.147  169.704  176.261  182.818  189.374  225  \n",
      "165  156.599  163.151  169.703  176.255  182.807  189.359  226  \n",
      "166  156.608  163.153  169.699  176.245  182.791  189.336  227  \n",
      "167  156.614  163.155  169.696  176.237  182.777  189.318  228  \n",
      "\n",
      "[168 rows x 15 columns]\n"
     ]
    }
   ],
   "source": [
    "gh = pd.read_excel(\"HFA_girls.xlsx\")\n",
    "gh['age']= gh['Month']\n",
    "gh = gh.drop(\"Month\",axis = 1)\n",
    "print(gh)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 448,
   "id": "13b0b854-0930-4770-8923-c661903caf00",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  height  L         M       S   StDev  SD5neg  SD4neg  SD3neg  \\\n",
      "bharani   83  4     141  1  120.3374  0.0452  5.4393  93.141   98.58  104.02   \n",
      "\n",
      "          SD2neg   SD1neg      SD0      SD1      SD2      SD3      SD4  \\\n",
      "bharani  109.459  114.898  120.337  125.777  131.216  136.655  142.094   \n",
      "\n",
      "               HZ  \n",
      "bharani  3.798795  \n"
     ]
    }
   ],
   "source": [
    "height_df_girl = pd.DataFrame()\n",
    "height_df_girl['age'] = df_girl['age']\n",
    "height_df_girl['id'] = df_girl['id']\n",
    "height_df_girl['height'] = df_girl['height']\n",
    "merged_gh = pd.merge(height_df_girl, gh, left_on='age', right_on='age', how='left')\n",
    "merged_gh.set_index(df_girl.index, inplace=True)\n",
    "# print(merged_gh)\n",
    "# Calculate L * M * S and create a new column in df_girl\n",
    "merged_gh['HZ'] = ((merged_gh['height']/merged_gh['M'])**merged_gh['L'] -1)/(merged_gh['L'] * merged_gh['S'])\n",
    "# print(merged_df_girl)\n",
    "# print(merged_df_girl['HZ'])\n",
    "# Now you can use merged_df_girl to see the updated DataFrame\n",
    "height_df_girl = merged_gh\n",
    "# Show the updated df_girl with the new column\n",
    "print(height_df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 449,
   "id": "66118703-f9c3-4d8f-bb43-dd67fc7d42ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  weight\n",
      "bharani   83  4      28\n"
     ]
    }
   ],
   "source": [
    "weight_df_girl = pd.DataFrame()\n",
    "weight_df_girl[['age','id','weight']] = df_girl[['age','id','weight']]\n",
    "print(weight_df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 450,
   "id": "596daa34-9639-4f1d-9dfc-1c58e37dda8c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         L        M        S  SD4neg  SD3neg  SD2neg  SD1neg     SD0     SD1  \\\n",
      "0  -0.4681  18.2579  0.14295  10.742  12.352  13.961  15.899  18.258  21.169   \n",
      "1  -0.4711  18.4329  0.14350  10.828  12.456  14.083  16.043  18.433  21.386   \n",
      "2  -0.4742  18.6073  0.14404  10.915  12.559  14.204  16.187  18.607  21.601   \n",
      "3  -0.4773  18.7811  0.14459  11.000  12.662  14.324  16.330  18.781  21.817   \n",
      "4  -0.4803  18.9545  0.14514  11.085  12.764  14.444  16.473  18.954  22.032   \n",
      "5  -0.4834  19.1276  0.14569  11.169  12.866  14.564  16.616  19.128  22.247   \n",
      "6  -0.4864  19.3004  0.14624  11.253  12.968  14.683  16.758  19.300  22.462   \n",
      "7  -0.4894  19.4730  0.14679  11.337  13.069  14.801  16.899  19.473  22.677   \n",
      "8  -0.4924  19.6455  0.14735  11.419  13.169  14.919  17.041  19.646  22.893   \n",
      "9  -0.4954  19.8180  0.14790  11.503  13.270  15.038  17.182  19.818  23.108   \n",
      "10 -0.4984  19.9908  0.14845  11.586  13.371  15.156  17.323  19.991  23.324   \n",
      "11 -0.5013  20.1639  0.14900  11.669  13.471  15.274  17.465  20.164  23.541   \n",
      "12 -0.5043  20.3377  0.14955  11.752  13.572  15.393  17.607  20.338  23.759   \n",
      "13 -0.5072  20.5124  0.15010  11.835  13.673  15.512  17.750  20.512  23.978   \n",
      "14 -0.5100  20.6885  0.15065  11.919  13.775  15.632  17.893  20.688  24.199   \n",
      "15 -0.5129  20.8661  0.15120  12.004  13.878  15.753  18.038  20.866  24.423   \n",
      "16 -0.5157  21.0457  0.15175  12.089  13.982  15.875  18.185  21.046  24.648   \n",
      "17 -0.5185  21.2274  0.15230  12.175  14.087  15.998  18.333  21.227  24.877   \n",
      "18 -0.5213  21.4113  0.15284  12.263  14.193  16.124  18.483  21.411  25.108   \n",
      "19 -0.5240  21.5979  0.15339  12.352  14.301  16.250  18.635  21.598  25.343   \n",
      "20 -0.5268  21.7872  0.15393  12.443  14.411  16.379  18.790  21.787  25.581   \n",
      "21 -0.5294  21.9795  0.15448  12.534  14.522  16.510  18.947  21.980  25.823   \n",
      "22 -0.5321  22.1751  0.15502  12.627  14.635  16.643  19.106  22.175  26.069   \n",
      "23 -0.5347  22.3740  0.15556  12.722  14.750  16.779  19.268  22.374  26.320   \n",
      "24 -0.5372  22.5762  0.15610  12.819  14.867  16.916  19.433  22.576  26.574   \n",
      "25 -0.5398  22.7816  0.15663  12.917  14.987  17.056  19.601  22.782  26.832   \n",
      "26 -0.5423  22.9904  0.15717  13.017  15.108  17.199  19.772  22.990  27.095   \n",
      "27 -0.5447  23.2025  0.15770  13.119  15.231  17.343  19.945  23.202  27.362   \n",
      "28 -0.5471  23.4180  0.15823  13.222  15.356  17.490  20.121  23.418  27.633   \n",
      "29 -0.5495  23.6369  0.15876  13.327  15.483  17.639  20.299  23.637  27.908   \n",
      "30 -0.5518  23.8593  0.15928  13.434  15.612  17.791  20.481  23.859  28.188   \n",
      "31 -0.5541  24.0853  0.15980  13.542  15.744  17.946  20.666  24.085  28.472   \n",
      "32 -0.5563  24.3149  0.16032  13.652  15.877  18.102  20.853  24.315  28.761   \n",
      "33 -0.5585  24.5482  0.16084  13.764  16.013  18.262  21.044  24.548  29.054   \n",
      "34 -0.5606  24.7853  0.16135  13.878  16.151  18.424  21.238  24.785  29.352   \n",
      "35 -0.5627  25.0262  0.16186  13.994  16.291  18.588  21.435  25.026  29.655   \n",
      "36 -0.5647  25.2710  0.16237  14.112  16.434  18.756  21.635  25.271  29.963   \n",
      "37 -0.5667  25.5197  0.16287  14.232  16.579  18.926  21.838  25.520  30.275   \n",
      "38 -0.5686  25.7721  0.16337  14.353  16.726  19.098  22.045  25.772  30.593   \n",
      "39 -0.5704  26.0284  0.16386  14.477  16.875  19.274  22.254  26.028  30.914   \n",
      "40 -0.5722  26.2883  0.16435  14.602  17.027  19.452  22.467  26.288  31.241   \n",
      "41 -0.5740  26.5519  0.16483  14.730  17.181  19.632  22.683  26.552  31.572   \n",
      "42 -0.5757  26.8190  0.16532  14.859  17.337  19.815  22.901  26.819  31.907   \n",
      "43 -0.5773  27.0896  0.16579  14.989  17.495  20.001  23.123  27.090  32.247   \n",
      "44 -0.5789  27.3635  0.16626  15.122  17.655  20.188  23.347  27.364  32.591   \n",
      "45 -0.5804  27.6406  0.16673  15.255  17.817  20.378  23.574  27.641  32.939   \n",
      "46 -0.5819  27.9208  0.16719  15.391  17.980  20.570  23.803  27.921  33.290   \n",
      "47 -0.5833  28.2040  0.16764  15.528  18.146  20.764  24.035  28.204  33.645   \n",
      "48 -0.5847  28.4901  0.16809  15.666  18.313  20.960  24.270  28.490  34.004   \n",
      "49 -0.5859  28.7791  0.16854  15.805  18.482  21.158  24.506  28.779  34.367   \n",
      "50 -0.5872  29.0711  0.16897  15.947  18.653  21.359  24.745  29.071  34.733   \n",
      "51 -0.5883  29.3663  0.16941  16.089  18.825  21.561  24.987  29.366  35.104   \n",
      "52 -0.5895  29.6646  0.16983  16.234  19.000  21.766  25.232  29.665  35.478   \n",
      "53 -0.5905  29.9663  0.17025  16.380  19.176  21.973  25.479  29.966  35.856   \n",
      "54 -0.5915  30.2715  0.17066  16.528  19.355  22.183  25.729  30.272  36.238   \n",
      "55 -0.5925  30.5805  0.17107  16.678  19.536  22.395  25.982  30.580  36.625   \n",
      "56 -0.5934  30.8934  0.17146  16.830  19.720  22.610  26.239  30.893  37.017   \n",
      "57 -0.5942  31.2105  0.17186  16.983  19.906  22.828  26.499  31.210  37.414   \n",
      "58 -0.5950  31.5319  0.17224  17.140  20.095  23.049  26.763  31.532  37.815   \n",
      "59 -0.5958  31.8578  0.17262  17.298  20.286  23.274  27.031  31.858  38.223   \n",
      "\n",
      "       SD2     SD3     SD4  age  \n",
      "0   24.817  29.468  34.118   61  \n",
      "1   25.090  29.823  34.555   62  \n",
      "2   25.363  30.178  34.992   63  \n",
      "3   25.637  30.535  35.433   64  \n",
      "4   25.911  30.893  35.876   65  \n",
      "5   26.185  31.253  36.321   66  \n",
      "6   26.460  31.614  36.768   67  \n",
      "7   26.735  31.977  37.218   68  \n",
      "8   27.011  32.342  37.673   69  \n",
      "9   27.288  32.708  38.129   70  \n",
      "10  27.566  33.077  38.589   71  \n",
      "11  27.845  33.448  39.051   72  \n",
      "12  28.126  33.822  39.519   73  \n",
      "13  28.409  34.200  39.991   74  \n",
      "14  28.695  34.581  40.468   75  \n",
      "15  28.983  34.968  40.953   76  \n",
      "16  29.276  35.360  41.443   77  \n",
      "17  29.572  35.757  41.942   78  \n",
      "18  29.871  36.158  42.446   79  \n",
      "19  30.176  36.568  42.960   80  \n",
      "20  30.485  36.983  43.482   81  \n",
      "21  30.799  37.406  44.013   82  \n",
      "22  31.118  37.836  44.554   83  \n",
      "23  31.443  38.274  45.104   84  \n",
      "24  31.773  38.719  45.664   85  \n",
      "25  32.109  39.171  46.232   86  \n",
      "26  32.450  39.632  46.813   87  \n",
      "27  32.796  40.099  47.401   88  \n",
      "28  33.149  40.574  48.000   89  \n",
      "29  33.507  41.058  48.610   90  \n",
      "30  33.870  41.549  49.228   91  \n",
      "31  34.239  42.048  49.857   92  \n",
      "32  34.614  42.555  50.497   93  \n",
      "33  34.996  43.072  51.149   94  \n",
      "34  35.383  43.596  51.809   95  \n",
      "35  35.776  44.128  52.481   96  \n",
      "36  36.176  44.670  53.164   97  \n",
      "37  36.582  45.220  53.858   98  \n",
      "38  36.994  45.778  54.562   99  \n",
      "39  37.412  46.343  55.275  100  \n",
      "40  37.836  46.918  55.999  101  \n",
      "41  38.265  47.499  56.734  102  \n",
      "42  38.702  48.092  57.481  103  \n",
      "43  39.142  48.688  58.233  104  \n",
      "44  39.589  49.293  58.997  105  \n",
      "45  40.041  49.906  59.771  106  \n",
      "46  40.497  50.525  60.552  107  \n",
      "47  40.958  51.149  61.340  108  \n",
      "48  41.424  51.781  62.138  109  \n",
      "49  41.895  52.419  62.944  110  \n",
      "50  42.370  53.063  63.755  111  \n",
      "51  42.851  53.714  64.578  112  \n",
      "52  43.335  54.371  65.407  113  \n",
      "53  43.826  55.035  66.244  114  \n",
      "54  44.321  55.705  67.089  115  \n",
      "55  44.823  56.385  67.947  116  \n",
      "56  45.329  57.069  68.809  117  \n",
      "57  45.843  57.764  69.685  118  \n",
      "58  46.363  58.465  70.568  119  \n",
      "59  46.890  59.177  71.465  120  \n"
     ]
    }
   ],
   "source": [
    "gw = pd.read_excel(\"WFA_girls.xlsx\")\n",
    "gw['age']=gw['Month']\n",
    "gw = gw.drop('Month',axis=1)\n",
    "print(gw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 451,
   "id": "f8cb4d60-75f2-43fd-abaf-30f6921e350d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  weight       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "bharani   83  4      28 -0.5321  22.1751  0.15502  12.627  14.635  16.643   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4       WZ  \n",
      "bharani  19.106  22.175  26.069  31.118  37.836  44.554  1.41493  \n"
     ]
    }
   ],
   "source": [
    "merged_gw = pd.DataFrame()\n",
    "# print(merged_gw)\n",
    "merged_gw = pd.merge(weight_df_girl, gw, left_on='age', right_on='age', how='left')\n",
    "merged_gw.set_index(df_girl.index, inplace=True)\n",
    "# print(merged_gw)\n",
    "# Calculate L * M * S and create a new column in df_girl\n",
    "merged_gw['WZ'] = ((merged_gw['weight']/merged_gw['M'])**merged_gw['L'] -1)/(merged_gw['L'] * merged_gw['S'])\n",
    "# print(merged_df_girl)\n",
    "# print(merged_df_girl['WZ'])\n",
    "# Now you can use merged_df_girl to see the updated DataFrame\n",
    "weight_df_girl = merged_gw\n",
    "# Show the updated df_girl with the new column\n",
    "print(weight_df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 452,
   "id": "19b9cc83-4fd9-403a-a6c3-7f5e58e44be3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          L        M        S  SD4neg  SD3neg  SD2neg  SD1neg     SD0     SD1  \\\n",
      "0   -0.8886  15.2441  0.09692  10.793  11.770  12.748  13.891  15.244  16.870   \n",
      "1   -0.9068  15.2434  0.09738  10.786  11.763  12.741  13.885  15.243  16.879   \n",
      "2   -0.9248  15.2433  0.09783  10.780  11.757  12.734  13.881  15.243  16.889   \n",
      "3   -0.9427  15.2438  0.09829  10.775  11.752  12.728  13.876  15.244  16.900   \n",
      "4   -0.9605  15.2448  0.09875  10.770  11.746  12.723  13.872  15.245  16.911   \n",
      "..      ...      ...      ...     ...     ...     ...     ...     ...     ...   \n",
      "163 -0.7819  21.3752  0.14405  12.977  14.731  16.484  18.648  21.375  24.905   \n",
      "164 -0.7738  21.3884  0.14414  12.971  14.729  16.488  18.657  21.388  24.920   \n",
      "165 -0.7658  21.4014  0.14423  12.964  14.728  16.491  18.665  21.401  24.935   \n",
      "166 -0.7577  21.4143  0.14432  12.958  14.726  16.494  18.673  21.414  24.951   \n",
      "167 -0.7496  21.4269  0.14441  12.951  14.724  16.497  18.681  21.427  24.965   \n",
      "\n",
      "        SD2     SD3     SD4  age  \n",
      "0    18.858  21.340  23.821   61  \n",
      "1    18.886  21.403  23.920   62  \n",
      "2    18.915  21.468  24.020   63  \n",
      "3    18.946  21.535  24.123   64  \n",
      "4    18.977  21.603  24.230   65  \n",
      "..      ...     ...     ...  ...  \n",
      "163  29.626  36.219  42.812  224  \n",
      "164  29.637  36.209  42.781  225  \n",
      "165  29.649  36.200  42.751  226  \n",
      "166  29.659  36.190  42.720  227  \n",
      "167  29.670  36.179  42.689  228  \n",
      "\n",
      "[168 rows x 13 columns]\n"
     ]
    }
   ],
   "source": [
    "gb = pd.read_excel(\"BFA_girls.xlsx\")\n",
    "gb['age']=gb['Month']\n",
    "gb = gb.drop('Month',axis=1)\n",
    "print(gb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 453,
   "id": "17ff28e5-5888-4c72-b1df-12b47b9364ec",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id    BMI\n",
      "bharani   83  4  14.08\n"
     ]
    }
   ],
   "source": [
    "bmi_df_girl = pd.DataFrame()\n",
    "bmi_df_girl[['age','id','BMI']] = df_girl[['age','id','BMI']]\n",
    "print(bmi_df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 454,
   "id": "eb755b1c-3f2a-443a-9c9d-a106efe46c5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id    BMI       L        M      S  SD4neg  SD3neg  SD2neg  \\\n",
      "bharani   83  4  14.08 -1.2433  15.3871  0.107  10.762  11.745  12.728   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        BZ  \n",
      "bharani  13.916  15.387  17.259  19.734  23.178  26.621 -0.877183  \n"
     ]
    }
   ],
   "source": [
    "merged_gb = pd.DataFrame()\n",
    "# print(merged_gb)\n",
    "merged_gb = pd.merge(bmi_df_girl, gb, left_on='age', right_on='age', how='left')\n",
    "merged_gb.set_index(df_girl.index, inplace=True)\n",
    "# print(merged_gb)\n",
    "# Calculate L * M * S and create a new column in df_girl\n",
    "merged_gb['BZ'] = ((merged_gb['BMI']/merged_gb['M'])**merged_gb['L'] -1)/(merged_gb['L'] * merged_gb['S'])\n",
    "# print(merged_gb)\n",
    "# print(merged_gb['BZ'])\n",
    "# Now you can use merged_df_girl to see the updated DataFrame\n",
    "bmi_df_girl = merged_gb\n",
    "# Show the updated df_girl with the new column\n",
    "print(bmi_df_girl)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77d97a36-8c6f-412d-879d-bd26b606806a",
   "metadata": {},
   "source": [
    "\\\n",
    " GIRLS\\\n",
    " HEIGHT FOR AGE - height_df_girl  -> HZ Z-Score\\\n",
    " BMI FOR AGE - bmi_df_girl -> BZ Z-Score\\\n",
    " WEIGHT FOR AGE - weight_df_girl -> WZ Z-Score\\\n",
    " \\\n",
    " BOYS\\\n",
    " HEIGHT FOR AGE - height_df_boy -> HZ Z-Score\\\n",
    " BMI FOR AGE - bmi_df_boy -> BZ Z-Score\\\n",
    " WEIGHT FOR AGE - weight_df_boy -> WZ Z-Score\\\n",
    "\\\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 455,
   "id": "b466ad2e-ee57-4984-98fa-e62bdfab268a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  height  L         M       S   StDev  SD5neg  SD4neg  SD3neg  \\\n",
      "bharani   83  4     141  1  120.3374  0.0452  5.4393  93.141   98.58  104.02   \n",
      "\n",
      "          SD2neg   SD1neg      SD0      SD1      SD2      SD3      SD4  \\\n",
      "bharani  109.459  114.898  120.337  125.777  131.216  136.655  142.094   \n",
      "\n",
      "               HZ  \n",
      "bharani  3.798795  \n",
      "         age id    BMI       L        M      S  SD4neg  SD3neg  SD2neg  \\\n",
      "bharani   83  4  14.08 -1.2433  15.3871  0.107  10.762  11.745  12.728   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        BZ  \n",
      "bharani  13.916  15.387  17.259  19.734  23.178  26.621 -0.877183  \n",
      "         age id  weight       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "bharani   83  4      28 -0.5321  22.1751  0.15502  12.627  14.635  16.643   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4       WZ  \n",
      "bharani  19.106  22.175  26.069  31.118  37.836  44.554  1.41493  \n"
     ]
    }
   ],
   "source": [
    "print(height_df_girl)\n",
    "print(bmi_df_girl)\n",
    "print(weight_df_girl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 456,
   "id": "f0d11152-3380-43f3-a3b2-750d095ef6ab",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         age id  height  L         M        S   StDev  SD5neg  SD4neg  \\\n",
      "goutham   78  2     110  1  118.8700  0.04295  5.1055  93.343  98.448   \n",
      "john123   65  3     130  1  112.3895  0.04195  4.7147  88.816  93.531   \n",
      "\n",
      "          SD3neg   SD2neg   SD1neg     SD0      SD1      SD2      SD3  \\\n",
      "goutham  103.554  108.659  113.765  118.87  123.975  129.081  134.186   \n",
      "john123   98.245  102.960  107.675  112.39  117.104  121.819  126.534   \n",
      "\n",
      "             SD4        HZ  \n",
      "goutham  139.292 -1.737353  \n",
      "john123  131.248  3.735201  \n",
      "         age id    BMI       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "goutham   78  2  16.53 -1.1230  15.3825  0.08865  11.292  12.189  13.086   \n",
      "john123   65  3  11.24 -0.8322  15.2619  0.08490  11.207  12.114  13.021   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        BZ  \n",
      "goutham  14.136  15.382  16.888  18.745  21.097  23.449  0.779658  \n",
      "john123  14.060  15.262  16.667  18.328  20.320  22.312 -4.102910  \n",
      "         age id  weight       L        M        S  SD4neg  SD3neg  SD2neg  \\\n",
      "goutham   78  2      20 -0.3804  21.6810  0.13554   12.96  14.855  16.751   \n",
      "john123   65  3      19 -0.2443  19.2132  0.13142   11.48  13.186  14.892   \n",
      "\n",
      "         SD1neg     SD0     SD1     SD2     SD3     SD4        WZ  \n",
      "goutham  18.997  21.681  24.918  28.862  33.723  38.583 -0.604660  \n",
      "john123  16.882  19.213  21.959  25.210  29.083  32.956 -0.085023  \n"
     ]
    }
   ],
   "source": [
    "print(height_df_boy)\n",
    "print(bmi_df_boy)\n",
    "print(weight_df_boy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 459,
   "id": "e9fbd549-6c9e-43d9-a070-cf6f3b66d326",
   "metadata": {},
   "outputs": [],
   "source": [
    "height_df_girl.to_excel('height_df_girl.xlsx', index=True, index_label='Name')\n",
    "bmi_df_girl.to_excel('bmi_df_girl.xlsx', index=True, index_label='Name')\n",
    "weight_df_girl.to_excel('weight_df_girl.xlsx', index=True, index_label='Name')\n",
    "height_df_boy.to_excel('height_df_boy.xlsx', index=True, index_label='Name')\n",
    "bmi_df_boy.to_excel('bmi_df_boy.xlsx', index=True, index_label='Name')\n",
    "weight_df_boy.to_excel('weight_df_boy.xlsx', index=True, index_label='Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d928f409-a163-4e08-8f84-2562f4fae566",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}