. Pandas

import pandas as pd

# Creating a pandas series from a list 
var=pd.Series([1,2,2,3,4,5])
print(type(var))
print(var)

# Creating a pandas data frame from list
var = pd.DataFrame([1,2,3,4,5])
print(type(var))
print(var)# notice that default column name will be 0 

# Creating a pandas data frame from list with column name
var = pd.DataFrame([1,2,3,4,5],columns=['sample'])
print(type(var))
print(var)# column name is now changed to sample

# Creating a pandas data frame from list with column name
var = pd.DataFrame([1,2,3,4,5],columns=['sample'])
print(type(var))
print(var)# column name is now changed to sample

# Creating an empty data frame in python
var = pd.DataFrame()
print(type(var))
print(var)

# Creating a data frame from dict
var_list1=[25,26,30,21,22,24]
var_list2=['p1','p2','p3','p4','p5','v6'] # the length of the array should be equal here
var_dict={'age':var_list1,'name':var_list2}
df=pd.DataFrame(var_dict)
df

import random
var1=[random.randint(1,100) for x in range(1000)]
var2=[random.randint(1,100) for x in range(1000)]
var3=[random.randint(1,100) for x in range(1000)]
var4=[random.choice(['hello','world','python','is','the','best']) for x in range(1000)]

df=pd.DataFrame({'c1':var1,'c2':var2,'c3':var3,'c4':var4})

# see top n rows of a df
df.head() # by default it is 5 rows 

df.head(10) # if value is passed it will shoe that many number of rows

df.tail() # show last 5 rows 

df.info() # show infor about null values and data type of a columns

df.describe() # returns details about numeric columns 

# selecting 1 column as series 
print(type(df['c1']))
print(df['c1'])

# selecting 1 column as dataframe 
print(type(df[['c1']]))
df[['c1']]

# selecting multiple columns 
df[['c1','c2']]

# dropping a columns in python
print(df.head())
df.drop(['c2'],axis=1,inplace=True)
df.head()

# list all the columns in a dataframe
df.columns

# accessing columns and rows using loc
print(df.loc[1:5,'c1']) # columns c1 rows 1 to 5 
print(df.loc[:,'c1']) # all rows of columns c1
df.loc[1,:] # only 1 row with all columns

# accessing columns and rows using iloc, columns and rows both are accessed by index only
df.iloc[1:5,1] # columns with index 1 ,rows 1 to 5 

# creating a sample dataframe
var1=[random.randint(1,100) for x in range(1000)]
var2=[random.randint(1,100) for x in range(1000)]
var3=[random.randint(1,100) for x in range(1000)]
var4=[random.choice(['hello','world','python','is','the','best']) for x in range(1000)]
df=pd.DataFrame({'c1':var1,'c2':var2,'c3':var3,'c4':var4})

# accessing the indexes of a dataframe 
print(df.index) # range element
print(list(df.index)) # converting index to list

# setting a column as index
print(df.head())
df.set_index(['c1'],inplace=True)

df

df.loc[10,:]  # accessing all rows with index 10 

# reading data from a csv file
df=pd.read_csv('./data/fifa_eda.csv') # reading fifa data this file is saved in my current directory/data folder
df.head()

# count all the categories 
count_dict={}
count_dict['high']=0
count_dict['low']=0
count_dict['medium']=0
for i in df['wage_category']:
    count_dict[i]=count_dict[i]+1

# performing basic EDA on this data
print(f"The data has {df.shape[0]} rows and {df.shape[1]} columns")
# null values check
df.info()

# numeric values check 
df.describe()

df['c5']='abc' # this will add a constant value 'abc' to each row 
df['c6']=[random.randint(1,10) for x in range(df.shape[0])] # using a list to add a column 
df

df.drop(['c5','c6'],axis=1,inplace=True) # dropping above 2 columns as they are not required for our anlaysis

# create a function which return 3 values 'low','medium' and 'high' under following conditions 
# low - if Wage is below 3
# medium - if Wage is between 3 and 9 inclusice 
# high - if Wage is above 9
def wage_category(wage):
    if wage<3:
        return 'low'
    elif wage>=3 and wage<=9:
        return 'medium'
    else:
        return 'high'
wage_series=df['Wage'].apply(wage_category) # it will return a pandas series with length equal to number of rows in data frame
# this can now be assigned as a columns of the dataframe
df['wage_category']=wage_series
df.head()

# using lambda function with apply function , this works row wise 
# add column value and wage using apply and lambda 
df.apply(lambda row:row['Value']+row['Wage'],axis=1)

 
# +,-,/,* between two numeric columns 
df['new_col']=df['Value']+df['Wage']
df['new_col']=df['Value']-df['Wage']
df['new_col']=df['Value']*df['Wage']
df['new_col']=df['Value']/df['Wage']
df.head()

