<jupyter_start><jupyter_text>**We use a dataset which includes salaries and compensation for all employees of the County of San Mateo. Data can be found on https://data.smcgov.org/.**<jupyter_code>''' Load and read data '''

df = pd.read_json("https://data.smcgov.org/resource/e4sp-qk5h.json")
df.head(5)<jupyter_output><empty_output><jupyter_text>**We can also load data as a csv file.**<jupyter_code>df1 = pd.read_csv("https://data.smcgov.org/resource/e4sp-qk5h.csv")
df1.head(5)<jupyter_output><empty_output><jupyter_text>**.describe( ) returns statistics such as mean and standard deviation of the numerical columns in a dataset.**<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>**We can also find the statistics of non-numeric columns if we include all columns.**<jupyter_code>df.describe(datetime_is_numeric=True, include="all")
df.plot(subplots=True, figsize=(8,20), color="blue", grid=True)
plt.legend(fontsize=10)
plt.tight_layout()
plt.show()
df.hist(figsize=(15,10), color='blue', bins=100)
plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>**.drop( ) deletes rows or columns in a dataframe.**<jupyter_code>df.drop("other_employer_cost", axis=1)
df.describe(datetime_is_numeric=rue, include="all")<jupyter_output><empty_output><jupyter_text>**If we drop a column, it doesn't change the original data frame by default. If we want to change the original data frame we need to use inplace=True**<jupyter_code>df.drop("other_employer_cost", axis=1, inplace=True)
df
df.dtypes
print(' Average salary is', df.regular_pay.mean())
print('total number of employees is',df.name.count())<jupyter_output>total number of employees is 1000<jupyter_text>**We can also count the unique values of a columns.**<jupyter_code>print('Departments: \n', df.department_name.value_counts())<jupyter_output>Departments: 
 Health System                           503
Sheriff's Office                        175
District Attorney's Office               67
Information Services Department          53
County Counsel                           29
Department of Public Works               28
Human Services Agency                    20
Human Resources Department               17
Controller's Office                      16
County Manager/Clerk of the Board        12
Assessor-County Clerk-Recorder           10
Planning and Building                    10
Probation Department                      9
Board of Supervisors                      7
County Library                            7
Retirement Office                         7
Public Safety Communications              5
Department of Child Support Services      5
Parks Department                          4
Office of Sustainability                  4
Department of Housing                     3
Treasurer - Tax Collector                 3
Coroner's Office [...]<jupyter_text>**We can do the same by using a function and then apply it to the dataframe**<jupyter_code>def map_department(x):
    if x == "Health System":
        return 1

df['department_mapped_value'] = df.department_name.apply(map_department)

print('Total number of employees working for health system is',
      df.department_mapped_value.value_counts()[1])<jupyter_output>Total number of employees working for health system is 503<jupyter_text>**We can also find the total number of employees working for departments using groupby**<jupyter_code>df['id'] = [i for i in range(1000)]
df.groupby('department_name', sort=False)["id"].count().reset_index(name ='Total number of employees')
df.groupby('job_title', sort=False)["id"].count().reset_index(name ='Total number of employees')<jupyter_output><empty_output><jupyter_text>**Next we show how to join two data frame in pandas.**<jupyter_code>ind = (0,1,2,3)
first_name = ('Danny', 'Daniel', 'Don', 'Ali')
last_name = ('Reed', 'Coto', 'Gomez', 'Sori')
job_title = ('data analyst', 'software developer', 'computer scientist', 'programmer')
language = ('Python', 'C++', 'Java', 'Python')

df1 = pd.DataFrame(list(zip(first_name, last_name, job_title, language)), 
                  columns =['first_name', 'last_name', 'job_title', 'language'],
                  index=ind) 


first_name = ('Danny', 'Daniel', 'Don', 'Moh')
last_name = ('Reed', 'Coto', 'Gomez', 'Danesh')
salary = (150000, 160000, 120000, 120000 )
age = (24, 32, 29, 33)
df2 = pd.DataFrame(list(zip(first_name, last_name, salary, age)), 
                  columns =['first_name', 'last_name', 'salary', 'age'],
                  index=ind) 
df1
df2<jupyter_output><empty_output><jupyter_text>**Inner join: An inner join combines two DataFrames based on a join key and returns a new DataFrame that contains only those rows that have matching values in both of the original DataFrames.**<jupyter_code>inner_join = pd.merge(left=df1, right=df2)
inner_join<jupyter_output><empty_output><jupyter_text>**Left join: Like an inner join, a left join uses join keys to combine two DataFrames. But it also returns all of the rows from the left DataFrame, even those rows whose join key(s) do not have values in the right DataFrame**.<jupyter_code>left_join = pd.merge(left=df1, right=df2, how='left')
left_join
right_join = pd.merge(left=df1, right=df2, how='right')
right_join<jupyter_output><empty_output><jupyter_text>**We can also concatanate two data frames:**<jupyter_code>df3 = pd.concat( [df1, df2],axis=0,ignore_index=True)
df3
 df3.groupby('language', sort=False)['job_title'].count().reset_index(name='total')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11789282470481381, 'fraction_numerical': 0.0223433242506812, 'mean_word_length': 4.056014692378329, 'pattern_counts': {'":': 1, '<': 50, '<?xml version=': 0, '>': 50, 'https://': 3, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '488830', 'license': 'MIT', 'n_tokens_mistral': 1835, 'n_tokens_neox': 1685, 'n_words': 484, 'path': 'Pandas_Python/pandas - data analysis.ipynb', 'repo_name': 'mdaneshv/DataScience', 'size': '5505'}