Skip to content

Instantly share code, notes, and snippets.

@TarekDib03

TarekDib03/Wide-to-Long-formatted-Data-Set.ipynp

Created January 26, 2021 22:48
Show Gist options
  • Save TarekDib03/4e528bc3013bf0e0732b0fec4771f6c4 to your computer and use it in GitHub Desktop.
Save TarekDib03/4e528bc3013bf0e0732b0fec4771f6c4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Wide-to-Long-formatted-Data-Set.ipynp
{
"cells": [
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [],
"source": [
"# Import pandas and read the data set. Use dropna() to drop NaN values. Then reset the index\n",
"import pandas as pd\n",
"data = pd.read_csv(\"C:/Users/Tarek/Documents/AMURT/2020/ECE/PRE_Medicine_NFI.csv\").dropna().reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 61,
"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>index</th>\n",
" <th>HH_Code</th>\n",
" <th>Medicine 1</th>\n",
" <th>Medicine 2</th>\n",
" <th>Medicine 3</th>\n",
" <th>Medicine 4</th>\n",
" <th>Medicine 5</th>\n",
" <th>Medicine 6</th>\n",
" <th>Medicine 7</th>\n",
" <th>Medicine 8</th>\n",
" <th>Medicine 9</th>\n",
" <th>Medicine 10</th>\n",
" <th>Medicine 11</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>5</td>\n",
" <td>AAB-527</td>\n",
" <td>SINAMAX 70</td>\n",
" <td>RIVOTIL</td>\n",
" <td>MAGNESIUM-B6</td>\n",
" <td>Voltaren50</td>\n",
" <td>GLIBENCLAMIDE 5 mg</td>\n",
" <td>CALDY VIT</td>\n",
" <td>New Omeprazole 40mg</td>\n",
" <td>Slofag 500 S.R</td>\n",
" <td>CARISOL</td>\n",
" <td>Biogabalin 75</td>\n",
" <td>DIFEN B12</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>39</td>\n",
" <td>HA-097</td>\n",
" <td>IBUPROFEN ORAL SUSPENSION 100mg/5ml</td>\n",
" <td>Co-Amoxiclav 312.5mg/5ml</td>\n",
" <td>Acetaminophen Oral Suspension USP 125mg/5ml</td>\n",
" <td>PediaVIT D</td>\n",
" <td>Clenil 250mg</td>\n",
" <td>Vermox 30ml</td>\n",
" <td>OTIPAX</td>\n",
" <td>FUCIDIN and Micozol (cream)</td>\n",
" <td>ArthroStop</td>\n",
" <td>MANSMIX</td>\n",
" <td>Lansomid 30 and HISTAMID-F</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>47</td>\n",
" <td>KA-252</td>\n",
" <td>GROPPAMOL</td>\n",
" <td>Eltroxin 100 mcg</td>\n",
" <td>Monelukast 5 mg</td>\n",
" <td>Timo Ferol</td>\n",
" <td>Nursie(baby milk )</td>\n",
" <td>PROSPAN (syrup) 100 ml</td>\n",
" <td>ATROALDO</td>\n",
" <td>Panadol</td>\n",
" <td>CALDY VIT</td>\n",
" <td>Prokinin</td>\n",
" <td>MAGNE B6 48mg/5mg</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>57</td>\n",
" <td>MA-326</td>\n",
" <td>PediaForte D400</td>\n",
" <td>Ovol 15ml</td>\n",
" <td>Profinal</td>\n",
" <td>Denti baby 25mL</td>\n",
" <td>Panadol (Pediatric &amp; infant )</td>\n",
" <td>PREDALONE 15mg/5ml (Syrup)</td>\n",
" <td>Scopinal (Syrup) 100mL</td>\n",
" <td>OMNICEF 40mL</td>\n",
" <td>PROSPAN</td>\n",
" <td>Tylenol 100 mg</td>\n",
" <td>tot'hema 10ml</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>61</td>\n",
" <td>MA-946</td>\n",
" <td>Panadol (baby&amp;infant )</td>\n",
" <td>Coldin (Syrup)</td>\n",
" <td>Ci-Cal D125 250mg</td>\n",
" <td>HISTAMED-F - Panadol</td>\n",
" <td>Rapidus 50</td>\n",
" <td>DIFEN FLEX</td>\n",
" <td>Gastrimut 20mg</td>\n",
" <td>Leflumax Levofloxacin 750mg</td>\n",
" <td>Buscopan</td>\n",
" <td>Optimal</td>\n",
" <td>Fludalt duon</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index HH_Code Medicine 1 \\\n",
"0 5 AAB-527 SINAMAX 70 \n",
"1 39 HA-097 IBUPROFEN ORAL SUSPENSION 100mg/5ml \n",
"2 47 KA-252 GROPPAMOL \n",
"3 57 MA-326 PediaForte D400 \n",
"4 61 MA-946 Panadol (baby&infant ) \n",
"\n",
" Medicine 2 Medicine 3 \\\n",
"0 RIVOTIL MAGNESIUM-B6 \n",
"1 Co-Amoxiclav 312.5mg/5ml Acetaminophen Oral Suspension USP 125mg/5ml \n",
"2 Eltroxin 100 mcg Monelukast 5 mg \n",
"3 Ovol 15ml Profinal \n",
"4 Coldin (Syrup) Ci-Cal D125 250mg \n",
"\n",
" Medicine 4 Medicine 5 \\\n",
"0 Voltaren50 GLIBENCLAMIDE 5 mg \n",
"1 PediaVIT D Clenil 250mg \n",
"2 Timo Ferol Nursie(baby milk ) \n",
"3 Denti baby 25mL Panadol (Pediatric & infant ) \n",
"4 HISTAMED-F - Panadol Rapidus 50 \n",
"\n",
" Medicine 6 Medicine 7 \\\n",
"0 CALDY VIT New Omeprazole 40mg \n",
"1 Vermox 30ml OTIPAX \n",
"2 PROSPAN (syrup) 100 ml ATROALDO \n",
"3 PREDALONE 15mg/5ml (Syrup) Scopinal (Syrup) 100mL \n",
"4 DIFEN FLEX Gastrimut 20mg \n",
"\n",
" Medicine 8 Medicine 9 Medicine 10 \\\n",
"0 Slofag 500 S.R CARISOL Biogabalin 75 \n",
"1 FUCIDIN and Micozol (cream) ArthroStop MANSMIX \n",
"2 Panadol CALDY VIT Prokinin \n",
"3 OMNICEF 40mL PROSPAN Tylenol 100 mg \n",
"4 Leflumax Levofloxacin 750mg Buscopan Optimal \n",
"\n",
" Medicine 11 \n",
"0 DIFEN B12 \n",
"1 Lansomid 30 and HISTAMID-F \n",
"2 MAGNE B6 48mg/5mg \n",
"3 tot'hema 10ml \n",
"4 Fludalt duon "
]
},
"execution_count": 61,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# The first 6 records of the data set\n",
"data.head()"
]
},
{
"cell_type": "code",
"execution_count": 64,
"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>HH_Code</th>\n",
" <th>variable</th>\n",
" <th>value</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>AAB-527</td>\n",
" <td>Medicine 1</td>\n",
" <td>SINAMAX 70</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AAB-527</td>\n",
" <td>Medicine 10</td>\n",
" <td>Biogabalin 75</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAB-527</td>\n",
" <td>Medicine 11</td>\n",
" <td>DIFEN B12</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAB-527</td>\n",
" <td>Medicine 2</td>\n",
" <td>RIVOTIL</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AAB-527</td>\n",
" <td>Medicine 3</td>\n",
" <td>MAGNESIUM-B6</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" HH_Code variable value\n",
"0 AAB-527 Medicine 1 SINAMAX 70 \n",
"1 AAB-527 Medicine 10 Biogabalin 75 \n",
"2 AAB-527 Medicine 11 DIFEN B12 \n",
"3 AAB-527 Medicine 2 RIVOTIL \n",
"4 AAB-527 Medicine 3 MAGNESIUM-B6 "
]
},
"execution_count": 64,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"long_df = data.melt(id_vars = ['HH_Code']).sort_values(['HH_Code', 'variable']).reset_index(drop = True)\n",
"long_df.head()"
]
},
{
"cell_type": "code",
"execution_count": 63,
"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>HH_Code</th>\n",
" <th>Medicine</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>AAB-527</td>\n",
" <td>SINAMAX 70</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>AAB-527</td>\n",
" <td>Biogabalin 75</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>AAB-527</td>\n",
" <td>DIFEN B12</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAB-527</td>\n",
" <td>RIVOTIL</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>AAB-527</td>\n",
" <td>MAGNESIUM-B6</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" HH_Code Medicine\n",
"0 AAB-527 SINAMAX 70 \n",
"1 AAB-527 Biogabalin 75 \n",
"2 AAB-527 DIFEN B12 \n",
"3 AAB-527 RIVOTIL \n",
"4 AAB-527 MAGNESIUM-B6 "
]
},
"execution_count": 63,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Keep only the HH_Code and Value columns. Then rename 'vlaue' column to Medicine\n",
"medicines = cleaned[['HH_Code', 'value']].rename(columns = {'value': 'Medicine'})\n",
"medicines.head()"
]
},
{
"cell_type": "code",
"execution_count": 57,
"metadata": {},
"outputs": [],
"source": [
"# Export medicines to a csv file\n",
"medicines.to_csv(r\"C:/Users/Tarek/Documents/AMURT/2020/ECE/medicine_list_long_format.csv\", index = False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<b><em>Resources</em></b>\n",
"\n",
"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html\n",
"https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"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.7.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment