<jupyter_start><jupyter_text>Numeric data or ... ?In this exercise, and throughout this chapter, you'll be working with bicycle ride sharing data in San Francisco called `ride_sharing`. It contains information on the start and end stations, the trip duration, and some user information for a bike sharing service.The `user_type` column contains information on whether a user is taking a free ride and takes on the following values:- `1` for free riders.- `2` for pay per ride.- `3` for monthly subscribers.In this instance, you will print the information of `ride_sharing` using `.info()` and see a firsthand example of how an incorrect data type can flaw your analysis of the dataset.Instructions- Print the information of `ride_sharing`.- Use `.describe()` to print the summary statistics of the `user_type` column from `ride_sharing`.- By looking at the summary statistics - they don't really seem to offer much description on how users are distributed along their purchase type, why do you think that is?- Convert `user_type` into categorical by assigning it the `'category'` data type and store it in the `user_type_cat` column.- Make sure you converted `user_type_cat` correctly by using an `assert` statement.<jupyter_code># Import packages
import pandas as pd

# Import dataframe
ride_sharing = pd.read_csv('ride_sharing.csv', index_col=0)

# Print the information of ride_sharing
print(ride_sharing.info(), '\n')

# Print summary statistics of user_type column
print(ride_sharing['user_type'].describe())
# Convert user_type from integer to category
ride_sharing['user_type_cat'] = ride_sharing['user_type'].astype('category')

# Write an assert statement confirming the change
assert ride_sharing['user_type_cat'].dtype == 'category'

# Print new summary statistics
print(ride_sharing.info())<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 25760 entries, 0 to 25759
Data columns (total 12 columns):
 #   Column           Non-Null Count  Dtype   
---  ------           --------------  -----   
 0   duration         25760 non-null  object  
 1   station_A_id     25760 non-null  int64   
 2   station_A_name   25760 non-null  object  
 3   station_B_id     25760 non-null  int64   
 4   station_B_name   25760 non-null  object  
 5   ride_id          25760 non-null  int64   
 6   user_type        25760 non-null  int64   
 7   user_birth_year  25760 non-null  int64   
 8   user_gender      25760 non-null  object  
 9   tire_sizes       25760 non-null  float64 
 10  ride_date        25760 non-null  object  
 11  user_type_cat    25760 non-null  category
dtypes: category(1), float64(1), int64(5), object(5)
memory usage: 2.4+ MB
None<jupyter_text>Summing strings and concatenating numbersIn the previous exercise, you were able to identify that `category` is the correct data type for `user_type` and convert it in order to extract relevant statistical summaries that shed light on the distribution of `user_type`.Another common data type problem is importing what should be numerical values as strings, as mathematical operations such as summing and multiplication lead to string concatenation, not numerical outputs.In this exercise, you'll be converting the string column `duration` to the type `int`. Before that however, you will need to make sure to strip `"minutes"` from the column in order to make sure pandas reads it as numerical. The pandas package has been imported as pd.Instructions- Use the `.strip()` method to strip `duration` of `"minutes"` and store it in the `duration_trim` column.- Convert `duration_trim` to `int` and store it in the `duration_time` column.- Write an `assert` statement that checks if `duration_time`'s data type is now an `int`.- Print the average ride duration.<jupyter_code># Strip duration of minutes
ride_sharing['duration_trim'] = ride_sharing['duration'].str.strip('minutes')

# Convert duration to integer
ride_sharing['duration_time'] = ride_sharing['duration_trim'].astype('int')

# Write an assert statement making sure of conversion
assert ride_sharing['duration_time'].dtype == 'int'

# Print formed columns and calculate average ride duration 
print(ride_sharing[['duration', 'duration_trim', 'duration_time']])
print(ride_sharing['duration_time'].mean())<jupyter_output>duration duration_trim  duration_time
0      12 minutes           12              12
1      24 minutes           24              24
2       8 minutes            8               8
3       4 minutes            4               4
4      11 minutes           11              11
...           ...           ...            ...
25755  11 minutes           11              11
25756  10 minutes           10              10
25757  14 minutes           14              14
25758  14 minutes           14              14
25759  29 minutes           29              29

[25760 rows x 3 columns]
11.389052795031056<jupyter_text>Tire size constraintsIn this lesson, you're going to build on top of the work you've been doing with the `ride_sharing` DataFrame. You'll be working with the `tire_sizes` column which contains data on each bike's tire size.Bicycle tire sizes could be either 26″, 27″ or 29″ and are here correctly stored as a categorical value. In an effort to cut maintenance costs, the ride sharing provider decided to set the maximum tire size to be 27″.In this exercise, you will make sure the `tire_sizes` column has the correct range by first converting it to an integer, then setting and testing the new upper limit of 27″ for tire sizes.Instructions- Convert the `tire_sizes` column from `category` to `'int'`.- Use `.loc[]` to set all values of tire_sizes above 27 to 27.- Reconvert back `tire_sizes` to `'category'` from int.- Print the description of the `tire_sizes`.<jupyter_code># Convert tire_sizes to integer
ride_sharing['tire_sizes'] = ride_sharing['tire_sizes'].astype('int')

# Set all values above 27 to 27
ride_sharing.loc[ride_sharing['tire_sizes'] > 27, 'tire_sizes'] = 27

# Reconvert tire_sizes back to categorical
ride_sharing['tire_sizes'] = ride_sharing['tire_sizes'].astype('category')

