import numpy as np
import pandas as pdPandas operations for data cleaning and tidying
Author: Dr. Hickman
Import
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)