Skip to content

Instantly share code, notes, and snippets.

@highsmallxu

highsmallxu/read_multiple_files_format_breaking.ipynb

Last active July 10, 2022 19:42
Show Gist options
  • Save highsmallxu/ac2c3c6696b387e3b5c0cf2c652b1294 to your computer and use it in GitHub Desktop.
Save highsmallxu/ac2c3c6696b387e3b5c0cf2c652b1294 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
read_multiple_files_format_breaking.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<style type='text/css'>\n",
".datatable table.frame { margin-bottom: 0; }\n",
".datatable table.frame thead { border-bottom: none; }\n",
".datatable table.frame tr.coltypes td { color: #FFFFFF; line-height: 6px; padding: 0 0.5em;}\n",
".datatable .bool { background: #DDDD99; }\n",
".datatable .object { background: #565656; }\n",
".datatable .int { background: #5D9E5D; }\n",
".datatable .float { background: #4040CC; }\n",
".datatable .str { background: #CC4040; }\n",
".datatable .time { background: #40CC40; }\n",
".datatable .row_index { background: var(--jp-border-color3); border-right: 1px solid var(--jp-border-color0); color: var(--jp-ui-font-color3); font-size: 9px;}\n",
".datatable .frame tbody td { text-align: left; }\n",
".datatable .frame tr.coltypes .row_index { background: var(--jp-border-color0);}\n",
".datatable th:nth-child(2) { padding-left: 12px; }\n",
".datatable .hellipsis { color: var(--jp-cell-editor-border-color);}\n",
".datatable .vellipsis { background: var(--jp-layout-color0); color: var(--jp-cell-editor-border-color);}\n",
".datatable .na { color: var(--jp-cell-editor-border-color); font-size: 80%;}\n",
".datatable .sp { opacity: 0.25;}\n",
".datatable .footer { font-size: 9px; }\n",
".datatable .frame_dimensions { background: var(--jp-border-color3); border-top: 1px solid var(--jp-border-color0); color: var(--jp-ui-font-color3); display: inline-block; opacity: 0.6; padding: 1px 10px 1px 5px;}\n",
"</style>\n"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"import pandas as pd\n",
"import dask.dataframe as dd\n",
"import datatable as dt\n",
"import matplotlib.pyplot as plt\n",
"import warnings\n",
"warnings.filterwarnings('ignore')"
]
},
{
"cell_type": "code",
"execution_count": 8,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>gross_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>310.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name gross_amount\n",
"0 2022-02-25 Milan Greater Milan 310.0"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.read_csv(\"data.csv\")"
]
},
{
"cell_type": "code",
"execution_count": 9,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>net_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>320</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name net_amount\n",
"0 2022-02-25 Milan Greater Milan 320"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.read_csv(\"data_rename_col.csv\")"
]
},
{
"cell_type": "code",
"execution_count": 2,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>gross_amount</th>\n",
" <th>net_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>310.0</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>NaN</td>\n",
" <td>320.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name gross_amount net_amount\n",
"0 2022-02-25 Milan Greater Milan 310.0 NaN\n",
"1 2022-02-25 Milan Greater Milan NaN 320.0"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"files = [\"data.csv\",\"data_rename_col.csv\"]\n",
"combined = []\n",
"for f in files:\n",
" combined.append(pd.read_csv(f))\n",
"combined_df = pd.concat(combined,ignore_index=True)\n",
"combined_df.head()\n",
"# The rows without the extra column will be filled with NaN in pandas."
]
},
{
"cell_type": "code",
"execution_count": 3,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>gross_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>310.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name gross_amount\n",
"0 2022-02-25 Milan Greater Milan 310.0"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = dd.read_csv(files)\n",
"df.head()\n",
"# dask will use the schema of the first file as the baseline. If the following files\n",
"# do not have the same schema, then they will be ignored without warning!!\n",
"# This is pretty risky. "
]
},
{
"cell_type": "code",
"execution_count": 4,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>net_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>320</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name net_amount\n",
"0 2022-02-25 Milan Greater Milan 320"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = dd.read_csv(files[::-1])\n",
"df.head()\n",
"# This example reads `data_rename_col.csv` first, so it only shows the row from that file\n",
"# and ignores the other one."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"ename": "ValueError",
"evalue": "Column net_amount is not found in the original frame; if you want to rbind the frames anyways filling missing values with NAs, then use force=True",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)",
"\u001b[1;32m/Users/xiaoxu/Repo/sandbox/source/read_multiple_files_format_breaking.ipynb Cell 5'\u001b[0m in \u001b[0;36m<cell line: 2>\u001b[0;34m()\u001b[0m\n\u001b[1;32m <a href='vscode-notebook-cell:/Users/xiaoxu/Repo/sandbox/source/read_multiple_files_format_breaking.ipynb#ch0000005?line=0'>1</a>\u001b[0m df \u001b[39m=\u001b[39m dt\u001b[39m.\u001b[39miread(files)\n\u001b[0;32m----> <a href='vscode-notebook-cell:/Users/xiaoxu/Repo/sandbox/source/read_multiple_files_format_breaking.ipynb#ch0000005?line=1'>2</a>\u001b[0m df \u001b[39m=\u001b[39m dt\u001b[39m.\u001b[39;49mrbind(df)\n\u001b[1;32m <a href='vscode-notebook-cell:/Users/xiaoxu/Repo/sandbox/source/read_multiple_files_format_breaking.ipynb#ch0000005?line=2'>3</a>\u001b[0m df \u001b[39m=\u001b[39m df\u001b[39m.\u001b[39mto_pandas()\n",
"\u001b[0;31mValueError\u001b[0m: Column net_amount is not found in the original frame; if you want to rbind the frames anyways filling missing values with NAs, then use force=True"
]
}
],
"source": [
"df = dt.iread(files)\n",
"df = dt.rbind(df)\n",
"df = df.to_pandas()\n",
"# datatable will simply raise an exception if the files don't have the same schema."
]
},
{
"cell_type": "code",
"execution_count": 7,
"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>date</th>\n",
" <th>city_name</th>\n",
" <th>market_name</th>\n",
" <th>gross_amount</th>\n",
" <th>net_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>310.0</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2022-02-25</td>\n",
" <td>Milan</td>\n",
" <td>Greater Milan</td>\n",
" <td>NaN</td>\n",
" <td>320.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" date city_name market_name gross_amount net_amount\n",
"0 2022-02-25 Milan Greater Milan 310.0 NaN\n",
"1 2022-02-25 Milan Greater Milan NaN 320.0"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = dt.iread(files)\n",
"df = dt.rbind(df,force=True)\n",
"df = df.to_pandas()\n",
"df.head()\n",
"# if force=True, then the result is the same as pandas"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3.9.0 64-bit ('3.9.0')",
"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.0"
},
"orig_nbformat": 4,
"vscode": {
"interpreter": {
"hash": "285b4027c56aef32f9cffa7b798ac9ff266d7923f973d093da0977b0f49ab1ea"
}
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment