Data_Manipulation Summary.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load in the pandas module \n",
    "import pandas as pd \n",
    "\n",
    "# read in data from workign directory (folder in top right)\n",
    "# can read in from anywhere if full path is the pd.read_csv()\n",
    "data = pd.read_csv('craigslistVehicles.csv')\n",
    "\n",
    "#view columns & rename columns \n",
    "data.columns\n",
    "data.rename(index=str,columns={\"url\":\"new_url\"})\n",
    "data.rename(index=str,columns={\"new_url\":\"url\"})\n",
    "\n",
    "# view all rows for one column\n",
    "data['url']\n",
    "\n",
    "#view all columns for select group of rows\n",
    "data[0:10]\n",
    "\n",
    "#filter for multiple columns (all below do the same thing ) \n",
    "data[['url','city','price']]\n",
    "data.loc[:,['url','city','price']]\n",
    "data.iloc[:,0:3]\n",
    "\n",
    "#filter by rows and columns \n",
    "data.loc[0:100,['url','city','price']]\n",
    "data.iloc[0:100,0:3]\n",
    "\n",
    "#filter by column list \n",
    "data[data.columns]\n",
    "\n",
    "\n",
    "#drop / add column #inplace = True \n",
    "#axis & inplace \n",
    "data.drop('url', axis = 1)\n",
    "data.drop(['url','price'], axis = 1)\n",
    "\n",
    "#add column \n",
    "data['age'] = 2019 - data['year']\n",
    "\n",
    "#filtering data by columns & boolean indexing \n",
    "data[(data['age'] < 5)]\n",
    "\n",
    "data.loc[(data.age <5),:]\n",
    "\n",
    "# basic operators on columns \n",
    "data['price_per_mile'] = data['price'] / data['odometer']\n",
    "\n",
    "# apply function \n",
    "\n",
    "def timex2(x):\n",
    "    return 2*x\n",
    "\n",
    "data['price2x'] = data['price'].apply(timex2)\n",
    "data['price'].head()\n",
    "data['price2x'].head()\n",
    "\n",
    "#lambda function \n",
    "data['price3x'] = data['price'].apply(lambda x: x*3)\n",
    "data['price3x'].head()\n",
    "\n",
    "#tenary operator \n",
    "data['expensive'] = data['price'].apply(lambda x: 'expensive' if x > 10000 else 'cheap')\n",
    "\n",
    "data['newandcheap'] = data.apply(lambda x: 'yes' if x['price'] < 10000 and x['age'] < 5 else 'no', axis = 1)\n",
    "data['newandcheap2'] = data[['price','age']].apply(lambda x: 'yes' if x[0] < 10000 and x[1] < 5 else 'no', axis = 1)\n",
    "\n",
    "#binning pd.cut / pd.qcut\n",
    "pd.qcut(data.price,5) #even number \n",
    "pd.cut(data.price,5 ) #even spacing \n",
    "\n",
    "#dummy variables \n",
    "data_dummies = pd.get_dummies(data[['price','year','fuel','transmission','type']])\n",
    "\n",
    "#pivot table / sort_index / sort_values \n",
    "data.pivot_table(index='year',columns='type',values='price',aggfunc ='mean').sort_index(ascending=False)\n",
    "data.pivot_table(index='year',columns='type',values='price',aggfunc ='count').sort_index(ascending=False)\n",
    "data.pivot_table(index='year',columns='type',values='price',aggfunc ='count').sort_index(ascending=False).plot()\n",
    "\n",
    "#groupby \n",
    "data.groupby('type').mean()\n",
    "data.groupby(['type','fuel']).mean()\n",
    "data.groupby(['type','fuel'],as_index = False).mean()\n",
    "\n",
    "# pd.merge == to join in sql  \n",
    "df1 = data[['url','city']]\n",
    "df2 = data[['url','price']]\n",
    "\n",
    "df_merged = pd.merge(df1,df2,on='url')\n",
    "\n",
    "\n",
    "#append and concatenate (pd.concat / pd.append)\n",
    "data100 = data.sample(100, random_state = 1)\n",
    "data1002 = data.sample(100, random_state = 2)\n",
    "\n",
    "data100.append(data1002)\n",
    "pd.concat([data100,data1002], axis = 0)\n",
    "\n",
    "# write to a csv file pd.to_csv()\n",
    "data100.to_csv('data100.csv')"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}