<jupyter_start><jupyter_text>Pandas Tips for Getting StartedIn this notebook we will load some sample data and run through some examples to help you get started using Python Pandas to work with your spreadsheets.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Load Sample DataLoad the sample report from the GitHub repo.  The following command will load the data from the local folder as a Pandas DataFrame called 'df'.<jupyter_code>df = pd.read_csv("cpd_sample_report.csv")
df<jupyter_output><empty_output><jupyter_text>Apply TransformationsWe can transform the data that is stored in the DataFrame in numerous ways.  First we will start by transforming the "Region" column from all caps to only capitalizing the first letter.  We will do this by applying a lambda function to the column.<jupyter_code>df['Region'].apply(lambda x: x.capitalize())<jupyter_output><empty_output><jupyter_text>Notice that the function only returns the changed data, it doesn't change the DataFrame itself.  In order to change the DataFrame, we have to replace the contents of df['Region'] with the output of the function.<jupyter_code>df['Region'] = df['Region'].apply(lambda x: x.capitalize())
df<jupyter_output><empty_output><jupyter_text>Next we are going to transform the Bookings data.  We will assume that there is a sales tax of 7% and we want to create a new column that show the after tax bookings.  Here is one of the best parts of Pandas, we can apply mathmatical computations to entire columns at a time.<jupyter_code>df['Bookings'] * (1-0.07)<jupyter_output><empty_output><jupyter_text>Again, running the function didn't change anything in the DataFrame.  For that we need to put the output data into the DataFrame.  This time we will create a whole new column.<jupyter_code>df['After Tax Bookings'] = df['Bookings'] * (1-0.07)
df<jupyter_output><empty_output><jupyter_text>Now lets assume that we want to round the output to the nearest penny for display.  For this we will apply a lambda function as we did previously to the Region column.<jupyter_code>df['After Tax Bookings'] = df['After Tax Bookings'].apply(lambda x: round(x, 2))
df<jupyter_output><empty_output><jupyter_text>VLookup Mapping by ColumnOne of the most common tasks you'll want to perform in Pandas is mapping column values to a lookup table similar to Excel's VLookup function.  Here, we will assume that each of the three different regions in our data set has a different sales tax that we want to apply.  First load the lookup table from the local file.<jupyter_code>df_lookup = pd.read_csv("cdp_sample_lookup_table.csv")
df_lookup<jupyter_output><empty_output><jupyter_text>Now you can merge the two Data Frames on their common column, Region.<jupyter_code>df = df.merge(df_lookup)
df<jupyter_output><empty_output><jupyter_text>Performing Math on ColumnsNow that we have the per region tax rate in the Data Frame as a column, we can use it to perform a column wise calculation.<jupyter_code>df['After Tax Bookings'] = df['Bookings'] * (1 - df['Tax Rate'])<jupyter_output><empty_output><jupyter_text>Again, we will round the output to the nearest penny.<jupyter_code>df['After Tax Bookings'] = df['After Tax Bookings'].apply(lambda x: round(x, 2))
df<jupyter_output><empty_output><jupyter_text>Here we have the tax calculated on a per region basis.    Drop a ColumnNow that we are done with the tax calculation, lets get rid of 'Tax Rate' column, using the drop function.<jupyter_code>df.drop(columns=['Tax Rate'], inplace=True)
df<jupyter_output><empty_output><jupyter_text>Filter Rows by ValueNow lets try to filter the Data Frame based on the column values.  We will assume that you want to create a new Data Frame with just records from the Enterprise Segment, in the years 2019 or 2020 and the months January through May.<jupyter_code>filter1 = df['Segment'] == 'Enterprise'
filter2 = df['Year'].isin([2019, 2020])
filter3 = df['Month'] <= 5

df2 = df.loc[filter1 & filter2 & filter3, :]
df2<jupyter_output><empty_output><jupyter_text>Create a Pivot TableNow we will create a pivot table that aggregates the after tax bookings by region and year.<jupyter_code>my_pivot = pd.pivot_table(df2, index='Region', columns='Year', values='After Tax Bookings', aggfunc='sum')
my_pivot<jupyter_output><empty_output><jupyter_text>Next we will turn the pivot table into a normal Data Frame that can be uploaded back to one of your cloud data providers via CloudPandas.<jupyter_code>my_pivot2 = my_pivot.reset_index()
my_pivot2<jupyter_output><empty_output><jupyter_text>Upload Data via CloudPandasFinally, you can upload your new DataFrame to your cloud data provider using CloudPandas.<jupyter_code>import cloudpandas

user_token = "aa<PASSWORD>"
client = cloudpandas.Client(user_token)
client.sheets.create(my_pivot2, "Google", "New Sample Pivot")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08032046014790468, 'fraction_numerical': 0.008422350041084634, 'mean_word_length': 6.234769687964339, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 66, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '382467', 'license': 'MIT', 'n_tokens_mistral': 1500, 'n_tokens_neox': 1453, 'n_words': 578, 'path': 'example/Pandas Tips.ipynb', 'repo_name': 'jefalexa/cloudpandas', 'size': '4871'}