Dec-30-2019, 09:55 PM
How do I replace "NaN" field with an empty space?
See, python script below:
See, python script below:
{ "cells": [ { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "colnames=[\"1\",\"first_name\",\"last_name\", \"email\", \"address\",\"city\",\"state\",\"zip_code\",\"zip_code_plus\",\"campaign\"] \n", "\n", "df=pd.read_csv(\"C:\\\\Users\\\\AdminUser\\\\Desktop\\\\data\\\\raw_data.csv\",names=colnames, sep=\";\", header=None,quotechar=\"'\")\n", "# use your location of file insted of mine also check the file type either it is csv or excel" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "df=df.drop(columns=[\"1\"],axis=1)" ] }, { "cell_type": "code", "execution_count": 11, "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>first_name</th>\n", " <th>last_name</th>\n", " <th>email</th>\n", " <th>address</th>\n", " <th>city</th>\n", " <th>state</th>\n", " <th>zip_code</th>\n", " <th>zip_code_plus</th>\n", " <th>campaign</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <td>0</td>\n", " <td>\"\"Varun\"\"</td>\n", " <td>\"\"Aggarwal\"\"</td>\n", " <td>\"\"[email protected]\"\"</td>\n", " <td>\"\"29 Royal Grv\"\"</td>\n", " <td>\"\"Irvine\"\"</td>\n", " <td>\"\"CA\"\"</td>\n", " <td>\"\"92620\"\"</td>\n", " <td>\"\"3548\"\"</td>\n", " <td>\"\"Subscription Campaign\"\"\"</td>\n", " </tr>\n", " <tr>\n", " <td>1</td>\n", " <td>\"\"Sara\"\"</td>\n", " <td>\"\"Alkire\"\"</td>\n", " <td>\"\"[email protected]\"\"</td>\n", " <td>\"\"3958 Cochran St</td>\n", " <td>\"\"Subscription Campaign\"\"\"</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " </tr>\n", " <tr>\n", " <td>2</td>\n", " <td>\"\"Simi Valley\"\"</td>\n", " <td>\"\"CA\"\"</td>\n", " <td>\"\"93063\"\"</td>\n", " <td>\"\"2360\"\"</td>\n", " <td>\"\"Subscription Campaign\"\"\"</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " first_name last_name email \\\n", "0 \"\"Varun\"\" \"\"Aggarwal\"\" \"\"[email protected]\"\" \n", "1 \"\"Sara\"\" \"\"Alkire\"\" \"\"[email protected]\"\" \n", "2 \"\"Simi Valley\"\" \"\"CA\"\" \"\"93063\"\" \n", "\n", " address city state zip_code \\\n", "0 \"\"29 Royal Grv\"\" \"\"Irvine\"\" \"\"CA\"\" \"\"92620\"\" \n", "1 \"\"3958 Cochran St \"\"Subscription Campaign\"\"\" NaN NaN \n", "2 \"\"2360\"\" \"\"Subscription Campaign\"\"\" NaN NaN \n", "\n", " zip_code_plus campaign \n", "0 \"\"3548\"\" \"\"Subscription Campaign\"\"\" \n", "1 NaN NaN \n", "2 NaN NaN " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "new_df = pd.DataFrame(df)\n", "new_df=new_df.apply(lambda s:s.str.replace('\"', \"\"))\n" ] }, { "cell_type": "code", "execution_count": 15, "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>first_name</th>\n", " <th>last_name</th>\n", " <th>email</th>\n", " <th>address</th>\n", " <th>city</th>\n", " <th>state</th>\n", " <th>zip_code</th>\n", " <th>zip_code_plus</th>\n", " <th>campaign</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <td>0</td>\n", " <td>Varun</td>\n", " <td>Aggarwal</td>\n", " <td>[email protected]</td>\n", " <td>29 Royal Grv</td>\n", " <td>Irvine</td>\n", " <td>CA</td>\n", " <td>92620</td>\n", " <td>3548</td>\n", " <td>Subscription Campaign</td>\n", " </tr>\n", " <tr>\n", " <td>1</td>\n", " <td>Sara</td>\n", " <td>Alkire</td>\n", " <td>[email protected]</td>\n", " <td>3958 Cochran St</td>\n", " <td>Subscription Campaign</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " </tr>\n", " <tr>\n", " <td>2</td>\n", " <td>Simi Valley</td>\n", " <td>CA</td>\n", " <td>93063</td>\n", " <td>2360</td>\n", " <td>Subscription Campaign</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " <td>NaN</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " first_name last_name email address \\\n", "0 Varun Aggarwal [email protected] 29 Royal Grv \n", "1 Sara Alkire [email protected] 3958 Cochran St \n", "2 Simi Valley CA 93063 2360 \n", "\n", " city state zip_code zip_code_plus campaign \n", "0 Irvine CA 92620 3548 Subscription Campaign \n", "1 Subscription Campaign NaN NaN NaN NaN \n", "2 Subscription Campaign NaN NaN NaN NaN " ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "new_df" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "new_df.to_csv(\"C:\\\\Users\\\\AdminUser\\\\Desktop\\\\data\\\\analysis_output.csv\" ,index=False)\n", "#this is my location but use your location on which you want to export it" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "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.4" } }, "nbformat": 4, "nbformat_minor": 2 }