<jupyter_start><jupyter_text>Read data with Pandas<jupyter_code>import pandas as pd
df = pd.read_csv('MonthlySales.csv')
df.head(5)
import json
from pandas.io.json import json_normalize

with open('monthlySalesbyCategoryMultiple.json') as json_data:
    d = json.load(json_data)
df = json_normalize(d['contents'], 'monthlySales', ['category', 'region'])
df
# reading parquet into pandas dataframe
import pyarrow.parquet as pq
table = pq.read_table('MonthlyProductSales.parquet')
table.to_pandas().head()<jupyter_output><empty_output><jupyter_text>__Read data from html__- You need to install html5lib`pip install html5lib`- Reading data from a html site<jupyter_code>df = pd.read_html('https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations')
# get part that has the states and abbreviatioins
df_usa = df[0]
# remove unnecessary rows and columns
final_df = df_usa.drop(df_usa.index[range(0,11)]).drop(df_usa.columns[range(10,15)], axis=1)
final_df.head()
# rename columns
final_df.rename(columns={0:'Region Name', 1:'Region Status', 2:'ISO', 3:'ANSI_Letter', 4:'ANSI_Code',
                         5:'USPS', 6:'USCG', 7:'GPO', 8:'AP', 9:'Other Abbreviations'}, inplace=True)
# reset index of rows
final_df.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Inspect DataFrames with Pandas<jupyter_code>df = pd.read_csv('MonthlyProductSales.csv', engine='python')
# show first 10 rows
df.head(10) 
# show last 10 rows
df.tail(10)
# show summary stats of the sales column
df.describe()
df.info()
# return as a series
s = df['Product Name']

# get count of values
s.value_counts(dropna=False)
s.unique()<jupyter_output><empty_output><jupyter_text>Aggregate data with Pandas<jupyter_code># yearly sales summary
df.groupby(df['Month of Order Date'].str[:4].rename(columns={'Month of Order Date': 'Year of Order'})).describe().reset_index()
# yearly product sales totals
df_export = df.groupby([df['Month of Order Date'].str[:4], 'Product Name']).sum().reset_index()
df_export.rename(columns={'Month of Order Date':'Year of Order'})
# overall product sales totals
df.groupby('Product Name').sum().reset_index()<jupyter_output><empty_output><jupyter_text>Export data with Pandas<jupyter_code>df = pd.read_csv('MonthlyProductSales.csv', encoding='cp1252')
# yearly product sales totals
df_export = df.groupby([df['Month of Order Date'].str[:4], 'Product Name']).sum().reset_index()
df_export.head()
df_export = df_export.rename(columns={'Month of Order Date': 'Year of Order'})
df_export.head()
# export to csv, check csv when finished
df_export.to_csv('YearlyProductSalesTotals.csv', header=True, index=False, encoding='utf-8')
df = pd.read_csv('YearlyProductSalesTotals.csv')
df.tail()
# export to json
df_export.to_json('YearlyProductSalesTotals.json', orient='records')
# export to excel
df_export.to_excel('YearlyProductSalesTotals.xlsx', header=True, index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14275909402882636, 'fraction_numerical': 0.013040494166094716, 'mean_word_length': 7.188202247191011, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '295717', 'license': 'MIT', 'n_tokens_mistral': 1048, 'n_tokens_neox': 997, 'n_words': 224, 'path': '2_Exploring Data.ipynb', 'repo_name': 'augustine0890/Python-for-Data-Science-Tips-Tricks-Techniques', 'size': '2914'}