قسمت پنجم – واکشی تخصصی داده از اکسل با پانداس

در این سری آموزش می خواهیم به صورت تخصصی داده ها را از فایل اکسل استخراج کنیم ، برای این مقاله ما از دیتاست زیر استفاده کرده ایم  که می توانید آن را دانلود کنید.در این آموزش می خواهیم به سوالات زیر پاسخ دهیم :

• چگونه از طریق پانداس یک شیت از فایل اکسل را فراخوانی کنیم؟
• چگونه بعضی از ستون های فایل اکسل را واکشی کنیم؟
• ویژگی های یک فایل اکسل همانند نام ستون ها و تعداد ستون ها را چگونه واکشی کنیم ؟
• چگونه خروجی های مختلف دریافت کنیم به طور مثال خروجی CSV,Json و…
• چگونه رکوردهای خاصی را واکشی کنیم ؟
• چگونه اگر چندین هدر Header داریم آن ها را نادیده بگیریم؟
• چگونه تمام شیت های اکسل را واکشی کنیم؟
• چگونه دو شیت اکسل را با هم ترکیب (Union) کنیم و همچنین خروجی را در یک فایل جدید ذخیره کنیم؟
• چگونه دو شیت از اکسل را باهم مرج (Join) کنیم ؟
• چگونه ستون جدید به دیتاهای موجود اضافه کنیم ؟
• واکشی داده از ابتدا و انتها چگونه انجام می شود ؟

برای خواندن فایل اکسل از متد read_excel باید استفاده کرد که خروجی این تابع در واقع یک دیتا فریم Data Frame می باشد.

نکته ای خیلی مهم که باید به آن اشاره کنم این است که خیلی از عملیاتی که در ادامه انجام خواهیم داد ، فارغ از منبع اطلاعات  ، بر روی دیتا فریم Data Frame انجام می شود چون ما داده ها را در یک دیتا فریم ذخیره می کنیم پس اگر داده های از فایل اکسل یا فایل تکست و …. فراخوانی کردید و دادهها را داخل یک دیتا فریم قرار دادید عملیاتی که بر روی داده انجام می شود را رویه همه دیتاست ها می توانید انجام بدهید.

در آموزش های بعدی ما یاد خواهیم گرفت که به صورت تخصصی داده ها را از فایل های CSV ، بانک اطلاعاتی ، آدرس های وب به صورت تخصصی واکشی کنیم.

• چگونه از طریق پانداس یک شیت از فایل اکسل را فراخوانی کنیم؟

برای فراخوانی یک شیت از فایل اکسل می توانید از دستور زیر استفاده کنید.طبیعتا از متد read_excel در ابتدا مسیر فایل و از طریق ویژگی sheet_name شیت مورد نظر را واکشی می کنیم.

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', sheet_name='Sheet1')
print(df)

• چگونه بعضی از ستون های فایل اکسل را واکشی کنیم؟ برای واکشی بعضی از ستون های فایل اکسل می توانید از ویژگی usecols  استفاده کنید و لیست فیلدهایی را که می خواهید وارد کنید  می توانید هم اسامی ستون ها را ذکر کنید هم از طریق نامگذاری در فایل اکسل و به شیوه اکسل

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet1', usecols=['Country', 'Segment'])
print(df)

روش دوم در این روش ما ستون های A,B,C  و G را واکشی کردیم

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet2', usecols="A:C,G")

print(df)

• ویژگی های یک فایل اکسل همانند نام ستون ها و تعداد ستون ها را چگونه واکشی کنیم ؟ برای بدست آوردن لیست ستون های فایل اکسل می توانید از ویژگی columns استفاده کنید.

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet1')
print(df.columns)

یا به طور مثال می خواهیم ببینیم نوع داده های، داده های ما چیست ؟ ویژگی dtypes به ما نشان می دهد که هر فیلد نوع داده اش چه چیزی می باشد (عدد صحیح ، اعشاری و…)

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1')

print(df.dtypes)

• چگونه خروجی های مختلف دریافت کنیم به طور مثال خروجی به صورت Json ،Dictionary و … . برای این کار می توانیم از متدهای to_dic , to_json , to_csv استفاده کنیم

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet1', usecols=['Product','Country'])
print('Excel Sheet to Dict:', df.to_dict())
print('Excel Sheet to JSON:', df.to_json())
print('Excel Sheet to CSV:\n', df.to_csv())

• چگونه رکوردهای خاصی را واکشی کنیم ؟ گاهی اوقات نیاز داریم که بر اساس یک شرط خاص داده واکشی کنیم به طور مثال فروش های بزرگتر از یک عدد خاص و…

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet2')

filterDataset=df[ (df['Country'] == 'کانادا' ) & (df['Segment']=='دولتی') & (df['Profit'] >=22000 )  ]
print(filterDataset)

یا به طور مثال دنبال داده هایی هستیم که به طور مثال نام کشورهایی که با کاراکتر F شروع می شوند ، برای اینکار از تابع startwith که داخل کتابخانه str هست استفاده می کنیم.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1')

