<jupyter_start><jupyter_text>Creating first df<jupyter_code>df = pd.read_csv('serverdata/2017-09-07 23:00:022000obikesZH.csv')
del df['Unnamed: 0']
del df['index']
del df['countyId']
del df['helmet']
del df['imei']
def split1(elem):
    elem = elem.replace('POINT (', '')
    elem = elem.replace(')', '')
    return elem.split(' ')[0]
def split2(elem):
    elem = elem.replace('POINT (', '')
    elem = elem.replace(')', '')
    return elem.split(' ')[1]
df['Long'] = df['2017-09-07 23:00:02'].apply(split1)
df['Lat'] = df['2017-09-07 23:00:02'].apply(split2)
df['Timestamp'] = '2017-09-07 23:00:02'
del df['2017-09-07 23:00:02']
df.head(3)<jupyter_output><empty_output><jupyter_text>Creating list of names in file<jupyter_code>file_names = []
for name in glob.glob('serverdata/*'):
    name = name.split('/')[-1]
    file_names.append(name)<jupyter_output><empty_output><jupyter_text>Iterating through contents of all the server files<jupyter_code>for file in file_names[0:]:
    df_new = pd.read_csv('serverdata/'+ file)
    
    del df_new['Unnamed: 0']
    del df_new['index']
    del df_new['countyId']
    del df_new['helmet']
    del df_new['imei']
    
    df_new['Long'] = df_new[file[:19]].apply(split1)
    df_new['Lat'] = df_new[file[:19]].apply(split2)
    df_new['Timestamp'] = file[:19]
    del df_new[file[:19]]
    #print(file[:19])
    frames = [df, df_new]

    df = pd.concat(frames)
