Challenges on this page

Comparing Pandas with SQL
Transformation: df.groupby().transform()
Filtration: df.groupby().filter(),
Save and read JSON files and strings
Read CSV files
Handling Missing Data
Handling outliers
Other data anomalies
Advanced examples
1. Aggregations
Analytic and aggregate functions

Comparing Pandas with SQL

Pandas methods that are equivalent to SQL queries.
Source: https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html

Note: to run SQL queries on a dataframe - https://pypi.org/project/pandasql/

Python

import pandas as pd
import numpy as np

Load the tips dataset which can be considered a database table.
Note: size = the size of the party (# people)

Python

url = (
    "https://raw.githubusercontent.com/pandas-dev"
    "/pandas/main/pandas/tests/io/data/csv/tips.csv"
)
tips = pd.read_csv(url)
tips

Output

     total_bill   tip     sex smoker   day    time  size
0         16.99  1.01  Female     No   Sun  Dinner     2
1         10.34  1.66    Male     No   Sun  Dinner     3
2         21.01  3.50    Male     No   Sun  Dinner     3
3         23.68  3.31    Male     No   Sun  Dinner     2
4         24.59  3.61  Female     No   Sun  Dinner     4
..          ...   ...     ...    ...   ...     ...   ...
239       29.03  5.92    Male     No   Sat  Dinner     3
240       27.18  2.00  Female    Yes   Sat  Dinner     2
241       22.67  2.00    Male    Yes   Sat  Dinner     2
242       17.82  1.75    Male     No   Sat  Dinner     2
243       18.78  3.00  Female     No  Thur  Dinner     2

[244 rows x 7 columns]

Copies vs. in place operations

Most pandas operations return copies of the Series/DataFrame: you must assign them to a new variable or overwrite the original one:

Python

#sorted_df = tips.sort_values("total_bill") OR
tips = tips.sort_values("total_bill")

Note: you will see an inplace=True or copy=False arguments for some methods:

Python

tips.replace(3, inplace=True)
tips.tail(50)    # replaces 3 with None inplace

Show output (51 lines)

     total_bill    tip     sex smoker   day    time  size
211       25.89   5.16    Male    Yes   Sat  Dinner     4
57        26.41   1.50  Female     No   Sat  Dinner     2
206       26.59   3.41    Male    Yes   Sat  Dinner     2
72        26.86   3.14  Female    Yes   Sat  Dinner     2
7         26.88   3.12    Male     No   Sun  Dinner     4
143       27.05   5.00  Female     No  Thur   Lunch     6
240       27.18   2.00  Female    Yes   Sat  Dinner     2
77        27.20   4.00    Male     No  Thur   Lunch     4
96        27.28   4.00    Male    Yes   Fri  Dinner     2
216       28.15   4.00    Male    Yes   Sat  Dinner     5
214       28.17   6.50  Female    Yes   Sat  Dinner     5
192       28.44   2.56    Male    Yes  Thur   Lunch     2
48        28.55   2.05    Male     No   Sun  Dinner     2
90        28.97   2.05    Male    Yes   Fri  Dinner     2
239       29.03   5.92    Male     No   Sat  Dinner     2
125       29.80   4.20  Female     No  Thur   Lunch     6
155       29.85   5.14  Female     No   Sun  Dinner     5
116       29.93   5.07    Male     No   Sun  Dinner     4
210       30.06   2.00    Male    Yes   Sat  Dinner     4
219       30.14   3.09  Female    Yes   Sat  Dinner     4
44        30.40   5.60    Male     No   Sun  Dinner     4
187       30.46   2.00    Male    Yes   Sun  Dinner     5
39        31.27   5.00    Male     No   Sat  Dinner     5
167       31.71   4.50    Male     No   Sun  Dinner     4
173       31.85   3.18    Male    Yes   Sun  Dinner     2
47        32.40   6.00    Male     No   Sun  Dinner     4
83        32.68   5.00    Male    Yes  Thur   Lunch     2
237       32.83   1.17    Male    Yes   Sat  Dinner     2
175       32.90   3.11    Male    Yes   Sun  Dinner     2
141       34.30   6.70    Male     No  Thur   Lunch     6
179       34.63   3.55    Male    Yes   Sun  Dinner     2
180       34.65   3.68    Male    Yes   Sun  Dinner     4
52        34.81   5.20  Female     No   Sun  Dinner     4
85        34.83   5.17  Female     No  Thur   Lunch     4
11        35.26   5.00  Female     No   Sun  Dinner     4
238       35.83   4.67  Female     No   Sat  Dinner     4
56        38.01   4.67    Male    Yes   Sat  Dinner     4
112       38.07   4.00    Male     No   Sun  Dinner     4
207       38.73   4.00    Male    Yes   Sat  Dinner     4
23        39.42   7.58    Male     No   Sat  Dinner     4
95        40.17   4.73    Male    Yes   Fri  Dinner     4
184       40.55   4.73    Male    Yes   Sun  Dinner     2
142       41.19   5.00    Male     No  Thur   Lunch     5
197       43.11   5.00  Female    Yes  Thur   Lunch     4
102       44.30   2.50  Female    Yes   Sat  Dinner     4
182       45.35   3.50    Male    Yes   Sun  Dinner     4
156       48.17   5.00    Male     No   Sun  Dinner     6
59        48.27   6.73    Male     No   Sat  Dinner     4
212       48.33   9.00    Male     No   Sat  Dinner     4
170       50.81  10.00    Male    Yes   Sat  Dinner     4

Inplace / copy may be depricated soon for most methods (e.g. dropna) except for a very small subset of methods (including replace).

SELECT

In SQL, selection is done using a comma-separated list of columns you’d like to select (or a * to select all columns):

SELECT total_bill, tip, smoker, time
FROM tips;
LIMIT 10;

Python

tips[["total_bill", "tip", "smoker", "time"]].head(10)

Output

   total_bill   tip smoker    time
0       16.99  1.01     No  Dinner
1       10.34  1.66     No  Dinner
2       21.01  3.50     No  Dinner
3       23.68  3.31     No  Dinner
4       24.59  3.61     No  Dinner
5       25.29  4.71     No  Dinner
6        8.77  2.00     No  Dinner
7       26.88  3.12     No  Dinner
8       15.04  1.96     No  Dinner
9       14.78  3.23     No  Dinner

SELECT * => calling the DataFrame without the list of column names

Adding a calculated column

SELECT *, tip/total_bill as tip_rate
FROM tips;

Python

tips.assign(tip_rate = tips["tip"] / tips["total_bill"])

Output

     total_bill   tip     sex smoker   day    time  size  tip_rate
0         16.99  1.01  Female     No   Sun  Dinner     2  0.059447
1         10.34  1.66    Male     No   Sun  Dinner     3  0.160542
2         21.01  3.50    Male     No   Sun  Dinner     3  0.166587
3         23.68  3.31    Male     No   Sun  Dinner     2  0.139780
4         24.59  3.61  Female     No   Sun  Dinner     4  0.146808
..          ...   ...     ...    ...   ...     ...   ...       ...
239       29.03  5.92    Male     No   Sat  Dinner     3  0.203927
240       27.18  2.00  Female    Yes   Sat  Dinner     2  0.073584
241       22.67  2.00    Male    Yes   Sat  Dinner     2  0.088222
242       17.82  1.75    Male     No   Sat  Dinner     2  0.098204
243       18.78  3.00  Female     No  Thur  Dinner     2  0.159744

[244 rows x 8 columns]

WHERE

Filtering in SQL is done via a WHERE clause.

SELECT *
FROM tips
WHERE time = 'Dinner';

DataFrames can be filtered in multiple ways, e.g. boolean indexing

Python

tips[tips["time"] == "Dinner"]

Output

     total_bill   tip     sex smoker   day    time  size
0         16.99  1.01  Female     No   Sun  Dinner     2
1         10.34  1.66    Male     No   Sun  Dinner     3
2         21.01  3.50    Male     No   Sun  Dinner     3
3         23.68  3.31    Male     No   Sun  Dinner     2
4         24.59  3.61  Female     No   Sun  Dinner     4
..          ...   ...     ...    ...   ...     ...   ...
239       29.03  5.92    Male     No   Sat  Dinner     3
240       27.18  2.00  Female    Yes   Sat  Dinner     2
241       22.67  2.00    Male    Yes   Sat  Dinner     2
242       17.82  1.75    Male     No   Sat  Dinner     2
243       18.78  3.00  Female     No  Thur  Dinner     2

[176 rows x 7 columns]

Python

is_dinner = tips["time"] == "Dinner"
tips[is_dinner]

Output

     total_bill   tip     sex smoker   day    time  size
0         16.99  1.01  Female     No   Sun  Dinner     2
1         10.34  1.66    Male     No   Sun  Dinner     3
2         21.01  3.50    Male     No   Sun  Dinner     3
3         23.68  3.31    Male     No   Sun  Dinner     2
4         24.59  3.61  Female     No   Sun  Dinner     4
..          ...   ...     ...    ...   ...     ...   ...
239       29.03  5.92    Male     No   Sat  Dinner     3
240       27.18  2.00  Female    Yes   Sat  Dinner     2
241       22.67  2.00    Male    Yes   Sat  Dinner     2
242       17.82  1.75    Male     No   Sat  Dinner     2
243       18.78  3.00  Female     No  Thur  Dinner     2

[176 rows x 7 columns]

SQL’s OR and AND = | and & in pandas

Tips of more than $5 at Dinner meals:

SELECT *
FROM tips
WHERE time = 'Dinner' AND tip > 5.00;

Python

tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]

