import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
add new column to pandas dataframe with default value
<pre><code>#here is the simplist way to add the new column
df['My new column'] = 'default value'
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>My new column</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>default value</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>default value</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>default value</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>default value</td>
</tr>
</tbody>
</table>
<pre><code>#if you want to specify the order of the column, you can use insert
#here, we are inserting at index 1 (so should be second col in dataframe)
df.insert(1, 'My 2nd new column', 'default value 2')
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>My 2nd new column</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>My new column</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>default value 2</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>default value</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>default value 2</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>default value</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>default value 2</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>default value</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>default value 2</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>default value</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to add new column to Pandas DataFrame with default value. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Add new column to Pandas dataframe with default value

Import modules
<pre><code>import pandas as pd</code></pre>
 
Create some dummy data
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
Create the pivot table
<pre><code>#strip dataframe to contain just name, favorite_color
df = df[['name', 'favorite_color']]

#pivot the df (note that reset_index can be removed if we want to hold pivot levels. In most cases I reset the index)
df_pivot = df.groupby(['favorite_color']).count().reset_index()

#rename 'name' column to n-count of colors
df_pivot.columns = ['favorite_color', 'name']

df_pivot.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>favorite_color</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>blue</td>
<td>2</td>
</tr>
<tr>
<td>1</td>
<td>green</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>yellow</td>
<td>1</td>
</tr>
</tbody>
</table>
<pre><code>#you can pull mean, other aggregate functions by replacing 'count' with 'mean', etc
#you can select multiple levels for your pivot in the groupby function, e.g. df.groupby(['X', 'Y', 'Z']).count()</code></pre>

A step-by-step Python code example that shows how to create pivot table using Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a pivot table from a Pandas dataframe

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
get list from column of Pandas dataframe
<pre><code>#select column to convert to list here
age_list = df["age"].tolist()
age_list</code></pre>
<pre><code>[20, 19, 22, 21]</code></pre>

A step-by-step Python code example that shows how to convert a column in a Pandas DataFrame to a list. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Convert column in Pandas dataframe to a list

Import modules
<pre><code>import pandas as pd</code></pre>
 
Create some dummy data
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', &#x26;quot;green&#x26;quot;],
'test_one': [88, 92, 95, 70],
'test_two': [78, 100, 90, 85]}
df = pd.DataFrame(raw_data)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>name</th>
<th>test_one</th>
<th>test_two</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>Willard Morris</td>
<td>88</td>
<td>78</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>Al Jennings</td>
<td>92</td>
<td>100</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>Omar Mullins</td>
<td>95</td>
<td>90</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>Spencer McDaniel</td>
<td>70</td>
<td>85</td>
</tr>
</tbody>
</table>
<pre><code>df['test_average'] = (df.test_one + df.test_two)/2
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>name</th>
<th>test_one</th>
<th>test_two</th>
<th>test_average</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>Willard Morris</td>
<td>88</td>
<td>78</td>
<td>83.0</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>Al Jennings</td>
<td>92</td>
<td>100</td>
<td>96.0</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>Omar Mullins</td>
<td>95</td>
<td>90</td>
<td>92.5</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>Spencer McDaniel</td>
<td>70</td>
<td>85</td>
<td>77.5</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to create new column to existing Pandas Dataframe. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a new column in Pandas dataframe

<pre><code>import pandas as pd
import numpy as np</code></pre>
Create a dataframe with random integers
<pre><code>#using numpy's randint
df = pd.DataFrame(np.random.randint(0,100,size=(15, 4)), columns=list('ABCD'))</code></pre>
<pre><code>#preview the df
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>A</th>
<th>B</th>
<th>C</th>
<th>D</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>91</td>
<td>96</td>
<td>36</td>
<td>89</td>
</tr>
<tr>
<td>1</td>
<td>17</td>
<td>18</td>
<td>40</td>
<td>97</td>
</tr>
<tr>
<td>2</td>
<td>38</td>
<td>12</td>
<td>22</td>
<td>63</td>
</tr>
<tr>
<td>3</td>
<td>38</td>
<td>13</td>
<td>17</td>
<td>96</td>
</tr>
<tr>
<td>4</td>
<td>48</td>
<td>68</td>
<td>65</td>
<td>59</td>
</tr>
<tr>
<td>5</td>
<td>45</td>
<td>28</td>
<td>65</td>
<td>79</td>
</tr>
<tr>
<td>6</td>
<td>49</td>
<td>73</td>
<td>36</td>
<td>20</td>
</tr>
<tr>
<td>7</td>
<td>0</td>
<td>69</td>
<td>11</td>
<td>87</td>
</tr>
<tr>
<td>8</td>
<td>90</td>
<td>19</td>
<td>49</td>
<td>7</td>
</tr>
<tr>
<td>9</td>
<td>93</td>
<td>35</td>
<td>97</td>
<td>55</td>
</tr>
<tr>
<td>10</td>
<td>28</td>
<td>80</td>
<td>27</td>
<td>40</td>
</tr>
<tr>
<td>11</td>
<td>74</td>
<td>42</td>
<td>1</td>
<td>26</td>
</tr>
<tr>
<td>12</td>
<td>81</td>
<td>12</td>
<td>28</td>
<td>53</td>
</tr>
<tr>
<td>13</td>
<td>63</td>
<td>63</td>
<td>60</td>
<td>61</td>
</tr>
<tr>
<td>14</td>
<td>10</td>
<td>54</td>
<td>39</td>
<td>0</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to create Pandas dataframe with random numbers. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create Pandas dataframe with random numbers

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 20, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>20</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
return the frequency of each unique value in 'age' column in Pandas dataframe
<pre><code>df.groupby('age').size()</code></pre>
<pre><code> age 20 2 21 1 22 1 dtype: int64</code></pre>

A step-by-step Python code example that shows how to count the frequency a value occurs in Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Count the frequency a value occurs in Pandas dataframe

<pre><code>import pandas as pd
import numpy as np</code></pre>
Create a dataframe
<pre><code>#create a dataframe
df = pd.DataFrame({'date': ['2013-04-01','2013-04-01','2013-04-01','2013-04-02', '2013-04-02'],
'user_id': ['0001', '0001', '0002', '0002', '0002'],
'duration': [30, 15, 20, 15, 30]})
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>date</th>
<th>duration</th>
<th>user_id</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>2013-04-01</td>
<td>30</td>
<td>0001</td>
</tr>
<tr>
<td>1</td>
<td>2013-04-01</td>
<td>15</td>
<td>0001</td>
</tr>
<tr>
<td>2</td>
<td>2013-04-01</td>
<td>20</td>
<td>0002</td>
</tr>
<tr>
<td>3</td>
<td>2013-04-02</td>
<td>15</td>
<td>0002</td>
</tr>
<tr>
<td>4</td>
<td>2013-04-02</td>
<td>30</td>
<td>0002</td>
</tr>
</tbody>
</table>
Count distinct in Pandas aggregation
<pre><code>#here we can count the number of distinct users viewing on a given day
df = df.groupby("date").agg({"duration": np.sum, "user_id": pd.Series.nunique})
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>duration</th>
<th>user_id</th>
</tr>
</thead>
<tbody>
<tr>
<td>date</td>
<td></td>
<td></td>
</tr>
<tr>
<td>2013-04-01</td>
<td>65</td>
<td>2</td>
</tr>
<tr>
<td>2013-04-02</td>
<td>45</td>
<td>1</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to count distinct in a Pandas aggregation. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Count distinct in Pandas aggregation