df.drop(['new_col'],axis=1,inplace=True)

# + with 2 non numeric columns 
df['new_col']=df['Name']+' '+df['Nationality'] # it will concat all the string values 
df.head()

df.drop(['new_col'],axis=1,inplace=True)

# constanct value with columns
df['new_col']=df['Value']*2
df['new_col']=df['Value']+2
df['new_col']=df['Value']-2
df['new_col']=df['Value']/2
df.head()

df.drop(['new_col'],axis=1,inplace=True)

# sum,avg,min ,max rows wise 
print(df['Wage'].mean())
print(df['Wage'].min())
print(df['Wage'].max())
print(df['Wage'].sum())

# value counts function 
df['Wage'].value_counts()

# drop function can be used to delete rows also from a data from using index and axis 
df.drop([0,1],axis=0) # not writing inplace here as we donot want to drop it from table 

# iterrows() return 1 variable for index and 1 variable for row 
for i,j in df.head(2).iterrows():
    print("i--->",i)
    print("j--->",j)

# iterrows() - indivial col value of rows can also be accesed
for i,j in df.head(2).iterrows():
    print("i--->",i)
    print("j--->",j['Wage'],j['Value'])

# iteritems - return rows and key value pair 
# iterrows() - indivial col value of rows can also be accesed
for i,j in df.head(2).iteritems():
    print("i--->",i) # i will be columns name here
    print("j--->",j) # values will be value of that column 

# itertuples - 
for i in df.head(2).itertuples():
    print(i)

df.sort_values(by='Wage',inplace=True)

df.head()

# grouping data using a column 
df.groupby(['Position'])['Wage'].mean() 

df.groupby(['Position','Preferred Foot'])['Wage'].sum() 

df.groupby(['Position','Preferred Foot']).agg({'Wage':'sum','Overall':'mean'}).reset_index().head()

# concat 2 data frames
df1=pd.DataFrame({'c1':[1,2,3],'c2':[1,2,3]})
df2=pd.DataFrame({'c1':[6,7,8],'c2':[1,2,3]})
final_df=pd.concat([df1,df2])

final_df

# concat 2 data frames with different columns 
df1=pd.DataFrame({'c1':[1,2,3],'c2':[1,2,3]})
df2=pd.DataFrame({'c3':[6,7,8],'c2':[1,2,3]})
final_df=pd.concat([df1,df2])
final_df

# join two dataframes 
df1=df[['ID','Name','Age','Nationality']]
df2=df[['ID','Wage','Preferred Foot','Position']]

merged_df=pd.merge(left=df1,right=df2,how='inner',left_on=['ID'],right_on=['ID']) # left ,right,inner ,outer
merged_df.head()

# converting to columns to datetime type 
# notice that "Contract Valid Until" is object type in the datafra,e
df.info()

# working with date and time in python and pandas 
# python , pandas 
import datetime as dt
sample_var=dt.datetime(year=2024,month=1,day=5,hour=14,minute=55,second=45)
type(sample_var)
# str to date 
sample_str_date='2024-12-31 12:45:45'
dt.datetime.strptime(sample_str_date,'%Y-%m-%d %H:%M:%S')
# str to date 
sample_date=dt.datetime(year=2024,month=1,day=5,hour=14,minute=55,second=45)
dt.datetime.strftime(sample_date,'%Y-%m-%d %H:%M:%S')
# adding n days to date ,n months to date 
sample_date+dt.timedelta(days=12)

# we will now convert this column to datetime columns 
df['Contract Valid Until']=pd.to_datetime(df['Contract Valid Until'])
df.info()

# extracting info from datetime column in pandas 
print(pd.DatetimeIndex(df['Contract Valid Until']).hour)
print(pd.DatetimeIndex(df['Contract Valid Until']).minute)
print(pd.DatetimeIndex(df['Contract Valid Until']).day)

# we can also find out min , max time from a timestamp column and also do a group of them 
print(df['Contract Valid Until'].max())
print(df['Contract Valid Until'].min())

# subtract n days from datetime column 
df['Contract Valid Until']-pd.DateOffset(days=10)

# convert datetime format for all the values in pandas columns 
[dt.datetime.strftime(x,'%d-%m-%Y') for x in df[~df['Contract Valid Until'].isna()]['Contract Valid Until']]

# convert a value of columns to upper or lower 
df['Name']=df['Name'].str.upper()# with assignment
df.head()

# Splitting basis a seperator
df["Name"].str.split(".")

# Splitting basis a seperator and expand option
df["Name"].str.split(".",expand=True)

# replace function 
df["Name"].replace('J. GULLEY','abc')