NULL checking with notna() and isna()

Python

frame = pd.DataFrame(
    {"col1": ["A", "B", np.nan, "C", "D"], "col2": ["F", np.nan, "G", "H", "I"]}
)
frame

Output

  col1 col2
0    A    F
1    B  NaN
2  NaN    G
3    C    H
4    D    I

See only the records where col2 IS NULL:

SELECT *
FROM frame
WHERE col2 IS NULL;

Python

frame[frame["col2"].isna()]

Output

  col1 col2
1    B  NaN

Where col1 IS NOT NULL:

SELECT *
FROM frame
WHERE col1 IS NOT NULL;

Python

frame[frame["col1"].notna()]

Output

  col1 col2
0    A    F
1    B  NaN
3    C    H
4    D    I

UNION ALL

SELECT city, rank
FROM df1
UNION ALL
SELECT city, rank
FROM df2;

Python

df1 = pd.DataFrame(
    {"city": ["Chicago", "San Francisco", "New York City"], "rank": range(1, 4)}
)
df2 = pd.DataFrame(
    {"city": ["Chicago", "Boston", "Los Angeles"], "rank": [1, 4, 5]}
)
pd.concat([df1, df2])

Output

            city  rank
0        Chicago     1
1  San Francisco     2
2  New York City     3
0        Chicago     1
1         Boston     4
2    Los Angeles     5

UNION

Similar to UNION ALL, but removes duplicate rows.

SELECT city, rank
FROM df1
UNION
SELECT city, rank
FROM df2;

Python

pd.concat([df1, df2]).drop_duplicates()

Output

            city  rank
0        Chicago     1
1  San Francisco     2
2  New York City     3
1         Boston     4
2    Los Angeles     5

UPDATE

UPDATE tips
SET tip = tip*2
WHERE tip < 2;

Python

tips.loc[tips["tip"] < 2, "tip"] *= 2
tips.head()

Output

   total_bill   tip     sex smoker  day    time  size
0       16.99  2.02  Female     No  Sun  Dinner     2
1       10.34  3.32    Male     No  Sun  Dinner     3
2       21.01  3.50    Male     No  Sun  Dinner     3
3       23.68  3.31    Male     No  Sun  Dinner     2
4       24.59  3.61  Female     No  Sun  Dinner     4

DELETE

DELETE FROM tips
WHERE tip > 9;

In pandas - select the rows that should remain:

Python

tips = tips.loc[tips["tip"] <= 9]
tips.head()

GROUP BY

In pandas, groupby() means splitting a dataset into groups, applying a func (typically aggregation, but also transform or filter), and combining the groups together.

SQL for getting the number of tips left by sex:

SELECT sex, count(*)
FROM tips
GROUP BY sex;

df.size() - returns an int representing the number of elements in this object:

Python

tips.groupby("sex").size()

Output

sex
Female     87
Male      157
dtype: int64

Notice the use of df.groupby.size() because df.groupby.count() applies func to each column, returning # NOT NULL records in each.

Python

tips.groupby("sex").count()

Output

        total_bill  tip  smoker  day  time  size
sex                                             
Female          87   87      87   87    87    87
Male           157  157     157  157   157   157

Alternatively, you can apply df.groupby().count() to one column:

Python

tips.groupby("sex")["total_bill"].count()

Output

sex
Female     87
Male      157
Name: total_bill, dtype: int64

Apply multiple functions at once

Pass a {col: func OR list of funcs} dictionary to df.groupby.agg()

SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;

Python

tips.groupby("day").agg({"tip": "mean", "day": "size"})

Output

           tip  day
day                
Fri   2.586842   19
Sat   2.986897   87
Sun   3.292105   76
Thur  2.775484   62

Group by more than one column

Pass a list of columns to groupby()

