Pandas operations for data cleaning and tidying

Author: Dr. Hickman

Import

import numpy as np
import pandas as pd

Creation

#SERIES
print("----------------------")
s = pd.Series([1, 3, 5, np.nan, 6, 8])
print(s)
----------------------
0    1.0
1    3.0
2    5.0
3    NaN
4    6.0
5    8.0
dtype: float64
#DATA-FRAME (FROM DICTIONARY)
print("----------------------")
df2 = pd.DataFrame(
    {
        "A": 1.0,
        "B": pd.Timestamp("20130102"),
        "C": pd.Series(1, index=list(range(4)), dtype="float32"),
        "D": np.array([3] * 4, dtype="int32"),
        "E": pd.Categorical(["test", "train", "test", "train"]),
        "F": "foo",
    }
)
print(df2)
print("\n column types:")
print(df2.dtypes)

#
----------------------
     A          B    C  D      E    F
0  1.0 2013-01-02  1.0  3   test  foo
1  1.0 2013-01-02  1.0  3  train  foo
2  1.0 2013-01-02  1.0  3   test  foo
3  1.0 2013-01-02  1.0  3  train  foo

 column types:
A           float64
B    datetime64[ns]
C           float32
D             int32
E          category
F            object
dtype: object
#DATA-FRAME (FROM NP ARRAY)
values=np.array([[-2,0,1,2],[1,2,3,4],[5,6,7.,8],[9,10,11,12]])
df = pd.DataFrame(values, columns=list("ABCD"))
print(df)
     A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

Pandas basics

df = pd.read_csv('data/mpg.csv')
print("----------------------")
print("GENERAL:")
print("----------------------")
print(df.index)
print(df.columns)
print("number of rows:", len(df.index))
print("number of col:",  len(df.columns))
print("keys:",  df.keys(),type(df.keys()))
----------------------
GENERAL:
----------------------
RangeIndex(start=0, stop=234, step=1)
Index(['Unnamed: 0', 'manufacturer', 'model', 'displ', 'year', 'cyl', 'trans',
       'drv', 'cty', 'hwy', 'fl', 'class'],
      dtype='object')
number of rows: 234
number of col: 12
keys: Index(['Unnamed: 0', 'manufacturer', 'model', 'displ', 'year', 'cyl', 'trans',
       'drv', 'cty', 'hwy', 'fl', 'class'],
      dtype='object') <class 'pandas.core.indexes.base.Index'>