df.info()
df = df.drop_duplicates()
df.info()
df.to_csv('timestamps.csv')<jupyter_output><empty_output><jupyter_text>Die beliebtesten Standorte<jupyter_code>df['Long'].value_counts(ascending=False).head(5)
df['Lat'].value_counts(ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>How many bikes are there in total?<jupyter_code>id_list = list(set(list(df['id'])))
len(id_list)<jupyter_output><empty_output><jupyter_text>Counting which bikes have the most location changes?<jupyter_code>id_list = list(set(list(df['id'])))
list_count = []
for elem in id_list:
    count = df[df['id'] == elem]['Long'].value_counts().count()
    mini_dict = {'Count':count,
                 'ID': elem}
    list_count.append(mini_dict)
    df_change_count = pd.DataFrame(list_count)
print("Startzeit:", df['Timestamp'].head(1)[0],
     "Endzeit:",str(df['Timestamp'].tail(1))[:26][7:])
df_change_count.sort_values(by='Count', ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Average amount of time bikes are used in the above defined time period?<jupyter_code>df_change_count['Count'].mean()<jupyter_output><empty_output><jupyter_text>How many bikes never moved in the past three days?<jupyter_code>df_change_count[df_change_count['Count'] == 1].count()<jupyter_output><empty_output><jupyter_text>Percentage of total? (Bikes that haven't moved in the above mentioned time frame)<jupyter_code>total = len(list(set(list(df['id']))))
never_moved = df_change_count[df_change_count['Count'] == 1]['Count'].count()
round(never_moved / total * 100, 1)<jupyter_output><empty_output><jupyter_text>Save list of bikes that have never moved, and created a del list<jupyter_code>Del_list = list(df_change_count[df_change_count['Count'] == 1]['ID'])<jupyter_output><empty_output><jupyter_text>Creating DataFrame of the bikes that have never moved, with location data<jupyter_code>Del_list[0:5]
df_never_moved = df.head(0)
for id_never_moved in Del_list:
    df_new = df[df['id'] == id_never_moved]
    frames = [df_never_moved, df_new]
    df_never_moved = pd.concat(frames)
df_never_moved = df_never_moved.drop_duplicates(subset='id')
df_never_moved.info() #remove Time
df_never_moved.to_csv('never_moved.csv')
df_never_moved['Long'].value_counts().head(5)
df_never_moved['Lat'].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>Delete these bikes from the list<jupyter_code>for bike in Del_list:
    id_list.remove(bike)
len(id_list)<jupyter_output><empty_output><jupyter_text>Create list of bikes that have moved<jupyter_code>rel_list_count = []
for elem in id_list:
    count = df[df['id'] == elem]['Long'].value_counts().count()
    mini_dict = {'Count':count,
                 'ID': elem}
    rel_list_count.append(mini_dict)
df_moved = pd.DataFrame(rel_list_count)
df_moved.sort_values(by='Count', ascending=False).head()<jupyter_output><empty_output><jupyter_text>The bike with the most movements<jupyter_code>df[df['id']==41000425].to_csv('BikeID41000425.csv')<jupyter_output><empty_output><jupyter_text>What is the average distance per bike move?  First the top bike<jupyter_code>top_bike = df[df['id']==41000425]
long_list = list(top_bike['Long'])
last_elem = long_list[-1]
long_list.append(last_elem)
long_list.pop(0)
top_bike['newLong'] = long_list
lat_list = list(top_bike['Lat'])
last_elem = lat_list[-1]
lat_list.append(last_elem)
lat_list.pop(0)
top_bike['newLat'] = lat_list
la1 = list(top_bike['Lat'])
lo1 = list(top_bike['Long'])
la2 = list(top_bike['newLat'])
lo2 = list(top_bike['newLong'])
distance_list = []

for lat1, lon1, lat2, lon2 in zip(la1, lo1, la2, lo2):

    lat1 = radians(float(lat1))
    lon1 = radians(float(lon1))
    lat2 = radians(float(lat2))
    lon2 = radians(float(lon2))

    dlon = lon2 - lon1
    dlat = lat2 - lat1

    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
    c = 2 * atan2(sqrt(a), sqrt(1 - a))

    distance = R * c
    
    distance_list.append(distance)
top_bike['Distance'] = distance_list
final_topbike = top_bike[top_bike['Distance'] != 0.000000]
final_topbike.head(0)<jupyter_output><empty_output><jupyter_text>Now with all the bikes<jupyter_code>final_topbikes = final_topbike.head(0)
moved_bikes = list(df_moved['ID'])
for bike in moved_bikes:
    
    top_bike = df[df['id']== bike]
    
    long_list = list(top_bike['Long'])
    last_elem = long_list[-1]
    long_list.append(last_elem)
    long_list.pop(0)
    top_bike['newLong'] = long_list
    
    lat_list = list(top_bike['Lat'])
    last_elem = lat_list[-1]
    lat_list.append(last_elem)
    lat_list.pop(0)
    top_bike['newLat'] = lat_list
    
    la1 = list(top_bike['Lat'])
    lo1 = list(top_bike['Long'])
    la2 = list(top_bike['newLat'])
    lo2 = list(top_bike['newLong'])
    
    distance_list = []

    for lat1, lon1, lat2, lon2 in zip(la1, lo1, la2, lo2):
        lat1 = radians(float(lat1))
        lon1 = radians(float(lon1))
        lat2 = radians(float(lat2))
        lon2 = radians(float(lon2))

        dlon = lon2 - lon1
        dlat = lat2 - lat1

        a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
        c = 2 * atan2(sqrt(a), sqrt(1 - a))

        distance = R * c
        distance_list.append(distance)
        
    top_bike['Distance'] = distance_list
    final_topbike = top_bike[top_bike['Distance'] != 0.000000]
    
    frames = [final_topbikes, final_topbike]
    final_topbikes = pd.concat(frames)
final_topbikes = final_topbikes.drop_duplicates()<jupyter_output><empty_output><jupyter_text>Mittelere Strecke<jupyter_code>final_topbikes['Distance'].mean()<jupyter_output><empty_output><jupyter_text>Anzahl Bewegungen<jupyter_code>final_topbikes['Distance'].count()
final_topbikes['Distance'].count() / len(list(set(list(final_topbikes['id'])))) / 14
final_topbikes['Distance'].count() * 1.5<jupyter_output><empty_output><jupyter_text>Total Kilometer (Luftlinie)<jupyter_code>round(final_topbikes['Distance'].sum())<jupyter_output><empty_output><jupyter_text>Zu welcher Tageszeit werden die Velos am meisten benutzt?<jupyter_code>def pmam(x):
    x = str(x)
    #x = (':'.join(a+b for a,b in zip(x[::2], x[1::2])))
    if x == 'NaN':
        pass
    try:
        x = str(x[:2] + ':' + x[2:])
        date = dateutil.parser.parse(x)
        return str(date.strftime('%d/%m/%Y %H:%M %p'))
    except:
        return 'NaN'

print(pmam('2017-09-09 07:00:03'))<jupyter_output><empty_output><jupyter_text>Adding on two hour, because time on the server is wrong<jupyter_code>def addtwo(elem):
    mytime = datetime.strptime(elem, "%Y-%m-%d %H:%M:%S")
    mytime += timedelta(hours=2)
    return mytime.strftime("%Y.%m.%d %H:%M:%S")
final_topbikes['Timestamp +2'] = final_topbikes['Timestamp'].apply(addtwo)
final_topbikes['Timestamp index'] = final_topbikes['Timestamp +2'].apply(pmam)
final_topbikes['Timestamp index'] = final_topbikes['Timestamp index'].apply(lambda x: 
                                    dt.datetime.strptime(x,'%d/%m/%Y %H:%M %p'))
final_topbikes.index = final_topbikes['Timestamp index']<jupyter_output><empty_output><jupyter_text>When are the most bikes rented?<jupyter_code>final_topbikes.groupby(by=final_topbikes.index.hour)['id'].count().plot(kind='bar', figsize=(12,6))<jupyter_output><empty_output><jupyter_text>Median Distance according to time of day<jupyter_code>final_topbikes.groupby(by=final_topbikes.index.hour)['Distance'].median().plot(kind='bar', figsize=(12,6))<jupyter_output><empty_output><jupyter_text>What about day of week?<jupyter_code>final_topbikes.groupby(by=final_topbikes.index.weekday)['id'].count().plot(kind='bar', figsize=(6,3))
final_topbikes.groupby(by=final_topbikes.index.weekday)['Distance'].count().plot(kind='bar', figsize=(12,6))
final_topbikes.groupby(by=final_topbikes.index.weekday)['Distance'].median().plot(kind='bar', figsize=(12,6))
len(list(set(list(final_topbikes['id']))))<jupyter_output><empty_output><jupyter_text>Die Längste Strecken<jupyter_code>final_topbikes.sort_values(by='Distance', ascending=False)<jupyter_output><empty_output><jupyter_text>Saving Top Bikes<jupyter_code>final_topbikes.to_csv('final_top_bikes.csv')<jupyter_output><empty_output><jupyter_text>Looking for Info for Carto<jupyter_code>df.info()
#Getting right time
del_id = list(df_never_moved['id'])
df['Timestamp +2'] = df['Timestamp'].apply(addtwo)
#Developing list for the dates
times = df['Timestamp +2'].value_counts().reset_index()
times['date'] = times['index'].apply(lambda x: 
                                    dt.datetime.strptime(x,'%Y.%m.%d %H:%M:%S'))
date_list = list(times.sort_values(by='date')['index'])
final_date_list = []

for elem in date_list:
    if '07:00' in elem:
        final_date_list.append(elem)
    elif '12:00' in elem:
        final_date_list.append(elem)
    elif '15:00' in elem:
        final_date_list.append(elem)
    elif '00:00' in elem:
        final_date_list.append(elem)
    else:
        pass
#Pulling out relevant files to make final df for carto
df_carto = df.head(0)
for elem in final_date_list:
    df_new = df[df['Timestamp +2'] == elem]
    df_carto = pd.concat([df_carto, df_new])
df_carto.head()
del df['Timestamp']
df_carto.to_csv('obike_carto.csv')
len(final_date_list)
36390 / 55<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16278195488721806, 'fraction_numerical': 0.028289473684210528, 'mean_word_length': 5.544280442804428, 'pattern_counts': {'":': 0, '<': 109, '<?xml version=': 0, '>': 109, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '46268', 'license': 'MIT', 'n_tokens_mistral': 4272, 'n_tokens_neox': 4084, 'n_words': 637, 'path': '02 Analysis_of_all_the_files.ipynb', 'repo_name': 'morgulbrut/obike', 'size': '10640'}