{"id":45,"date":"2023-11-26T18:45:00","date_gmt":"2023-11-26T15:45:00","guid":{"rendered":"https:\/\/fsaeed.blog\/?p=45"},"modified":"2026-10-31T00:18:54","modified_gmt":"2026-10-30T21:18:54","slug":"pandas-cleaning-messy-system-exports","status":"publish","type":"post","link":"https:\/\/fsaeed.blog\/index.php\/2023\/11\/26\/pandas-cleaning-messy-system-exports\/","title":{"rendered":"pandas for Excel Users: Cleaning Messy System Exports"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Hello, tech enthusiasts! \ud83d\udc4b<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You click <strong>Export<\/strong> in your company system, and you get a file full of surprises. Names have extra spaces. Some dates are text. Numbers have commas, so Excel thinks they are words. Some rows appear twice. You spend 30 minutes cleaning before you can even start your real work.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Today we will let Python do the cleaning. We will use <strong>pandas<\/strong>, a free Python library for working with tables. A library is like a toolbox: someone already built the tools, and you just use them. If you know Excel, pandas will feel familiar. A pandas table is called a <strong>DataFrame<\/strong>, and it is very much like a sheet with columns and rows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What you need<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Python 3 from <a href=\"https:\/\/www.python.org\">https:\/\/www.python.org<\/a><\/li>\n\n\n<li>pandas and openpyxl (openpyxl lets pandas read and write <code>.xlsx<\/code> files)<\/li>\n\n\n<li>A messy file, for example <code>export.xlsx<\/code> or <code>export.csv<\/code><\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Install the libraries by typing this in the Command Prompt:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pip install pandas openpyxl<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Read the file<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a file called <code>clean.py<\/code> and start with this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import pandas as pd\n\n# Read an Excel export (change the sheet name if needed)\ndf = pd.read_excel(\"export.xlsx\", sheet_name=0)\n\n# For a CSV export, use this line instead:\n# df = pd.read_csv(\"export.csv\", encoding=\"utf-8\")\n\nprint(df.head())   # show the first 5 rows\nprint(df.dtypes)   # show the type of each column<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>df.dtypes<\/code> is very useful. If a number column shows <code>object<\/code>, pandas sees it as text. We will fix that.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Clean the column names and the text<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">System exports often have spaces in the headers, like <code>\" Customer \"<\/code>. Let us make the names clean and simple:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># \" Invoice Date \" becomes \"invoice_date\"\ndf.columns = (\n    df.columns.str.strip()\n              .str.lower()\n              .str.replace(\" \", \"_\")\n)\n\n# Remove extra spaces from every text column\nfor col in df.select_dtypes(include=\"object\").columns:\n    df[col] = df[col].str.strip()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>str.strip()<\/code> works like the <code>TRIM<\/code> function in Excel. It removes spaces at the start and the end.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Fix the dates<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Dates stored as text cannot be sorted or filtered by month. Let us turn them into real dates:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df[\"invoice_date\"] = pd.to_datetime(\n    df[\"invoice_date\"], dayfirst=True, errors=\"coerce\"\n)<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>dayfirst=True<\/code> reads <code>05\/03\/2023<\/code> as 5 March, not May 3. Use it if your system writes the day first.<\/li>\n\n\n<li><code>errors=\"coerce\"<\/code> turns bad values into empty dates (<code>NaT<\/code>) instead of stopping the script.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Fix numbers stored as text<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Values like <code>\"1,250.00\"<\/code> or <code>\"SAR 300\"<\/code> are text. We remove everything that is not a digit, a dot or a minus sign, then convert:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df[\"amount\"] = (\n    df[\"amount\"].astype(str)\n                .str.replace(r\"[^0-9.\\-]\", \"\", regex=True)\n)\ndf[\"amount\"] = pd.to_numeric(df[\"amount\"], errors=\"coerce\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The strange text <code>[^0-9.\\-]<\/code> is a <strong>regular expression<\/strong>. It is a small pattern language. This one means &#8220;any character that is not a digit, a dot or a minus&#8221;.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5: Remove duplicates and empty rows<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>before = len(df)\n\n# Remove rows that are exactly the same\ndf = df.drop_duplicates()\n\n# Or remove rows with the same invoice number, keeping the first one\ndf = df.drop_duplicates(subset=[\"invoice_number\"], keep=\"first\")\n\n# Remove rows where the amount is missing\ndf = df.dropna(subset=[\"amount\"])\n\nprint(f\"Removed {before - len(df)} rows\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Printing how many rows you removed is a good habit. If the number is huge, something is wrong, and you will see it fast.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 6: Save the clean file<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>df.to_excel(\"export_clean.xlsx\", index=False)\nprint(\"Saved export_clean.xlsx\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>index=False<\/code> stops pandas from adding an extra column with row numbers. Run the script:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>python clean.py<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Open <code>export_clean.xlsx<\/code>. The dates are real dates, the amounts are real numbers, and the duplicates are gone. \ud83c\udf89 Your original file is not changed, so you can always start again.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common problems<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>KeyError: 'invoice_date'<\/code><\/strong> \u2192 The column has a different name in your file. Print <code>df.columns<\/code> and use the exact name.<\/li>\n\n\n<li><strong><code>ModuleNotFoundError: No module named 'openpyxl'<\/code><\/strong> \u2192 Run <code>pip install openpyxl<\/code>.<\/li>\n\n\n<li><strong>Strange letters in a CSV (like <code>\u00c3\u00a9<\/code>).<\/strong> \u2192 Try <code>encoding=\"utf-8-sig\"<\/code> or <code>encoding=\"cp1252\"<\/code> in <code>read_csv<\/code>.<\/li>\n\n\n<li><strong><code>PermissionError<\/code> when saving.<\/strong> \u2192 The output file is open in Excel. Close it and run again.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Wrap-up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You now have a cleaning script that you can use every week. Next week, export the new file with the same name, run <code>python clean.py<\/code>, and the cleaning is done in seconds. The steps never get tired and never forget a step.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next, try adding a summary at the end, like <code>df.groupby(\"region\")[\"amount\"].sum()<\/code>. It works like a PivotTable, and you can save it to a second file. Happy cleaning!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Clean a messy export file with a few lines of Python and pandas, and run the same cleaning again every week.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[14,5,13,12],"class_list":["post-45","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-data-cleaning","tag-excel","tag-pandas","tag-python"],"_links":{"self":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/45","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":1,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/45\/revisions"}],"predecessor-version":[{"id":88,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/45\/revisions\/88"}],"wp:attachment":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/media?parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/categories?post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/tags?post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}