<jupyter_start><jupyter_text>The purpose of this notebook is to clean the excel spreadsheets I inputted manually from my notebooks.<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Mileage<jupyter_code>df = pd.read_excel('mileage.xlsx')
df =  df.iloc[:, :6]
df.head()<jupyter_output><empty_output><jupyter_text>How many days was I on the road for?Some days have NaN mileage, sometimes because I didn't drive that day but sometimes because I didn't write down the mileage.<jupyter_code>driving_days = []
for d, subdf in df.groupby('day'):
    if subdf.dropna().shape[0] > 0:
        driving_days.append(d)
len(driving_days), len(df['day'].unique())
no_mileage_df = df.loc[df['mileage'].isna(), :]
not_driving_days = [i for i in no_mileage_df['day'].tolist() if i not in driving_days]
len(not_driving_days)<jupyter_output><empty_output><jupyter_text>So there are 16 days without any driving. I'm skeptical that the number is this high, but let's see...<jupyter_code>df.query('day == @not_driving_days')<jupyter_output><empty_output><jupyter_text>Cross-checking with blog, here's what I get:Correct no-driving days:2019-04-24, 2019-04-27, 2019-05-03, 2019-05-11, 2019-05-12, 2019-05-13, 2019-05-18, 2019-05-25, 2019-05-26, 2019-05-28, 2019-05-30- 2019-04-27: hiking the grand canyon (technically did drive quite a bit in the park, but we'll count it)- 2019-05-03: correct, hiking bryce- 2019-05-18: drove through badlands, but we'll leave it as a "no driving" day- 2019-05-25, 2019-05-26: memorial day weekend at sheldon wildlife refuge- 2019-05-28: day in susanville- 2019-05-30: day in oaklandIncorrect no-driving days:2019-04-18, 2019-05-04, 2019-05-14, 2019-05-17- 2019-04-18: Ozarks –> Hot Springs, AR –> Ouachita Forest- 2019-05-04: Bryce Canyon –> Grand Staircase Escalante National Monument- 2019-05-14: Grand Lake --> Denver- 2019-05-17: Fort Robinson State Park, NE –> Badlands National Park, SD Now that we have that sorted, let's just create a new column called driving day or not.<jupyter_code>to_remove = [pd.Timestamp('2019-04-18'),
             pd.Timestamp('2019-05-04'),
             pd.Timestamp('2019-05-14'),
             pd.Timestamp('2019-05-17')]
print(len(not_driving_days))
not_driving_days = [i for i in not_driving_days if i not in to_remove]
print(len(not_driving_days))
df['driving_day'] = True
df.loc[df.query('day == @not_driving_days').index, 'driving_day'] = False
df.tail()
df['driving_day'].sum()
df.loc[df.index[-1], 'day'] - df.loc[0, 'day']<jupyter_output><empty_output><jupyter_text>Money<jupyter_code>money = pd.read_excel('money.xlsx')
money = money.iloc[:, :4]
money.head()
money['price'].sum()
money.groupby('category').sum()<jupyter_output><empty_output><jupyter_text>Gas money<jupyter_code>gas = money[money['item'].str.contains('gas')]
gas
gas['price'].sum()
gas.shape
gas['price'].describe()<jupyter_output><empty_output><jupyter_text>Groceries<jupyter_code>groceries = money[money['item'].str.contains('groceries')]
groceries
groceries['price'].sum()
groceries['price'].describe()
print(money[money['item'].str.contains('oil change')].sum())
money[money['item'].str.contains('oil change')]
money.query('category == "car"').sum()
money.query('category == "car"')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1559549192811453, 'fraction_numerical': 0.07554066402680475, 'mean_word_length': 5.743326488706366, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '424292', 'license': 'MIT', 'n_tokens_mistral': 1400, 'n_tokens_neox': 1167, 'n_words': 280, 'path': '_drafts/road-trip/2019-07-02.data_cleaning_and_quick_stats.ipynb', 'repo_name': 'XMarusic/xmarusic.github.io', 'size': '3283'}