# Pandas%20%282%29
course: Academy — 65-GENAI-for-Engineers
module: Academy/65-GENAI-for-Engineers
type: notebook
source_url: https://personal-learn.armco.dev/files/Academy/65-GENAI-for-Engineers/folders/Live_Intervention_Pandas_Folder/Pandas%20%282%29.ipynb
---
[cell 1 markdown]
2 data structures- data series and data frame
Name age
0 a 10
1 b 20
2 c 30
[cell 2 code]
import pandas as pd
import numpy as np
[cell 3 code]
ds=pd.Series()
[cell 4 code]
ds
[cell 5 code]
#create a ds from a list
l=[1,2,3]
myser=pd.Series(l)
[cell 6 code]
myser
[cell 7 code]
myser[0]
[cell 8 code]
myser[2]
[cell 9 code]
l2=[10,20,30]
myser2=pd.Series(l2,index=['a','b','c'])
[cell 10 code]
myser2
[cell 11 code]
myser2["b"]
[cell 12 code]
l=['a','b']
ds=pd.Series(l)
[cell 13 code]
ds
[cell 14 code]
s=pd.Series([10,20,30,40,50])
[cell 15 code]
s
[cell 16 code]
s.dtype
[cell 17 code]
s.values
[cell 18 code]
s.index
[cell 19 code]
print(s.name)
[cell 20 code]
s.name="age"
[cell 21 code]
s
[cell 22 code]
s[2]
[cell 23 code]
s[1:4]
[cell 24 code]
# iloc-> location based indexing
s.iloc[3]
[cell 25 code]
s.iloc[[1,3,4]]
[cell 26 code]
index=["Carol","Alex","Simran","Bob","Tarun"]
[cell 27 code]
s.index=index
[cell 28 code]
s
[cell 29 code]
s['Alex']
[cell 30 code]
#loc-> label based indexing
[cell 31 code]
s.loc[['Simran','Bob']]
[cell 32 code]
s.iloc[2:4]
[cell 33 code]
s
[cell 34 code]
s.loc['Simran':'Tarun']
[cell 35 code]
s
[cell 36 code]
#filter
s[s>30]
[cell 37 code]
s["Bob"]=45
[cell 38 code]
s
[cell 39 code]
s["abc"]=55
[cell 40 code]
s
[cell 41 markdown]
DataFrame
[cell 42 code]
#list
l=[1,2,3]
df1=pd.DataFrame(l)
[cell 43 code]
df1
[cell 44 code]
#list of lists
data=[['a',10,'Blr'],['b',20,'Delhi'],['c',30,'Roorkee']]
df2=pd.DataFrame(data)
[cell 45 code]
df2
[cell 46 code]
df2=pd.DataFrame(data,columns=["name","age","city"],index=['a','b','c'])
[cell 47 code]
df2
[cell 48 code]
df2['age']
[cell 49 code]
df2['age']=df2['age'].astype(float)
[cell 50 code]
df2
[cell 51 code]
df2["age"]=df2["age"]+5
[cell 52 code]
df2
[cell 53 code]
df2['age']=df2['age']+3
[cell 54 code]
df2
[cell 55 code]
#add column last name
df2['last_name']=['A','B','C']
[cell 56 code]
df2
[cell 57 code]
df2['number']=pd.Series([1,2,3],index=['a','b','c'])
[cell 58 code]
df2
[cell 59 code]
df2['number2']=df2['age']+df2['number']
[cell 60 code]
df2
[cell 61 code]
del df2['number2']
[cell 62 code]
df2
[cell 63 code]
data={
"Name":['Alice','Bob','Carol','David','Eva','Alice'],
"Age":[25,30,35,np.nan,29,25],
"Department":['HR','IT','Finance','HR','IT','HR'],
"Salary":[50000,60000,55000,70000,65000,50000]
}
[cell 64 code]
df=pd.DataFrame(data)
[cell 65 code]
df
[cell 66 code]
df.head(2)
[cell 67 code]
df.tail(3)
[cell 68 code]
df
[cell 69 code]
#loc and iloc
df.iloc[1:3,:2] #rows,columns
[cell 70 code]
df.loc[1:3,['Age','Department']]
[cell 71 code]
df.loc[1:3,'Name':'Department']
[cell 72 code]
df[["Age","Department"]]
[cell 73 code]
df.shape
[cell 74 code]
df
[cell 75 code]
df.info()
[cell 76 code]
df.describe()
[cell 77 code]
df
[cell 78 code]
df['Salary']=df['Salary']+5000
[cell 79 code]
df
[cell 80 code]
#rename column
df.rename(columns={"Department":"Dept"},inplace=True)
[cell 81 code]
df
[cell 82 code]
df['Dept'].unique()
[cell 83 code]
df['Dept'].value_counts()
[cell 84 code]
#data cleaning
df.isnull().sum()
[cell 85 code]
df.dropna(how="any")
[cell 86 code]
df
[cell 87 code]
df['Age'].fillna(df['Age'].mean())
[cell 88 code]
df['Age'].mean()
[cell 89 code]
df
[cell 90 code]
#forward fill and backward fill
df['Age'].fillna(method='ffill')
[cell 91 code]
df['Age'].fillna(method='bfill')
[cell 92 code]
df
[cell 93 code]
df["Name"]=df["Name"].replace("Eva","Eve")
[cell 94 code]
df
[cell 95 code]
df
[cell 96 code]
df= df.drop_duplicates()
[cell 97 code]
df
[cell 98 code]
df=pd.read_csv('data.csv')
[cell 99 code]
df = pd.DataFrame({
'A': [1, 2, 3],
'B': [4, 5, 6],
'C': [7, 8, 9]
})
df.insert(1, 'D', [10, 11, 12])
print(df)
[cell 100 markdown]
Data Cleaning and Preprocessing
* -Handling missing data (using fillna, dropna).
* Data type conversions (using astype)
* Renaming columns and reindexing
* Filtering data based on conditions.
[cell 101 code]
import pandas as pd
import numpy as np
data = {
'A': [1, 2, np.nan, 4],
'B': [5, np.nan, np.nan, 8],
'C': [10, 11, 12, 13]
}
df = pd.DataFrame(data)
[cell 102 code]
df_fill=df.fillna(1)
[cell 103 code]
#forward/backward fillna
df_ffill=df.fillna(method='ffill')
df_bfill=df.fillna(method='bfill')
[cell 104 code]
data = {
'A': [1, 2, np.nan, 4],
'B': [5, np.nan, np.nan, 8],
'C': [10, 11, 12, 13]
}
df=pd.DataFrame(data)
[cell 105 code]
df_droprow=df.dropna()
[cell 106 code]
df_dropcol=df.dropna(axis=1)
[cell 107 code]
data = {'col1': [1, 2, 3], 'col2': [4, 5, 6], 'col3': [7, 8, 9]}
df=pd.DataFrame(data)
df_renamed=df.rename(columns={'col1':'A','col2':'B','col3':'C'})
[cell 108 code]
#Filtering data based on conditions.
import pandas as pd
# Sample data
data = {
'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva'],
'Age': [24, 17, 50, 33, 29],
'Salary': [70000, 48000, 110000, 95000, 65000]
}
df=pd.DataFrame(data)
[cell 109 code]
df
[cell 110 code]
df['Age']
[cell 111 code]
# Filter for Age > 30
df_older= df[df['Age']>30]
[cell 112 code]
df_older
[cell 113 code]
# Filter for Age > 25 and Salary > 95000
condition=(df['Age']>25)&(df['Salary']>95000)
df_filtered=df[condition]
print(df_filtered)
[cell 114 markdown]
Group by and aggregate
[cell 115 code]
data = {
'Department': ['Sales', 'Marketing', 'IT', 'HR', 'Sales', 'IT'],
'Employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eva', 'Frank'],
'Salary': [70000, 48000, 110000, 50000, 72000, 95000]
}
df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)
[cell 116 code]
# Group by 'Department'
grouped= df.groupby('Department')
[cell 117 code]
# Calculate mean salary per department
mean_salary=grouped['Salary'].mean()
[cell 118 code]
mean_salary
[cell 119 code]
# Calculate total salary per department
mean_salary=grouped['Salary'].sum()
[cell 120 code]
mean_salary
[cell 121 code]
# Applying multiple aggregation functions
agg_result=grouped['Salary'].agg(['mean','sum','count','max','min'])
[cell 122 code]
agg_result
[cell 123 code]
# Group by multiple columns
multi_grouped=df.groupby(['Department','Employee'])
result=multi_grouped['Salary'].mean()
[cell 124 code]
result
[cell 125 code]
import pandas as pd
df1 = pd.DataFrame({'ID':[1,2,3,5,9],
'Col_1':[1,2,3,4,5],
'Col_2':[6,7,8,9,10],
'Col_3':[11,12,13,14,15],
'Col_4':['apple','orange','banana','strawberry','raspberry']
})
df2 = pd.DataFrame({'ID':[1,1,3,5],
'Col_A':[8,9,10,11],
'Col_B':[12,13,15,17],
'Col_4':['apple','orange','banana','kiwi']
})
[cell 126 code]
df1
[cell 127 code]
df2
[cell 128 code]
#inner
inr=pd.merge(df1,df2)
[cell 129 code]
inr
[cell 130 code]
#on
inr=pd.merge(df1,df2,on='ID')
[cell 131 code]
inr
[cell 132 code]
#on-multiple columns
inr=pd.merge(df1,df2,on=['ID','Col_4'])
[cell 133 code]
inr
[cell 134 code]
#adding suffix and joining of separate columns
pd.merge(df1,df2,on='ID',suffixes=('_left','_right'))
[cell 135 code]
pd.merge(df1,df2,left_on='Col_2',right_on='Col_A')
[cell 136 code]
#types of joins- Inner, Outer, Left, Right
#Left Join
df3=pd.merge(df1,df2,on='ID',how='left')
[cell 137 code]
del df3['ID']
[cell 138 code]
df3
[cell 139 code]
df3[['Col_A']]
[cell 140 code]
#join
df1.join(df2, lsuffix="_left", rsuffix="_right")
[cell 141 code]
#default=left join
df1.join(df2,on='ID', lsuffix="_left", rsuffix="_right")
[cell 142 code]
#default=left join
df1.join(df2,on='ID', how='inner', lsuffix="_left", rsuffix="_right")