print("info",df.info() )
print("head", df.head())
print("TYPES", df.dtypes)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 234 entries, 0 to 233
Data columns (total 12 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   Unnamed: 0    234 non-null    int64  
 1   manufacturer  234 non-null    object 
 2   model         234 non-null    object 
 3   displ         234 non-null    float64
 4   year          234 non-null    int64  
 5   cyl           234 non-null    int64  
 6   trans         234 non-null    object 
 7   drv           234 non-null    object 
 8   cty           234 non-null    int64  
 9   hwy           234 non-null    int64  
 10  fl            234 non-null    object 
 11  class         234 non-null    object 
dtypes: float64(1), int64(5), object(6)
memory usage: 22.1+ KB
info None
head    Unnamed: 0 manufacturer model  displ  year  cyl       trans drv  cty  hwy  \
0           1         audi    a4    1.8  1999    4    auto(l5)   f   18   29   
1           2         audi    a4    1.8  1999    4  manual(m5)   f   21   29   
2           3         audi    a4    2.0  2008    4  manual(m6)   f   20   31   
3           4         audi    a4    2.0  2008    4    auto(av)   f   21   30   
4           5         audi    a4    2.8  1999    6    auto(l5)   f   16   26   

  fl    class  
0  p  compact  
1  p  compact  
2  p  compact  
3  p  compact  
4  p  compact  
TYPES Unnamed: 0        int64
manufacturer     object
model            object
displ           float64
year              int64
cyl               int64
trans            object
drv              object
cty               int64
hwy               int64
fl               object
class            object
dtype: object

# ("----------------------")
# ("BASIC STATISTICS:")
# ("----------------------")
print(df.describe())
       Unnamed: 0       displ         year         cyl         cty         hwy
count  234.000000  234.000000   234.000000  234.000000  234.000000  234.000000
mean   117.500000    3.471795  2003.500000    5.888889   16.858974   23.440171
std     67.694165    1.291959     4.509646    1.611534    4.255946    5.954643
min      1.000000    1.600000  1999.000000    4.000000    9.000000   12.000000
25%     59.250000    2.400000  1999.000000    4.000000   14.000000   18.000000
50%    117.500000    3.300000  2003.500000    6.000000   17.000000   24.000000
75%    175.750000    4.600000  2008.000000    8.000000   19.000000   27.000000
max    234.000000    7.000000  2008.000000    8.000000   35.000000   44.000000

# ("----------------------")
# ("CORRELATION MATRIX:")
# ("----------------------")
print(df.corr())
            Unnamed: 0     displ      year       cyl       cty       hwy
Unnamed: 0    1.000000 -0.402459 -0.073577 -0.407698  0.401822  0.327046
displ        -0.402459  1.000000  0.147843  0.930227 -0.798524 -0.766020
year         -0.073577  0.147843  1.000000  0.122245 -0.037232  0.002158
cyl          -0.407698  0.930227  0.122245  1.000000 -0.805771 -0.761912
cty           0.401822 -0.798524 -0.037232 -0.805771  1.000000  0.955916
hwy           0.327046 -0.766020  0.002158 -0.761912  0.955916  1.000000
/var/folders/0l/7994y9sn0l1d6cxc7lqc26p80000gn/T/ipykernel_18079/2731510891.py:4: FutureWarning: The default value of numeric_only in DataFrame.corr is deprecated. In a future version, it will default to False. Select only valid columns or specify the value of numeric_only to silence this warning.
  print(df.corr())

Example

#DATA-FRAME (FROM NP ARRAY)
# np.random.seed(seed=123243)
values=np.array([[-2,0,1,2],[1,2,3,4],[5,6,7.,8],[9,10,11,12]])
df = pd.DataFrame(values, columns=list("ABCD"))
print(df)
     A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

#VIEWING DATA
print('------A------\n',df)
print('------B------\n',df.dtypes)
print(df.head())
print(df.tail(3))
print(df.index)
print(df.columns)
print(df.T)                                                         #TRANSPOSE
print('------C------\n',df.to_numpy())
print('------D------\n',df.describe())                              #GET BASIC STATISTICS 
print('------E------\n',df.sort_index(axis=0, ascending=False))     #SORT BY AXIS
print('------F------\n',df.sort_values(by="B"))                     #SORT BY VALUES 
------A------
      A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
------B------
 A    float64
B    float64
C    float64
D    float64
dtype: object
     A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
     A     B     C     D
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
RangeIndex(start=0, stop=4, step=1)
Index(['A', 'B', 'C', 'D'], dtype='object')
     0    1    2     3
A -2.0  1.0  5.0   9.0
B  0.0  2.0  6.0  10.0
C  1.0  3.0  7.0  11.0
D  2.0  4.0  8.0  12.0
------C------
 [[-2.  0.  1.  2.]
 [ 1.  2.  3.  4.]
 [ 5.  6.  7.  8.]
 [ 9. 10. 11. 12.]]
------D------
               A          B          C          D
count  4.000000   4.000000   4.000000   4.000000
mean   3.250000   4.500000   5.500000   6.500000
std    4.787136   4.434712   4.434712   4.434712
min   -2.000000   0.000000   1.000000   2.000000
25%    0.250000   1.500000   2.500000   3.500000
50%    3.000000   4.000000   5.000000   6.000000
75%    6.000000   7.000000   8.000000   9.000000
max    9.000000  10.000000  11.000000  12.000000
------E------
      A     B     C     D
3  9.0  10.0  11.0  12.0
2  5.0   6.0   7.0   8.0
1  1.0   2.0   3.0   4.0
0 -2.0   0.0   1.0   2.0
------F------
      A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
#SELECTION
print('------G------\n',df["A"])                                    #GET COLUMN A
print('------H------\n',df[0:3])                                    #SLICE BY ROWS
print('------I------\n',df[0:3]["A"])
print('------K------\n',df.loc[:, ["A", "B"]])                      
------G------
 0   -2.0
1    1.0
2    5.0
3    9.0
Name: A, dtype: float64
------H------
      A    B    C    D
0 -2.0  0.0  1.0  2.0
1  1.0  2.0  3.0  4.0
2  5.0  6.0  7.0  8.0
------I------
 0   -2.0
1    1.0
2    5.0
Name: A, dtype: float64
------K------
      A     B
0 -2.0   0.0
1  1.0   2.0
2  5.0   6.0
3  9.0  10.0
#SELECT BY INDEX VALUE "LOCATION" (SIMILAR TO NUMPY)
print('------O------\n',df.iloc[3])                              
print('------P------\n',df.iloc[1:2, 0:2])                               
print('------Q------\n',df.iloc[[1, 2, 3], [0, 2]])
print('------R------\n',df.iloc[1:3, :])
print('------S------\n',df.iloc[1, 1])
------O------
 A     9.0
B    10.0
C    11.0
D    12.0
Name: 3, dtype: float64
------P------
      A    B
1  1.0  2.0
------Q------
      A     C
1  1.0   3.0
2  5.0   7.0
3  9.0  11.0
------R------
      A    B    C    D
1  1.0  2.0  3.0  4.0
2  5.0  6.0  7.0  8.0
------S------
 2.0
print("------BOOLEAN INDEXING------")
print(df[df["A"] > 0])
print('------------\n',df[df > 0])
df2 = df.copy()
df2["E"] = ["one", "two", "three", "four"]
print('------------\n',df2)
print('------------\n',df2[df2["E"].isin(["two", "four"])])         #SELECT VARIABLE IN LIST

# BOOLEAN EXAMPLES
print()
#       (Expression 1)  AND   (Expression 2)
# print(   (Sales > 15)    &   (Newspaper > 10)  )
------BOOLEAN INDEXING------
     A     B     C     D
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
------------
      A     B     C     D
0  NaN   NaN   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
------------
      A     B     C     D      E
0 -2.0   0.0   1.0   2.0    one
1  1.0   2.0   3.0   4.0    two
2  5.0   6.0   7.0   8.0  three
3  9.0  10.0  11.0  12.0   four
------------
      A     B     C     D     E
1  1.0   2.0   3.0   4.0   two
3  9.0  10.0  11.0  12.0  four
print(df)
print(df > 0)
     A     B     C     D
0 -2.0   0.0   1.0   2.0
1  1.0   2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0
       A      B     C     D
0  False  False  True  True
1   True   True  True  True
2   True   True  True  True
3   True   True  True  True

Convert to datetime objects

# CREATE DATA 
df = pd.DataFrame(
    [
        [1, "210298", "022198", "980221", "19980221"],
        [2, "190399", "031999", "990319", "19990319"],
    ]
)

print("BEFORE\n",df)
df[4] = pd.to_datetime(df[4])  #convert column-4
df[3] = pd.to_datetime(df[3])  #convert column-3
df[2] = pd.to_datetime(df[2])  #convert column-2
print("AFTER\n",df)
BEFORE
    0       1       2       3         4
0  1  210298  022198  980221  19980221
1  2  190399  031999  990319  19990319
AFTER
    0       1          2          3          4
0  1  210298 1998-02-21 1998-02-21 1998-02-21
1  2  190399 1999-03-19 1999-03-19 1999-03-19

Renaming, randomizing, reductions

# AVERAGING
df = pd.DataFrame(
    [
        ["pos", "M", 1],
        ["pos", "M", 2],
        ["neg", "M", 3],
        ["neg", "M", 4],
        ["sev", "M", 6],
        ["sev", "M", 7],
        ["pos", "F", 8],
        ["pos", "F", 9],
        ["neg", "F", 10],
        ["neg", "F", 11],
        ["sev", "F", 12],
        ["sev", "F", 13],
    ]
)
print("ORIGINAL DATA-FRAME\n",df)
ORIGINAL DATA-FRAME
       0  1   2
0   pos  M   1
1   pos  M   2
2   neg  M   3
3   neg  M   4
4   sev  M   6
5   sev  M   7
6   pos  F   8
7   pos  F   9
8   neg  F  10
9   neg  F  11
10  sev  F  12
11  sev  F  13
# RENAMING COLUMNS 
df=df.rename(columns={0: "thrombosis", 1: "m_f", 2: "num"})
print(df)
   thrombosis m_f  num
0         pos   M    1
1         pos   M    2
2         neg   M    3
3         neg   M    4
4         sev   M    6
5         sev   M    7
6         pos   F    8
7         pos   F    9
8         neg   F   10
9         neg   F   11
10        sev   F   12
11        sev   F   13
# RANDOMIZE ROWS AND RESET INDEX
df=df.sample(frac=1).reset_index(drop=True)
print(df)
   thrombosis m_f  num
0         sev   F   13
1         pos   M    1
2         sev   M    7
3         neg   F   11
4         sev   F   12
5         neg   M    4
6         pos   F    8
7         neg   M    3
8         sev   M    6
9         pos   M    2
10        neg   F   10
11        pos   F    9
# SUM THE "num" BASED ON "m_f" CATEGORY
print(df.groupby(['m_f'])['num'].sum().reset_index())
  m_f  num
0   F   63
1   M   23
# SUM THE "num" BASED ON "m_f" AND "thrombosis" CATEGORY
print(df.groupby(['m_f','thrombosis'])['num'].sum().reset_index())
  m_f thrombosis  num
0   F        neg   21
1   F        pos   17
2   F        sev   25
3   M        neg    7
4   M        pos    3
5   M        sev   13

GAP-MINDER EXAMPLE

# read data
df = pd.read_csv('./data/gapminder.csv')
# print original df
print(df.head(10))
       country continent  year  lifeExp       pop   gdpPercap
0  Afghanistan      Asia  1952   28.801   8425333  779.445314
1  Afghanistan      Asia  1957   30.332   9240934  820.853030
2  Afghanistan      Asia  1962   31.997  10267083  853.100710
3  Afghanistan      Asia  1967   34.020  11537966  836.197138
4  Afghanistan      Asia  1972   36.088  13079460  739.981106
5  Afghanistan      Asia  1977   38.438  14880372  786.113360
6  Afghanistan      Asia  1982   39.854  12881816  978.011439
7  Afghanistan      Asia  1987   40.822  13867957  852.395945
8  Afghanistan      Asia  1992   41.674  16317921  649.341395
9  Afghanistan      Asia  1997   41.763  22227415  635.341351
# print df size
print(df.shape)

print(type(df))
(1704, 6)
<class 'pandas.core.frame.DataFrame'>
# GROUP BY CONTINENT 
# print((df.groupby('continent')).head())
# print('----\n',df.groupby('continent').tail())
print("----AVERAGE LIFE EXPECTANCY BY CONTINENT----")
print(df.groupby('continent')['lifeExp'].mean())
----AVERAGE LIFE EXPECTANCY BY CONTINENT----
continent
Africa      48.865330
Americas    64.658737
Asia        60.064903
Europe      71.903686
Oceania     74.326208
Name: lifeExp, dtype: float64

print("----AVERAGE LIFE EXPECTANCY BY COUNTRY----")
print(df.groupby('country')['lifeExp'].mean())
----AVERAGE LIFE EXPECTANCY BY COUNTRY----
country
Afghanistan           37.478833
Albania               68.432917
Algeria               59.030167
Angola                37.883500
Argentina             69.060417
                        ...    
Vietnam               57.479500
West Bank and Gaza    60.328667
Yemen, Rep.           46.780417
Zambia                45.996333
Zimbabwe              52.663167
Name: lifeExp, Length: 142, dtype: float64

print("----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----")
print(df.groupby(['continent','year'])['lifeExp'].mean())
----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----
continent  year
Africa     1952    39.135500
           1957    41.266346
           1962    43.319442
           1967    45.334538
           1972    47.450942
           1977    49.580423
           1982    51.592865
           1987    53.344788
           1992    53.629577
           1997    53.598269
           2002    53.325231
           2007    54.806038
Americas   1952    53.279840
           1957    55.960280
           1962    58.398760
           1967    60.410920
           1972    62.394920
           1977    64.391560
           1982    66.228840
           1987    68.090720
           1992    69.568360
           1997    71.150480
           2002    72.422040
           2007    73.608120
Asia       1952    46.314394
           1957    49.318544
           1962    51.563223
           1967    54.663640
           1972    57.319269
           1977    59.610556
           1982    62.617939
           1987    64.851182
           1992    66.537212
           1997    68.020515
           2002    69.233879
           2007    70.728485
Europe     1952    64.408500
           1957    66.703067
           1962    68.539233
           1967    69.737600
           1972    70.775033
           1977    71.937767
           1982    72.806400
           1987    73.642167
           1992    74.440100
           1997    75.505167
           2002    76.700600
           2007    77.648600
Oceania    1952    69.255000
           1957    70.295000
           1962    71.085000
           1967    71.310000
           1972    71.910000
           1977    72.855000
           1982    74.290000
           1987    75.320000
           1992    76.945000
           1997    78.190000
           2002    79.740000
           2007    80.719500
Name: lifeExp, dtype: float64
print("----AVERAGE+MEDIAN EXPECTANCY BY YEAR----")
print(df.groupby('year')['lifeExp'].agg([np.mean,np.median]))
----AVERAGE+MEDIAN EXPECTANCY BY YEAR----
           mean   median
year                    
1952  49.057620  45.1355
1957  51.507401  48.3605
1962  53.609249  50.8810
1967  55.678290  53.8250
1972  57.647386  56.5300
1977  59.570157  59.6720
1982  61.533197  62.4415
1987  63.212613  65.8340
1992  64.160338  67.7030
1997  65.014676  69.3940
2002  65.694923  70.8255
2007  67.007423  71.9355

Mutate,select,split,filter,arrange

print("----ORIGINAL----")
mpg = pd.read_csv('data/mpg.csv')
print(mpg.head(10))
----ORIGINAL----
   Unnamed: 0 manufacturer       model  displ  year  cyl       trans drv  cty  \
0           1         audi          a4    1.8  1999    4    auto(l5)   f   18   
1           2         audi          a4    1.8  1999    4  manual(m5)   f   21   
2           3         audi          a4    2.0  2008    4  manual(m6)   f   20   
3           4         audi          a4    2.0  2008    4    auto(av)   f   21   
4           5         audi          a4    2.8  1999    6    auto(l5)   f   16   
5           6         audi          a4    2.8  1999    6  manual(m5)   f   18   
6           7         audi          a4    3.1  2008    6    auto(av)   f   18   
7           8         audi  a4 quattro    1.8  1999    4  manual(m5)   4   18   
8           9         audi  a4 quattro    1.8  1999    4    auto(l5)   4   16   
9          10         audi  a4 quattro    2.0  2008    4  manual(m6)   4   20   

   hwy fl    class  
0   29  p  compact  
1   29  p  compact  
2   31  p  compact  
3   30  p  compact  
4   26  p  compact  
5   26  p  compact  
6   27  p  compact  
7   26  p  compact  
8   25  p  compact  
9   28  p  compact  
print("----MUTATE----")
mpg['cty'] = mpg['cty'] * 1.609/3.7854 # mile/gallon --> km/l
mpg['hwy'] = mpg['hwy'] * 1.609/3.7854
mpg['avg'] = (mpg.cty + mpg.hwy)/2
print(mpg.head())
----MUTATE----
   Unnamed: 0 manufacturer model  displ  year  cyl       trans drv       cty  \
0           1         audi    a4    1.8  1999    4    auto(l5)   f  7.650975   
1           2         audi    a4    1.8  1999    4  manual(m5)   f  8.926137   
2           3         audi    a4    2.0  2008    4  manual(m6)   f  8.501083   
3           4         audi    a4    2.0  2008    4    auto(av)   f  8.926137   
4           5         audi    a4    2.8  1999    6    auto(l5)   f  6.800866   

         hwy fl    class        avg  
0  12.326571  p  compact   9.988773  
1  12.326571  p  compact  10.626354  
2  13.176679  p  compact  10.838881  
3  12.751625  p  compact  10.838881  
4  11.051408  p  compact   8.926137  
#SPLIT STRING COLUMN by delimiter "." AND ADD THE TWO PARTS AS NEW COLUMNS
# just prints, doesn't overwrite original
print("----SPLIT----")
print(pd.concat([mpg, mpg['displ'].astype("str").str.split('.', expand=True)], axis=1))
----SPLIT----
     Unnamed: 0 manufacturer   model  displ  year  cyl       trans drv  \
0             1         audi      a4    1.8  1999    4    auto(l5)   f   
1             2         audi      a4    1.8  1999    4  manual(m5)   f   
2             3         audi      a4    2.0  2008    4  manual(m6)   f   
3             4         audi      a4    2.0  2008    4    auto(av)   f   
4             5         audi      a4    2.8  1999    6    auto(l5)   f   
..          ...          ...     ...    ...   ...  ...         ...  ..   
229         230   volkswagen  passat    2.0  2008    4    auto(s6)   f   
230         231   volkswagen  passat    2.0  2008    4  manual(m6)   f   
231         232   volkswagen  passat    2.8  1999    6    auto(l5)   f   
232         233   volkswagen  passat    2.8  1999    6  manual(m5)   f   
233         234   volkswagen  passat    3.6  2008    6    auto(s6)   f   

          cty        hwy fl    class        avg  0  1  
0    7.650975  12.326571  p  compact   9.988773  1  8  
1    8.926137  12.326571  p  compact  10.626354  1  8  
2    8.501083  13.176679  p  compact  10.838881  2  0  
3    8.926137  12.751625  p  compact  10.838881  2  0  
4    6.800866  11.051408  p  compact   8.926137  2  8  
..        ...        ... ..      ...        ... .. ..  
229  8.076029  11.901516  p  midsize   9.988773  2  0  
230  8.926137  12.326571  p  midsize  10.626354  2  0  
231  6.800866  11.051408  p  midsize   8.926137  2  8  
232  7.650975  11.051408  p  midsize   9.351191  2  8  
233  7.225921  11.051408  p  midsize   9.138664  3  6  

[234 rows x 15 columns]
print("----SELECT----")
print(mpg[['year','manufacturer','avg']])
----SELECT----
     year manufacturer        avg
0    1999         audi   9.988773
1    1999         audi  10.626354
2    2008         audi  10.838881
3    2008         audi  10.838881
4    1999         audi   8.926137
..    ...          ...        ...
229  2008   volkswagen   9.988773
230  2008   volkswagen  10.626354
231  1999   volkswagen   8.926137
232  1999   volkswagen   9.351191
233  2008   volkswagen   9.138664

[234 rows x 3 columns]
print("----FILTER----")  
print(mpg[mpg.manufacturer=='audi'])
----FILTER----
    Unnamed: 0 manufacturer       model  displ  year  cyl       trans drv  \
0            1         audi          a4    1.8  1999    4    auto(l5)   f   
1            2         audi          a4    1.8  1999    4  manual(m5)   f   
2            3         audi          a4    2.0  2008    4  manual(m6)   f   
3            4         audi          a4    2.0  2008    4    auto(av)   f   
4            5         audi          a4    2.8  1999    6    auto(l5)   f   
5            6         audi          a4    2.8  1999    6  manual(m5)   f   
6            7         audi          a4    3.1  2008    6    auto(av)   f   
7            8         audi  a4 quattro    1.8  1999    4  manual(m5)   4   
8            9         audi  a4 quattro    1.8  1999    4    auto(l5)   4   
9           10         audi  a4 quattro    2.0  2008    4  manual(m6)   4   
10          11         audi  a4 quattro    2.0  2008    4    auto(s6)   4   
11          12         audi  a4 quattro    2.8  1999    6    auto(l5)   4   
12          13         audi  a4 quattro    2.8  1999    6  manual(m5)   4   
13          14         audi  a4 quattro    3.1  2008    6    auto(s6)   4   
14          15         audi  a4 quattro    3.1  2008    6  manual(m6)   4   
15          16         audi  a6 quattro    2.8  1999    6    auto(l5)   4   
16          17         audi  a6 quattro    3.1  2008    6    auto(s6)   4   
17          18         audi  a6 quattro    4.2  2008    8    auto(s6)   4   

         cty        hwy fl    class        avg  
0   7.650975  12.326571  p  compact   9.988773  
1   8.926137  12.326571  p  compact  10.626354  
2   8.501083  13.176679  p  compact  10.838881  
3   8.926137  12.751625  p  compact  10.838881  
4   6.800866  11.051408  p  compact   8.926137  
5   7.650975  11.051408  p  compact   9.351191  
6   7.650975  11.476462  p  compact   9.563718  
7   7.650975  11.051408  p  compact   9.351191  
8   6.800866  10.626354  p  compact   8.713610  
9   8.501083  11.901516  p  compact  10.201300  
10  8.076029  11.476462  p  compact   9.776246  
11  6.375812  10.626354  p  compact   8.501083  
12  7.225921  10.626354  p  compact   8.926137  
13  7.225921  10.626354  p  compact   8.926137  
14  6.375812  10.626354  p  compact   8.501083  
15  6.375812  10.201300  p  midsize   8.288556  
16  7.225921  10.626354  p  midsize   8.926137  
17  6.800866   9.776246  p  midsize   8.288556  

#-----------------------------
print("----ARRANGE----")  
#-----------------------------
print(mpg.sort_values(by=['avg'], ascending=False))
----ARRANGE----
     Unnamed: 0 manufacturer                model  displ  year  cyl  \
221         222   volkswagen           new beetle    1.9  1999    4   
212         213   volkswagen                jetta    1.9  1999    4   
222         223   volkswagen           new beetle    1.9  1999    4   
196         197       toyota              corolla    1.8  2008    4   
195         196       toyota              corolla    1.8  1999    4   
..          ...          ...                  ...    ...   ...  ...   
126         127         jeep   grand cherokee 4wd    4.7  2008    8   
59           60        dodge          durango 4wd    4.7  2008    8   
65           66        dodge  ram 1500 pickup 4wd    4.7  2008    8   
54           55        dodge    dakota pickup 4wd    4.7  2008    8   
69           70        dodge  ram 1500 pickup 4wd    4.7  2008    8   

          trans drv        cty        hwy fl       class        avg  
221  manual(m5)   f  14.876895  18.702383  d  subcompact  16.789639  
212  manual(m5)   f  14.026787  18.702383  d     compact  16.364585  
222    auto(l4)   f  12.326571  17.427220  d  subcompact  14.876895  
196  manual(m5)   f  11.901516  15.727004  r     compact  13.814260  
195  manual(m5)   f  11.051408  14.876895  r     compact  12.964152  
..          ...  ..        ...        ... ..         ...        ...  
126    auto(l5)   4   3.825487   5.100650  e         suv   4.463069  
59     auto(l5)   4   3.825487   5.100650  e         suv   4.463069  
65     auto(l5)   4   3.825487   5.100650  e      pickup   4.463069  
54     auto(l5)   4   3.825487   5.100650  e      pickup   4.463069  
69   manual(m6)   4   3.825487   5.100650  e      pickup   4.463069  

[234 rows x 13 columns]
print("----CONVERT----")  
print(mpg.dtypes)
mpg['manufacturer']=mpg.manufacturer.astype('category')
mpg['model']=mpg.model.astype('string')
print('--------')
print(mpg.dtypes)
----CONVERT----
Unnamed: 0        int64
manufacturer     object
model            object
displ           float64
year              int64
cyl               int64
trans            object
drv              object
cty             float64
hwy             float64
fl               object
class            object
avg             float64
dtype: object
--------
Unnamed: 0         int64
manufacturer    category
model             string
displ            float64
year               int64
cyl                int64
trans             object
drv               object
cty              float64
hwy              float64
fl                object
class             object
avg              float64
dtype: object

MERGING (JOINS)

print("----JOINING DATAFRAMES---")
df1 = pd.DataFrame([['x1', 'y1','p1'], ['x2', 'y2','p2'], ['x3', 'y3','p3']], columns=['x', 'y','p'])
df2 = pd.DataFrame([['x1', 'y1','w1', 'z1']
                    , ['x2','y2', 'w2', 'z2']
                    , ['x4','y4', 'w4', 'z4']],columns=['x', 'y','w', 'z'])
print("ORIGINALS:")
print(df1,'\n')
print(df2)
----JOINING DATAFRAMES---
ORIGINALS:
    x   y   p
0  x1  y1  p1
1  x2  y2  p2
2  x3  y3  p3 

    x   y   w   z
0  x1  y1  w1  z1
1  x2  y2  w2  z2
2  x4  y4  w4  z4
print('INNER JOIN: \n',df1.merge(df2, how='inner'))
INNER JOIN: 
     x   y   p   w   z
0  x1  y1  p1  w1  z1
1  x2  y2  p2  w2  z2
print('LEFT JOIN:  \n',df1.merge(df2, how='left'))
LEFT JOIN:  
     x   y   p    w    z
0  x1  y1  p1   w1   z1
1  x2  y2  p2   w2   z2
2  x3  y3  p3  NaN  NaN
print('RIGHT JOIN: \n',df1.merge(df2, how='right'))
RIGHT JOIN: 
     x   y    p   w   z
0  x1  y1   p1  w1  z1
1  x2  y2   p2  w2  z2
2  x4  y4  NaN  w4  z4
print('OUTER JOIN: \n',df1.merge(df2, how='outer'))
OUTER JOIN: 
     x   y    p    w    z
0  x1  y1   p1   w1   z1
1  x2  y2   p2   w2   z2
2  x3  y3   p3  NaN  NaN
3  x4  y4  NaN   w4   z4

Concatenating series

s1 = pd.Series(['a', 'b'])
s2 = pd.Series(['c', 'd'])
print("ORIGINAL")
print(s1)
print(s2)
print("OUTPUT")
print(pd.concat([s1, s2],axis=0))
print(pd.concat([s1, s2],axis=1))
ORIGINAL
0    a
1    b
dtype: object
0    c
1    d
dtype: object
OUTPUT
0    a
1    b
0    c
1    d
dtype: object
   0  1
0  a  c
1  b  d

Concatenating data-frames

df1 = pd.DataFrame([['a', 1], ['b', 2]], columns=['letter', 'number'])
df2 = pd.DataFrame([['c', 3], ['d', 4]], columns=['letter', 'number'])
print("ORIGINAL:")
print(df1)
print(df2)
ORIGINAL:
  letter  number
0      a       1
1      b       2
  letter  number
0      c       3
1      d       4
print("CONCAT:")
print(pd.concat([df1, df2],axis=0))
print(pd.concat([df1, df2],axis=1))
CONCAT:
  letter  number
0      a       1
1      b       2
0      c       3
1      d       4
  letter  number letter  number
0      a       1      c       3
1      b       2      d       4

APPLY AND LAMBDA FUNCTIONS

https://www.w3schools.com/python/python_lambda.asp

A lambda function is a small anonymous function.

A lambda function can take any number of arguments, but can only have one expression.

#iterate and apply a function along an axis of the DataFrame.
df1 = pd.DataFrame([[1, 2], [3, 2]], columns=['A', 'B'])
print("ORIGINAL")
print(df1)
print("ADD\n",df1.apply(lambda x: x.A+x.B, axis=1))
ORIGINAL
   A  B
0  1  2
1  3  2
ADD
 0    3
1    5
dtype: int64
f = lambda a : a + 10
print("EX-1:",f(5))
EX-1: 15
f = lambda a, b : a * b
print("EX-2:",f(5, 6))
EX-2: 30
def myfunc(n):
    return lambda a : a * n
mytripler = myfunc(3)
print("EX-3:",mytripler(11))
EX-3: 33

Melting and Pivoting

#----------------------
#CONSIDER A SURVEY
#----------------------
# with 3 participants (subject) 
# and  2 measurements (HEIGHT,WEIGHT)=(H,W)
#print("----------",example,"----------")

print("----WIDE FORMAT----")
# ONE "OBSERVATION" IS ONE "PARTICIPANT"
df1 = pd.DataFrame(
    {'ID': {0: 'ID0', 1: 'ID1', 2: 'ID2'},
        'H' : {0: "H0" , 1: "H1" , 2: "H2"},
        'W' : {0: "W0" , 1: "W1" , 2: "W2"}  ,
        'A' : {0: "A0" , 1: "A1" , 2: "A2"}
        }
                    )
print(df1)
----WIDE FORMAT----
    ID   H   W   A
0  ID0  H0  W0  A0
1  ID1  H1  W1  A1
2  ID2  H2  W2  A2
   
print("----LONG FORMAT----")
# ONE "OBSERVATION" IS ONE "MEASUREMENT" OF A PARTICIPANT

#NEED TELL IT WHAT AN "OBSERVATION" (ID) IS
df2=pd.melt(df1, id_vars =['ID']) 
print(df2)
----LONG FORMAT----
    ID variable value
0  ID0        H    H0
1  ID1        H    H1
2  ID2        H    H2
3  ID0        W    W0
4  ID1        W    W1
5  ID2        W    W2
6  ID0        A    A0
7  ID1        A    A1
8  ID2        A    A2
print("MELT-1 ONLY ALONG A AND W DIMENSIONS")
print(pd.melt(df1, id_vars =['ID','H']))

print("MELT-2 ONLY ALONG A DIMENSION")
print(pd.melt(df1, id_vars =['ID','H','W']))
# RENAME
# print(df.melt(id_vars=["ID"], var_name = 'var-name', value_name='VALUE'))
MELT-1 ONLY ALONG A AND W DIMENSIONS
    ID   H variable value
0  ID0  H0        W    W0
1  ID1  H1        W    W1
2  ID2  H2        W    W2
3  ID0  H0        A    A0
4  ID1  H1        A    A1
5  ID2  H2        A    A2
MELT-2 ONLY ALONG A DIMENSION
    ID   H   W variable value
0  ID0  H0  W0        A    A0
1  ID1  H1  W1        A    A1
2  ID2  H2  W2        A    A2
#CONVERT BACK
print("----WIDE FORMAT (PIVOT BACK)----")
df2=df2.pivot(*df2).rename_axis(columns = None).reset_index() 
print(df2)
----WIDE FORMAT (PIVOT BACK)----
    ID   A   H   W
0  ID0  A0  H0  W0
1  ID1  A1  H1  W1
2  ID2  A2  H2  W2
/var/folders/0l/7994y9sn0l1d6cxc7lqc26p80000gn/T/ipykernel_18079/2262497541.py:3: FutureWarning: In a future version of pandas all arguments of DataFrame.pivot will be keyword-only.
  df2=df2.pivot(*df2).rename_axis(columns = None).reset_index()

Splitting

#---------------------------------
# SPLITTING STRINGS INTO MULTIPLE COLUMN  
#---------------------------------
df1 = pd.DataFrame([['m143'], ['f1232']], columns=['A'])
print("ORIGNIAL-0\n",df1)

#SPLITTING m143 --> m 143
#Series.str()-- >Vectorized string functions for Series and Index.
df1["MF"]  = df1.A.str[0]
df1["NUM"] = df1.A.str[1:]
df1=df1.drop('A',axis=1)                                 
print("MODIFIED-0\n",df1)
ORIGNIAL-0
        A
0   m143
1  f1232
MODIFIED-0
   MF   NUM
0  m   143
1  f  1232
#---------------------------------
# SPLITTING A LIST STORED AS A STRING
#---------------------------------
# #Series.str()-- >Vectorized string functions for Series and Index.
df1 = pd.DataFrame([['[1, 2]','[3, 4]'], ['[NA, 6]','[7, 8]']], columns=['A', 'B'])
print("ORIGNIAL-1\n",df1)

df1[['A1','A2']] = pd.DataFrame(df1.A.str.replace('[','', regex=True).str.replace(']','', regex=True).str.split(',').tolist())
df1[['B1','B2']] = pd.DataFrame(df1.B.str.replace('[','', regex=True).str.replace(']','', regex=True).str.split(',').tolist())
df1=df1.drop(['A','B'],axis=1)                                 
print("MODIFIED-1\n",df1)
ORIGNIAL-1
          A       B
0   [1, 2]  [3, 4]
1  [NA, 6]  [7, 8]
MODIFIED-1
    A1  A2 B1  B2
0   1   2  3   4
1  NA   6  7   8
#---------------------------------
# SPLITTING STRINGS INTO MULTIPLE COLUMNS (ALTERNATIVE METHOD)
#---------------------------------
df1 = pd.DataFrame([['m143'], ['f1232']], columns=['A'])
print("ORIGNIAL-0\n",df1)

#SPLITTING m143 --> m 143
df1 = df1.assign(                               # create new columns
  MF    = lambda x: x.A.str[0].astype(str),
  NUM   = lambda x: x.A.str[1:].astype(str)).drop('A', axis=1)                     # Remove old column
print("MODIFIED-0\n",df1)
ORIGNIAL-0
        A
0   m143
1  f1232
MODIFIED-0
   MF   NUM
0  m   143
1  f  1232
#---------------------------------
# #SPLITTING STRINGS INTO MULTIPLE COLUMN (ALTERNATIVE METHOD)
#---------------------------------
df1 = pd.DataFrame([['m143'], ['f1232']], columns=['A'])
print("ORIGNIAL-0\n",df1)

#SPLITTING m143 --> m 143
df1 = df1.assign(                          # create new columns
  MF    = df1.A.str[0],
  NUM   = df1.A.str[1:],
                ).drop('A', axis=1)        # Remove old column
print("MODIFIED-0\n",df1)
ORIGNIAL-0
        A
0   m143
1  f1232
MODIFIED-0
   MF   NUM
0  m   143
1  f  1232
#---------------------------------
# SPLITTING A LIST STORED AS A STRING (ALTERNATIVE METHOD)
#---------------------------------
df1 = pd.DataFrame([['[1, 2]','[3, 4]'], ['[5, 6]','[7, 8]']], columns=['A', 'B'])
print("ORIGNIAL-2\n",df1)
df1['A']  = df1.apply(lambda x: x.A.replace('[','').replace(']','').split(','), axis=1)
df1['B']  = df1.apply(lambda x: x.B.replace('[','').replace(']','').split(','), axis=1)
df1[['A1','A2']] = pd.DataFrame(df1.A.tolist())
df1[['B1','B2']] = pd.DataFrame(df1.B.tolist())
df1=df1.drop(['A','B'],axis=1)
print("MODIFIED-2\n",df1)
ORIGNIAL-2
         A       B
0  [1, 2]  [3, 4]
1  [5, 6]  [7, 8]
MODIFIED-2
   A1  A2 B1  B2
0  1   2  3   4
1  5   6  7   8

MISSING VALUES

#DATA-FRAME (FROM NP ARRAY)
values=np.array([[-2,-1,1,2],[1,-2,3,4],[5,6,7.,8],[9,10,11,12]])
df = pd.DataFrame(values, columns=list("ABCD"))
print(df)

print("\n------INJECT MISSING DATA------")
df1=df[df > -1]
print(df1)

print("\nisnull\n",df1.isnull())
print("\nisna\n",df1.isna())

# drop all columns that have missing data
print("\ndrop columns\n",df1.dropna(axis= 1))

# #to drop any rows that have missing data.
print("\ndrop rows\n",df1.dropna(axis= 0))

print("\nfill NaN with 5\n",df1.fillna(value=5))

print("\naverage of col-D: ",df1["D"].mean())
print("\nfill NaN with average of col-D\n",df1.fillna(df1["D"].mean()))
     A     B     C     D
0 -2.0  -1.0   1.0   2.0
1  1.0  -2.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

------INJECT MISSING DATA------
     A     B     C     D
0  NaN   NaN   1.0   2.0
1  1.0   NaN   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

isnull
        A      B      C      D
0   True   True  False  False
1  False   True  False  False
2  False  False  False  False
3  False  False  False  False

isna
        A      B      C      D
0   True   True  False  False
1  False   True  False  False
2  False  False  False  False
3  False  False  False  False

drop columns
       C     D
0   1.0   2.0
1   3.0   4.0
2   7.0   8.0
3  11.0  12.0

drop rows
      A     B     C     D
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

fill NaN with 5
      A     B     C     D
0  5.0   5.0   1.0   2.0
1  1.0   5.0   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

average of col-D:  6.5

fill NaN with average of col-D
      A     B     C     D
0  6.5   6.5   1.0   2.0
1  1.0   6.5   3.0   4.0
2  5.0   6.0   7.0   8.0
3  9.0  10.0  11.0  12.0

EXTRA CODE


# print("----USING INDEX NOT ID----")
# print(df1)
# print(df1.index)
# print(df1.columns)

# df2.index=df1['ID']
# df2=df2.drop(['ID'],axis=1)
# print(df2)
# print(df2.index)
# print(df2.columns)    
# print(pd.melt(df2))

# #DATES
# print("----------------------")
# dates = pd.date_range("20130101", periods=4)
# print(dates)

# #---------------
# # OPERATIONS
# #---------------
# print("\n------STATS------")
# print(df)
# print('------------')
# print(df.mean(0))     #axis=0 (down rowns)
# print('------------')
# print(df.mean(1))     #axis=1 (accross columns)


# print("\n------APPLY FUNCTIONS------")
# print(df)
# print('------------')
# print(df.apply(np.cumsum))    #CUMULATIVE SUM DOWN COLUMN
# print('------------')
# print(df.apply(lambda x: x.max() - x.min()))
 
# # create a sample column
# nums = pd.Series(["1", "2", "3.14", "5"])

# # print the series
# print(nums)

# # print the data type
# print('\nData Type: ',nums.dtype)