<jupyter_start><jupyter_text>Merging DataFrame  Querying 1D series Data Structure and 2D (DataFrame) Data Structure  using Loc or iloc for Row querying and square brackets[ ] on object itself for Column based Querywe can query a DataFrame in Series using Boolean masking -Boolean masking is a powerful filtering method whic allows us to use broadcasting to determine what data shold be kepth in our analysis<jupyter_code>import pandas as pd

purchase_1=pd.Series({'Name':'chris',
                      'item_purschased':'Dog food',
                     'cost':22.50})
purchase_2=pd.Series({'Name':'Keyvn',
                      'item_purschased':'kitty litter',
                     'cost':2.50})
purchase_3=pd.Series({'Name':'Vinod',
                      'item_purschased':'bird seed',
                     'cost':5.0})
df=pd.DataFrame([purchase_1,purchase_2,purchase_3],index=['Store 1','Store 2','Store 3'])
df<jupyter_output><empty_output><jupyter_text>Adding Horizontally by using the column name and default scaler value<jupyter_code>df['Delivered']=True
df<jupyter_output><empty_output><jupyter_text>Adding Horizontally by using the column name and different values in a Series but the list must be of same length as of dataFrame.<jupyter_code>df['Date']=['December 1','January 1','Mid May']

df
df['Sery']=pd.Series({'Name':'Vinod',
                      'item_purschased':'bird seed',
                     'cost':5.0})
df<jupyter_output><empty_output><jupyter_text>Adding  Series with lenght less than length of DatFrame 1) we have to Manually enter None or NaN where value is missing  2) We have to pass a Dictionary as Series  such that keys of dictionary  mathching index of DatFrame will  help placing value at desired indices and missing will be left to NaN<jupyter_code>adf=df.reset_index()
adf
adf['Date']=pd.Series({0:'December 1',2:'Mid May'})
adf<jupyter_output><empty_output><jupyter_text>Adding Horizontally by using the column name and a scaller value<jupyter_code>df=df.append(pd.Series(data={'Cost':3.00,'Item Purchased':'Kitty Food'},name=('Store 4')))
df<jupyter_output><empty_output><jupyter_text>c) Merging two DataFrame 1)  Union = Outer Join 2) Intersection= Inner Join<jupyter_code># Creating Two dataframes  with Name Column as its index

staff_df=pd.DataFrame([{'Name':'Kelly','Role':'Director HR'},
                 {'Name':'Sally','Role':'Course Liasion'},
                 {'Name':'James','Role':'Grader'}])
staff_df.set_index('Name')

student_df=pd.DataFrame([{'Name':'James','School':'Director HR'},
                 {'Name':'Mike','School':'Law'},
                 {'Name':'Sally','School':'Engineering'}])
student_df.set_index('Name')

print(staff_df)
print("\n")
print(student_df)<jupyter_output>Name            Role
0  Kelly     Director HR
1  Sally  Course Liasion
2  James          Grader


    Name       School
0  James  Director HR
1   Mike          Law
2  Sally  Engineering<jupyter_text>1) Merge using Outer joint or Union<jupyter_code>pd.merge(staff_df,student_df, how='outer',left_index=True,right_index=True)
pd.merge(staff_df,student_df, how='outer')<jupyter_output><empty_output><jupyter_text>2) Intersection using inner joint<jupyter_code>pd.merge(staff_df,student_df, how='inner')
pd.merge(staff_df,student_df, how='inner',left_index=True,right_index=True)<jupyter_output><empty_output><jupyter_text>d) Merge only Staff who may be students also<jupyter_code>pd.merge(staff_df,student_df,how='left')<jupyter_output><empty_output><jupyter_text>e) Merge only who are student or may be staff also<jupyter_code>pd.merge(staff_df,student_df,how='right')<jupyter_output><empty_output><jupyter_text>Challenging Exercise<jupyter_code>products_df=pd.DataFrame([{'Product ID':'4109','Price':5.0,'Product':'Sushi Roll'},
                          {'Product ID':'1412','Price':0.5,'Product':'Egg'},
                          {'Product ID':'8931','Price':1.5,'Product':'Bagel'}])
products_df.set_index('Product ID')

invoices_df=pd.DataFrame([{'Customer':'Ali','Product ID':'4109','Quantity':1},
                          {'Customer':'Eric','Product ID':'1412','Quantity':12},
                          {'Customer':'Ande','Product ID':'8931','Quantity':6},
                          {'Customer':'Sam','Product ID':'4109','Quantity':2}])
invoices_df.set_index('Product ID')
invoices_df
products_df
df0=pd.merge(invoices_df,products_df,how='outer')
df0
df0['Sum Price']=df0['Quantity']*df0['Price']
df0
df0['Sum Price'].sum()


# solution
print(pd.merge(products_df, invoices_df, left_index=True, right_on='Product ID'))

pd.merge(products_df, invoices_df, left_index=True, right_on='Product ID')
products_df.set_index('Product ID')
invoices_df.set_index('Product ID')
pd.merge(products_df, invoices_df)
# invoices_df<jupyter_output><empty_output><jupyter_text>Multi Index Merge First Name of Student and Staff might overlap but last name might not: thats why we use list of multiple column as left_on and right_on<jupyter_code>staff_df=pd.DataFrame([{'First Name':'Kelly','Last_Name':'Dejardeen','Role':'director HR'},
                       {'First Name':'Sally','Last_Name':'Brooks','Role':'course Liasion'},
                       {'First Name':'James','Last_Name':'Wilde','Role':'Grader'}])

