calculating weighted mean SAT scores per state with apply
- something about ‘.apply’ method
thegroup
object has four methods that accepts a function (or functions ) to perform a calculations on each group.
these four methods are.agg
,.filter
,.transform
, and.apply
.
each of the first three of these method has a specific output that the function must return .
.agg
must return a scalar value,
.filter
must return a Boolean,
.transform
must a Series or DataFrame with the same length as the the passed group.
the.apply
method, however, may return a scalar value, a Series,or even a DataFrame of any shape, therefore making it very flexible
the.apply
method is also called only once per group (on a DataFrame), while the.transform
and.agg
method get called once for each aggregating column(on a Series).
the.apply
method 's ability to return a single object when operating on multiple columns at the same time .
# Task: calculate the weight average of both the math and verbal SAT scores pre state from the college dataset
# weight the scores by the population of undergraduate students per school
import pandas as pd
college=pd.read_csv('college.csv')
subset=['UGDS','SATMTMID','SATVRMID']
# drop any rows that have missing values in the subeset
# .dropna ,by default, drops rows that have one or more missing values
college2=college.dropna(subset=subset)
college.shape
Out[13]: (7535, 27)
college2.shape
Out[14]: (1184, 27)
# create a user-defined function to calculate the weighted average of the SAT math scores
# this function gets passed a DataFrame of all the original columns for each group, and returns a single scalar
def weighted_math_average(df):
weight_math=df['UGDS']*df['SATMTMID']
return int(weight_math.sum()/df['UGDS'].sum())
# group by state and pass this function the .apply method
# because each of group has multiple columns and we want to reduce those to a single value
# we need to use .apply
# the weight_math_average function will be called once for each group (not on the individual column in the group
# we cannot use .agg method since it returns a value for each of its aggregating columns.
college2.groupby('STABBR').apply(weighted_math_average)
Out[22]:
STABBR
AK 503
AL 536
AR 529
AZ 569
CA 564
... ...
WV 500
WY 540
dtype: int64
# we successfully returned a scalar value for each group
# take a detour to look what happen if calling .agg method
college2.groupby('STABBR').agg(weighted_math_average)
Traceback (most recent call last):
File "D:\PyCharm2020\python2020\lib\site-
....
KeyError: 'UGDS'
# this error is caused by lack of aggregating columns
# the weighted_math_average function gets applied to each non-aggregating column in the DataFrame
# the nice feature of .apply is that you can create multiple new columns by returning a Series
# the index of this returned Series will be the column names
# modify function to calculate the weight and arithmetic average for both SAT scores along with the count of insitutions from each group
def weighted_average(df):
weight_m=df['UGDS']*df['SATMTMID']
weight_v=df['UGDS']*df['SATVRMID']
wm_avg=weight_m.sum()/df['UGDS'].sum()
wv_avg=weight_v.sum()/df['UGDS'].sum()
data={'w_math_avg':wm_avg,
'w_verbal_avg':wv_avg,
'math_avg':df['SATMTMID'].mean(),
'verbal_avg':df['SATVRMID'].mean(),
'count':len(df)}
return pd.Series(data)
college2.groupby('STABBR').apply(weighted_average)
Out[35]:
w_math_avg w_verbal_avg math_avg verbal_avg count
STABBR
AK 503.000000 555.000000 503.000000 555.000000 1.0
AL 536.137917 533.383387 504.285714 508.476190 21.0
AR 529.112332 504.876157 515.937500 491.875000 16.0
AZ 569.313985 557.303350 536.666667 538.333333 6.0
CA 564.945420 539.316605 562.902778 549.083333 72.0
CO 553.123820 547.033996 540.214286 537.714286 14.0
... ... ...
WI 593.562862 556.348332 545.071429 516.857143 14.0
WV 500.725781 487.680187 481.705882 473.411765 17.0
WY 540.000000 535.000000 540.000000 535.000000 1.0
# to use .apply to create multiple columns, you must return a Series.
# the index values are used as column names in the resulting DataFrame
# you can return as many values as you want with this method
college2.groupby('STABBR').apply(weighted_average).astype(int)
Out[36]:
w_math_avg w_verbal_avg math_avg verbal_avg count
STABBR
AK 503 555 503 555 1
AL 536 533 504 508 21
AR 529 504 515 491 16
AZ 569 557 536 538 6
... ... ...
WY 540 535 540 535 1
# it's possible to return any numbeof rows and columns for each group by returning a DataFrame
# Task: also find the geometric and harmonic meams for both SAT columns
# and return the results as a DataFrame with rows as the name of type of mean and columns as the SAT type
# use the NumPy function average to compute the weighted average
# the SciPy functions `gmean` and `hmean` for geometric and harmonic means
from scipy.stats import gmean,hmean
def calculate_means(df):
df_mean=pd.DataFrame(index=['Arithmetic','Weight','Geometric','Harmonic'])
cols=['SATMTMID','SATVRMID']
for col in cols:
arithmetic=df[col].mean()
weighted=np.average(df[col],weights=df["UGDS"])
geometric=gmean(df[col])
harmonic=hmean(df[col])
df_mean[col]=[arithmetic,weighted,geometric,harmonic]
df_mean['count']=len(df)
return df_mean.astype(int)
import numpy as np
college2.groupby('STABBR').apply(calculate_means)
Out[48]:
SATMTMID SATVRMID count
STABBR
AK Arithmetic 503 555 1
Weight 503 555 1
Geometric 503 555 1
Harmonic 503 555 1
AL Arithmetic 504 508 21
... ... ...
WV Harmonic 480 472 17
WY Arithmetic 540 535 1
Weight 540 535 1
Geometric 540 534 1
Harmonic 540 535 1
[212 rows x 3 columns]
Grouping by continuous variables
import pandas as pd
import numpy as np
flights=pd.read_csv('flights.csv')
flights
Out[5]:
MONTH DAY WEEKDAY AIRLINE ... SCHED_ARR ARR_DELAY DIVERTED CANCELLED
0 1 1 4 WN ... 1905 65.0 0 0
1 1 1 4 UA ... 1333 -13.0 0 0
2 1 1 4 MQ ... 1453 35.0 0 0
3 1 1 4 AA ... 1935 -7.0 0 0
4 1 1 4 WN ... 2225 39.0 0 0
... ... ... ... ... ... ... ... ...
58487 12 31 4 AA ... 1045 -19.0 0 0
58488 12 31 4 F9 ... 2050 4.0 0 0
58489 12 31 4 OO ... 1956 -5.0 0 0
58490 12 31 4 WN ... 855 34.0 0 0
58491 12 31 4 OO ... 1146 -1.0 0 0
[58492 rows x 14 columns]
# if we want to find the distribution of airlines overa range of distance,
# we need to place the values of 'DIST' column into discrete bins
# use the pandas `cut` function to split the data into five bins
bins=[-np.inf,200,500,1000,2000,np.inf]
# the bins are created by a sequence of six numbers defining the edges
# you always need one more edge than the number of bins
# `.cut` function places each value of the `DIST` column into one of five bins
cuts=pd.cut(flights['DIST'],bins=bins)
cuts
Out[13]:
0 (500.0, 1000.0]
1 (1000.0, 2000.0]
2 (500.0, 1000.0]
3 (1000.0, 2000.0]
4 (1000.0, 2000.0]
...
58487 (1000.0, 2000.0]
58488 (200.0, 500.0]
58489 (200.0, 500.0]
58490 (500.0, 1000.0]
58491 (500.0, 1000.0]
Name: DIST, Length: 58492, dtype: category
Categories (5, interval[float64]): [(-inf, 200.0] < (200.0, 500.0] < (500.0, 1000.0] <
(1000.0, 2000.0] < (2000.0, inf]]
# `cuts` variable is now a Series of five ordered categories
# or pass the bins parameter one integer, which automatically creates that number of equal-width bins
# negetive infinity and positive infinity values are availiable in NumPy and ensure that all values get placed in bins
# if you have values that are outside the bin edges, they will be made missing and not placed in a bin
pd.cut(flights['DIST'],bins=5)
Out[15]:
0 (62.565, 954.0]
1 (954.0, 1841.0]
2 (62.565, 954.0]
3 (954.0, 1841.0]
4 (954.0, 1841.0]
...
58487 (954.0, 1841.0]
58488 (62.565, 954.0]
58489 (62.565, 954.0]
58490 (62.565, 954.0]
58491 (62.565, 954.0]
Name: DIST, Length: 58492, dtype: category
Categories (5, interval[float64]): [(62.565, 954.0] < (954.0, 1841.0] < (1841.0, 2728.0] <
(2728.0, 3615.0] < (3615.0, 4502.0]]
# an order categorical Series is created
# to help get an idea of what happen, count the values of each category
cuts.value_counts()
Out[18]:
(500.0, 1000.0] 20659
(200.0, 500.0] 15874
(1000.0, 2000.0] 14186
(2000.0, inf] 4054
(-inf, 200.0] 3719
Name: DIST, dtype: int64
# the `cuts` Series can be now be used to form groups
# pandas allows you to pass many types into the .groupby method
flights.groupby(cuts)['AIRLINE'].value_counts(normalize=True)
Out[21]:
DIST AIRLINE
(-inf, 200.0] OO 0.325625
EV 0.289325
MQ 0.210809
DL 0.086045
AA 0.052165
UA 0.027427
WN 0.008604
(200.0, 500.0] WN 0.193902
DL 0.188736
OO 0.158687
EV 0.156293
MQ 0.100164
AA 0.071375
UA 0.062051
VX 0.028222
US 0.016001
NK 0.011843
B6 0.006867
F9 0.004914
AS 0.000945
(500.0, 1000.0] DL 0.205625
AA 0.143908
WN 0.138196
UA 0.131129
OO 0.106443
EV 0.100683
MQ 0.051213
F9 0.038192
NK 0.029527
US 0.025316
AS 0.023234
VX 0.003582
B6 0.002953
(1000.0, 2000.0] AA 0.263781
UA 0.199070
DL 0.165092
WN 0.159664
OO 0.046454
NK 0.045115
US 0.040462
F9 0.030664
AS 0.015931
EV 0.015579
VX 0.012125
B6 0.003313
MQ 0.002749
(2000.0, inf] UA 0.289097
AA 0.211643
DL 0.171436
B6 0.080414
VX 0.073754
US 0.065121
WN 0.046374
HA 0.027627
NK 0.019240
AS 0.011593
F9 0.003700
Name: AIRLINE, dtype: float64
flights.groupby(cuts)['AIRLINE'].value_counts(normalize=True).round(3).head(3)
Out[22]:
DIST AIRLINE
(-inf, 200.0] OO 0.326
EV 0.289
MQ 0.211
Name: AIRLINE, dtype: float64
# .value_counts method is used to get a sense of its distribution
# the .groupgy method allows you to pass any object to group on
# this means that you are able to form groups from somthing completely unrelated DataFrame
# here we group by the values in the `cuts` variable
###############################
# create informative string labels when using the `cut` fuction
# these labels replace the interval notation found in the index
# chain .unstack method, which transpose the inner index level to columnnames
labels=['Under an Hour','1 Hour','1-2 Hours','2-4 Hours','4+ Hours']
cuts2=pd.cut(flights['DIST'],labels=labels,bins=bins)
flights.groupby(cuts2)['AIRLINE'].value_counts(normalize=True).round(3)
Out[33]:
DIST AIRLINE
Under an Hour OO 0.326
EV 0.289
MQ 0.211
DL 0.086
AA 0.052
UA 0.027
WN 0.009
1 Hour WN 0.194
DL 0.189
OO 0.159
EV 0.156
MQ 0.100
AA 0.071
UA 0.062
VX 0.028
US 0.016
NK 0.012
B6 0.007
F9 0.005
AS 0.001
1-2 Hours DL 0.206
AA 0.144
WN 0.138
UA 0.131
OO 0.106
EV 0.101
MQ 0.051
F9 0.038
NK 0.030
US 0.025
AS 0.023
VX 0.004
B6 0.003
2-4 Hours AA 0.264
UA 0.199
DL 0.165
WN 0.160
OO 0.046
NK 0.045
US 0.040
F9 0.031
AS 0.016
EV 0.016
VX 0.012
B6 0.003
MQ 0.003
4+ Hours UA 0.289
AA 0.212
DL 0.171
B6 0.080
VX 0.074
US 0.065
WN 0.046
HA 0.028
NK 0.019
AS 0.012
F9 0.004
Name: AIRLINE, dtype: float64
flights.groupby(cuts2)['AIRLINE'].value_counts(normalize=True).round(3).unstack()
Out[34]:
AIRLINE AA AS B6 DL ... UA US VX WN
DIST ...
Under an Hour 0.052 NaN NaN 0.086 ... 0.027 NaN NaN 0.009
1 Hour 0.071 0.001 0.007 0.189 ... 0.062 0.016 0.028 0.194
1-2 Hours 0.144 0.023 0.003 0.206 ... 0.131 0.025 0.004 0.138
2-4 Hours 0.264 0.016 0.003 0.165 ... 0.199 0.040 0.012 0.160
4+ Hours 0.212 0.012 0.080 0.171 ... 0.289 0.065 0.074 0.046
[5 rows x 14 columns]
Counting the total number of flights between two city
generic 普通的,一般的
data=pd.DataFrame(index=list('abc'),data=[1,2,3])
data
Out[36]:
0
a 1
b 2
c 3
data.reset_index(drop=True)
Out[37]:
0
0 1
1 2
2 3
# .reset_index(drop=True) 表示删除索引
flights[['ORG_AIR','DEST_AIR']]
Out[41]:
ORG_AIR DEST_AIR
0 LAX SLC
1 DEN IAD
2 DFW VPS
3 DFW DCA
4 LAX MCI
... ...
58487 SFO DFW
58488 LAS SFO
58489 SFO SBA
58490 MSP ATL
58491 SFO BOI
[58492 rows x 2 columns]
# apply the .size method to the `groupby` object, which returns the total number of each group
flights_ct=flights.groupby(['ORG_AIR','DEST_AIR']).size()
flights_ct
Out[45]:
ORG_AIR DEST_AIR
ATL ABE 31
ABQ 16
ABY 19
ACY 6
AEX 40
...
SFO SNA 122
STL 20
SUN 10
TUS 20
XNA 2
Length: 1130, dtype: int64
# select the total number of flights between IAH and ATL in both directions
# one way to select rows from a MultipleIndex is to pass the `.loc` index operator a tuple of the exact level values
flights_ct.loc[[('ATL','IAH'),('IAH','ATL')]]
Out[48]:
ORG_AIR DEST_AIR
ATL IAH 121
IAH ATL 148
dtype: int64
# we can simply sum these two numbers together to find the total flights between two cities
# there is a more efficient and automated solution that can work for all flights
# we would like to have just one label for all flights between Atlanta and Houston
# sort the origin and destination columns for each row alphabetically
# use axis='columns' to do that
# .apply method on a DataFrame, must be passed a function.
# by default, this function is passed each column,
# we can change the direction of computation by using axis='columns' (or axis=1)
# the lambda function has each row of data passed to it impilicity as a Series,
# it returns a Series with sorted airport codes
# call .reset_index so that the columns do not realign
# the .apply function iterates over all rows using the lambda function
f_part3=(flights[['ORG_AIR','DEST_AIR']]
.apply(lambda ser:ser.sort_values().reset_index(drop=True),axis='columns' )
)
f_part3
Out[54]:
0 1
0 LAX SLC
1 DEN IAD
2 DFW VPS
3 DCA DFW
4 LAX MCI
... ...
58487 DFW SFO
58488 LAS SFO
58489 SBA SFO
58490 ATL MSP
58491 BOI SFO
[58492 rows x 2 columns]
# rename them to something more generic
#and again find the total number of flights between cities
rename_dirt={0:'AIR1',1:'AIR2'}
f_part3.rename(columns=rename_dirt).groupby(['AIR1','AIR2']).size()
Out[58]:
AIR1 AIR2
ABE ATL 31
ORD 24
ABI DFW 74
ABQ ATL 16
DEN 46
...
SFO SNA 122
STL 20
SUN 10
TUS 20
XNA 2
Length: 1085, dtype: int64
(f_part3.rename(columns=rename_dirt).groupby(['AIR1','AIR2']).size()
.loc[('ATL','IAH')]
)
Out[59]: 269
least performant operations 最低性能的操作
consecutive 连续不断的