SELECT smoker, day, COUNT(*), AVG(tip)
FROM tips
GROUP BY smoker, day;

Python

tips.groupby(["smoker", "day"]).agg({"tip": ["size", "mean"]})

Output

             tip          
            size      mean
smoker day                
No     Fri     4  2.562500
       Sat    45  3.052889
       Sun    57  3.178070
       Thur   45  2.712667
Yes    Fri    15  2.593333
       Sat    42  2.916190
       Sun    19  3.634211
       Thur   17  2.941765

GROUP BY - ANOTHER EXAMPLE

Python

ipl_data = { 'Team': [ 'Riders', 'Riders', 'Angels', 'Angels', 'Kings', 'kings', 'Kings', 'Kings', 'Riders',
                       'Royals', 'Royals', 'Riders' ],
              'Rank': [ 1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2 ],
              'Year': [ 2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017 ],
              'Points': [ 876,789,863,673,741,812,756,788,694,701,804,690 ]}
df = pd.DataFrame(ipl_data)
df

Output

      Team  Rank  Year  Points
0   Riders     1  2014     876
1   Riders     2  2015     789
2   Angels     2  2014     863
3   Angels     3  2015     673
4    Kings     3  2014     741
5    kings     4  2015     812
6    Kings     1  2016     756
7    Kings     1  2017     788
8   Riders     2  2016     694
9   Royals     4  2014     701
10  Royals     1  2015     804
11  Riders     2  2017     690

Python

# agg() on all columns in each df from grouped
grouped = df.groupby('Team')
grouped.agg(np.size)

Output

        Rank  Year  Points
Team                      
Angels     2     2       2
Kings      3     3       3
Riders     4     4       4
Royals     2     2       2
kings      1     1       1

Python

df.groupby('Team').agg(np.size)

Output

        Rank  Year  Points
Team                      
Angels     2     2       2
Kings      3     3       3
Riders     4     4       4
Royals     2     2       2
kings      1     1       1

Python

# agg() on one column in each df from grouped
df.groupby('Team')['Points'].agg(np.mean)

Output

Team
Angels    768.000000
Kings     761.666667
Riders    762.250000
Royals    752.500000
kings     812.000000
Name: Points, dtype: float64

Python

# MULTIPLE AGGREGATION FUNCTIONS ON ONE COL
df.groupby('Team')['Points'].agg([np.sum, np.mean, np.std])

Output

         sum        mean         std
Team                                
Angels  1536  768.000000  134.350288
Kings   2285  761.666667   24.006943
Riders  3049  762.250000   88.567771
Royals  1505  752.500000   72.831998
kings    812  812.000000         NaN

Python

# MULTIPLE AGGREGATION FUNCTIONS ON MANY COLs
df.groupby('Team')[['Points', 'Rank']].agg([np.sum, np.mean, np.std])

Output

       Points                         Rank                    
          sum        mean         std  sum      mean       std
Team                                                          
Angels   1536  768.000000  134.350288    5  2.500000  0.707107
Kings    2285  761.666667   24.006943    5  1.666667  1.154701
Riders   3049  762.250000   88.567771    7  1.750000  0.500000
Royals   1505  752.500000   72.831998    5  2.500000  2.121320
kings     812  812.000000         NaN    4  4.000000       NaN

Transformation: df.groupby().transform()

Applied to group or column, returns an obj w/same index size

Python

score = lambda x: (x - x.mean()) / x.std()*10
df.groupby('Team').transform(score)

Output

         Rank       Year     Points
0  -15.000000 -11.618950  12.843272
1    5.000000  -3.872983   3.020286
2   -7.071068  -7.071068   7.071068
3    7.071068   7.071068  -7.071068
4   11.547005 -10.910895  -8.608621
5         NaN        NaN        NaN
6   -5.773503   2.182179  -2.360428
7   -5.773503   8.728716  10.969049
8    5.000000   3.872983  -7.705963
9    7.071068  -7.071068  -7.071068
10  -7.071068   7.071068   7.071068
11   5.000000  11.618950  -8.157595

Filtration: df.groupby().filter(),

Returns subset of df, filtering data on a criteria

Python

# TEAMS THAT PARTICIPATED 3 TIMES OR MORE - applied to group (df), and not name (str) when iteriting groupby()
df.groupby('Team').filter(lambda x: len(x) >= 3)

Output

      Team  Rank  Year  Points
0   Riders     1  2014     876
1   Riders     2  2015     789
4    Kings     3  2014     741
6    Kings     1  2016     756
7    Kings     1  2017     788
8   Riders     2  2016     694
11  Riders     2  2017     690

JOIN

df.join() is used for joining dataframes on their indices or joining dataframe with a series.
df.merge() is used if you need more control over the join operation or when joining on columns.
In each method you can specify the type of join (LEFT, RIGHT, INNER, FULL) or the columns to join on (column names or indices).

Warning

If both key columns contain rows where the key is a null value, those rows will be matched against each other. This is different from usual SQL join behaviour and can lead to unexpected results.

Python

DataFrame.merge( right_df,
                 how='inner',               # default ‘inner’, {‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}
                 on=None,                   # join key(s)
                 left_on=None,              # if join key(s) have different names in the two dfs (but are same)
                 right_on=None,
                 left_index=False,          # use index as join key(s)
                 right_index=False, 
                 sort=False,                # Sort join keys lexicographically
                 suffixes=('_x', '_y'), 
                 copy=True,
                 indicator=False,           # col _merge saying source of each row
                 validate=None,             # check merge keys if they are “1:1”, “1:m”, “m:1”, “m:m”
               )

Python

df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})
df1, df2

Output

(  key     value
 0   A -1.772000
 1   B  0.581612
 2   C  0.265104
 3   D  0.231492,
   key     value
 0   B -0.009561
 1   D  0.177602
 2   D -0.944116
 3   E  0.336153)

INNER JOIN

SELECT *
FROM df1
INNER JOIN df2
  ON df1.key = df2.key;

merge() performs an INNER JOIN by default:

Python

pd.merge(df1, df2, on="key")

Output

  key   value_x   value_y
0   B  0.581612 -0.009561
1   D  0.231492  0.177602
2   D  0.231492 -0.944116

merge() can join one df’s column with another df’s index:

Python

indexed_df2 = df2.set_index("key")
pd.merge(df1, indexed_df2, left_on="key", right_index=True)

Output

  key   value_x   value_y
1   B -0.007690  0.378410
3   D  1.314529  0.531660
3   D  1.314529 -2.127951

LEFT OUTER JOIN

SELECT *
FROM df1
LEFT OUTER JOIN df2
  ON df1.key = df2.key;

Python

df1, df2

Output

