<jupyter_start><jupyter_text>in this session we will learn the following :1. how to read from text file2. how to read from csv file3. how to read form excel file4. how to save to a file5. what is merge merge and how you can apply it as SQL joins6. concatenate your data7. how does combine works 8. reshape your data 9. how to pivote your dataframe10. how to deal with duplicates11. how to map your values12. replace data13. rename columns and indeces<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>reading data from files<jupyter_code># reading from text file
df_text = pd.read_csv('textfile_sample.txt')
df_text
#but what if we donnt wanna make the first row as the header
df_text = pd.read_csv('textfile_sample.txt' , header = None)
df_text
#how to read form csv
df_csv = pd.read_csv('csvfile_sample.csv' , header = None)
df_csv
# how to read from excel sheet
df_xl = pd.read_csv('excelfile_sample.xlsx' , header = None)
df_xl
#how to save to a file
df_xl.to_csv('outputfile.txt' , sep='_') # this sep argument to make seperators = _
# the output is afile 
"""
_0_1_2_3_4
0_q_b_c_d_apple
1_1_2_3_4_orange
2_5_9_4_2_lemon
3_5_8_6_1_pinapple

"""<jupyter_output><empty_output><jupyter_text>Concat<jupyter_code>json_obj = {'id' : ['1','2','3','4','5'] , 
           'Feature1' : ['A' , 'C' , 'E' , 'G' , 'I' ],
           'Feature2' : ['B' , 'E' , 'F' , 'H' , 'J']
           
           }
df_1 = pd.DataFrame(json_obj , columns = ['id' , 'Feature1' , 'Feature2'])

json_obj2 = {
        'id': ['1', '2', '6', '7', '8'],
        'Feature1': ['K', 'M', 'O', 'Q', 'S'],
        'Feature2': ['L', 'N', 'P', 'R', 'T']}
df_2 = pd.DataFrame(json_obj2) # you dont have to specify the columns if you want all of them
df_2
df_1<jupyter_output><empty_output><jupyter_text>Merge<jupyter_code>json_obj3 = {
        'id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],
        'Feature3': [12, 13, 14, 15, 16, 17, 15, 12, 13, 23]}
df_3 = pd.DataFrame(json_obj3)
df_3
df_vertical = pd.concat([df_1 , df_2])

df_vertical
# the index seems not right if you want you can reindex it if you want
df_vertical_reindex = pd.concat([df_1 , df_2] , ignore_index = True)
df_vertical_reindex
#but what if you want to label the indeces 
df_labled_ds = pd.concat([df_1 , df_2] , keys = ['x' , 'y'])
df_labled_ds
# note that if you used the ignore_index along with keys you are going to neglect the keys or labeling the data .. this is so imprtant to put in mind
df_labled_ds = pd.concat([df_1 , df_2] , keys = ['x' , 'y'] , ignore_index = True)
df_labled_ds
df_labled_ds = pd.concat([df_1 , df_2] , keys = ['x' , 'y'])
df_labled_ds
df_labled_ds.loc['y'] # this how to select a specific data frame with label
# but imagine we want to concate herizontally this is so different than joining or merging 
df_horizontal = pd.concat([df_1, df_2] , axis = 1)
df_horizontal

# note that the result is a horizontal alighnmetal  with no matching of any keys 
df_vertical
# how to make join for your data based on a key ,
#note how featuer 3 gonna be added with respect to id column values
df_merge_col = pd.merge(df_vertical , df_3 , on ='id')
df_merge_col
# sometimes the key you want to merge based on has different name that the other data frame
# how to be more specific for joining column

df_merge_keys = pd.merge(df_vertical , df_3 , right_on = 'id' ,left_on = 'id')
df_merge_keys
# here we are going to use merge to do the same job of joins in SQL
# we have three types of joins in sql and they are all the same in pandas merge
#full outer , inner , right  and left joins
# first: full outer join
# in this type of join we are making a simple join but we are ging to include all data form both dataframes 

