Loop for cleaning dataset (R)

user_16028633 0
Viewed 108

I am a beginner data scientist working within an NCAA football program. We record repeated measures data during practice that needs to be organized then analyzed. I have this sample dataset that I am having trouble analyzing due to the variations/typos in the "Activity" column. I need a way to rename/organize the activities in the dataframe.

Raw Dataset looks like this:

Position Activity Max_Velocity (mph)
WR Special Team 1 20
WR special team 1 19
WR Special team 1 18
WR special Team 2 13
WR ST 2 16
WR St 2 19
WR team 1 12
WR team 1 20
WR team 1 23
WR team 2 21
WR team 2 10
WR team 2 3
WR team 3 21
WR team 3 11
WR team 3 16
WR Indy 1 20
WR indy 2 21
WR INDY 3 22

I need it to look like this:

Position Activity Max_Velocity (mph) Activity_Catagory
WR Special Team 1 20 Special Team
WR special team 1 19 Special Team
WR Special team 1 18 Special Team
WR special Team 2 13 Special Team
WR ST 2 16 Special Team
WR St 2 19 Special Team
WR team 1 12 Team
WR team 1 20 Team
WR team 1 23 Team
WR team 2 21 Team
WR team 2 10 Team
WR team 2 3 Team
WR team 3 21 Team
WR team 3 11 Team
WR team 3 16 Team
WR Indy 1 20 Indy
WR indy 2 21 Indy
WR INDY 3 22 Indy

This way I can analyze the average of the dependent variable "Max_velocity" during each type of activity. I would rather not make individual lines of code correcting each typo in the "Activity" column. I think this can be done with loops but I am a beginner and don't know where to start. I would greatly appreciate any help to make this a simple fix.

data-science dataframe r list
2 Answers

try:

In first step, I am using regex to capture the text before appearance of digit. see: https://regex101.com/r/8xTxPl/1 Then I am trying to capitallize the 1st letter of the words. last is to map the short form with the full form.

actv = (df1.Activity.str.extract('([\w\s]+)\s\d', expand=False).str.title())
df1 = df1.assign(new_Actv = np.where(actv=='St', 'Special Team', actv))

df1:

Position Activity Max_Velocity (mph) new_Actv
0 WR Special Team 1 20 Special Team
1 WR special team 1 19 Special Team
2 WR Special team 1 18 Special Team
3 WR special Team 2 13 Special Team
4 WR ST 2 16 Special Team
5 WR St 2 19 Special Team
6 WR team 1 12 Team
7 WR team 1 20 Team
8 WR team 1 23 Team
9 WR team 2 21 Team
10 WR team 2 10 Team
11 WR team 2 3 Team
12 WR team 3 21 Team
13 WR team 3 11 Team
14 WR team 3 16 Team
15 WR Indy 1 20 Indy
16 WR indy 2 21 Indy
17 WR INDY 3 22 Indy
user_66603730

With this solution you can use arbitrary keywords and custom patterns:

categ = (df.Activity.str.extract('(?P<SpecialTeam>special team|st)|(?P<Team>team)|(?P<Indy>indy)', flags=2)
                    .notnull()
        )
df['Activity_Category'] = categ[categ].stack().reset_index()['level_1']

This defines a regex with grouped names of the form '(?P<group1_name>pattern1|pattern2|pattern3)|(?P<group2_name>pattern1|…)'

flags=2 is equivalent to flags=re.IGNORECASE and makes the matches case insensitive

You get a dataframe categ with one column per group and True/False

    SpecialTeam   Team   Indy
0          True  False  False
1          True  False  False
…
6         False   True  False
…
17        False  False   True

It is then stacked to keep only the True values, that are the categories names.

output:

   Position         Activity  Max_Velocity (mph) Activity_Category
0       WR   Special Team 1                   20       SpecialTeam
1       WR   special team 1                   19       SpecialTeam
2       WR   Special team 1                   18       SpecialTeam
3       WR   special Team 2                   13       SpecialTeam
4       WR             ST 2                   16       SpecialTeam
5       WR             St 2                   19       SpecialTeam
6       WR           team 1                   12              Team
7       WR           team 1                   20              Team
8       WR           team 1                   23              Team
9       WR           team 2                   21              Team
10      WR           team 2                   10              Team
11      WR           team 2                    3              Team
12      WR           team 3                   21              Team
13      WR           team 3                   11              Team
14      WR           team 3                   16              Team
15      WR           Indy 1                   20              Indy
16      WR           indy 2                   21              Indy
17      WR           INDY 3                   22              Indy

One limitation is that you cannot use spaces in regex group names, but if you use CamelCase, you can fix it with:

df['Activity_Category'] = df.Activity_Category.apply(lambda s: re.sub('([a-z])([A-Z])','\g<1> \g<2>', s))
user_163434640
Related