(  key     value
 0   A -1.772000
 1   B  0.581612
 2   C  0.265104
 3   D  0.231492,
   key     value
 0   B -0.009561
 1   D  0.177602
 2   D -0.944116
 3   E  0.336153)

Python

pd.merge(df1, df2, on="key", how="left")

Output

  key   value_x   value_y
0   A -1.772000       NaN
1   B  0.581612 -0.009561
2   C  0.265104       NaN
3   D  0.231492  0.177602
4   D  0.231492 -0.944116

RIGHT JOIN

SELECT *
FROM df1
RIGHT OUTER JOIN df2
  ON df1.key = df2.key;

Python

pd.merge(df1, df2, on="key", how="right")

Output

  key   value_x   value_y
0   B -0.007690  0.378410
1   D  1.314529  0.531660
2   D  1.314529 -2.127951
3   E       NaN  0.843397

FULL JOIN

Pandas allows for FULL JOINs, but not all RDBMS support it (MySQL).

SELECT *
FROM df1
FULL OUTER JOIN df2
  ON df1.key = df2.key;

Python

pd.merge(df1, df2, on="key", how="outer")

Output

  key   value_x   value_y
0   A  0.295379       NaN
1   B -0.007690  0.378410
2   C  1.950469       NaN
3   D  1.314529  0.531660
4   D  1.314529 -2.127951
5   E       NaN  0.843397

SUBQUERY

Python

df = pd.DataFrame({ 'Person': ['Adam', 'Adam', 'Cesar', 'Diana', 'Diana', 'Diana', 'Erika', 'Erika'],
                    'Belonging': ['House', 'Car', 'Car', 'House', 'Car', 'Bike', 'House', 'Car'],
                    'Value': [300, 10, 12, 450, 15, 2, 600, 11],
                    })
df

Output

  Person Belonging  Value
0   Adam     House    300
1   Adam       Car     10
2  Cesar       Car     12
3  Diana     House    450
4  Diana       Car     15
5  Diana      Bike      2
6  Erika     House    600
7  Erika       Car     11

Task: find value of people's car, if their house's value > 400

SELECT * 
FROM df 
WHERE person IN 
    (SELECT person 
        FROM df 
        WHERE belonging='House' AND value>400)
AND belonging='Car';

person      belonging   value     
----------  ----------  ----------
Diana       Car         15        
Erika       Car         11

Python

persons = df[(df['Belonging'] == 'House') & (df['Value'] > 400)]['Person'].values
df[ (df['Person'].isin(persons)) & (df['Belonging'] == 'Car') ]

Output

  Person Belonging  Value
4  Diana       Car     15
7  Erika       Car     11

Save and read JSON files and strings

Python

import json

sample_data = {
    "name": "John Doe",
    "age": 30,
    "email": "johndoe@example.com",
    "is_active": True,
    "skills": ["Python", "Machine Learning", "Data Analysis"]
}

# SAVE JSON
file_path = "sample_data.json"
with open(file_path, 'w') as json_file:
    json.dump(sample_data, json_file, indent=4)

# READ JSON
with open(file_path, 'r') as json_file:
    data = json.load(json_file)
print('JSON data from file:', data)


# STRING MANIPULATIONS
json_string = json.dumps(sample_data, indent=4)
print('\nJSON string:', json_string)

data = json.loads(json_string)
print('\nJSON data from string:', data)

Output

JSON data from file: {'name': 'John Doe', 'age': 30, 'email': 'johndoe@example.com', 'is_active': True, 'skills': ['Python', 'Machine Learning', 'Data Analysis']}

JSON string: {
    "name": "John Doe",
    "age": 30,
    "email": "johndoe@example.com",
    "is_active": true,
    "skills": [
        "Python",
        "Machine Learning",
        "Data Analysis"
    ]
}

JSON data from string: {'name': 'John Doe', 'age': 30, 'email': 'johndoe@example.com', 'is_active': True, 'skills': ['Python', 'Machine Learning', 'Data Analysis']}

Read CSV files