# you gonna notice that some of the rows has NaN values that it doest not has a mach on ther other data frame and vice versa
df_outer = pd.merge(df_1, df_2 , on= 'id', how = 'outer')
df_outer 
# incase you want to label your data you can use the suffixes argument
df_merged_labeled_cols = pd.merge(df_1 , df_2 , on = 'id' , how = 'outer' , suffixes = ('_left' , '_right'))
df_merged_labeled_cols
#second: inner join
df_inner = pd.merge(df_1 , df_2 , on = 'id' , how = 'inner')
df_inner # only the maching keys will generate the join otherwise you it will deleted 
# third : right join
df_right = pd.merge(df_1 , df_2 , on = 'id' , how = 'right')
df_right # take all the right and only matching from the left 
# third : left join
df_left = pd.merge(df_1 , df_2 , on = 'id' , how = 'left')
df_left # take all the left and only matching from the right 
# finally what if we want to make joins based on the index of a dataframe
df_index = pd.merge(df_1, df_2 , right_index = True ,  left_index = True)
df_index
#The merge_asof() is similar to an ordered left-join except that you match on nearest key rather than equal keys
trades = pd.DataFrame({
    'time': pd.to_datetime(['20160525 13:30:00.023',
                            '20160525 13:30:00.038',
                            '20160525 13:30:00.048',
                            '20160525 13:30:00.048',
                            '20160525 13:30:00.048']),
    'ticker': ['MSFT', 'MSFT','GOOG', 'GOOG', 'AAPL'],
    'price': [51.95, 51.95,720.77, 720.92, 98.00],
    'quantity': [75, 155,100, 100, 100]},
    columns=['time', 'ticker', 'price', 'quantity'])


quotes = pd.DataFrame({
    'time': pd.to_datetime(['20160525 13:30:00.023',
                            '20160525 13:30:00.023',
                            '20160525 13:30:00.030',
                            '20160525 13:30:00.041',
                            '20160525 13:30:00.048',
                            '20160525 13:30:00.049',
                            '20160525 13:30:00.072',
                            '20160525 13:30:00.075']),
    'ticker': ['GOOG', 'MSFT', 'MSFT','MSFT', 'GOOG', 'AAPL', 'GOOG','MSFT'],
    'bid': [720.50, 51.95, 51.97, 51.99,720.50, 97.99, 720.50, 52.01],
    'ask': [720.93, 51.96, 51.98, 52.00,720.93, 98.01, 720.88, 52.03]},
    columns=['time', 'ticker', 'bid', 'ask'])
trades
quotes
# lets see how we can merge knowing that we have a time_series info in both of data frames
df_merge_asof = pd.merge_asof(trades , quotes , on = 'time' , by = 'ticker')

df_merge_asof
# thre result will be based on ticker for matching and time for 
# the nan value form aapl because there is no trads sequence bigger than the one on quotes
# lets see how we can merge knowing that we have a time_series info in both of data frames
# adding tollerance with 2 mili  seconds if further igore .. and thats why we are expecting more nulls
df_merge_asof_tolerance = pd.merge_asof(trades , quotes , on = 'time' , by = 'ticker' , tolerance = pd.Timedelta('2ms'))

df_merge_asof_tolerance<jupyter_output><empty_output><jupyter_text>how does combine works<jupyter_code>import numpy as np
ser1 = pd.Series([2, np.nan , 4 , np.nan , 6 , np.nan] , index = list('QRSTUV'))
ser1
ser2 = pd.Series(np.arange(len(ser1) ), dtype = np.float64 , index = list('ARSTUV'))
ser2
# what if we want ser1 nan values to be replace with the counter part form ser2
# note carefully the following
ser1.combine_first(ser2) 
nan = np.nan # I am gonna use it to make thing easy for typing =D
dframe_odds = pd.DataFrame({
    'X' : [1, nan , 3 , nan],
    'Y' : [ nan , 5 , nan , 7],
    'Z' : [ nan , 9 , nan, 11]
})
dframe_odds
dframe_evens = pd.DataFrame({
    'X' : [2, 4 , nan , 6 , 8],
    'Y' : [ nan , 10  ,12 , 14 , 16]
})
dframe_evens
# lets see how datafrme will behave for combine method
dframe_odds.combine_first(dframe_evens) # you will note its gonna fill frome events to odds and leave it nan if it does has to counter part in the evens frame<jupyter_output><empty_output><jupyter_text>Reshape<jupyter_code>df_1 = pd.DataFrame(np.arange(8).reshape(2,4) , index = pd.Index('LA,SF'.split(',') , name = 'city') , 
                   columns = pd.Index(list('ABCD') , name = 'letter'))
