Skip to content

Instantly share code, notes, and snippets.

@highsmallxu

highsmallxu/read_multiple_files_format_new_col.ipynb

Last active July 10, 2022 15:48
Show Gist options
  • Save highsmallxu/caa2725e25cc3d8572495eb09071e228 to your computer and use it in GitHub Desktop.
Save highsmallxu/caa2725e25cc3d8572495eb09071e228 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
read_multiple_files_format_new_col.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [],
"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": 25,
"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>310.0</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 310.0 320.0"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"files = [\"data.csv\",\"data_extra_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": 26,
"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": 26,
"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": 27,
"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>320</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 320"
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = dd.read_csv(files[::-1])\n",
"df.head()\n",
"# This example reads `data_extra_col.csv` first, so it only shows the row from that file\n",
"# and ignores the other one."
]
},
{
"cell_type": "code",
"execution_count": 36,
"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>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 320.0"
]
},
"execution_count": 36,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = dd.read_csv(files[::-1],dtype={\"date\":\"str\",\"city_name\":\"str\",\"market_name\":\"str\",\"gross_amount\":\"float\",\"net_amount\":\"float\"})\n",
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {},
"outputs": [
{
"ename": "ValueError",
"evalue": "Cannot rbind frame with 5 columns to a frame with 4 columns without parameter 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.ipynb Cell 6'\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.ipynb#ch0000003?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.ipynb#ch0000003?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.ipynb#ch0000003?line=2'>3</a>\u001b[0m df \u001b[39m=\u001b[39m df\u001b[39m.\u001b[39mto_pandas()\n",
"\u001b[0;31mValueError\u001b[0m: Cannot rbind frame with 5 columns to a frame with 4 columns without parameter 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": 39,
"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>310.0</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 310.0 320.0"
]
},
"execution_count": 39,
"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