<jupyter_start><jupyter_text>Part IInstallation and Data Loading<jupyter_code>import pandas as pd
main_df = pd.read_csv('data/survey_results_public.csv', index_col='ResponseId')
schema_df = pd.read_csv('data/survey_results_schema.csv', index_col='qname')
main_df.head()
main_df.tail()
main_df.shape
main_df.info()
pd.set_option('display.max_columns', 85)
pd.set_option('display.max_rows', 85)
schema_df<jupyter_output><empty_output><jupyter_text>Part IISelecting Rows and Columns<jupyter_code>main_df.columns
main_df.iloc[[0, 1]]
main_df.iloc[[0, 1], 2]
main_df.loc[19]
main_df.loc[1:10, 'YearsCodePro']
main_df['OpSys'].value_counts()
main_df.loc[0:15, 'OpSys':'SOAccount']
main_df['DevType'].value_counts()<jupyter_output><empty_output><jupyter_text>Part IIIIndexes - Set, Reset, Use<jupyter_code>main_df.set_index('OpSys', inplace=True)
main_df.loc['Linux-based']
main_df.reset_index(inplace=True)
schema_df.columns
schema_df.sort_index()
schema_df.loc['Webframe', 'question']
main_df['WebframeHaveWorkedWith'].unique()
schema_df.loc['CompTotal', 'question']
main_df['CompTotal'].mean()
schema_df.loc['EdLevel', 'question']
main_df['EdLevel'].unique()
schema_df.loc['MainBranch', 'question']
main_df['MainBranch'].unique()
schema_df.loc['Employment', 'question']
main_df['Employment'].unique()<jupyter_output><empty_output><jupyter_text>Part IVConditionals - Filtering Rows and Columns<jupyter_code>filt = (main_df['MainBranch'] == 'I am a developer by profession')
main_df.loc[filt]
main_df.loc[filt, 'Employment']
high_salary = (main_df['CompTotal'] > 70000)
countries = ['United States of America', 'Germany', 'Canada', 'India', 'United Kingdom']
filt = main_df['Country'].isin(countries)
main_df.loc[high_salary, ['Country', 'LanguageHaveWorkedWith', 'CompTotal']]
main_df.loc[filt, ['Country', 'LanguageHaveWorkedWith', 'CompTotal']]
using_python = main_df['LanguageHaveWorkedWith'].str.contains('Python', na=False)
main_df.loc[using_python, 'LanguageHaveWorkedWith']<jupyter_output><empty_output><jupyter_text>Part VUpdating Data in DataFrame - Rows and Columns<jupyter_code>main_df.columns
main_df.columns = main_df.columns.str.replace('NEW', 'New')
main_df.columns = main_df.columns.str.replace('SO', 'So')
main_df.columns
# https://stackoverflow.com/questions/1175208/elegant-python-function-to-convert-camelcase-to-snake-case

def camel_to_snake(s):
    return ''.join(['_'+c.lower() if c.isupper() else c for c in s]).lstrip('_')