# Print tire size description
print(ride_sharing['tire_sizes'].describe())<jupyter_output>count     25760
unique        2
top          27
freq      13274
Name: tire_sizes, dtype: int64<jupyter_text>Back to the futureA new update to the data pipeline feeding into the `ride_sharing` DataFrame has been updated to register each ride's date. This information is stored in the `ride_date column` of the type `object`, which represents strings in `pandas`.A bug was discovered which was relaying rides taken today as taken next year. To fix this, you will find all instances of the `ride_date` column that occur anytime in the future, and set the maximum possible value of this column to today's date. Before doing so, you would need to convert `ride_date` to a `datetime` object.The `datetime` package has been imported as `dt`, alongside all the packages you've been using till now.Instructions- Convert `ride_date` to a `datetime` object and store it in `ride_dt` column using `to_datetime()`.- Create the variable `today`, which stores today's date by using the `dt.date.today()` function.- For all instances of `ride_dt` in the future, set them to today's date.- Print the maximum date in the `ride_dt` column.<jupyter_code># Import datetime
import datetime as dt

# Convert ride_date to datetime
ride_sharing['ride_dt'] = pd.to_datetime(ride_sharing['ride_date'])

# Save today's date
today = dt.date.today()

#######ERRO####### Set all in the future to today's date
#ride_sharing.loc[ride_sharing['ride_dt'] > today, 'ride_dt'] = today

# Print maximum of ride_dt column
print(ride_sharing['ride_dt'].max())<jupyter_output>2022-04-25 00:00:00<jupyter_text>How big is your subset?You have the following `loans` DataFrame which contains loan and credit score data for consumers, and some metadata such as their first and last names. You want to find both complete and incomplete duplicates using `.duplicated()`.```first_name        last_name        credit_score        has_loan---------------------------------------------------------------<NAME>      600                 1Hadrien           Lacroix          450                 0```Choose the **correct** usage of `.duplicated()` below: `loans.duplicated(subset = ['first_name', 'last_name'], keep = False)` because subsetting on consumer metadata and not discarding any duplicate returns all duplicated rows.  Finding duplicatesA new update to the data pipeline feeding into `ride_sharing` has added the `ride_id` column, which represents a unique identifier for each ride.The update however coincided with radically shorter average ride duration times and irregular user birth dates set in the future. Most importantly, the number of rides taken has increased by 20% overnight, leading you to think there might be both complete and incomplete duplicates in the `ride_sharing` DataFrame.In this exercise, you will confirm this suspicion by finding those duplicates. A sample of `ride_sharing` is in your environment, as well as all the packages you've been working with thus far.Instructions- Find duplicated rows of `ride_id` in the `ride_sharing` DataFrame while setting `keep` to `False`.- Subset `ride_sharing` on `duplicates` and sort by `ride_id` and assign the results to `duplicated_rides`.- Print the `ride_id`, `duration` and `user_birth_year` columns of `duplicated_rides` in that order.<jupyter_code># Find duplicates
duplicates = ride_sharing.duplicated(subset='ride_id', keep=False)

# Sort your duplicated rides
duplicated_rides = ride_sharing[duplicates].sort_values('ride_id')

# Print relevant columns
print(duplicated_rides[['ride_id','duration','user_birth_year']])<jupyter_output>ride_id    duration  user_birth_year
3638        11  12 minutes             1988
6088        11   5 minutes             1985
10857       11   4 minutes             1987
10045       27  13 minutes             1989
16104       27  10 minutes             1970
...        ...         ...              ...
8812      6638  10 minutes             1986
6815      6638   5 minutes             1995
8456      6638   7 minutes             1983
8300      6638   6 minutes             1962
8380      6638   8 minutes             1984

[25717 rows x 3 columns]<jupyter_text>Treating duplicatesIn the last exercise, you were able to verify that the new update feeding into `ride_sharing contains` a bug generating both complete and incomplete duplicated rows for some values of the `ride_id` column, with occasional discrepant values for the `user_birth_year` and `duration` columns.In this exercise, you will be treating those duplicated rows by first dropping complete duplicates, and then merging the incomplete duplicate rows into one while keeping the average `duration`, and the minimum `user_birth_year` for each set of incomplete duplicate rows.Instructions- Drop complete duplicates in `ride_sharing` and store the results in `ride_dup`.- Create the statistics dictionary which holds **minimum** aggregation for `user_birth_year` and **mean** aggregation for `duration`.- Drop incomplete duplicates by grouping by `ride_id` and applying the aggregation in `statistics`.- Find duplicates again and run the `assert` statement to verify de-duplication.<jupyter_code># Drop complete duplicates from ride_sharing
ride_dup = ride_sharing.drop_duplicates()

# Create statistics dictionary for aggregation function
statistics = {'user_birth_year': 'min', 'duration': 'mean'}

# Group by ride_id and compute new statistics
ride_unique = ride_dup.groupby('ride_id').agg(statistics).reset_index()

# Find duplicated values again
duplicates = ride_unique.duplicated(subset='ride_id', keep=False)
duplicated_rides = ride_unique[duplicates == True]

# Assert duplicates are processed
assert duplicated_rides.shape[0] == 0<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07870760924582419, 'fraction_numerical': 0.03526235869748608, 'mean_word_length': 3.6691610870421427, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '487435', 'license': 'MIT', 'n_tokens_mistral': 3482, 'n_tokens_neox': 3079, 'n_words': 1405, 'path': 'cleaning_data_in_python/1_common_data_problems.ipynb', 'repo_name': 'vhsenna/datacamp-courses', 'size': '11877'}