<pre><code>import pandas as pd</code></pre>
<pre><code>#create a dataframe
raw_data = {'name': ['Willard Morris', 'Al Jennings'],
'age': [20, 19],
'favorite_color': ['blue', 'red'],
'grade': [88, 92]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>
<pre><code>#create another dataframe
raw_data_2 = {'name': [ 'Omar Mullins', 'Spencer McDaniel'],
'age': [22, 21],
'favorite_color': ['yellow', "green"],
'grade': [95, 70]}
df_2 = pd.DataFrame(raw_data, index = ['Omar Mullins', 'Spencer McDaniel'])
df_2</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Omar Mullins</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>
<pre><code>#create list of dataframes we want to concatenate
dataframes = [df, df_2]</code></pre>
<pre><code>#concatenate the dataframes. analagous to union function in sql
dataframes_concat = pd.concat(dataframes)
dataframes_concat</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>

Code example that shows how to concatentate Pandas dataframes (like a union function in SQL). Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Concatenate Pandas dataframes (like a union function in SQL)

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
change order of the columns
<pre><code>#now 'age' will appear at the end of our df
df = df[['favorite_color','grade','name','age']]
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>20</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>19</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>22</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>21</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to change the order of DataFrame columns. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Change the order of columns in Pandas dataframe

Using iPython's display function, we can dynamically refresh our plot
<pre><code>%matplotlib inline
import time
import pylab as pl
from IPython import display
for i in range(10):
pl.plot(pl.randn(100))
display.clear_output(wait=True)
display.display(pl.gcf())
time.sleep(1.0)</code></pre>
<a href="/images/snippets/dynamic_update_plot_loop_ipython_image_1.png"><img src="/images/snippets/dynamic_update_plot_loop_ipython_image_1.png" alt="dynamic update plot loop ipython image 1"></a>

A step-by-step Python code example that shows how to dynamically update a plot in iPython notebook with matplotlib. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Dynamically update a plot in iPython notebook

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
 
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
 
drop a row by name
<pre><code>df.drop(['Willard Morris', 'Spencer McDaniel'])</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
</tbody>
</table>
 
drop a row by number
<pre><code>df.drop(df.index[0], inplace=True)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
 
drop first 2 rows (put ':' to left of # to drop last X rows)
<pre><code>df.drop(df.index[:2], inplace=True)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
 
dropping column by name
<pre><code>df.drop(['age'], axis = 1, inplace = True)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Spencer McDaniel</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to drop duplicate row values in a Pandas DataFrame based on a given column value. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Dropping rows/columns from a Pandas dataframe

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
drop a duplicate row, based on column name
<pre><code>#here we should drop Al Jennings' record from the df, 
#since his favorite color, blue, is a duplicate with Willard Morris
df = df.drop_duplicates(subset='favorite_color', keep="first")
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>

Drop duplicate rows in Pandas based on column value

import modules
<pre><code>import pandas as pd
import numpy as np
import datetime</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70],
'birth_date': ['01-02-1996', '08-05-1997', '04-28-1996', '12-16-1995']}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>01-02-1996</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>08-05-1997</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>04-28-1996</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>12-16-1995</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
create a new column with year of date field 'birth_date'
<pre><code>#pandas datetimeindex docs: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.html
#efficient way to extract year from string format date
df['year'] = pd.DatetimeIndex(df['birth_date']).year
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>year</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>01-02-1996</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>1996</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>08-05-1997</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>1997</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>04-28-1996</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>1996</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>12-16-1995</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>1995</td>
</tr>
</tbody>
</table>
create a new column with month of date field 'birth_date'
<pre><code>#pandas datetimeindex docs: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.html
df['month'] = pd.DatetimeIndex(df['birth_date']).month
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>year</th>
<th>month</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>01-02-1996</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>1996</td>
<td>1</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>08-05-1997</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>1997</td>
<td>8</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>04-28-1996</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>1996</td>
<td>4</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>12-16-1995</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>1995</td>
<td>12</td>
</tr>
</tbody>
</table>
<pre><code>#if the date format comes in datetime, we can also extract the day/month/year using the to_period function
#where 'D', 'M', 'Y' are inputs
df['month_year'] = pd.to_datetime(df['birth_date']).dt.to_period('M')
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
<th>year</th>
<th>month</th>
<th>month_year</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>01-02-1996</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
<td>1996</td>
<td>1</td>
<td>1996-01</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>08-05-1997</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
<td>1997</td>
<td>8</td>
<td>1997-08</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>04-28-1996</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
<td>1996</td>
<td>4</td>
<td>1996-04</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>12-16-1995</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
<td>1995</td>
<td>12</td>
<td>1995-12</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to extract month and year from a date column and put the values into new columns in Pandas. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Extract month and year from column in Pandas, create new column

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
 
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
 