pandas.read_csv( * filepath, sep=',' / regex, header='infer' / None / row number (numbers for multi-index), * names=col names. If they already exist in file, pass header=0 to rename, * index_col=col(s) for row labels (None), usecols=subset of cols to use (None) * dtype=dtypes for cols (None), skiprows=line numbers to skip (0-indexed) or # lines to skip (int) * skipfooter=# lines at bottom of file to skip (0), nrows=# rows to read (None), * na_values=additional strings to recognize as NaN, (also true_values and false_values), * skip_blank_lines=True, parse_dates=None, infer_datetime_format=_NoDefault.no_default, * keep_date_col= keep original date col, date_format=None, * thousands=None, decimal='.',quotechar='"', quoting=0 (csv.QUOTE_MINIMAL, etc.), * escapechar=None, encoding=None, * encoding_errors='strict' / 'ignore' / 'replace', on_bad_lines='error' / 'warn' / 'skip', * delim_whitespace=whether or not whitespace will be used as the sep (equ. to sep='\s+')

Python

df.to_json(file_name)
df = pd.read_json(file_name)

Handling Missing Data

Python

df = pd.DataFrame({
    'A': [1, 1, 2, None, 4],
    'B': [5, None, 2, 3, 4],
    'C': [5, 1, None, 3, None],
    'D': [1, 2, 4, 5, 7],
})
df

Output

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
3  NaN  3.0  3.0  5
4  4.0  4.0  NaN  7

Check for missing data

Python

print(df.isnull())              # Shows a dataframe of the same shape with boolean values
print(df.isnull().sum())

Output

       A      B      C      D
0  False  False  False  False
1  False   True  False  False
2  False  False   True  False
3   True  False  False  False
4  False  False   True  False
A    1
B    1
C    2
D    0
dtype: int64

Removing Missing Data

Python

# Remove rows with ANY missing values
print(df, '\n')
df_dropped_rows = df.dropna()
print(df_dropped_rows, '\n')

# Remove columns with ANY missing values
df_dropped_columns = df.dropna(axis=1)
print(df_dropped_columns, '\n')

# Remove rows with missing values in specific columns
df_dropped_specific1 = df.dropna(subset=['A'])
print(df_dropped_specific1, '\n')

# Remove columns with missing values in specific rows
df_dropped_specific2 = df.dropna(subset=[1], axis=1)
print(df_dropped_specific2)

Output

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
3  NaN  3.0  3.0  5
4  4.0  4.0  NaN  7 

     A    B    C  D
0  1.0  5.0  5.0  1 

   D
0  1
1  2
2  4
3  5
4  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
4  4.0  4.0  NaN  7 

     A    C  D
0  1.0  5.0  1
1  1.0  1.0  2
2  2.0  NaN  4
3  NaN  3.0  5
4  4.0  NaN  7

Filling Missing Data

Python

DataFrame.fillna(
    value=None,    # number/dict/Series/DataFrame - which value to use for each index (Series) or col (DataFrame)
    method=None,   # ‘bfill’, ‘ffill’, None
    limit=None,    # max # of consecutive NaNs to forward/backward fill
    axis=None,
    inplace=False,
)

Python

# Fill missing values with a constant value
print(df, '\n')
df_filled_constant = df.fillna(250)
print(df_filled_constant, '\n')

# Forward fill - propagate the next valid observation forward
df_filled_ffill = df.fillna(method='ffill')
print(df_filled_ffill, '\n')

# Backward fill - propagating the next valid observation backward
df_filled_bfill = df.fillna(method='bfill')
print(df_filled_bfill, '\n')

# Fill all NaNs in cols ‘A’, ‘B’, ‘C’, with 100, 200, 300, respectively.
values = {"A": 100, "B": 200, "C": 300,}
print(df.fillna(value=values), '\n')

# When filling using a DataFrame, replacement happens along the same column names and same indices
df2 = pd.DataFrame(np.zeros((5, 4)), columns=list("ABCD"))
print(df.fillna(df2), '\n')

Show output (41 lines)

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
3  NaN  3.0  3.0  5
4  4.0  4.0  NaN  7 

       A      B      C  D
0    1.0    5.0    5.0  1
1    1.0  250.0    1.0  2
2    2.0    2.0  250.0  4
3  250.0    3.0    3.0  5
4    4.0    4.0  250.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  5.0  1.0  2
2  2.0  2.0  1.0  4
3  2.0  3.0  3.0  5
4  4.0  4.0  3.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  2.0  1.0  2
2  2.0  2.0  3.0  4
3  4.0  3.0  3.0  5
4  4.0  4.0  NaN  7 

       A      B      C  D
0    1.0    5.0    5.0  1
1    1.0  200.0    1.0  2
2    2.0    2.0  300.0  4
3  100.0    3.0    3.0  5
4    4.0    4.0  300.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  0.0  1.0  2
2  2.0  2.0  0.0  4
3  0.0  3.0  3.0  5
4  4.0  4.0  0.0  7

Additional methods:

Python

# Fill missing values with the mean of the column
print(df, '\n')
df_filled_mean = df.fillna(df.mean())
print(df_filled_mean, '\n')

# Fill missing values with the median of the column
df_filled_median = df.fillna(df.median())
print(df_filled_median, '\n')

# Fill missing values with the mode of the column (mode() returns a dataframe)
df_filled_mode = df.fillna(df.mode().iloc[0])
print(df_filled_mode, '\n')

# Replace missing data by interpolation (default: linear)
df_interpolated = df.interpolate() # method:‘linear’,‘time’,‘index’,‘pad’,‘nearest’,‘zero’ (other scipy methods)
print(df_interpolated)

Show output (34 lines)

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
3  NaN  3.0  3.0  5
4  4.0  4.0  NaN  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  3.5  1.0  2
2  2.0  2.0  3.0  4
3  2.0  3.0  3.0  5
4  4.0  4.0  3.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  3.5  1.0  2
2  2.0  2.0  3.0  4
3  1.5  3.0  3.0  5
4  4.0  4.0  3.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  2.0  1.0  2
2  2.0  2.0  1.0  4
3  1.0  3.0  3.0  5
4  4.0  4.0  1.0  7 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  3.5  1.0  2
2  2.0  2.0  2.0  4
3  3.0  3.0  3.0  5
4  4.0  4.0  3.0  7

Python

# Scikit-learn's Imputer for more complex imputation strategies
from sklearn.impute import SimpleImputer
print(df, '\n')
imputer    = SimpleImputer(strategy='mean')
df_imputed = pd.DataFrame(imputer.fit_transform(df), columns=df.columns)
print(df_imputed, '\n')

# Custom func to fill missing values with column mean
def custom_imputer(column):
    return column.fillna(column.mean())

df_custom_imputed = df.apply(custom_imputer)
print(df_custom_imputed)

Output

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  NaN  1.0  2
2  2.0  2.0  NaN  4
3  NaN  3.0  3.0  5
4  4.0  4.0  NaN  7 

     A    B    C    D
0  1.0  5.0  5.0  1.0
1  1.0  3.5  1.0  2.0
2  2.0  2.0  3.0  4.0
3  2.0  3.0  3.0  5.0
4  4.0  4.0  3.0  7.0 

     A    B    C  D
0  1.0  5.0  5.0  1
1  1.0  3.5  1.0  2
2  2.0  2.0  3.0  4
3  2.0  3.0  3.0  5
4  4.0  4.0  3.0  7

Handling outliers

Python

data = {'A': [10, -50, 14, 15, 20, 100, 22, 30, 40, 50],} #'B': [10, 5, 140, 15, -54, 25, 20, 38, 45, 55]}
df   = pd.DataFrame(data)
df

Output

Identifying outliers using the IQR (Interquartile Range)

Python

# Calculate Q1 (25th percentile) and Q3 (75th percentile)
Q1 = df['A'].quantile(0.25)
Q3 = df['A'].quantile(0.75)
IQR = Q3 - Q1

# Define outlier boundaries
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Identify outliers
outliers = df[(df['A'] < lower_bound) | (df['A'] > upper_bound)]
print(lower_bound, upper_bound)
print(outliers)

Output

-20.625 72.375
     A  A_capped
1  -50   -20.625
5  100    72.375

Removing outliers

Python

df_no_outliers = df[(df['A'] >= lower_bound) & (df['A'] <= upper_bound)]
print(df_no_outliers)

Output

    A  A_capped
0  10      10.0
2  14      14.0
3  15      15.0
4  20      20.0
6  22      22.0
7  30      30.0
8  40      40.0
9  50      50.0

Cap outliers (replace w/boundary values)

Python

df['A_capped'] = np.where(df['A'] > upper_bound, upper_bound, df['A'])
df['A_capped'] = np.where(df['A'] < lower_bound, lower_bound, df['A_capped'])
print(df)

Output

     A  A_capped
0   10    10.000
1  -50   -20.625
2   14    14.000
3   15    15.000
4   20    20.000
5  100    72.375
6   22    22.000
7   30    30.000
8   40    40.000
9   50    50.000

Transforming Data

Z-Score: the number of standard deviations a data point is from the mean.
z-score = (value - mean) / std_dev => standardization w/StandardScaler

Python

from scipy.stats import zscore

df['A_log']    = np.log(df['A'])         # Log transformation
df['A_zscore'] = zscore(df['A'])         # Z-score normalization
print(df)

Output

     A  A_capped     A_log  A_zscore
0   10    10.000  2.302585 -0.425312
1  -50   -20.625       NaN -2.115292
2   14    14.000  2.639057 -0.312646
3   15    15.000  2.708050 -0.284480
4   20    20.000  2.995732 -0.143648
5  100    72.375  4.605170  2.109658
6   22    22.000  3.091042 -0.087316
7   30    30.000  3.401197  0.138015
8   40    40.000  3.688879  0.419678
9   50    50.000  3.912023  0.701342
/Users/andrew/opt/anaconda3/envs/top/lib/python3.10/site-packages/pandas/core/arraylike.py:402: RuntimeWarning: invalid value encountered in log
  result = getattr(ufunc, method)(*inputs, **kwargs)

Imputing outliers (replace w/mean or median)

Python

median = df['A'].median()
print(median)
df['A_median'] = df['A']
df['A_median'] = np.where(df['A_median'] > upper_bound, median, df['A_median'])
df['A_median'] = np.where(df['A_median'] < lower_bound, median, df['A_median'])
print(df)

Output

21.0
     A  A_capped     A_log  A_zscore  A_median
0   10    10.000  2.302585 -0.425312      10.0
1  -50   -20.625       NaN -2.115292      21.0
2   14    14.000  2.639057 -0.312646      14.0
3   15    15.000  2.708050 -0.284480      15.0
4   20    20.000  2.995732 -0.143648      20.0
5  100    72.375  4.605170  2.109658      21.0
6   22    22.000  3.091042 -0.087316      22.0
7   30    30.000  3.401197  0.138015      30.0
8   40    40.000  3.688879  0.419678      40.0
9   50    50.000  3.912023  0.701342      50.0

Scaling w/Robust Scaler (robust to outliers)

Python

from sklearn.preprocessing import RobustScaler
scaler = RobustScaler()
df['A_scaled'] = scaler.fit_transform(df[['A']])
print(df)

Output

     A  A_capped     A_log  A_zscore  A_median  A_scaled
0   10    10.000  2.302585 -0.425312      10.0 -0.473118
1  -50   -20.625       NaN -2.115292      21.0 -3.053763
2   14    14.000  2.639057 -0.312646      14.0 -0.301075
3   15    15.000  2.708050 -0.284480      15.0 -0.258065
4   20    20.000  2.995732 -0.143648      20.0 -0.043011
5  100    72.375  4.605170  2.109658      21.0  3.397849
6   22    22.000  3.091042 -0.087316      22.0  0.043011
7   30    30.000  3.401197  0.138015      30.0  0.387097
8   40    40.000  3.688879  0.419678      40.0  0.817204
9   50    50.000  3.912023  0.701342      50.0  1.247312

Isolation Forest - ML to machine learning algorithms to identify and handle outliers.

Python

from sklearn.ensemble import IsolationForest

# Isolation Forest
iso = IsolationForest(contamination=0.1)
df['outliers'] = iso.fit_predict(df[['A']])
print(df)
outliers = df[df['outliers'] == -1]
print("Outliers identified by Isolation Forest:")
print(outliers)

Output

     A  A_capped     A_log  A_zscore  A_median  A_scaled  outliers
0   10    10.000  2.302585 -0.425312      10.0 -0.473118         1
1  -50   -20.625       NaN -2.115292      21.0 -3.053763        -1
2   14    14.000  2.639057 -0.312646      14.0 -0.301075         1
3   15    15.000  2.708050 -0.284480      15.0 -0.258065         1
4   20    20.000  2.995732 -0.143648      20.0 -0.043011         1
5  100    72.375  4.605170  2.109658      21.0  3.397849         1
6   22    22.000  3.091042 -0.087316      22.0  0.043011         1
7   30    30.000  3.401197  0.138015      30.0  0.387097         1
8   40    40.000  3.688879  0.419678      40.0  0.817204         1
9   50    50.000  3.912023  0.701342      50.0  1.247312         1
Outliers identified by Isolation Forest:
    A  A_capped  A_log  A_zscore  A_median  A_scaled  outliers
1 -50   -20.625    NaN -2.115292      21.0 -3.053763        -1
/Users/andrew/opt/anaconda3/envs/top/lib/python3.10/site-packages/sklearn/base.py:439: UserWarning: X does not have valid feature names, but IsolationForest was fitted with feature names
  warnings.warn(

Other data anomalies

Inconsistent data (different date formats, inconsistent categorical data). Handling: standardizing.
Data entry errors (typos or incorrect numerical values, etc.) Handling: validate and clean.
Out-of-range values (person's age = 200). Handling: use logical bounds and filter.
Data drift: changes in statistical properties of data over time, affect model performance. Handling: retrain and monitor data distribution.
Structural anomalies (e.g. issues with schema, missing cols, mismatched data types (mixed types in same column). Handling: ensure schema consistency, enforce data types.
Temporal anomalies (in time-series data - missing timestamps or inconsistent time intervals. Handling: impute missing timestamps, resample data to ensure consistent intervals.

Duplicates

Python

df.drop_duplicates(subset=[cols]).reset_index(drop=True)    # keep{‘first’, ‘last’, False (drop all)}

Advanced examples

1. Aggregations

1a. Aggregations on entire dataframe

WINDOW FUNCTIONS - used to find trends in data graphically by smoothing the curve (if a lot of data) * df.rolling() - rolling window calculations; window=window size, min_periods=min num observations in window required to have a value. * df.expanding() - same as rolling, but uses all the data up to that point in time. These two statements are equivalent: [df.rolling(window=len(df), min_periods=1).mean()] = [df.expanding(min_periods=1).mean()] * df.ewm() - exponentially weighted window similar to expanding window, but each prior point is exponentially weighted down relative to the current point

Python

ipl_data = {  'Team':   [ 1, 1, 2, 2, 3, 4, 3, 3, 1, 5, 5, 1 ],
              'Rank':   [ 1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2 ],
              'Year':   [ 2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017 ],
              'Points': [ 876,789,863,673,741,812,756,788,694,701,804,690 ]}
df = pd.DataFrame(ipl_data)
df

Output

    Team  Rank  Year  Points
0      1     1  2014     876
1      1     2  2015     789
2      2     2  2014     863
3      2     3  2015     673
4      3     3  2014     741
5      4     4  2015     812
6      3     1  2016     756
7      3     1  2017     788
8      1     2  2016     694
9      5     4  2014     701
10     5     1  2015     804
11     1     2  2017     690

Python

# Apply Aggregation on a Whole Dataframe
r = df.rolling(window=3, min_periods=1)
r.agg(np.sum)

Output

    Team  Rank    Year  Points
0    1.0   1.0  2014.0   876.0
1    2.0   3.0  4029.0  1665.0
2    4.0   5.0  6043.0  2528.0
3    5.0   7.0  6044.0  2325.0
4    7.0   8.0  6043.0  2277.0
5    9.0  10.0  6044.0  2226.0
6   10.0   8.0  6045.0  2309.0
7   10.0   6.0  6048.0  2356.0
8    7.0   4.0  6049.0  2238.0
9    9.0   7.0  6047.0  2183.0
10  11.0   7.0  6045.0  2199.0
11  11.0   7.0  6046.0  2195.0

Python

# Aggregation on a Single Column
r = df.rolling(window=3,min_periods=1)
r['Points'].agg(np.sum)

Output

0      876.0
1     1665.0
2     2528.0
3     2325.0
4     2277.0
5     2226.0
6     2309.0
7     2356.0
8     2238.0
9     2183.0
10    2199.0
11    2195.0
Name: Points, dtype: float64

Python

# Aggregation on Multiple Columns
r = df.rolling(window=3,min_periods=1)
r[['Points', 'Rank']].agg(np.sum)

Output

    Points  Rank
0    876.0   1.0
1   1665.0   3.0
2   2528.0   5.0
3   2325.0   7.0
4   2277.0   8.0
5   2226.0  10.0
6   2309.0   8.0
7   2356.0   6.0
8   2238.0   4.0
9   2183.0   7.0
10  2199.0   7.0
11  2195.0   7.0

Python

# Multiple Functions on a Single Column
r = df.rolling(window=3,min_periods=1)
r['Points'].agg([np.sum,np.mean])

Output

       sum        mean
0    876.0  876.000000
1   1665.0  832.500000
2   2528.0  842.666667
3   2325.0  775.000000
4   2277.0  759.000000
5   2226.0  742.000000
6   2309.0  769.666667
7   2356.0  785.333333
8   2238.0  746.000000
9   2183.0  727.666667
10  2199.0  733.000000
11  2195.0  731.666667

Python

# Multiple Functions on Multiple Columns
r = df.rolling(window=3,min_periods=1)
r[['Points', 'Rank']].aggregate([np.sum,np.mean])

Output

    Points              Rank          
       sum        mean   sum      mean
0    876.0  876.000000   1.0  1.000000
1   1665.0  832.500000   3.0  1.500000
2   2528.0  842.666667   5.0  1.666667
3   2325.0  775.000000   7.0  2.333333
4   2277.0  759.000000   8.0  2.666667
5   2226.0  742.000000  10.0  3.333333
6   2309.0  769.666667   8.0  2.666667
7   2356.0  785.333333   6.0  2.000000
8   2238.0  746.000000   4.0  1.333333
9   2183.0  727.666667   7.0  2.333333
10  2199.0  733.000000   7.0  2.333333
11  2195.0  731.666667   7.0  2.333333

Python

# Different Functions to Different Columns
r = df.rolling(window=3,min_periods=1)
r.aggregate({'Points' : np.sum,'Rank' : np.mean})

Output

    Points      Rank
0    876.0  1.000000
1   1665.0  1.500000
2   2528.0  1.666667
3   2325.0  2.333333
4   2277.0  2.666667
5   2226.0  3.333333
6   2309.0  2.666667
7   2356.0  2.000000
8   2238.0  1.333333
9   2183.0  2.333333
10  2199.0  2.333333
11  2195.0  2.333333

1b. Aggregations with Groupby()

Python

ipl_data = { 'Team': [ 'Riders', 'Riders', 'Angels', 'Angels', 'Kings', 'kings', 'Kings', 'Kings', 'Riders',
                       'Royals', 'Royals', 'Riders' ],
              'Rank': [ 1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2 ],
              'Year': [ 2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017 ],
              'Points': [ 876,789,863,673,741,812,756,788,694,701,804,690 ]}
df = pd.DataFrame(ipl_data)
df

Output

      Team  Rank  Year  Points
0   Riders     1  2014     876
1   Riders     2  2015     789
2   Angels     2  2014     863
3   Angels     3  2015     673
4    Kings     3  2014     741
5    kings     4  2015     812
6    Kings     1  2016     756
7    Kings     1  2017     788
8   Riders     2  2016     694
9   Royals     4  2014     701
10  Royals     1  2015     804
11  Riders     2  2017     690

Groupby() returns groups

Python

# ONE COLUMN
print( df.groupby('Team'), '\n' )
print( df.groupby('Team').groups, '\n' )

# SEVERAL COLUMNS
print(df.groupby(['Team','Year']).groups, '\n')

Output

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f9758829180> 

{'Angels': [2, 3], 'Kings': [4, 6, 7], 'Riders': [0, 1, 8, 11], 'Royals': [9, 10], 'kings': [5]} 

{('Angels', 2014): [2], ('Angels', 2015): [3], ('Kings', 2014): [4], ('Kings', 2016): [6], ('Kings', 2017): [7], ('Riders', 2014): [0], ('Riders', 2015): [1], ('Riders', 2016): [8], ('Riders', 2017): [11], ('Royals', 2014): [9], ('Royals', 2015): [10], ('kings', 2015): [5]}

Python

# COMPOSITION OF GROUPED OBJECT
grouped = df.groupby('Year')
for group in grouped:
    print( type(group), '\n',          # each group is tuple
           type(group[0]), '\n',       # first elem: name as str
           type(group[1]), '\n',       # second elem: group as df
           len(group), '\n',
           group, '\n', sep='')

Show output (35 lines)

<class 'tuple'>
<class 'int'>
<class 'pandas.core.frame.DataFrame'>
2
(2014,      Team  Rank  Year  Points
0  Riders     1  2014     876
2  Angels     2  2014     863
4   Kings     3  2014     741
9  Royals     4  2014     701)

<class 'tuple'>
<class 'int'>
<class 'pandas.core.frame.DataFrame'>
2
(2015,       Team  Rank  Year  Points
1   Riders     2  2015     789
3   Angels     3  2015     673
5    kings     4  2015     812
10  Royals     1  2015     804)

<class 'tuple'>
<class 'int'>
<class 'pandas.core.frame.DataFrame'>
2
(2016,      Team  Rank  Year  Points
6   Kings     1  2016     756
8  Riders     2  2016     694)

<class 'tuple'>
<class 'int'>
<class 'pandas.core.frame.DataFrame'>
2
(2017,       Team  Rank  Year  Points
7    Kings     1  2017     788
11  Riders     2  2017     690)

Python

# ITERATE OVER GROUPS
grouped = df.groupby('Year')

for name, group in grouped:
    print( name )              # str
    print( group, '\n' )       # df

Output

2014
     Team  Rank  Year  Points
0  Riders     1  2014     876
2  Angels     2  2014     863
4   Kings     3  2014     741
9  Royals     4  2014     701 

2015
      Team  Rank  Year  Points
1   Riders     2  2015     789
3   Angels     3  2015     673
5    kings     4  2015     812
10  Royals     1  2015     804 

2016
     Team  Rank  Year  Points
6   Kings     1  2016     756
8  Riders     2  2016     694 

2017
      Team  Rank  Year  Points
7    Kings     1  2017     788
11  Riders     2  2017     690

Python

temp = grouped.get_group(2015)
print( type(temp) )
print( temp )

Output

<class 'pandas.core.frame.DataFrame'>
      Team  Rank  Year  Points
1   Riders     2  2015     789
3   Angels     3  2015     673
5    kings     4  2015     812
10  Royals     1  2015     804

Python

# SAME, SHORTER
df[ df['Year']==2015 ]

Output

      Team  Rank  Year  Points
1   Riders     2  2015     789
3   Devils     3  2015     673
5    kings     4  2015     812
10  Royals     1  2015     804

Analytic and aggregate functions

Top n rows with offset

-- MySQL
SELECT * FROM tips
ORDER BY tip DESC
LIMIT 10 OFFSET 5;

df.nlargest() = df.sort_values(columns, ascending=False).head(n), but more performant.
df.nlargest() retgurns 15 rows below, but df.tail(10) skips the top 5 tips which is equivalent to OFFSET 5

Python

tips.nlargest(10 + 5, columns="tip").tail(10)

Output

     total_bill   tip     sex smoker   day    time  size
183       23.17  6.50    Male    Yes   Sun  Dinner     4
214       28.17  6.50  Female    Yes   Sat  Dinner     3
47        32.40  6.00    Male     No   Sun  Dinner     4
239       29.03  5.92    Male     No   Sat  Dinner     3
88        24.71  5.85    Male     No  Thur   Lunch     2
181       23.33  5.65    Male    Yes   Sun  Dinner     2
44        30.40  5.60    Male     No   Sun  Dinner     4
52        34.81  5.20  Female     No   Sun  Dinner     4
85        34.83  5.17  Female     No  Thur   Lunch     4
211       25.89  5.16    Male    Yes   Sat  Dinner     4

Top n rows per group

-- Oracle's ROW_NUMBER() analytic function
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
  FROM tips t
)
WHERE rn < 3
ORDER BY day, rn;

Python

(
    tips.assign(
        rn=tips.sort_values(["total_bill"], ascending=False)
        .groupby(["day"])
        .cumcount()
        + 1
    )
    .query("rn < 3")
    .sort_values(["day", "rn"])
)

Output

     total_bill    tip     sex smoker   day    time  size  rn
95        40.17   4.73    Male    Yes   Fri  Dinner     4   1
90        28.97   3.00    Male    Yes   Fri  Dinner     2   2
170       50.81  10.00    Male    Yes   Sat  Dinner     3   1
212       48.33   9.00    Male     No   Sat  Dinner     4   2
156       48.17   5.00    Male     No   Sun  Dinner     6   1
182       45.35   3.50    Male    Yes   Sun  Dinner     3   2
197       43.11   5.00  Female    Yes  Thur   Lunch     4   1
142       41.19   5.00    Male     No  Thur   Lunch     5   2

the same using df.rank(method='first') function

Python

(
    tips.assign(
        rnk=tips.groupby(["day"])["total_bill"].rank(
            method="first", ascending=False
        )
    )
    .query("rnk < 3")
    .sort_values(["day", "rnk"])
)

Output

     total_bill    tip     sex smoker   day    time  size  rnk
95        40.17   4.73    Male    Yes   Fri  Dinner     4  1.0
90        28.97   3.00    Male    Yes   Fri  Dinner     2  2.0
170       50.81  10.00    Male    Yes   Sat  Dinner     3  1.0
212       48.33   9.00    Male     No   Sat  Dinner     4  2.0
156       48.17   5.00    Male     No   Sun  Dinner     6  1.0
182       45.35   3.50    Male    Yes   Sun  Dinner     3  2.0
197       43.11   5.00  Female    Yes  Thur   Lunch     4  1.0
142       41.19   5.00    Male     No  Thur   Lunch     5  2.0

-- Oracle's RANK() analytic function
SELECT * FROM (
  SELECT
    t.*,
    RANK() OVER(PARTITION BY sex ORDER BY tip) AS rnk
  FROM tips t
  WHERE tip < 2
)
WHERE rnk < 3
ORDER BY sex, rnk;

Let’s find tips with (rank < 3) per gender group for (tips < 2). Notice that when using rank(method='min') function rnk_min remains the same for the same tip (as Oracle’s RANK() function)

Python

(
    tips[tips["tip"] < 2]
    .assign(rnk_min=tips.groupby(["sex"])["tip"].rank(method="min"))
    .query("rnk_min < 3")
    .sort_values(["sex", "rnk_min"])
)

Show output (38 lines)

     total_bill  tip  sex smoker  day time  size  rnk_min
6           NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
26          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
27          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
36          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
61          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
67          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
86          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
92          NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
111         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
123         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
127         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
128         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
133         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
136         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
137         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
138         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
149         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
151         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
153         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
154         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
159         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
162         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
163         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
169         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
176         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
177         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
187         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
196         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
198         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
199         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
202         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
210         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
226         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
230         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
236         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
240         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0
241         NaN  NaN  NaN    NaN  NaN  NaN   NaN      1.0

Pandas vs. SQL: Data Wrangling Drills

Comparing Pandas with SQL

Copies vs. in place operations

SELECT

Adding a calculated column

WHERE

SQL’s OR and AND = | and & in pandas

NULL checking with notna() and isna()

UNION ALL

UNION

UPDATE

DELETE

GROUP BY

Apply multiple functions at once

Group by more than one column

GROUP BY - ANOTHER EXAMPLE

Transformation: df.groupby().transform()

Filtration: df.groupby().filter(),

JOIN

INNER JOIN

LEFT OUTER JOIN

RIGHT JOIN

FULL JOIN

SUBQUERY

Save and read JSON files and strings

Read CSV files

Handling Missing Data

Check for missing data

Removing Missing Data

Filling Missing Data

Handling outliers

Identifying outliers using the IQR (Interquartile Range)

Removing outliers

Cap outliers (replace w/boundary values)

Transforming Data

Imputing outliers (replace w/mean or median)

Scaling w/Robust Scaler (robust to outliers)

Isolation Forest - ML to machine learning algorithms to identify and handle outliers.

Other data anomalies

Duplicates

Advanced examples

1. Aggregations

1a. Aggregations on entire dataframe

1b. Aggregations with Groupby()

Groupby() returns groups

Analytic and aggregate functions

Top n rows with offset

Top n rows per group