df_1
df_stacked = df_1.stack()
df_stacked
# make the letter is columns
df_stacked.unstack('letter')
# make city the columns
df_stacked.unstack('city')
#lets revise some thing here :) promise its gonna be fun 
ser1 = pd.Series([0,1,2 ], index = list('QXY'))
ser2 = pd.Series([4,5,6 ], index = list('XYZ'))
dframe = pd.concat([ser1 , ser2] , keys = ['first' , 'second']) #remembe dont you =D
dframe
# lets use it for stacking and unstacking
dframe_unstacked = dframe.unstack()
dframe_unstacked<jupyter_output><empty_output><jupyter_text>Pivoting<jupyter_code># pivoting is just a way to convert data into columns of course this is depend on how frequent data are repeated and its more suitable for categorical daa 

df = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two',
                           'two'],
                   'bar': ['A', 'B', 'C', 'A', 'B', 'C'],
                   'baz': [1, 2, 3, 4, 5, 6],
                   'zoo': ['x', 'y', 'z', 'q', 'w', 't']})
df
pd.pivot_table(data = df , columns = 'foo' ,index = 'bar', values = 'baz' ) # by default the index is grouped and mean is calcualted 
# to change the default you can use aggfunc arguments
pd.pivot_table(data = df , columns = 'foo' ,index = 'bar', values = 'baz'  , aggfunc = 'count')
pd.pivot_table(data = df , columns = 'foo' ,index = 'bar', values = 'baz'  , aggfunc = 'sum')<jupyter_output><empty_output><jupyter_text>Duplicates<jupyter_code>df = pd.DataFrame({'key1' : ['A']*2 + ['B']*3 , 'key2' : [2,2,2 , 3 , 3]})
df
df.duplicated() # boolean series will by generated indicates false for non duplicate and true for duplicates which in most cases you have to get rid of them
df.duplicated().sum() # to see how many duplicates do you have in your data frame
#drop duplicates for all rows 
df.drop_duplicates()
df.drop_duplicates(['key1']) # drop all duplicates for only values of key1 column<jupyter_output><empty_output><jupyter_text>Mapping<jupyter_code>dframe = pd.DataFrame({'names': ['Mohamed' , 'Ahmed' , 'Mahmoud'], 'age' : [20 , 22 , 2686]}) # of course ages are non logic =D
dframe
dframe['also non real']  = dframe['names'].map({'Mohamed' : 15 , 'Ahmend' : 30 , 'Mahmoud' : 40})
dframe # as you can see ahmed not mapped correctly because I have a typo on the mapping
dframe['also non real']  = dframe['names'].map({'Mohamed' : 15 , 'Ahmed' : 30 , 'Mahmoud' : 40})
dframe # ya I wish i am 15 years old =D<jupyter_output><empty_output><jupyter_text>Replace<jupyter_code>ser1 = pd.Series([1,2,3,4,5,1,2,3,4])
ser1
ser1.replace([1,4] , [1000, 4000000]) # i think it simple enough to understand
ser1.replace({1: 64546 , 4: 54651})  # or you can replace using dictionaries<jupyter_output><empty_output><jupyter_text>Rename columns and indeces<jupyter_code>dframe
dframe.rename(columns= {'names' : 'Brothers' , 'also non real' : 'Still NOn Real' } , index = {0:'Zero' , 2 : '=D'})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13301267227823513, 'fraction_numerical': 0.05383405790398668, 'mean_word_length': 3.660344827586207, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '558094', 'license': 'MIT', 'n_tokens_mistral': 4206, 'n_tokens_neox': 3823, 'n_words': 1306, 'path': 'third session working with data part 1.ipynb', 'repo_name': 'MohammedHassanAli/data_analysis_visualization_course', 'size': '10811'}