student_df=pd.DataFrame([{'First Name':'James','Last_Name':'Hamound','Role':'Business'},
                       {'First Name':'Mike','Last_Name':'Smith','Role':'Law'},
                       {'First Name':'Sally','Last_Name':'Brooks','Role':'Engineering'}])
student_df
staff_df
pd.merge(student_df,staff_df,how='outer')
pd.merge(student_df,staff_df,how='inner')<jupyter_output><empty_output><jupyter_text>First Name of Student and Staff might overlap but last name might not: thats why we use list of multiple column as left_on and right_on right_on: Columns from the right DataFrame to use as keys<jupyter_code>pd.merge(student_df,staff_df,how='inner',left_on=['First Name','Last_Name'],right_on=['First Name','Last_Name'])
pd.merge(student_df,staff_df,right_on=['First Name'],left_on=['Last_Name'])<jupyter_output><empty_output><jupyter_text>Since the column are not specified explicitly , merge uses the overlapping column names as the keys.If the column names are different in each object, you can specify them separately using left_on and Right_on:<jupyter_code>df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data1': range(7)})
df3
df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'],'data2': range(3)})
df4
pd.merge(df3, df4,left_on='lkey', right_on='rkey')
pd.merge(df3,df4, how='outer')<jupyter_output><empty_output><jupyter_text>Pandas Idiom<jupyter_code>print(df.drop(df[df['Quantity'] == 0].index).rename(columns={'Weight': 'Weight (oz.)'}))
f=(df.drop(df[df['Quantity']==0].index))
f=Category  Quantity  Weight
Item                                             
Pack                       Pack         1    33.0
Tent                    Shelter         1    80.0
Sleeping Pad              Sleep         0    27.0
Sleeping Bag              Sleep         1    20.0
Toothbrush/Toothpaste    Health         1     2.0
Sunscreen                Health         1     5.0
Medical Kit              Health         1     3.7
Spoon                   Kitchen         0     0.7
Stove                   Kitchen         1    20.0
Water Filter            Kitchen         1     1.8
Water Bottles           Kitchen         2    35.0
Pack Liner              Utility         1     1.0
Stuff Sack              Utility         0     1.0
Trekking Poles          Utility         1    16.0
Rain Poncho            Clothing         0     6.0
Shoes                  Clothing         1    12.0
Hat                    Clothing         1     2.5
# Panda Idioms Practice 2
import pandas as pd
df=pd.read_csv('census.csv')
df.head()
df['STNAME'].unique()
df1=(df.where(df['STNAME']=='Alabama'))
df1
import numpy as np
np.average(df1['CENSUS2010POP'])
df2=df1.dropna()
df2
np.average(df2['CENSUS2010POP'])
%%timeit -n 10

for state in df['STNAME'].unique():
    df1=(df.where(df['STNAME']==state))
    df2=df1.dropna()
    avg=np.average(df2['CENSUS2010POP'])
    print("The average population in " +  state + " is "+ str(avg))
df01=df.groupby('STNAME')['CENSUS2010POP']
# np.average(df01)
%%timeit -n 10

for group,frame in df.groupby('STNAME'):
    avg=np.average(frame['CENSUS2010POP'])
    print("The average population in " + group+" is " +str(avg))
#     print("++++++++++++++++++++++++++++++++++++++++++++")
    
avg
for group,frame in df.groupby('STNAME'):
    print(group)<jupyter_output>Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut
Delaware
District of Columbia
Florida
Georgia
Hawaii
Idaho
Illinois
Indiana
Iowa
Kansas
Kentucky
Louisiana
Maine
Maryland
Massachusetts
Michigan
Minnesota
Mississippi
Missouri
Montana
Nebraska
Nevada
New Hampshire
New Jersey
New Mexico
New York
North Carolina
North Dakota
Ohio
Oklahoma
Oregon
Pennsylvania
Rhode Island
South Carolina
South Dakota
Tennessee
Texas
Utah
Vermont
Virginia
Washington
West Virginia
Wisconsin
Wyoming<jupyter_text>GROUPBY FUNCTION<jupyter_code>df=df.set_index('STNAME')
df
def fun(item):
    if item[0]<'M':
        return 0
    if item[0]<'Q':
        return 1
    return 2
for group, frame in df.groupby(fun):
    print("There are"+ str(len(frame))+'records in group' + str(group))
df=pd.read_csv('census.csv')
df=df[df['SUMLEV']==50]
df<jupyter_output><empty_output><jupyter_text>APPLYING GROUPBY<jupyter_code>df.groupby('STNAME').agg({'CENSUS2010POP':np.average})
# print(df.groupby('Category').apply(lambda df,a,b: sum(df[a] * df[b]), 'Weight (oz.)', 'Quantity'))


# Or alternatively without using a lambda:
def totalweight(df, w, q):
        return sum(df[w] * df[q])
        
print(df.groupby('Category').apply(totalweight, 'Weight (oz.)', 'Quantity'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14151617181920212, 'fraction_numerical': 0.021732476277930825, 'mean_word_length': 3.9705882352941178, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 67, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '152581', 'license': 'MIT', 'n_tokens_mistral': 3455, 'n_tokens_neox': 3197, 'n_words': 733, 'path': 'pandas/office_practice3/01_Pandas_IDIOMS.ipynb', 'repo_name': 'wahmed555/PhD_learning', 'size': '9801'}