<jupyter_start><jupyter_text>Instructions: 1. Make sure to organize the CSV files in folders named by year2. Select the year to process in the next field. 3. Then just let it run!<jupyter_code># Set year to concatenate

year_to_process = 2020
# Concatenate csv files

tic = timeit.default_timer()                            # Monitor performance

path = f'/Volumes/GoogleDrive/My Drive/Humanate/Tec Bootcamp/Tableau_Citibike/0. raw_data/{year_to_process}'
all_files = glob.glob(path + "/*.csv")

csv_file_list = []

for filename in all_files:
    try:
        df = pd.read_csv(filename, index_col=None, header=0)
        csv_file_list.append(df)
#         print (f'File {i} added')
    except:
        print(f'Concatenate error {filename}, could not be added')
        pass

citibike_rides_df = pd.concat(csv_file_list, axis=0, ignore_index=True)

toc = timeit.default_timer()                            # Monitor performance
print(f'Time (in seconds) to extract and concatenate excel files: {round(toc - tic, 2)}')

citibike_rides_df
# Clean up data and add an Age column

tic = timeit.default_timer()                            # Monitor performance

citibike_rides_df['starttime'] = pd.to_datetime(citibike_rides_df['starttime'])          # Convert to datetime values  
citibike_rides_df['stoptime'] = pd.to_datetime(citibike_rides_df['stoptime'])

citibike_rides_df['birth year'] = pd.to_numeric(citibike_rides_df['birth year'], errors='coerce')  # Convert to float

citibike_rides_df['Age'] = pd.DatetimeIndex(citibike_rides_df['starttime']).year - citibike_rides_df['birth year'] # Calculate age

citibike_rides_df['gender'].replace(to_replace=1, value='Male', inplace=True )           # Replace gender values
citibike_rides_df['gender'].replace(to_replace=2, value='Female', inplace=True )
citibike_rides_df['gender'].replace(to_replace=0, value='Unknown', inplace=True )

citibike_rides_df = citibike_rides_df.rename(columns={'tripduration': 'Duration (Sec)',  # Polish up column names
                                                      'starttime' : 'Start Time & Date',
                                                      'stoptime' : 'End Time & Date',
                                                      'start station id': 'Start Station ID',
                                                      'start station name': 'Start Station Name',
                                                      'start station latitude': 'Start Lat',
                                                      'start station longitude': 'Start Lon',
                                                      'end station id': 'End Station ID',
                                                      'end station name': 'End Station Name',
                                                      'end station latitude': 'End Lat',
                                                      'end station longitude': 'End Lon',
                                                      'bikeid': 'Bike ID',
                                                      'usertype': 'User Type', 	
                                                      'birth year': 'Birth Year',
                                                      'gender':'Gender'})

toc = timeit.default_timer()                            # Monitor performance
print(f'Time (in seconds) to process data: {round(toc - tic, 2)}')

for column in citibike_rides_df.columns:
    print(f'{column}, type: {type(citibike_rides_df.loc[0, column])}')

citibike_rides_df.head()

# Export to clean CSV file

tic = timeit.default_timer()                            # Monitor performance

citibike_rides_df.to_csv(f"../2_clean_datasets_by_year/rides_{year_to_process}.csv", index=False)

toc = timeit.default_timer()                            # Monitor performance
print(f'Time (in seconds) to export CSV file: {round(toc - tic, 2)}')

citibike_rides_df.head()<jupyter_output>Time (in seconds) to export CSV file: 423.8
{'fraction_non_alphanumeric': 0.10083607803394984, 'fraction_numerical': 0.005573853559665568, 'mean_word_length': 1.8650217706821481, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 28, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '482470', 'license': 'MIT', 'n_tokens_mistral': 1088, 'n_tokens_neox': 1056, 'n_words': 267, 'path': 'data_processing_in_pandas/1_csv-file-concatenation 2013-2020.ipynb', 'repo_name': 'kennethcandersen/Citibike-Strategic-Analysis-Tableau', 'size': '3947'}