df=df[df['Country'].str.startswith('F')]
print(df)



 

مثال دیگری که می توانیم پیاده سازی کنیم عمل شامل شدن هست که از تابع isin استفاده می کنیم.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1')

df=df[df['Country'].isin(['France','Canada'])]
print(df)



 

همچنین می توانیم چندین تابع را پشت سرهم وارد کنیم به طور مثال می خواهیم بر اساس تاریخ داده ها را سورت کنیم که از تابع sort_values استفاده می کنیم.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1')

df=df[df['Country'].isin(['France','Canada'])].sort_values(by=['Date'])

print(df)



 

یا در این مثال چندین شرط و چندین ستون برای مرتب سازی در فرمول لحاظ شده است.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1')


df=df[(df['Country'].str.contains('Fr')) & (df['Units Sold']<1000)].sort_values(by=['Segment','Year'])

print(df)



 

یا گاهی اوقات می خواهیم رکوردها را یونیک مشاهده کنیم و دنبال داده های واحد هستیم و نمی خواهیم داده های تکراری نمایش داده شود.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1', usecols=['Country','Segment'])
 
print(df['Country'].unique())

• چگونه اگر چندین هدر Header داریم آن ها را نادیده بگیریم؟ گاهی اوقات ما در فایل اکسل چندین هدر داریم و نیاز نیست آنهارا واکشی کنیم و می خواهیم از آن ها رد بشویم می توانیم از ویژگی Skiprows استفاده کنیم.

import pandas as pd

df = pd.read_excel('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx', 
sheet_name='Sheet3',skiprows=[0, 1])

print(df)

• چگونه تمام شیت های اکسل را واکشی کنیم؟ از طریق دستور with می توانیم به طور مثال یک فایل را در حافظه باز کنیم و سپس می توانیم ار شیتی از این فایل را واکشی کنیم. برای استفاده از قطعه کد زیر کتابخانه openpyxl را نیز importکنید.

import pandas as pd  
with pd.ExcelFile('C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx') as x:  
    s1 = pd.read_excel(x, 'Sheet1')
    s2 = pd.read_excel(x, 'Sheet2')
print(s1)
print(s2)

• چگونه دو شیت اکسل را با هم ترکیب کنیم و همچنین خروجی را در یک فایل جدید ذخیره کنیم؟در این مثال از تابع concat برای ترکیب دو دیتا فریم استفاده کرده ایم و همچنین از طریق متد to_excel خروجی را به داخل یک فایل جدید منتقل کرده ایم. البته در این روش ما می خواهیم دو دیتا را با هم ترکیب یا Union کنیم پس بهتر است تعداد ستون ها و دیتاتایپ ستون ها یکسان باشد.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df1 = pd.read_excel(Path, sheet_name='Sheet4')

df2 = pd.read_excel(Path, sheet_name='Sheet5')

newData = pd.concat([df1, df2])
newData.to_excel('final_output.xlsx')
print(newData)




 

• چگونه دو شیت از اکسل را باهم مرج (Join) کنیم ؟ برای جوین (Join) دو دیتا فریم می توانید از تابع Merge استفاده کنید و همچنین نوع ارتباط را نیز مشخص کنید.  در اینجا نوع ارتباط بین دو جدول را ویژگی how مشخص می کند.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df1 = pd.read_excel(Path, sheet_name='Sheet4')

df2 = pd.read_excel(Path, sheet_name='Sheet6')

newData  = df1.merge(df2, on="ProductID", how="inner")

print(newData)

• چگونه ستون جدید به دیتاهای موجود اضافه کنیم ؟ برای اضافه کردن ستون جدید و ایجاد آن از ستون های موجود باید از تابع insert استفاده کرد. در این تابع باید در ابتدا مشخص کنیم که در کدام ایندکس این ستون قرار بگیرید و چون ما ۴ ستون را فراخوانی کردیم و اندیس ستون ها هم از صفر شروع می شود ما تنظیم می کنیم که در اندیس ۴ قرار بگیرد و در ادامه نام ستون را وارد می کنیم و در انتها فرمولی که می خواهیم لحاظ شود.

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1',usecols=['Country', 'Segment','Units Sold','Sale Price'])

df.insert(4, "TotalAmount", df["Units Sold"]*df["Sale Price"])

print(df)

• واکشی داده از ابتدا و انتها چگونه انجام می شود ؟ از طریق تابع head و tail می توان به رکوردهای ابتدا و انتها دسترسی داشت به طور مثال ۳ رکورد اول یا ۳ رکورد انتها

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1',usecols=['Country', 'Segment','Units Sold','Sale Price'])

df=df.head(3)

print(df)

import pandas as pd

Path='C:\\Users\\pirooz\\Desktop\\Article\\Dataset\\Financial.xlsx'
df = pd.read_excel(Path, sheet_name='Sheet1',usecols=['Country', 'Segment','Units Sold','Sale Price'])

df=df.tail(3)

print(df)