main_df.columns = [camel_to_snake(column_name) for column_name in main_df.columns]
main_df.columns
main_df['age'] = main_df['age'].map({'Under 18 years old': 'kiddo', '65 years or older': 'old'})
filt = (main_df['age'] == 'kiddo') | (main_df['age'] == 'old')
main_df.loc[filt, 'age']
main_df['age'] = main_df['age'].map({'kiddo': 'Under 18 years old', 'old': 'Under 18 years old'})
main_df['age']
main_df['age'].replace({None: ' nothing left here ...'}, inplace=True)
main_df['age']
main_df.rename(columns={'CompTotal': 'SalaryUSD'}, inplace=True)
main_df['SalaryUSD']<jupyter_output><empty_output><jupyter_text>Part VIAdding/Removing Data in DataFrame - Rows and Columns df.drop["col_name"] - remove columndf.drop[index=n] - drop rowdf.append - add row (index should be set first)<jupyter_code>filt = main_df['OpSys'] == 'Windows'
main_df.drop(index=main_df[filt].index)['OpSys']<jupyter_output><empty_output><jupyter_text>Part VIISorting Data<jupyter_code>main_df.sort_values(by='Country')['Country']
main_df.sort_values(by=['Country', 'CompTotal'])
main_df.sort_values(by=['Country', 'CompTotal'], ascending=[True, False], inplace=True)
main_df[['Country', 'CompTotal']].head(50)
main_df['CompTotal'].nlargest(10)<jupyter_output><empty_output><jupyter_text>Part VIIIAggregating and Grouping Data<jupyter_code>main_df.describe()
filt = (main_df['Country'] == 'Poland')
main_df.loc[filt, 'CompTotal'].describe()
filt = (main_df['Country'] == 'Poland')
main_df.loc[filt, 'Age'].unique()
main_df['Age'].value_counts()
main_df['Gender'].value_counts()
main_df['LanguageHaveWorkedWith'].value_counts()
main_df['DatabaseHaveWorkedWith'].value_counts()
main_df['ToolsTechHaveWorkedWith'].value_counts(normalize=True)
main_df['WebframeHaveWorkedWith'].value_counts(normalize=True)
main_df['DevType'].value_counts(normalize=True)
main_df['PlatformHaveWorkedWith'].value_counts()
main_df['EdLevel'].value_counts(normalize=True)
main_df['Country'].value_counts()
country_grp = main_df.groupby(['Country'])
country_grp.get_group('United States of America')
country_grp['MainBranch'].value_counts().head(50)
filt = main_df['Country'] == 'India'
main_df.loc[filt]['LanguageHaveWorkedWith'].str.contains('Python').sum()
country_grp['LanguageHaveWorkedWith'].apply(lambda x: x.str.contains('Python').sum())
country_respondents = main_df['Country'].value_counts()
country_respondents
country_uses_python = country_grp['LanguageHaveWorkedWith'].apply(lambda x: x.str.contains('Python').sum())
country_uses_python
python_df = pd.concat([country_respondents, country_uses_python], axis='columns', sort=False)
python_df.rename(columns={'Country': 'NumRespondents', 'LanguageHaveWorkedWith': 'NumKnowsPython'}, inplace=True)
python_df['PctKnowsPython'] = (python_df['NumKnowsPython']/python_df['NumRespondents'])*100
python_df.sort_values(by=['PctKnowsPython', 'NumRespondents'], ascending=[False, False]).head(50)
filt =(python_df['NumRespondents'] >= 100)
big_python_df = python_df.loc[filt] 
big_python_df.sort_values(by=['PctKnowsPython', 'NumRespondents'], ascending=[False, False]).head(50)<jupyter_output><empty_output><jupyter_text>Part IX Cleaning Data<jupyter_code>main_df.dropna()
main_df.dropna(axis='index', how='any', subset=['EdLevel'])
main_df['YearsCode'].head(10)
main_df['YearsCode'].unique()
main_df['YearsCode'].replace('Less than 1 year', 0, inplace=True)
main_df['YearsCode'].replace('More than 50 years', 51, inplace=True)
main_df['YearsCode'].unique()
main_df['YearsCode'] = main_df['YearsCode'].astype(float)
main_df['YearsCode'].mean()
main_df['YearsCode'].median()<jupyter_output><empty_output><jupyter_text>_Part X intentionally ommited - is in seperate notebook, due to different data beind used_  Part XIReading/Writting to Different Data Sources - Excel, JSON, SQL, etc.<jupyter_code>filt = (main_df['Country'] == 'India')
india_df = main_df.loc[filt]
india_df.head()
india_df.to_csv('data/india.csv')
india_df.to_csv('data/india.tsv', sep='\t')
new_india_df = pd.read_csv('data/india.tsv', sep='\t')
new_india_df.head()
india_df.to_excel('data/india.xlsx')
posts_df = pd.read_json('data/posts.json')
posts_df.head()
from sqlalchemy import create_engine
import psycopg2
engine = create_engine('postgresql://dbrole:dbpass@localhost:5432/sampledb')
india_df.to_sql('new_table', engine, if_exists='replace')
sql_df = pd.read_sql('new_table', engine, index_col = 'ResponseId')
sql_df.head()
sql_df = pd.read_sql_query('SELECT * FROM new_table ', engine, index_col = 'ResponseId')
sql_df.head()
filt = (main_df['Country'] == 'Poland')
poland_df = main_df.loc[filt]
poland_df.to_sql('poland_table', engine, if_exists='replace')
main_df.to_sql('so_annual_survey_table', engine, if_exists='replace')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17152013376062422, 'fraction_numerical': 0.009196042914866935, 'mean_word_length': 10.180685358255452, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 43, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '326065', 'license': 'MIT', 'n_tokens_mistral': 2746, 'n_tokens_neox': 2659, 'n_words': 385, 'path': 'bamboo/so_annual_survey.ipynb', 'repo_name': 'MKaczkow/bamboo', 'size': '7177'}