strip/replace character in column
<pre><code>df.columns = [x.strip().replace('_', '_TEST_') for x in df.columns]
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_TEST_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to find and replace characters in a Pandas DataFrame column header. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Find and replace characters in Pandas dataframe columns

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, columns = ['name', 'age', 'favorite_color', 'grade'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>name</th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
</tr>
<tr>
<td>1</td>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
</tr>
<tr>
<td>2</td>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
</tr>
<tr>
<td>3</td>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
</tr>
</tbody>
</table>
get the row and column count of the df
<pre><code>df.shape</code></pre>
<pre><code>(4, 4)</code></pre>
Format is (# rows, # cols), so there are both 4 rows and 4 columns in our example df.

A step-by-step Python code example that shows how to calculate the row count and column count from a Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Get the row count and column count from a Pandas dataframe

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
return the average/mean from a Pandas column
<pre><code>df['grade'].mean()</code></pre>
<pre><code>86.25</code></pre>
return the median from a Pandas column
<pre><code>df['grade'].median()</code></pre>
<pre><code>90.0</code></pre>
return descriptive statistics from Pandas dataframe
<pre><code>#Aside from the mean/median, you may be interested in general descriptive statistics of your dataframe
#--'describe' is a handy function for this
df.describe()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>count</td>
<td>4.000000</td>
<td>4.000000</td>
</tr>
<tr>
<td>mean</td>
<td>20.500000</td>
<td>86.250000</td>
</tr>
<tr>
<td>std</td>
<td>1.290994</td>
<td>11.206397</td>
</tr>
<tr>
<td>min</td>
<td>19.000000</td>
<td>70.000000</td>
</tr>
<tr>
<td>25%</td>
<td>19.750000</td>
<td>83.500000</td>
</tr>
<tr>
<td>50%</td>
<td>20.500000</td>
<td>90.000000</td>
</tr>
<tr>
<td>75%</td>
<td>21.250000</td>
<td>92.750000</td>
</tr>
<tr>
<td>max</td>
<td>22.000000</td>
<td>95.000000</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to calculate the mean and median from a Pandas column.  Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Get the mean and median from a Pandas column in Python

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, columns = ['name', 'age', 'favorite_color', 'grade'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>name</th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
</tr>
<tr>
<td>1</td>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
</tr>
<tr>
<td>2</td>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
</tr>
<tr>
<td>3</td>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
</tr>
</tbody>
</table>
Iterate over rows in Pandas dataframe
Using iterrows:
<pre><code>for index, row in df.iterrows():
 print (row[&#x26;quot;name&#x26;quot;], row[&#x26;quot;age&#x26;quot;])</code></pre>
<pre><code>Willard Morris 20
Al Jennings 19
Omar Mullins 22
Spencer McDaniel 21</code></pre>
Using itertuples:
<pre><code>for row in df.itertuples(index=True, name='Pandas'):
 print (getattr(row, &#x26;quot;name&#x26;quot;), getattr(row, &#x26;quot;age&#x26;quot;))</code></pre>
<pre><code>Willard Morris 20 Al Jennings 19 Omar Mullins 22 Spencer McDaniel 21</code></pre>
If you wish to modify the rows you're iterating over, then df.apply is preferred:
<pre><code>def valuation_formula(x):
 return x * 0.5

df['age_half'] = df.apply(lambda row: valuation_formula(row['age']), axis=1)

df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>name</th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>age_half</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>10.0</td>
</tr>
<tr>
<td>1</td>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>9.5</td>
</tr>
<tr>
<td>2</td>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>11.0</td>
</tr>
<tr>
<td>3</td>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>10.5</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to Iterate over rows in a DataFrame in Pandas. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Iterate over rows in a dataframe in Pandas

Import modules
<pre><code>import pandas as pd</code></pre>
 
Create some dummy data
<pre><code>raw_data1 = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', &#x26;quot;green&#x26;quot;],
'test_one': [88, 92, 95, 70]}
df1 = pd.DataFrame(raw_data1)
df1</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>name</th>
<th>test_one</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>Willard Morris</td>
<td>88</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>Al Jennings</td>
<td>92</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>Omar Mullins</td>
<td>95</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>Spencer McDaniel</td>
<td>70</td>
</tr>
</tbody>
</table>
<pre><code>raw_data2 = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'test_two': [87, 94, 97, 72],
'test_three': [78, 100, 90, 85]}
df2 = pd.DataFrame(raw_data2)
df2</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>name</th>
<th>test_three</th>
<th>test_two</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Willard Morris</td>
<td>78</td>
<td>87</td>
</tr>
<tr>
<td>1</td>
<td>Al Jennings</td>
<td>100</td>
<td>94</td>
</tr>
<tr>
<td>2</td>
<td>Omar Mullins</td>
<td>90</td>
<td>97</td>
</tr>
<tr>
<td>3</td>
<td>Spencer McDaniel</td>
<td>85</td>
<td>72</td>
</tr>
</tbody>
</table>
 
Merge the two dataframes, using 'name' as the join key
<pre><code>df3 = df1.merge(df2, how='left', on=&#x26;quot;name&#x26;quot;)
df3.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>name</th>
<th>test_one</th>
<th>test_three</th>
<th>test_two</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>Willard Morris</td>
<td>88</td>
<td>78</td>
<td>87</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>Al Jennings</td>
<td>92</td>
<td>100</td>
<td>94</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>Omar Mullins</td>
<td>95</td>
<td>90</td>
<td>97</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>Spencer McDaniel</td>
<td>70</td>
<td>85</td>
<td>72</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to merge two dataframes together in Pandas. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Merge two dataframes together in Pandas

<pre><code>import pandas as pd
import numpy as np</code></pre>
Create a dataframe
<pre><code>df = pd.DataFrame({'B': range(5)})

df.index = [pd.Timestamp('20190101 09:00:00'),
pd.Timestamp('20190101 09:00:02'),
pd.Timestamp('20190101 09:00:03'),
pd.Timestamp('20190101 09:00:05'),
pd.Timestamp('20190101 09:00:06')]

df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>B</th>
</tr>
</thead>
<tbody>
<tr>
<td>2019-01-01 09:00:00</td>
<td>0</td>
</tr>
<tr>
<td>2019-01-01 09:00:02</td>
<td>1</td>
</tr>
<tr>
<td>2019-01-01 09:00:03</td>
<td>2</td>
</tr>
<tr>
<td>2019-01-01 09:00:05</td>
<td>3</td>
</tr>
<tr>
<td>2019-01-01 09:00:06</td>
<td>4</td>
</tr>
</tbody>
</table>
Create a rolling dataframe mean by time interval
<pre><code>#here it will create a rolling mean of the previous two records in the dataframe
#e.g. (0, (0+1)/2, (1+2)/2, etc)
df.rolling(2, min_periods=1).mean()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>B</th>
</tr>
</thead>
<tbody>
<tr>
<td>2019-01-01 09:00:00</td>
<td>0.0</td>
</tr>
<tr>
<td>2019-01-01 09:00:02</td>
<td>0.5</td>
</tr>
<tr>
<td>2019-01-01 09:00:03</td>
<td>1.5</td>
</tr>
<tr>
<td>2019-01-01 09:00:05</td>
<td>2.5</td>
</tr>
<tr>
<td>2019-01-01 09:00:06</td>
<td>3.5</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to create a rolling mean in a Pandas dataframe. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Pandas rolling mean in dataframe

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
 
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
rename a bunch of columns
<pre><code>df.columns = ['test_age', 'test_favorite_color','test_grade', 'test_name']
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>test_age</th>
<th>test_favorite_color</th>
<th>test_grade</th>
<th>test_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
 
rename a specific column
<pre><code>df.rename(columns={'age': 'is_just_a_number'}, inplace=True)
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>test_age</th>
<th>test_favorite_color</th>
<th>test_grade</th>
<th>test_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to rename columns in a Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Rename columns in a Pandas dataframe

<pre><code>import pandas as pd
import numpy as np</code></pre>
Create a dataframe
<pre><code>#create a dataframe
raw_data = {'name': ['Willard Morris', 'Al Jennings'],
'age': [20, 19],
'favorite_color': ['blue', 'red'],
'grade': [88, 92],
'grade': [88, 92]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>
Remove duplicate columns (based on column name)
<pre><code>#preview the df
df = df.loc[:,~df.columns.duplicated()]
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>
df.columns.duplicated() returns a boolean array: a True or False for each column--False means the column name is unique up to that point, True means it's a duplicate
Pandas allows one to index using boolean values whereby it selects only the True values.
Since we want to keep the unduplicated columns, we need the above boolean array to be flipped (ie [True, False, True] -> ~[False,True,False])

A step-by-step Python code example that shows how to remove duplicate columns by name in Pandas. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Remove duplicate columns by name in Pandas

<pre><code>import pandas as pd
import numpy as np</code></pre>
<pre><code>#create series
s = pd.Series([0,4,12,np.NaN,55,np.NaN,2,np.NaN])</code></pre>
<pre><code>#dropna - will work with pandas dataframe as well
s.dropna()</code></pre>
<pre><code>0 0.0
1 4.0
2 12.0
4 55.0
6 2.0
dtype: float64</code></pre>

Python code example that shows how to remove NaN values from a Pandas series. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Remove NaN values from a Pandas series

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
Create some NaN values in the dataframe
<pre><code>#First, we have to create the NaN values
df = df.replace(20,np.NaN)
df = df.replace(70,np.NaN)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>NaN</td>
<td>blue</td>
<td>88.0</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19.0</td>
<td>red</td>
<td>92.0</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22.0</td>
<td>yellow</td>
<td>95.0</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21.0</td>
<td>green</td>
<td>NaN</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
Replace the NaN values in the dataframe (with a 0 in this case)
<pre><code>#Now, we can replace them
df = df.fillna(0)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>0.0</td>
<td>blue</td>
<td>88.0</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19.0</td>
<td>red</td>
<td>92.0</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22.0</td>
<td>yellow</td>
<td>95.0</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21.0</td>
<td>green</td>
<td>0.0</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to replace all NaN values with 0's in a column of Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Replace all NaN values with 0's in a column of Pandas dataframe

<pre><code>import pandas as pd
import numpy as np</code></pre>
 
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, columns = ['name', 'age', 'favorite_color', 'grade'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>name</th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
</tr>
<tr>
<td>1</td>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
</tr>
<tr>
<td>2</td>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
</tr>
<tr>
<td>3</td>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
</tr>
</tbody>
</table>
 
save dataframe as csv
<pre><code>df.to_csv('../students.csv')</code></pre>
 
load dataframe in from csv
<pre><code>df = pd.read_csv('../students.csv')
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>Unnamed: 0</th>
<th>name</th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>0</td>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
</tr>
<tr>
<td>3</td>
<td>3</td>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to read data from a CSV and output data to CSV using Pandas DataFrames. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Read data from CSV into Pandas. Output data from Pandas to CSV

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
get the # of columns in the pandas dataframe
<pre><code>len(df.columns)</code></pre>
<pre><code>4</code></pre>

A step-by-step Python code example that shows how to return the number of columns in a Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Return the number of columns in a Pandas dataframe

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
sort Pandas dataframe based on two columns: age, grade
<pre><code>#age in ascending order, grade descending order
df.sort_values(['age', 'grade'], ascending=[True, False])</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to sort a DataFrame in Pandas based on multiple columns. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Sort a dataframe in Pandas based on multiple columns

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'red', 'yellow', "green"],
'grade': [88, 92, 95, 70],
'birth_date': ['01-02-1996', '08-05-1997', '04-28-1996', '12-16-1995']}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>01-02-1996</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>08-05-1997</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>04-28-1996</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>12-16-1995</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
Select Pandas dataframe rows between two dates
We can perform this using a boolean mask First, lets ensure the 'birth_date' column is in date format
<pre><code>df['birth_date'] = pd.to_datetime(df['birth_date'])</code></pre>
next, set the desired start date and end date to filter df with -- these can be in datetime (numpy and pandas), timestamp, or string format
<pre><code>start_date = '03-01-1996'
end_date = '06-01-1997'</code></pre>
next, set the mask -- we can then apply this to the df to filter it
<pre><code>mask = (df['birth_date'] &#x26;gt; start_date) &#x26;amp; (df['birth_date'] &#x26;lt;= end_date)</code></pre>
assign mask to df to return the rows with birth_date between our specified start/end dates
<pre><code>df = df.loc[mask]
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>birth_date</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>1996-04-28</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to select Pandas DataFrame rows between two dates. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Select Pandas dataframe rows between two dates

import modules
<pre><code>import pandas as pd</code></pre>
Create some dummy data
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', &#x26;quot;green&#x26;quot;],
'grade': [88, 92, 95, 70]}df = pd.DataFrame(raw_data)df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
<h3>Select rows based on column value:</h3>
<pre><code>#To select rows whose column value equals a scalar, some_value, use ==:df.loc[df['favorite_color'] == 'yellow']</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
</tbody>
</table>
<h3>Select rows whose column value is in an iterable array:</h3>
<pre><code>#To select rows whose column value is in an iterable array, which we'll define as array, you can use isin:array = ['yellow', 'green']df.loc[df['favorite_color'].isin(array)]</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
<h3>Select rows based on multiple column conditions:</h3>
<pre><code>#To select a row based on multiple conditions you can use &#x26;amp;:array = ['yellow', 'green']df.loc[(df['age'] == 21) &#x26;amp; df['favorite_color'].isin(array)]</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
<h3>Select rows where column does not equal a value:</h3>
<pre><code>#To select rows where a column value does not equal a value, use !=:df.loc[df['favorite_color'] != 'yellow']</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
<h3>Select rows whose column value is not in an iterable array:</h3>
<pre><code>#To return a rows where column value is not in an iterable array, use ~ in front of df:array = ['yellow', 'green']df.loc[~df['favorite_color'].isin(array)]</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>1</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to select rows from a Pandas DataFrame based on a column's values. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Select rows from a Pandas DataFrame based on values in a column

<pre><code>import pandas as pd
import numpy as np</code></pre>
Create a dataframe
<pre><code>#create a dataframe
raw_data = {'name': ['Willard Morris', 'Al Jennings'],
'age': [20, 19],
'favorite_color': ['blue', 'red'],
'grade': [88, 92],
'grade': [88, 92]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>
SearchInput pandas column with string contains
<pre><code>#here we can count the number of distinct users viewing on a given day
new_df = df[df['name'].str.contains('Morris', na=False)]
new_df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
</tbody>
</table>
SearchInput pandas column with string does not contain
<pre><code>#here we can count the number of distinct users viewing on a given day
new_df2 = df[~df['name'].str.contains('Morris', na=False)]
new_df2.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>red</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to search a Pandas column with string contains and does not contain. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

SearchInput pandas column with string contains and does not contain

Access the index of a Python for loop
<pre><code>#create list
a = [5,6,17,20]

#here we can use python's built-in enumerate function:
for idx, val in enumerate(a):
 print(idx, val)</code></pre>
<pre><code>0 5
1 6
2 17
3 20</code></pre>

A step-by-step Python code example that shows how to access the index of a Python for loop using enumerate. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Access the index of a Python for loop (enumerate)

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
Using ( )The preferred way of wrapping long lines is by using Python's implied line continuation inside parentheses, brackets and braces.
<pre><code>def print_something():
 print ('Look at us,', 
 'printing this sentence on multiple lines.')

print_something()</code></pre>
<pre><code>Look at us, printing this sentence on multiple lines.</code></pre>
Using \
<pre><code>#here we are putting multiple conditionals for our if statement on separate lines
z=99

if z &#x26;lt;100 or \
 z &#x26;gt; 0:
 print('True')</code></pre>
<pre><code>True</code></pre>

A step-by-step Python code example that shows how to break a long line into multiple lines in Python. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Break a long line into multiple lines in Python

<h3>Convert string to lowercase:</h3>
<pre><code>string = &#x26;quot;Snake&#x26;quot;print(string.lower())</code></pre>
<pre><code>snake</code></pre>
<h3>Convert string to uppercase:</h3>
<pre><code>string = &#x26;quot;BigSnake&#x26;quot;print(string.upper())</code></pre>
<pre><code>BIGSNAKE</code></pre>

A step-by-step Python code example that shows how to convert strings to lower and uppercase. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Convert strings to lower and uppercase in Python

import modules
<pre><code>import pandas as pd
import numpy as np
%matplotlib inline
import seaborn as sns</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
generate a scatter plot using seaborn
<pre><code># Set style of scatterplot
sns.set_context("notebook", font_scale=1.1)
sns.set_style("ticks")

# Create scatterplot of dataframe
sns.lmplot('age', # Select df column name for horizontal axis element
'grade', # Select df column name for vertical axis element
df, # Select data source
fit_reg = True, # Fix a regression line
scatter_kws={"s": 100}) # S marker size

# Set title
plt.title('Student grades vs. age')

# Set x-axis label
plt.xlabel('Age')

# Set y-axis label
plt.ylabel('Grade')

#complete docs: https://seaborn.pydata.org/generated/seaborn.lmplot.html#seaborn.lmplot</code></pre>
<a href="/images/snippets/seaborn_scatter_chart_python_image_1.png"><img src="/images/snippets/seaborn_scatter_chart_python_image_1.png" alt="seaborn scatter chart python image 1"></a>

A step-by-step Python code example that shows how to create a scatter plot in Python with Seaborn. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a scatter plot in Python with Seaborn

To test whether a given python string contains another string, you can use python's 'in' operator as shown below:
<pre><code>mystring = "The white fox jumped over the brown fence."

if "fence" in mystring: print("it's in the string")</code></pre>
<pre><code>it's in the string</code></pre>
<pre><code>mystring = "The white fox jumped over the brown fence."

if "blue" not in mystring: print("it's not in the string")</code></pre>
<pre><code>it's not in the string</code></pre>

Python code example that shows how to check whether a Python string contains another string. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Check whether a Python string contains another string

Check if a list is empty in Python
<pre><code>#create empty list
a = []

#empty list will return a boolean false
if not a:
print("List is empty")</code></pre>
<pre><code>List is empty</code></pre>
<pre><code>#if we want to check explicitly
if len(a) == 0:
print('List is empty')</code></pre>
<pre><code>List is empty</code></pre>

A step-by-step Python code example that shows how to check whether a list (array) is empty. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Check if a list is empty in Python

import modules
<pre><code>import pandas as pd
import numpy as np
%matplotlib inline
import matplotlib.pyplot as plt</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
generate a scatter plot using matplotlib
<pre><code>#set up the plot
plt.scatter(df['grade'], df['age'])
plt.xlabel('grade')
plt.ylabel('age')
plt.title('student age vs. grade')</code></pre>
<pre><code>&#x26;lt;matplotlib.text.Text at 0x10f17ffd0&#x26;gt;</code></pre>
<a href="/images/snippets/matplotlib_scatter_chart_python_image_1.png"><img src="/images/snippets/matplotlib_scatter_chart_python_image_1.png" alt="matplotlib scatter chart python image 1"></a>

A step-by-step Python code example that shows how to create a scatter plot in Python with matplotlib. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a scatter plot in Python with Matplotlib

import modules
<pre><code>import pandas as pd
import numpy as np
%matplotlib inline
import matplotlib.pyplot as plt</code></pre>
create dummy dataframe
<pre><code>raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}
df = pd.DataFrame(raw_data, index = ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'])
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>age</th>
<th>favorite_color</th>
<th>grade</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Willard Morris</td>
<td>20</td>
<td>blue</td>
<td>88</td>
<td>Willard Morris</td>
</tr>
<tr>
<td>Al Jennings</td>
<td>19</td>
<td>blue</td>
<td>92</td>
<td>Al Jennings</td>
</tr>
<tr>
<td>Omar Mullins</td>
<td>22</td>
<td>yellow</td>
<td>95</td>
<td>Omar Mullins</td>
</tr>
<tr>
<td>Spencer McDaniel</td>
<td>21</td>
<td>green</td>
<td>70</td>
<td>Spencer McDaniel</td>
</tr>
</tbody>
</table>
generate a bar plot using matplotlib
<pre><code>#filter the dataframe on student grades, so we can plot that variable
df = df[['grade']]
#set up the plot
my_plot = df.plot(kind='bar',title="Student grades",figsize=(9, 7))
my_plot.set_ylabel('Grade')</code></pre>
<pre><code>&#x26;lt;matplotlib.text.Text at 0x10ca91ba8&#x26;gt;</code></pre>
<a href="/images/snippets/matplotlib_bar_chart_python_image_1.png"><img src="/images/snippets/matplotlib_bar_chart_python_image_1.png" alt="matplotlib bar chart python image 1"></a>

A step-by-step Python code example that shows how to create a bar chart in Python with matplotlib. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a bar chart in Python with Matplotlib

<pre><code>#Return the post-tax earnings of an individual,
#assuming varying tax brackets depending on annual wage

def calcEarnings(hours, wage):
 earnings = hours*wage
 if earnings >= 2000:
 #30% tax rate for weekly earnings above $2000
 earnings = earnings*.70
 else:
 #15% tax rate for weekly earnings below $2000
 earnings = earnings*.85
 return earnings

#return the raw earnings of an individual (assuming no varying rates for overtime)
def calcEarnings_pretax(hours, wage):
 earnings = hours*wage
 return earnings

def main():
 hours = float(input('Enter hours worked for the week: '))
 wage = float(input('Enter dollars paid per hour: '))
 total = calcEarnings(hours, wage)
 total_pre_tax = calcEarnings_pretax(hours, wage)
 taxes = total_pre_tax - total
 print('Pre-tax earnings for {hours} hours at ${wage:.2f} per hour are ${total_pre_tax:.2f}.'
 .format(**locals()))
 print('Post-tax earnings for {hours} hours at ${wage:.2f} per hour are ${total:.2f}.'
 .format(**locals()))
 print('You gave uncle sam ${taxes:.2f} this week!'
 .format(**locals()))

main()</code></pre>
Output:
Enter hours worked for the week: 40 Enter dollars paid per hour: 55 Pre-tax earnings for 40.0 hours at $55.00 per hour are $2200.00. Post-tax earnings for 40.0 hours at $55.00 per hour are $1540.00. You gave uncle sam $660.00 this week!
You can run the above yourself if you copy/paste it into a notebook or text editor. Will ask you for two inputs (hours, wage) and calculate pre-tax earnings, post-tax earnings, and taxes paid.

A step-by-step Python code example that shows how to create and run a function. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create and run a function in Python

<h3>Convert to float:</h3>
<pre><code>i = 72</code></pre>
<pre><code>float(i)</code></pre>
<pre><code>72.0</code></pre>
<h3>Convert to int:</h3>
<pre><code>int(i)</code></pre>
<pre><code>72</code></pre>
<h3>Convert to str:</h3>
<pre><code>str(i)</code></pre>
<pre><code>'72'</code></pre>

A step-by-step Python code example that shows how to convert to a number to float, int, or string. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Convert to number to float, int, and string in Python

<pre><code>import pandas as pd</code></pre>
To convert a list of python dictionaries to a Pandas dataframe
<pre><code>my_dict = \
[{'score': 99, 'time': '5:15', 'year': 2010}, 
{'score': 75, 'time': '8:15', 'year': 2017}, 
{'score': 82, 'time': '12:30', 'year': 2030}]</code></pre>
<pre><code>df = pd.DataFrame(my_dict)
df</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>score</th>
<th>time</th>
<th>year</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>99</td>
<td>5:15</td>
<td>2010</td>
</tr>
<tr>
<td>1</td>
<td>75</td>
<td>8:15</td>
<td>2017</td>
</tr>
<tr>
<td>2</td>
<td>82</td>
<td>12:30</td>
<td>2030</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to convert a list of Python dictionaries to a Pandas dataframe. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Convert a list of Python dictionaries to a Pandas dataframe

<h3>Set up arrays</h3>
<pre><code>list_one = [7, 6, 5]list_two = [4, 3, 2]</code></pre>
<h3>Concatenate arrays horizontally</h3>
<pre><code>#horizontallymerged_list = list_one + list_twomerged_list</code></pre>
<pre><code>[7, 6, 5, 4, 3, 2]</code></pre>
<h3>Concatenate arrays vertically</h3>
<pre><code>#vertically
import numpy as np
np.vstack((list_one,list_two))</code></pre>
<pre><code>array([[7, 6, 5], [4, 3, 2]])</code></pre>

A step-by-step Python code example that shows how to concatenate two arrays (lists) in Python. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Concatenate two arrays (lists) in Python

Import modules
<pre><code>import datetime</code></pre>
Grab current datetime
<pre><code>datetime.datetime.now()</code></pre>
<pre><code>datetime.datetime(2017, 9, 7, 15, 6, 49, 819248)

Grab current time

datetime.datetime.now().time()</code></pre>
<pre><code>datetime.time(15, 8, 51, 723898)

Grab datetime in more readable format

str(datetime.datetime.now())</code></pre>
<pre><code>'2017-09-07 15:06:50.211892'
</code></pre>

A step-by-step Python code example that shows how to generate current time. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Generate current time in Python

<pre><code>#define array (you can also drop this directly into the for loop)
a = [1,2,3,4,5]
#loop through array, a
for i in a:
 #for each element i in array a, add 3
 k = i + 3
 #print result
 print(k)
else:
 #once loop is finished, print complete
 print('Complete')</code></pre>
#output`
<pre><code>45678Complete</code></pre>

A step-by-step Python code example that shows how to generate a For loop. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Generate a for loop in Python

Iterate over dictionaries in Python
<pre><code>#create a dict
mydict = {'jason':5,
'terry':1,
'michael':3,
'sam':6}

#using for loops
for key, value in mydict.items():
print (key, 'corresponds to', value)</code></pre>
<pre><code>terry corresponds to 1
jason corresponds to 5
michael corresponds to 3
sam corresponds to 6</code></pre>

A step-by-step Python code example that shows how to iterate through a dictionary (dict) in Python. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Iterate over dictionaries in Python

Prophet is a forecasting tool developed by Facebook to quickly forecast time series data, available in R and Python. In this post I'll walk you through a quick example of how to forecast U.S. candy sales using Prophet and Python.
First, we'll read in the data, which shows the 'industrial production index', or INDPRO (<a href="https://fred.stlouisfed.org/series/INDPRO#0">detail here</a>) for candy in the U.S. You can download the data in our github repository <a href="https://github.com/erood/interviewqs.com_code_snippets/blob/master/Datasets/candy_production.csv">here</a>.
<pre><code>#import libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from fbprophet import Prophet
#read in and preview our data
df = pd.read_csv('./datasets/candy_production.csv')
df.head()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>observation_date</th>
<th>IPG3113N</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>1972-01-01</td>
<td>85.6945</td>
</tr>
<tr>
<td>1</td>
<td>1972-02-01</td>
<td>71.8200</td>
</tr>
<tr>
<td>2</td>
<td>1972-03-01</td>
<td>66.0229</td>
</tr>
<tr>
<td>3</td>
<td>1972-04-01</td>
<td>64.5645</td>
</tr>
<tr>
<td>4</td>
<td>1972-05-01</td>
<td>65.0100</td>
</tr>
</tbody>
</table>
Great, so as we can see, we now have data showing U.S. candy production (normalized against 2012=100 in this dataset), which we can use as an input for our time series forecasting model with Prophet. Next, we'll need to do a little bit of cleaning to prep the data for Prophet.
<pre><code>#rename date column ds, value column y per Prohet specs
df.rename(columns={'observation_date': 'ds'}, inplace=True)
df.rename(columns={'IPG3113N': 'y'}, inplace=True)
#ensure our ds value is truly datetime
df['ds'] = pd.to_datetime(df['ds'])
#filtering here on >=1995, just to pull the last ~20 years of production information
start_date = '01-01-1995'
mask = (df['ds'] > start_date)
df = df.loc[mask]</code></pre>
Next, we can load our dataframe, df, into Prophet, and set a window for # of days we want it to predict
<pre><code>#initialize Prophet
m = Prophet()
#point towards dataframe
m.fit(df)
#set future prediction window of 2 years
future = m.make_future_dataframe(periods=730)
#preview our data -- note that Prophet is only showing future dates (not values), as we need to call the prediction method still
future.tail()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>ds</th>
</tr>
</thead>
<tbody>
<tr>
<td>996</td>
<td>2019-07-28</td>
</tr>
<tr>
<td>997</td>
<td>2019-07-29</td>
</tr>
<tr>
<td>998</td>
<td>2019-07-30</td>
</tr>
<tr>
<td>999</td>
<td>2019-07-31</td>
</tr>
<tr>
<td>1000</td>
<td>2019-08-01</td>
</tr>
</tbody>
</table>
Next, we can call the predict method, which will assign each row in our 'future' dataframe a predicted value, which it names yhat. Additionally, it will show lower/upper bounds of uncertainty, called yhat_lower and yhat_upper.
<pre><code>forecast = m.predict(future)
forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']].tail()</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>ds</th>
<th>yhat</th>
<th>yhat_lower</th>
<th>yhat_upper</th>
</tr>
</thead>
<tbody>
<tr>
<td>996</td>
<td>2019-07-28</td>
<td>117.796140</td>
<td>111.591576</td>
<td>123.519210</td>
</tr>
<tr>
<td>997</td>
<td>2019-07-29</td>
<td>117.016641</td>
<td>110.999394</td>
<td>122.908126</td>
</tr>
<tr>
<td>998</td>
<td>2019-07-30</td>
<td>116.001765</td>
<td>109.887603</td>
<td>122.016393</td>
</tr>
<tr>
<td>999</td>
<td>2019-07-31</td>
<td>114.757009</td>
<td>108.375085</td>
<td>121.465374</td>
</tr>
<tr>
<td>1000</td>
<td>2019-08-01</td>
<td>113.293294</td>
<td>107.234872</td>
<td>119.295134</td>
</tr>
</tbody>
</table>
We now have an initial time series forecast using Prophet, we can plot the results as shown below:
<pre><code>fig1 = m.plot(forecast)
fig1</code></pre>
<a href="/images/snippets/pic1.png"><img src="/images/snippets/pic1.png" alt="pic1"></a>
<pre><code>fig2 = m.plot_components(forecast)
fig2</code></pre>
<a href="/images/snippets/pic2.png"><img src="/images/snippets/pic2.png" alt="pic2"></a>
Here we can see at a high-level production is expected to continue it's upward trend over the next couple of years. Additionally, we can see the spikes in production for the various U.S. holidays (Valentine's Day, Halloween, Christmas). The Jupyter notebook used in this exercise can be found in our github repository <a href="https://github.com/erood/interviewqs.com_code_snippets/blob/master/iPython_Tutorial_Notebooks/FB_Prophet_Intro_v2_ddi.ipynb">here</a>.
For more in depth reading, would recommend checking out <a href="https://facebook.github.io/prophet/docs/saturating_forecasts.html">the docs</a>, as they're pretty easy to understand with additional detail/examples.

Introduction to time series forecasting with Prophet by Facebook, using Python.

Introduction to forecasting with FB Prophet

This post will walk through a practice problem which analyzes NBA player's season stats. We've also provided the practice problem's <a href="https://colab.research.google.com/drive/1UTzahnEZ2j1vqpfJEctv4NxV1T2K9S0g#scrollTo=xo0zBHonsD3r">Colab Notebook</a> so you can follow along, just copy the notebook and the two Google sheets (<a href="https://docs.google.com/spreadsheets/d/15NKCZNdFNe914qCnyDamuLj6Nq_-WyeBZ0IXCmvCm20/edit#gid=1473318657">season stats</a> and
<a href="https://docs.google.com/spreadsheets/d/19Xay4wMd2vxDTbz6n36y8RVPdbr1_o83nvFJhPQXPdA/edit">player data</a>) to your Google Drive.
This practice problem has a data set that contains NBA players and their individual season stats. This practice will take you through fitting a linear model using player stats to estimate <a href="https://www.basketball-reference.com/about/ws.html">win shares</a> per 48 minutes.
Topics covered in this tutorial:
<ul>
<li>Importing data from Google Sheets into Colab</li>
<li>Basic data cleaning / analysis</li>
<li>Splitting data into test and training sets</li>
<li>Training a single variable regression model</li>
<li>Training a multivariate regression model</li>
<li>Manually calculating Adjusted R-squared and R-squared</li>
<li>Calculating Root Mean Square Error</li>
<li>Comparing results of 2 regression models</li>
</ul>
Importing and cleaning data
<pre><code># Importing packages.
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sb
import numpy as np
import scipy.stats as ss
from sklearn.linear_model import LinearRegression
import seaborn as sns
sns.set(color_codes = True)
%matplotlib inline

# Setting figure sizes
plt.rcParams['figure.figsize'] = (8, 6)
plt.rcParams['font.size'] = 14</code></pre>
<pre><code># Code to connect Colab to Google Drive (save the spreadsheet provided into your drive)
# https://colab.research.google.com/notebooks/io.ipynb#scrollTo=JiJVCmu3dhFa

!pip install --upgrade -q gspread
from google.colab import auth
auth.authenticate_user()

import gspread
from oauth2client.client import GoogleCredentials

gc = gspread.authorize(GoogleCredentials.get_application_default())</code></pre>
<pre><code>### Importing sheet

# Open our new sheet and read some data.
worksheet = gc.open('Seasons_Stats').sheet1
# get_all_values gives a list of rows.
rows = worksheet.get_all_values()
# Convert to a DataFrame and render.
seasonPlayerStats = pd.DataFrame.from_records(rows)

### Fixing header of dataframe

# our header isn't named correctly lets fix our header
seasonPlayerStats.columns = seasonPlayerStats.iloc[0]
#drop the first row after we rename the header
seasonPlayerStats = seasonPlayerStats.reindex(seasonPlayerStats.index.drop(0))</code></pre>
<pre><code>### Importing sheet

# Open our new sheet and read some data.
worksheet = gc.open('player_data').sheet1
# get_all_values gives a list of rows.
rows = worksheet.get_all_values()
# Convert to a DataFrame and render.
playerData = pd.DataFrame.from_records(rows)

### Fixing header of dataframe
#rename header so it's the first row
playerData.columns = playerData.iloc[0]
#drop the first row after we rename the header
playerData = playerData.reindex(playerData.index.drop(0))</code></pre>
<pre><code># Merging dataframes and dropping null values so we avoid errors
# joining 
playerAllStats = pd.merge(seasonPlayerStats, playerData, how='left', on='Player')
# dropping null value columns to avoid errors 
playerAllStats = playerAllStats[pd.notnull(playerAllStats['height'])]
playerAllStats = playerAllStats[pd.notnull(playerAllStats['Points'])]
playerAllStats = playerAllStats[pd.notnull(playerAllStats['Games'])]
playerAllStats.head()</code></pre>
Output:
<a href="/images/snippets/img1.png"><img src="/images/snippets/img1.png" alt="img1"></a>
<pre><code># Viewing all columns that we have in the dataframe. I'll be referencing this figure out which fields I need to clean
playerAllStats.columns</code></pre>
Output:
<pre><code>
Index(['', 'Year', 'Player', 'Position', 'Age', 'Team', 'Games',
 'GamesStarted', 'MinutesPlayed', 'PlayerEfficiencyRating',
 'TrueShootingPerct', 'ThreePointAttemptRate', 'FreeThrowRate',
 'OffensiveReboundPercentage', 'DefensiveReboundPercentage',
 'TotalReboundPercentage', 'AssistPercentage', 'StealPercentage',
 'BlockPercentage', 'TurnoverPercentage', 'UsagePercentage',
 'OffesniveWinShares', 'DefensiveWinShares', 'WinShares',
 'WinsSharesPer48Minutes', 'OffensiveBoxPlusMinus',
 'DefensiveBoxPlusMinus', 'BoxPlusMinus', 'ValueOverReplacement',
 'FieldGoals', 'FieldGoalAttempts', 'FieldGoalPercentage',
 'ThreePointFieldGoals', 'ThreePointFieldGoalAttempts',
 'ThreePointFieldGoalPercentage', 'TwoPointFieldGoals',
 'TwoPointFieldGoalAttempts', 'TwoPointFieldGoalPercentage',
 'EffectiveFieldGoalPercentage', 'FreeThrows', 'FreeThrowAttempts',
 'FreeThrowPercentage', 'OffensiveRebounds', 'DefensiveRebounds',
 'TotalRebounds', 'Assists', 'Steals', 'Blocks', 'Turnovers',
 'PersonalFouls', 'Points', 'year_start', 'year_end', 'position',
 'height', 'weight', 'birth_date', 'college', 'feet', 'inches',
 'heightInches', 'PointsPerGame', 'TotalReboundsPerGame',
 'AssistsPerGame'],
 dtype='object', name=0)</code></pre>
Cleaning data even more
<ul>
<li>Converting height to inches (so we can use it if we decide to)</li>
<li>Converting some of the stats to a numeric value so we can do analysis</li>
<li>Calculating points per game, total rebounds per game, and assists per game</li>
</ul>
<pre><code>playerAllStats['feet'], playerAllStats['inches'] = playerAllStats['height'].str.split('-', 1).str
playerAllStats['feet'], playerAllStats['inches'], playerAllStats['weight'] = pd.to_numeric(playerAllStats.feet), \
 pd.to_numeric(playerAllStats.inches), pd.to_numeric(playerAllStats.weight)
playerAllStats['heightInches'] = playerAllStats['feet']*12 + playerAllStats['inches']

playerAllStats['Games'], playerAllStats['Points'] = pd.to_numeric(playerAllStats.Games), pd.to_numeric(playerAllStats.Points)
playerAllStats['MinutesPlayed'], playerAllStats['FieldGoalPercentage']= pd.to_numeric(playerAllStats.MinutesPlayed), pd.to_numeric(playerAllStats.FieldGoalPercentage)
playerAllStats['Games'], playerAllStats['TotalRebounds']= pd.to_numeric(playerAllStats.Games), pd.to_numeric(playerAllStats.TotalRebounds)
playerAllStats['Assists'], playerAllStats['WinsSharesPer48Minutes']= pd.to_numeric(playerAllStats.Assists), pd.to_numeric(playerAllStats.WinsSharesPer48Minutes)
playerAllStats['WinShares'], playerAllStats['Year'] = pd.to_numeric(playerAllStats.WinShares), pd.to_numeric(playerAllStats.Year)

playerAllStats['PointsPerGame'] = playerAllStats['Points']/ playerAllStats['Games']
playerAllStats['TotalReboundsPerGame'] = playerAllStats['TotalRebounds']/ playerAllStats['Games']
playerAllStats['AssistsPerGame'] = playerAllStats['Assists']/ playerAllStats['Games']
</code></pre>
Question: Can we create a linear regression model using points per game to estimate win shares per 48 minutes?
1 . Create a plot with the 2 statistics
Before we create the model, let's plot the points per game vs win shares per 48 minutes. This will let us understand the data set and see if we need to remove outliers to improve model accuracy.
<pre><code>sns.regplot(x="WinsSharesPer48Minutes", y = "PointsPerGame", data=playerAllStats)</code></pre>
Output:
<a href="/images/snippets/img2.png"><img src="/images/snippets/img2.png" alt="img2"></a>
There's obviously some outliers and data that doesn't below. Let's see what kind of data we're working with.
<pre><code>playerAllStats.describe()</code></pre>
Output:
<a href="/images/snippets/img3.png"><img src="/images/snippets/img3.png" alt="img3"></a>
There's a few things I see immediately that would make our data hard to plot.
<ul>
<li>
We have seasons from 11400...to minimize noise, we're only going to look at recent player's seasons. So I'm going to limit my analysis from 2014 to present.
</li>
<li>
We have players who've played 1 game all season. We're also going to limit the number of games a player has played in a given season to be >70% of all games. A given season has 82 games.
</li>
<li>
This isn't in the descriptive statistics above, but there are different positions in basketball. For the purpose of this analysis let's limit our data to include players who play Point Guard (PG) (or other positions along with PG). These are typically the players who score the most points, and it makes sense for us to group players who have even scoring potential to reduce skewness.
</li>
</ul>
<pre><code>#filtering on players who have played since 2014 and have played at least 70% of the games in a given season (assuming 82 games per season)
playerAllStatsSince2014 = playerAllStats.loc[(playerAllStats['Year'] >= 2014) &#x26;(playerAllStats['Games'] >= 58)] 
#filteirng on PG players
playerAllStatsSince2014PG = playerAllStatsSince2014[playerAllStatsSince2014['Position'].str.contains('PG')].reset_index(drop=True)
sns.regplot(x="WinsSharesPer48Minutes", y = "PointsPerGame", data=playerAllStatsSince2014PG)</code></pre>
Output:
<a href="/images/snippets/img4.png"><img src="/images/snippets/img4.png" alt="img4"></a>
The plot looks much better between points per game vs win shares per 48 minutes. Our data is in a format we can use. Let's move on to creating a training and test data set.
*2. Splitting data into training and test set *
We're going to split our data into dependent variables and an independent varaibles for both training and test sets using SKLearn's train_test_split function.
<pre><code>playerAllStatsSince2014PG_WinShare = playerAllStatsSince2014PG['WinsSharesPer48Minutes']
playerAllStatsSince2014PG_PPG = playerAllStatsSince2014PG['PointsPerGame']

 #importing packages and splitting out data into training + testing sets
from sklearn.model_selection import train_test_split
train_x, test_x, train_y, test_y = train_test_split(playerAllStatsSince2014PG['PointsPerGame'], \
 playerAllStatsSince2014PG['WinsSharesPer48Minutes'], test_size=0.20, random_state=42)</code></pre>
*3. Fitting the data to a linear regression and getting R-squared *
Here we're creating a linear regression model called model_1. We're going to fit the training data to this model. After that we're going to get the R-squared value. R-squared is a value between 0 and 1 which is a statistical measure of how close the data are to the fitted regression line. 1 = perfect fit, 0 = no fit
<pre><code>model_1 = LinearRegression()
model_1.fit(train_x.reshape(-1, 1), train_y.reshape(-1, 1))
model_1.score(train_x.reshape(-1, 1), train_y.reshape(-1, 1))</code></pre>
Output:
<pre><code>0.49632072484943907</code></pre>
A r-squared of 0.496 tells us we have a moderately effective fit. Since we're also going to be creating a multi-variate model, let's also calculate Adjusted R-squared and Root Mean Square Error (RMSE) so we have more statistics to compare. The below code calculates R-squared and Adjusted R-squared.
<pre><code>X = train_x.values.reshape(-1, 1)
y = train_y.values.reshape(-1, 1)

# compute with formulas from the theory
yhat = model_1.predict(X)
SS_Residual = sum((y-yhat)**2)
SS_Total = sum((y-np.mean(y))**2)
r_squared = 1 - np.divide(SS_Residual,SS_Total)
#r_squared = 1 - np.exp((float(SS_Residual))/SS_Total)
adjusted_r_squared = 1 - (1-r_squared)*(len(y)-1)/(len(y)-X.shape[1]-1)
print(r_squared, adjusted_r_squared)</code></pre>
Output:
<pre><code>[0.49632072] [0.49326812]</code></pre>
*4. Estimate values for the test data *
Last thing to do is to estimate Win Shares with our test data. We'll compare our estimate to the true values and calculate the Root Mean Square Error.
<pre><code>model_1_predict_values = model_1.predict(test_x.values.reshape(-1, 1))
model_1_predict = pd.DataFrame({'Points Per Game': test_x.values, 'Actual Win Share': test_y.values,'Predicted Win Share': model_1_predict_values.reshape(1, -1)[0]})</code></pre>
The code below calculate Root Mean Square Error. The lower the Root Mean Square Error the closer our estimations are to the real values.
<pre><code>#Root Mean square error on test dataset
np.sqrt(np.mean(np.square(model_1_predict['Actual Win Share'] - \
 model_1_predict['Predicted Win Share'])))</code></pre>
Output:
<pre><code>0.0523034857831081</code></pre>
Question: Can we create a linear regression model using points per game, total rebounds per game, and assists per game to estimate win shares per 48 minutes?
We'll go through the same process as above (less the data exploration, which we do not need to do 2 times). We'll create the model and generate the statistics. Then we'll compare the single variable to the multivariable model.
<pre><code>playerAllStatsSince2014PG_Ys = playerAllStatsSince2014PG['WinsSharesPer48Minutes']
playerAllStatsSince2014PG_Xs = playerAllStatsSince2014PG[['PointsPerGame', 'TotalReboundsPerGame', 'AssistsPerGame']]

#splitting test and training data
train_x, test_x, train_y, test_y = train_test_split(playerAllStatsSince2014PG_Xs, playerAllStatsSince2014PG_Ys, test_size=0.20, random_state=42)

#fitting model
model_1 = LinearRegression()
model_1.fit(train_x, train_y)
model_1.score(train_x, train_y)
</code></pre>
Output:
<pre><code>0.5060498809063979</code></pre>
It looks like this model's R-square value is already higher that our single variable. Next we'll calculate the model's estimate for Win Shares.
<pre><code>model_1_predict_values = model_1.predict(test_x)
model_1_predict = pd.DataFrame({'Actual Win Share': test_y.values,'Predicted Win Share': model_1_predict_values.reshape(1, -1)[0]})
#Root Mean square error on test dataset
np.sqrt(np.mean(np.square(model_1_predict['Actual Win Share'] - model_1_predict['Predicted Win Share'])))</code></pre>
Output:
<pre><code>0.05174714479057624</code></pre>
<pre><code>X = train_x.values
y = train_y.values

# compute with formulas from the theory
yhat = model_1.predict(X)
SS_Residual = sum((y-yhat)**2)
SS_Total = sum((y-np.mean(y))**2)
r_squared = 1 - np.divide(SS_Residual,SS_Total)
#r_squared = 1 - np.exp((float(SS_Residual))/SS_Total)
adjusted_r_squared = 1 - (1-r_squared)*(len(y)-1)/(len(y)-X.shape[1]-1)
print(r_squared, adjusted_r_squared)</code></pre>
Output:
<pre><code>0.5060498809063977 0.49695877442001235</code></pre>
Comparing single variable to multivariate linear regression model
Below is a summary of the test statistics of the two models we created. Across all 3 statistics, the multivariate model performs slightly better than the single variable.
<table>
<thead>
<tr>
<th>statistic</th>
<th>single</th>
<th>multi</th>
</tr>
</thead>
<tbody>
<tr>
<td>r-squared</td>
<td>0.49632</td>
<td>0.50604</td>
</tr>
<tr>
<td>adjusted r-squared</td>
<td>0.49326</td>
<td>0.49695</td>
</tr>
<tr>
<td>RMSE</td>
<td>0.0523</td>
<td>0.0517</td>
</tr>
</tbody>
</table>

Which news publications have the most positive headlines? (simple sentiment analysis with Textblob)

Introduction to Linear Regression using Python

import modules
<pre><code>import pymongo
import pandas as pd
from pymongo import MongoClient</code></pre>
connect to mongo, import collection into Pandas dataframe
<pre><code>client = MongoClient()

#point the client at mongo URI
client = MongoClient('Mongo URI')
#select database
db = client['database_name']
#select the collection within the database
test = db.test
#convert entire collection to Pandas dataframe
test = pd.DataFrame(list(test.find()))</code></pre>

A step-by-step Python code example that shows how to import data from MongoDB to a Pandas DataFrame. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Importing data from MongoDB into Python (Pandas)

import modules
<pre><code>import pandas as pd
import numpy as np</code></pre>
create test variable
<pre><code>test = 25</code></pre>
simple if statement
<pre><code>if test &#x26;lt; 50:
 print("Value is less than 50")</code></pre>
<pre><code>Value is less than 50</code></pre>
if else statement
<pre><code>dollars = 50
movie_cost = 20

if dollars &#x26;gt; movie_cost:
 print("You can afford to go to the movie")
else:
 print("You can't afford to go to the movie")</code></pre>
<pre><code>You can afford to go to the movie</code></pre>

A step-by-step Python code example that shows how to create IF and ELSE statements. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

If and else statements in Python

import modules
<pre><code>import webbrowser</code></pre>
create dummy dataframe
<pre><code>#select the url you want to open
url = 'http://docs.python.org/'

#set browser path
chrome_path = 'open -a /Applications/Google\ Chrome.app %s'

#open browser, search URL
webbrowser.get(chrome_path).open(url)</code></pre>
Your browser should open after running the above. Your Chrome path may vary, so if you need to debug I would recommend starting there.

A step-by-step Python code example that shows how to open a browser URL. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Open a browser URL using Python

<pre><code>import pandas as pd</code></pre>
Create some dummy data
<pre><code>string = '8754321'
string</code></pre>
<pre><code>'8754321'

#right 2 characters
string[-2:]</code></pre>
<pre><code>'21'

#left 2 characters
string[:2]</code></pre>
<pre><code>'87'

#4th through 6th characters (index starts at 0)
string[4:6]</code></pre>
'32'

A step-by-step Python code example that shows how to slice a string (right, left, mid equivalents). Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Slice a string in python (right, left, mid equivalents)

Sort a dictionary by value in Python
It is not possible to sort a dictionary, only to get a representation of a dictionary that is sorted. Dictionaries are inherently orderless, but other types, such as lists and tuples, are not. So you need an ordered data type to represent sorted values, which will be a list—probably a list of tuples.
<pre><code>mydict = {'jason':5,
'terry':1,
'michael':3,
'sam':6}

sorted_dict_value = sorted(mydict.items(), key=operator.itemgetter(0))
sorted_dict_value</code></pre>
<pre><code>[('jason', 5), ('michael', 3), ('sam', 6), ('terry', 1)]</code></pre>
Sort a dictionary by key in Python
<pre><code>import operator
sorted_dict_key = sorted(mydict.items(), key=lambda kv: kv[1])</code></pre>
<pre><code>sorted_dict_key</code></pre>
<pre><code>[('terry', 1), ('michael', 3), ('jason', 5), ('sam', 6)]</code></pre>

A step-by-step Python code example that shows how to sort a Python dictionary by keys and values. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Sort a Python dictionary by value or key

Slice notation in Python
<pre><code>#create list
#index [0,1,2,3] #for ref below
a = [5,6,17,20]</code></pre>
<pre><code>#a[start:stop] # items start through stop-1
a[2:4]</code></pre>
<pre><code>[17, 20]</code></pre>
<pre><code>#a[start:] # items start through the rest of the array
a[2:]</code></pre>
<pre><code>[17, 20]</code></pre>
<pre><code>#a[:stop] # items from the beginning through stop-1
a[:3]</code></pre>
<pre><code>[5, 6, 17]</code></pre>
<pre><code>#a[:] # a copy of the whole array
a[:]</code></pre>
<pre><code>[5, 6, 17, 20]</code></pre>
Slice in Python with negative number
<pre><code>a[-1] # last item in the array</code></pre>
<pre><code>20</code></pre>
<pre><code>a[-2:] # last two items in the array</code></pre>
<pre><code>[17, 20]</code></pre>
<pre><code>a[:-2] # everything except the last two items</code></pre>
<pre><code>[5, 6]</code></pre>
You can also adjust the step, defaults to 1
<pre><code>#a[start:stop:step] #here will jump by index of 2 rather than default of one
a[0:4:2]</code></pre>
<pre><code>[5, 17]</code></pre>

A step-by-step Python code example that shows how to use Python's slice notation. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Slice notation in Python

Time delay in python script
<pre><code>import time
time.sleep(5) # Time you want to delay for, in seconds (can be float value as well)
print('This message was delayed approx. 5 seconds')</code></pre>
<pre><code>This message was delayed approx. 5 seconds</code></pre>

A step-by-step Python code example that shows how to add a time delay (sleep) to a python script. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Time delay in Python script (sleep)

Load in packages
<pre><code>
#Packages
#--Web scraping packages
from bs4 import BeautifulSoup
import requests
#Pandas/numpy for data manipulation
import pandas as pd
import numpy as np</code></pre>
Load URLs we want to scrape into an array
<pre><code>#load URLs we want to scrape into an array
BASE_URL = [
'http://www.reuters.com/finance/stocks/company-officers/GOOG.O',
'http://www.reuters.com/finance/stocks/company-officers/AMZN',
'http://www.reuters.com/finance/stocks/company-officers/AAPL'
]</code></pre>
Loop through our URLs, scrape table, pass information to array
<pre><code>#loading empty array for board members
board_members = []
#Loop through our URLs we loaded above
for b in BASE_URL:
 html = requests.get(b).text
 soup = BeautifulSoup(html, &#x26;quot;html.parser&#x26;quot;)
 #identify table we want to scrape
 officer_table = soup.find('table', {&#x26;quot;class&#x26;quot; : &#x26;quot;dataTable&#x26;quot;})

 #try clause to skip any companies with missing/empty board member tables
 try:
 #loop through table, grab each of the 4 columns shown (try one of the links yourself to see the layout)
 for row in officer_table.find_all('tr'):
 cols = row.find_all('td')
 if len(cols) == 4:
 board_members.append((b, cols[0].text.strip(), cols[1].text.strip(), cols[2].text.strip(), cols[3].text.strip()))
 except: pass </code></pre>
Create new array, check length to ensure things pulled in correctly
<pre><code>#convert output to new array, check length
board_array = np.asarray(board_members)
len(board_array)</code></pre>
49
Convert new array to dataframe
<pre><code>#convert new array to dataframe
df = pd.DataFrame(board_array)</code></pre>
Rename columns, preview output
<pre><code>#rename columns, check output
df.columns = ['URL', 'Name', 'Age','Year_Joined', 'Title']
df.head(10)</code></pre>
<table>
<thead>
<tr>
<th></th>
<th>URL</th>
<th>Name</th>
<th>Age</th>
<th>Year_Joined</th>
<th>Title</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Eric Schmidt</td>
<td>61</td>
<td>2015</td>
<td>Executive Chairman of the Board of Director</td>
</tr>
<tr>
<td>1</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Sergey Brin</td>
<td>43</td>
<td>2015</td>
<td>President, Director</td>
</tr>
<tr>
<td>2</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Lawrence Page</td>
<td>44</td>
<td>2015</td>
<td>Chief Executive Officer, Director</td>
</tr>
<tr>
<td>3</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Ruth Porat</td>
<td>59</td>
<td>2015</td>
<td>Chief Financial Officer, Senior Vice President</td>
</tr>
<tr>
<td>4</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Sundar Pichai</td>
<td>45</td>
<td>2017</td>
<td>Director, Chief Executive Officer, Google Inc.</td>
</tr>
<tr>
<td>5</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>David Drummond</td>
<td>54</td>
<td>2015</td>
<td>Senior Vice President - Corporate Development,...</td>
</tr>
<tr>
<td>6</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>John Hennessy</td>
<td>64</td>
<td>2007</td>
<td>Lead Independent Director</td>
</tr>
<tr>
<td>7</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Diane Greene</td>
<td>61</td>
<td>2015</td>
<td>Director</td>
</tr>
<tr>
<td>8</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>L. John Doerr</td>
<td>65</td>
<td>2016</td>
<td>Independent Director</td>
</tr>
<tr>
<td>9</td>
<td><a href="http://www.reuters.com/finance/stocks/company-">http://www.reuters.com/finance/stocks/company-</a>...</td>
<td>Roger Ferguson</td>
<td>65</td>
<td>2016</td>
<td>Independent Director</td>
</tr>
</tbody>
</table>
Export data to CSV
<pre><code>#export data
df.to_csv('/Users/yourname/desktop/board_members.csv')</code></pre>
That's it! If you're interested in seeing how I used this data check out my visualization on the interconnectedness of companies through shared board members <a href="http://erikrood.com/board_members.html">here</a>.

A step-by-step Python code example that shows how to scrape a website's HTML using BeautifulSoup. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Web scraping with Python using Beautiful Soup

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
<pre><code>The sql extension is already loaded. To reload it, use: %reload_ext sql</code></pre>
Create a table
<pre><code>%%sql
CREATE TABLE players3 (first_name, last_name, age);
INSERT INTO players3 (first_name, last_name, age) VALUES 
 ('Lebron', 'James', 33),
 ('Steph', 'Curry', 30),
 ('Mike', 'Jordan', 55),
 ('Mike', 'Bibby', 40);</code></pre>
Concatenate multiple columns in SQL
<pre><code>%%sql
--Here, we will replace the first name with Goat for all Mikes over 45 years of age
--This is an example of modifying a field in a SQL table based on multiple conditions
SELECT
 first_name,
 last_name,
 age,
 --in sqlite, this syntax is first_name || last_name
 --for MySQL, Postgress, MS Access, syntax is as shown below
 concat(first_name, last_name) AS first_last_name
FROM players3</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>last_name</th>
<th>age</th>
<th>first_last_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>Lebron</td>
<td>James</td>
<td>33</td>
<td>LebronJames</td>
</tr>
<tr>
<td>Steph</td>
<td>Curry</td>
<td>30</td>
<td>StephCurry</td>
</tr>
<tr>
<td>Mike</td>
<td>Jordan</td>
<td>55</td>
<td>MikeJordan</td>
</tr>
<tr>
<td>Mike</td>
<td>Bibby</td>
<td>40</td>
<td>MikeBibby</td>
</tr>
</tbody>
</table>

A step-by-step SQL code example that shows how to concatenate columns together in SQL. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Concatenate multiple columns in SQL

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
Create a table
<pre><code>%%sql
 CREATE TABLE players2 (first_name, last_name, age);
 INSERT INTO players2 (first_name, last_name, age) VALUES 
 ('Lebron', 'James', 33),
 ('Steph', 'Curry', 30),
 ('Mike', 'Jordan', 55),
 ('Mike', 'Bibby', 40);</code></pre>
Perform a case statement based on multiple conditions in SQL
<pre><code>%%sql--Here, we will replace the first name with Goat for all Mikes over 45 years of age --This is an example of modifying a field in a SQL table based on multiple conditions
SELECT
 CASE WHEN first_name = 'Mike' 
 AND age &#x26;gt;45 THEN 'Goat'
 ELSE first_name
 END AS first_name,
 last_name,
 age
FROM players2</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>last_name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<tr>
<td>Lebron</td>
<td>James</td>
<td>33</td>
</tr>
<tr>
<td>Steph</td>
<td>Curry</td>
<td>30</td>
</tr>
<tr>
<td>Goat</td>
<td>Jordan</td>
<td>55</td>
</tr>
<tr>
<td>Mike</td>
<td>Bibby</td>
<td>40</td>
</tr>
</tbody>
</table>

A step-by-step SQL code example that shows how create a case statement with multiple conditions. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Create a case statement in SQL

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
Create a table
<pre><code>%%sql CREATE TABLE players2 (first_name, last_name, age); INSERT INTO players2 (first_name, last_name, age) VALUES ('Lebron', 'James', 33), ('Steph', 'Curry', 30),('Mike', 'Jordan', 55), ('Mike', 'Bibby', 40);</code></pre>
<pre><code>%%sql SELECT * FROM players2</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>last_name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<tr>
<td>Lebron</td>
<td>James</td>
<td>33</td>
</tr>
<tr>
<td>Steph</td>
<td>Curry</td>
<td>30</td>
</tr>
<tr>
<td>Mike</td>
<td>Jordan</td>
<td>55</td>
</tr>
<tr>
<td>Mike</td>
<td>Bibby</td>
<td>40</td>
</tr>
</tbody>
</table>
Find duplicate values in SQL table based on single column
<pre><code>%%sql --Select all duplicate first names, and return the count
 SELECT first_name, COUNT(first_name) 
 FROM players2
 GROUP BY first_name
 HAVING COUNT(first_name) &#x26;gt; 1</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>COUNT(first_name)</th>
</tr>
</thead>
<tbody>
<tr>
<td>Mike</td>
<td>2</td>
</tr>
</tbody>
</table>
Create another table
<pre><code>%%sql
 CREATE TABLE players3 (first_name, last_name, age);
 INSERT INTO players3 (first_name, last_name, age) VALUES 
 ('Lebron', 'James', 33),
 ('Steph', 'Curry', 30),
 ('Mike', 'Jordan', 55),
 ('Mike', 'Bibby', 40),
 ('Mike', 'Smith', 40);</code></pre>
Find duplicate values in SQL table based on multiple columns
<pre><code>%%sql --Where both first_name and age are duplicates, --here should return Mike with a count of two, since there are two 40 year old Mikes
 SELECT
 first_name, age, COUNT(*)
 FROM
 players3
 GROUP BY
 first_name, age
 HAVING 
 COUNT(*) &#x26;gt; 1</code></pre>
<pre><code> * sqlite:// Done.</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>age</th>
<th>COUNT(*)</th>
</tr>
</thead>
<tbody>
<tr>
<td>Mike</td>
<td>40</td>
<td>2</td>
</tr>
</tbody>
</table>

A step-by-step SQL code example that shows how to find duplicate values in a SQL table. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Finding duplicate values in a SQL table

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
Create a table
<pre><code>%%sql
CREATE TABLE players (first_name, last_name, age);</code></pre>
<pre><code>%%sql
INSERT INTO players (first_name, last_name, age) VALUES 
 ('Lebron', 'James', 33),
 ('Steph', 'Curry', 30),
 ('Mike', 'Jordan', 55),
 ('Mike', 'Bibby', 40);</code></pre>
Count the # of unique first names in the table
<pre><code>%%sql 
--Select the distinct # of first names in our table, players
SELECT COUNT(DISTINCT(first_name)) FROM players</code></pre>
<table>
<thead>
<tr>
<th>COUNT(DISTINCT(first_name))</th>
</tr>
</thead>
<tbody>
<tr>
<td>3</td>
</tr>
</tbody>
</table>

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
Create a table
<pre><code>%%sql CREATE TABLE players (first_name, last_name, age);</code></pre>
Insert multiple rows of records into table using a single SQL query
<pre><code>%%sql INSERT INTO players (first_name, last_name, age) VALUES ('Lebron', 'James', 33), ('Steph', 'Curry', 30);</code></pre>
Select all records from our table 'players', to confirm we inserted rows
<pre><code>%%sql --Select all records from our table 'players', to confirm we inserted rows SELECT * FROM players</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>last_name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<tr>
<td>Lebron</td>
<td>James</td>
<td>33</td>
</tr>
<tr>
<td>Steph</td>
<td>Curry</td>
<td>30</td>
</tr>
</tbody>
</table>

A step-by-step Python code example that shows how to insert multiple rows into a SQL table using a single query. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Insert multiple rows in a single SQL query

The following example uses <a href="https://github.com/catherinedevlin/ipython-sql">ipython-sql</a>. To install:pip install ipython-sql
%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.
Configure SQL for Jupyter notebook
<pre><code>#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://</code></pre>
Create a table
<pre><code>%%sql
CREATE TABLE players3 (first_name, last_name, age);
INSERT INTO players3 (first_name, last_name, age) VALUES 
 ('Lebron', 'James', 33),
 ('Steph', 'Curry', 30),
 ('Mike', 'Jordan', 55),
 ('Mike', 'Bibby', 40);</code></pre>
Select records from SQL table based on multiple conditions
<pre><code>%%sql
--Return records where age &#x26;lt; 40 and last_name = 'James'
SELECT
 *
FROM players3
WHERE age &#x26;lt; 40 AND last_name = 'James'</code></pre>
<table>
<thead>
<tr>
<th>first_name</th>
<th>last_name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<tr>
<td>Lebron</td>
<td>James</td>
<td>33</td>
</tr>
</tbody>
</table>

A step-by-step SQL code example that shows how to select records from a datatable based on multiple conditions using SQL's where clause. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

Code Snippets

Complete list of references

Pandas

Python

Sql