پایتون و روش کار با فایل های اکسل — از صفر تا صد

در این راهنما با روش خواندن و ایمپورت کردن فایل‌های اکسل در پایتون و روش نوشتن این داده‌ها در صفحات گسترده آشنا می‌شویم و همچنین بررسی می‌کنیم که کدام بسته به این منظور مناسب‌تر است.

استفاده از پایتون و اکسل برای علم داده

احتمالاً می‌دانید که اکسل یک برنامه صفحه گسترده (spreadsheet) توسعه یافته از سوی مایکروسافت است. شما می‌توانید از این برنامه به سادگی به عنوان ابزاری برای سازماندهی، تحلیل و ذخیره‌سازی داده‌هایتان در جدول‌های مختلف استفاده کنید. به علاوه این نرم‌افزار به طور گسترده‌ای در حوزه‌های مختلف در سراسر جهان مورد استفاده قرار می‌گیرد و چه دوست داشته باشید یا نداشته باشید، زمینه علم داده نیز جزو همین حوزه‌ها محسوب می‌شود.

فیلم آموزش کتابخانه های پایتون برای کار با اکسل در فرادرس

کلیک کنید

شما بالاخره زمانی نیاز خواهید داشت که با این اسپردشیت‌ها سر و کار داشته باشید؛ اما ممکن است دوست نداشته باشید همه فرایند تحلیل داده را روی آن‌ها انجام دهید. به همین دلیل توسعه‌دهندگان پایتون روش‌هایی برای خواندن، نوشتن و دستکاری این فایل‌ها و همچنین انواع دیگری از فایل‌ها تدارک دیده‌اند.

در این راهنما روش‌هایی برای استفاده همزمان از اکسل و پایتون به شما آموزش می‌دهیم. بدین منظور مروری خواهیم داشت بر بسته‌هایی که می‌توانید برای بارگذاری و نوشتن این اسپردشیت‌ها با استفاده از پایتون داشته باشید. همچنین یاد می‌گیرید که چگونه از بسته‌هایی مانند pandas، openpyxl، xlrd، xlutils و pyexcel استفاده کنید.

داده‌ها به عنوان نقطه آغاز کار

زمانی که شروع به ایجاد یک پروژه علم داده می‌کنید، در اغلب موارد داده‌هایی در اختیار دارید که یا از طریق وب اسکراپینگ به دست آورده‌اید و یا به احتمال بیشتر از مجموعه داده‌هایی استفاده می‌کنید که از جاهای مختلف مانند Kaggle، Quandl و غیره دانلود کرده‌اید.

اما به طور معمول داده‌ها را از طریق گوگل یا ریپازیتوری‌های که کاربران دیگر به اشتراک می‌گذارند به دست می‌آورید. این داده‌ها ممکن است در قالب فایل‌های اکسل باشند یا با پسوند CSV. ذخیره شده باشند. در برخی موارد وضعیت‌های احتمالی داده‌ها، واقعاً بی‌نهایت هستند. اما داده‌های خود را از هر کجا که تأمین می‌کنید، نخستین گام این است که مطمئن شوید داده‌هایتان کیفیت لازم را دارند.

در مورد اسپردشیت، می‌بایست تأیید کنید که داده‌ها دارای کیفیت مورد نیاز هستند، زیرا نه تنها باید بررسی کنید که داده‌ها می‌توانند به سؤال مورد تحقیق پاسخ دهند؛ بلکه باید مطمئن شود که داده‌هایی که در اسپردشیت ذخیره شده‌اند قابل اعتماد هستند.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

بررسی کیفیت اسپردشیت

• برای بررسی کیفیت اسپردشیت باید چک لیست زیر را بررسی کنید:
• آیا اسپردشیت داده‌های استاتیک را نمایش می‌دهد؟
• آیا اسپردشیت مخلوطی از داده، محاسبات و گزارش است؟
• آیا داده‌های داخل اسپردشیت کامل و یکپارچه هستند؟
  • آیا اسپردشیت شما ساختار ورک‌شیت سامان‌مندی دارد؟
  • آیا بررسی کرده‌اید که فرمول‌های زنده داخل اسپردشیت معتبر هستند؟

این فهرست از سؤالات تضمین می‌کنند که اسپردشیت مورد بررسی، قواعدی که به عنوان رویه مناسب در این زمینه تثبیت شده‌اند را دارند. البته فهرست فوق چندان جامع نیست و قواعد کلی زیاد دیگری نیز وجود دارند که باید رعایت شوند تا مطمئن شویم اسپردشیت دچار نقص نیست. با این حال، سؤالاتی که در بالا ارائه شده‌اند، زمانی که می‌خواهید اطمینان حاصل کنید اسپردشیت مورد بررسی کیفیت مناسبی دارد یا نه، جزو مرتبط‌ترین سؤالات محسوب می‌شوند.

رویه‌های مناسب برای داده‌های صفحات گسترده

پیش از آن که راهنمای کاربرد اسپردشیت‌ها یا همان صفحات گسترده در پایتون را بخوانید، باید روی فایل موردِ بررسی خود برخی اصلاحات را انجام دهید تا با موارد زیر هماهنگ باشد:

• ردیف نخست اسپردشیت معمولاً به عنوان هدر ذخیره می‌شود؛ در حالی که ستون نخست برای شناسایی واحدهای نمونه‌برداری کاربرد دارد.
• از نام‌ها، مقادیر و فیلدهای خالی پرهیز کنید. در غیر این صورت هر کلمه می‌تواند به عنوان یک متغیر جداگانه محسوب شود که موجب می‌شود خطاهایی در ارتباط با تعداد عناصر در هر خط از مجموعه داده ایجاد شود. بدین منظور می‌توانید از زیرخط، خط فاصله، نام‌گذاری شتری (یعنی حرف اول هر بخش از متن را با حروف بزرگ بنویسید) و کلمات به هم پیوسته استفاده کنید.
• نام‌های کوتاه‌تر نسبت به نام‌های بلند ارجحیت دارند.
• از نام‌هایی که شامل نمادهایی مانند، $،%، ^، &، *، (،)،-،#،?،،،<،>، /، |، \، [،]،{، و } هستند، استفاده نکنید.
• هر گونه کامنت که در فایل ایجاد کرده‌اید را حذف کنید تا از اضافه شدن ستون‌های بی‌مورد یا موارد NA خودداری کنید و
• مطمئن شوید که همه مقادیر جا افتاده در مجموعه داده با NA علامت‌گذاری شده‌اند.

در مرحله بعد، پس از این که تغییرات لازم را ایجاد کردید یا وقتی به طور جامعی داده‌ها را بررسی نمودید، باید مطمئن شوید تغییراتی که ایجاد کردید را ذخیره نموده‌اید. بدین ترتیب می‌توانید داده‌ها را در موارد بعدی نیز مورد بازبینی و ویرایش قرار دهید، موارد جدید اضافه کنید یا آن‌ها را تغییر دهید و همزمان فرمول‌هایی که برای محاسبه داده‌ها وارد نموده‌اید را نیز حفظ کنید.

اگر از مایکروسافت اکسل استفاده می‌کنید، متوجه می‌شوید که گزینه‌های زیادی برای ذخیره‌سازی فایل‌ها دارید. به طور پیش‌فرض پسوند xls. یا xslx. برای ذخیره‌سازی استفاده می‌شود. بدین منظور می‌توانید به برگه File بروید و روی Save AS کلیک کنید و یکی از پسوندهایی که به صورت Save as Type تعیین شده‌اند را انتخاب نمایید. پسوندهایی که بیشترین کاربرد را برای ذخیره مجموعه داده‌ها دارند شامل csv. و txt. هستند. بسته به گزینه ذخیره‌سازی که انتخاب می‌کنید، فیلدهای مجموعه داده شما با علامت tab یا کاما از هم جدا خواهند شد که کاراکترهای جداساز فیلد را تشکیل می‌دهند.

اینک که داده‌های خود را بررسی و ذخیره کردید، می‌توانید شروع به آماده‌سازی فضای کاری خود بکنید.

آماده‌سازی فضای کاری

آماده‌سازی فضای کاری یکی از نخستین چیزهایی است که می‌توانید انجام دهید تا مطمئن شوید که کار خود را به سادگی آغاز کنید. نخستین مرحله، بررسی دایرکتوری کاری است. وقتی مشغول کار روی ترمینال هستید، می‌توانید ابتدا به دایرکتوری که فایل در آن قرار دارد رفته و سپس پایتون را آغاز کنید. این بدان معنی است که باید مطمئن شوید که فایل شما در آن دایرکتوری که در آن کار می‌کنید قرار دارد. اما شاید مهم‌تر از آن، این است که اگر قبلاً نشست پایتون خود را آغاز کرده باشید، هیچ سرنخی در مورد دایرکتوری که در آن کار می‌کنید نخواهید داشت و باید دستورهای زیر را اجرا کنید:

# Import `os` 
import os

# Retrieve current working directory (`cwd`)
cwd = os.getcwd()
cwd

# Change directory 
os.chdir("/path/to/your/folder")

# List all files and directories in current directory
os.listdir('.')

می‌بینید که این دستورها نه تنها برای بارگذاری داده‌ها بلکه برای تحلیل‌های آتی نیز ضروری هستند. اما فعلاً به ادامه فرایند خود می‌پردازیم. بررسی می‌کنیم که آیا داده‌های خود را ذخیره‌سازی کرده و فضای کاری را آماده‌سازی کرده‌ایم یا نه.

نصب بسته‌ها برای خواندن و نوشتن فایل‌های اکسل

متأسفانه هنوز یک کار دیگر مانده است که باید انجام دهیم. با این که هنوز ایده‌ای از بسته‌هایی که برای ایمپورت کردن داده‌ها لازم هستند نداریم؛ اما باید مطمئن شویم که همه چیز برای نصب این بسته‌ها در زمان مورد نظر آماده است.

آماده‌سازی های دیگر برای محیط کاری: pip

به همین دلیل باید pip و setuptools را نصب کرده باشید. اگر نسخه پایتون 2 شما بالاتر یا برابر با 2.7.9 و یا اگر نسخه پایتون 3 شما بالاتر یا برابر با 3.4 است، نیازی نیست تا نگران باشید، زیرا به طور معمول این بسته‌ها را دارید. در چنین مواردی کافی است پایتون را به آخرین نسخه به‌روزرسانی کنید.

بدین منظور باید دستور زیر را در ترمینال اجرا کنید:

# For Linux/OS X
pip install -U pip setuptools

# For Windows
python -m pip install -U pip setuptools

اگر هنوز pip را نصب نکرده‌اید، اسکریپت python get-pip.py را از این صفحه دانلود کرده و اجرا کنید. به علاوه در صورتی که به کمک بیشتری برای نصب صحیح همه چیز نیاز دارید، باید از دستورالعمل‌های نصب که در آن صفحه ارائه شده‌اند پیروی کنید.

نصب Anaconda

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

این وضعیت به طور خاص در مواردی که در زمینه زبان برنامه‌نویسی پایتون یک مبتدی هستید و همچنین حتی در صورتی که توسعه‌دهنده جدی‌تری محسوب می‌شوید، مفید خواهد بود، زیرا بدین ترتیب می‌توانید سریعاً بدون نیاز به تست بسته‌های مختلف، همه چیز را آماده کار بسازید.

آناکوندا، 100 مورد از محبوب‌ترین بسته‌های پایتون، R و اسکالا را برای کاربردهای علم داده در خود جای داده است و محیط‌های دیگر توسعه متن-باز همچون ژوپیتر (Jupyter) و اسپایدر (Spyder) را نیز عرضه می‌کند. اگر می‌خواهید کار با نت‌بوک ژوپیتر را آغاز کنید، می‌توانید از مطلب «راه‌اندازی نت‌بوک ژوپیتر با پایتون ۳» استفاده کنید.

با مراجعه به این صفحه آناکوندا را نصب کنید. با پیگیری دستورالعمل‌های نصب، آماده شروع به کار خواهیم شد.

بارگذاری فایل‌های اکسل به عنوان دیتافریم‌های Pandas

این بارگذاری تنها کاری است که برای راه‌اندازی محیط کار خود نیاز دارید. اینک باید شروع به ایمپورت کردن فایل‌های خود بکنیم. یکی از روش‌هایی که بدین منظور غالباً استفاده می‌شود کمک گرفتن از بسته‌های Pandas است. کتابخانه Pandas بر مبنای NumPy ساخته شده است و ساختارها و ابزارهای تحلیل داده با کاربری آسان را برای زبان برنامه‌نویسی پایتون ارائه می‌کند.

فیلم آموزش کتابخانه های پایتون برای کار با اکسل در فرادرس

کلیک کنید

این کتابخانه قدرتمند و انعطاف‌پذیر به طور مکرر از سوی علاقه‌مندان علم داده برای دریافت داده‌ها در ساختارهای داده‌ای که برای تحلیل‌هایشان کاملاً گویا هستند مورد استفاده قرار می‌گیرد.

اگر Pandas را از طریق نصب آناکوندا روی سیستم خود دارید، می‌توانید فایل‌ها را روی دیتافریم‌های آن با دستور ()pd.Excelfile بارگذاری کنید:

# Import pandas
import pandas as pd

# Assign spreadsheet filename to `file`
file = 'example.xlsx'

# Load spreadsheet
xl = pd.ExcelFile(file)

# Print the sheet names
print(xl.sheet_names)

# Load a sheet into a DataFrame by name: df1
df1 = xl.parse('Sheet1')

اگر آناکوندا را نصب نکرده‌اید، می‌توانید با دستور pip install pandas بسته Pandas را در محیط خود نصب کنید و دستورهایی که در قطعه کد فوق ارائه شده‌اند را اجرا نمایید. می‌بینید که کار کاملاً ساده‌ای است.

برای خواندن فایل‌های csv. باید با تابع مشابهی به نام ()read_csv که داده‌ها را در دیتافریم بارگذاری می‌کند آشنا باشید. در ادامه مثالی از چگونگی استفاده از این تابع را ملاحظه می‌کنید:

# Import pandas
import pandas as pd

# Load csv
df = pd.read_csv("example.csv")

کاراکتر جداسازی (delimiter) که این تابع به طور پیش‌فرض استفاده می‌کند کاما است؛ اما شما می‌توانید کاراکترهای جداساز دیگری را در صورت نیاز تعیین کنید. در مستندات این کتابخانه می‌توانید با آرگومان‌های دیگر این تابع بیشتر آشنا شوید.

توجه داشته باشید که تابع‌های ()read_table و ()read_fwf نیز برای خواندن فایل‌های دارای کاراکترهای جداساز و جدول‌های قالب‌بندی شده با خطوط ثابت و قرار دادن داده‌ها در دیتافریم استفاده می‌شوند. در تابع اول کاراکتر جداساز پیش‌فرض tab است؛ اما می‌توانید کاراکتر دیگری تعیین کنید. به علاوه تابع‌های دیگری نیز هستند که می‌توانید برای دریافت داده‌ها به صورت دیتافریم استفاده کنید که در این لینک (+) بیشتر توضیح داده شده‌اند.

شیوه نوشتن دیتافریم‌های Pandas در فایل‌های اکسل

پس از این که داده‌های خود را مورد تحلیل قرار دادید، لازم است که آن‌ها را مجدداً در یک فایل جدید بنویسید. تابع ()to_excel روشی برای این کار محسوب می‌شود. اما پیش از استفاده از این تابع اگر می‌خواهید داده‌های خود را در چند ورک‌شیت در فایل xslx. ذخیره کنید، باید اطمینان حاصل کنید که XlsxWriter نصب شده است:

# Install `XlsxWriter` 
pip install XlsxWriter

# Specify a writer
writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')

# Write your DataFrame to a file     
yourData.to_excel(writer, 'Sheet1')

# Save the result 
writer.save()

دقت کنید که در قطعه کد فوق باید از شیء ExcelWriter برای خروجی دادن دیتافریم بهره بگیرید.

اگر بخواهیم به طرز متفاوتی عمل کنیم، باید متغیر writer را به تابع ()to_excel ارسال کنیم و نام شیت را در آن قید کنیم. بدین منظور باید یک شیت را به همراه داده‌ها به یک ورک‌بوک موجود اضافه کنیم. شما می‌توانید از ExcelWriter برای ذخیره چندباره دیتافریم‌ها با (اندکی) تفاوت در یک ورک‌بوک استفاده کنید.

همه این‌ها بدین معنی است که اگر بخواهید یک دیتافریم را در یک فایل ذخیره کنید، می‌توانید اقدام به نصب بسته XlsxWriter بکنید. سپس کافی است آرگومان engine را که به تابع ()pd.ExcelWriter ارسال می‌شود تعیین نکنید. بقیه مراحل به صورت مشابه است.

همانند تابع‌هایی که برای خواندن فایل‌های csv. استفاده می‌کنیم، می‌توانیم از تابع ()to_csv برای نوشتن نتایج در یک فایل با قالب‌بندی جداساز کاما نیز استفاده کنیم. در این مورد نیز کار به مقدار زیادی مشابه زمانی است که می‌خواهیم فایل را بخوانیم:

# Write the DataFrame to csv
df.to_csv("example.csv")

اگر می‌خواهید یک فایل جدا شده با tab داشته باشید، می‌توانید مقدار t/ را به آرگومان sep ارسال کنید تا بدین ترتیب عمل شود. توجه کنید که تابع‌های دیگری نیز وجود دارند که می‌توان برای ذخیره خروجی در فایل استفاده کرد. همه این توابع را می‌توانید در این لینک (+) ملاحظه کنید.

بسته‌هایی برای تجزیه فایل‌های اکسل و نوشتن دوباره آن‌ها با پایتون

علاوه بر بسته Pandas که در موارد زیادی برای بارگذاری داده‌ها استفاده می‌شود، می‌توان از بسته‌های دیگر نیز برای دریافت داده‌ها در پایتون استفاده کرد. در ادامه شیوه استفاده از این بسته‌ها را به کمک برخی مثال‌های عملی اما ساده بررسی می‌کنیم.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

استفاده از محیط‌های مجازی

توصیه کلی برای نصب این بسته‌ها این است که آن‌ها را در یک محیط مجازی پایتون بدون بسته‌های سیستمی نصب کنید. شما می‌توانید از virtualenv برای ایجاد محیط‌های ایزوله پایتون استفاده کنید، چون در این روش پوشه‌هایی ایجاد می‌شوند که شامل همه فایل‌های اجرایی مورد نیاز برای استفاده از بسته‌هایی هستند که پروژه پایتون به آن‌ها نیاز دارد.

برای آغاز کار با virtualenv ابتدا باید آن را نصب کنیم. سپس به آن دایرکتوری که قرار است پروژه‌ها را در آن قرار دهیم می‌رویم. یک virtualenv در این پوشه ایجاد کرده و در نسخه خاص پایتون بارگذاری می‌کنیم سپس محیط مجازی را فعال می‌کنیم. پس از آن می‌توانیم شروع به بارگذاری بسته‌های دیگر و آغاز به کار با آن‌ها بکنیم.

فراموش نکنید که وقتی کارتان تمام شد محیط مجازی را غیرفعال بکنید.

# Install virtualenv
$ pip install virtualenv

# Go to the folder of your project
$ cd my_folder

# Create a virtual environment `venv`
$ virtualenv venv

# Indicate the Python interpreter to use for `venv`
$ virtualenv -p /usr/bin/python2.7 venv

# Activate `venv`
$ source venv/bin/activate

# Deactivate `venv`
$ deactivate

توجه داشته باشید که محیط مجازی ممکن است در ابتدا یعنی زمانی که شروع به طراحی پروژه علم داده خود در پایتون می‌کنید، کمی مشکل به نظر برسد. این وضعیت به طور خاص در مواردی که تنها یک پروژه در ذهن دارید مشهودتر است و ممکن است دقیقاً ندانید که اساساً چرا به یک محیط مجازی نیاز دارید. اما با در نظر گرفتن میزان سهولتی که هنگام اجرای همزمان چند پروژه که قصد ندارید در آن‌ها از یک نصب پایتون استفاده کنید به دست می‌آید، متوجه ارزش این کار می‌شوید. همچنین هنگانی که الزامات پروژه‌ها با هم در تعارض هستند، محیط مجازی راه نجات شما خواهد بود.

اینک می‌توانیم شروع به نصب و ایمپورت کردن بسته‌هایی بکنیم که داده‌های اسپردشیت‌ها را خوانده و بارگذاری می‌کنند.

چگونه فایل‌های اکسل را با استفاده از openpyxl بخوانیم و بنویسیم

این بسته به طور کلی در مواردی که قصد دارید فایل‌های xlsx، xlsm، xltx و xltm را خوانید یا بنویسید توصیه می‌شود. شما می‌توانید openpyxl را با استفاده از pip نصب کنید. فرایند انجام این کار را در بخش قبل دیدیم.

توصیه کلی برای نصب این بسته آن است که این کار را در یک محیط مجازی پایتون بدون بسته‌های سیستمی انجام دهید. شما می‌توانید از محیط مجازی برای ایجاد محیط‌های ایزوله استفاده کنید. بدین ترتیب پوشه‌ای ایجاد می‌شود که شامل همه فایل‌های اجرایی ضروری برای بسته‌های مورد استفاده در یک پروژه پایتون است.

به آن دایرکتوری که پروژه‌تان در آن واقع شده است بروید و محیط مجازی venv را مجدداً فعال کنید. سپس اقدام به نصب openpyxl با استفاده از pip بکنید تا مطمئن شوید که می‌توانید فایل‌ها را به کمک آن بخوانید و بنویسید.

# Activate virtualenv
$ source activate venv

# Install `openpyxl` in `venv`
$ pip install openpyxl

اینک که openpyxl نصب شده است می‌توانید شروع به بارگذاری داده‌ها بکنید. اما منظور ما از داده دقیقاً چیست؟ ورک‌بوک دارای داده که تلاش می‌کنید وارد پایتون بکنید شیت های زیر را دارد:

همان طور که می‌بینید تابع ()load_workbook نام فایل را به عنوان آرگومان می‌گیرد و یک شیء workbook بازمی‌گرداند که نشان دهنده فایل است. این وضعیت را با استفاده از (type(wb می‌توان بررسی کرد. به خاطر داشته باشید که باید اطمینان حاصل کنید که در همان دایرکتوری که اسپردشیت قرار دارد، هستید. در غیر این صورت هنگام ایمپورت کردن با خطا مواجه می‌شوید.

# Import `load_workbook` module from `openpyxl`
from openpyxl import load_workbook

# Load in the workbook
wb = load_workbook('./test.xlsx')

# Get sheet names
print(wb.get_sheet_names())

همچنین به خاطر داشته باشید که می‌توانید با استفاده از ()os.chdir دایرکتوری کاری را تغییر دهید.

می‌بینید که قطعه کد فوق نام شیت‌های ورک‌بوک را که در پایتون بارگذاری شده‌اند بازمی‌گرداند. سپس می‌توانید از این اطلاعات برای بازیابی شیت‌های جداگانه این ورک‌بوک استفاده کنید.

همچنین می‌توانید به کمک wb.active بررسی کنید که کدام شیت در حال حاضر فعال است. همان طور که در کد زیر می‌بینید، می‌توان از این دستور برای بارگذاری یک شیت دیگر از ورک‌بوک نیز استفاده کرد.

# Get a sheet by name 
sheet = wb.get_sheet_by_name('Sheet3')

# Print the sheet title 
sheet.title

# Get currently active sheet
anotherSheet = wb.active

# Check `anotherSheet` 
anotherSheet

با استفاده از این شیءهای Workbook دیگر نمی‌توان در نگاه نخست کار زیادی انجام داد با این وجود می‌توانیم مقادیر مختلف را با استفاده از براکت [ ] از سلول‌های مشخصی در شیت ورک‌بوک بازیابی کنیم. در این براکت می‌توانید سلول دقیقی که می‌خواهید مقدارش را بخوانید تعیین نمایید.

توجه کنید که این فرایند بسیار شبیه انتخاب کردن، دریافت و اندیس‌گذاری آرایه‌های NymPy و دیتافریم‌های Pandas است، با این وجود، برای دریافت یک مقدار باید کارهای دیگری نیز انجام دهید، چون شما باید خصوصیت value را نیز اضافه کنید:

# Retrieve the value of a certain cell
sheet['A1'].value

# Select element 'B2' of your sheet 
c = sheet['B2']

# Retrieve the row number of your element
c.row

# Retrieve the column letter of your element
c.column

# Retrieve the coordinates of the cell 
c.coordinate

همان طور که می‌بینید علاوه بر value خصوصیت‌های دیگری نیز هستند که می‌توان برای بررسی یک سلول مورد استفاده قرار دارد. از آن جمله می‌توان به row، column و coordinate اشاره کرد.

• خصوصیت row مقدار 2 بازمی‌گرداند
• افزودن خصوصیت ستون column به c باعث بازگشت مقدار ‘B’ می‌شود و
• خصوصیت coordinate مقدار ‘B2’ را بازمی‌گرداند.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

همچنین می‌توانید مقادیر یک سلول را با استفاده از تابع ()call بازیابی کنید. آرگومان‌های row و column را ارسال کنید و مقادیر را به این آرگومان‌ها اضافه کنید چون متناظر با مقادیر سلولی است که می‌خواهید بازیابی کنید. ضمناً فراموش نکنید که خصوصیت value را نیز اضافه کنید:

# Retrieve cell value 
sheet.cell(row=1, column=2).value

# Print out values in column 2 
for i in range(1, 4):
     print(i, sheet.cell(row=i, column=2).value)

توجه داشته باشید که اگر خصوصیت value را تعیین نکرده باشید، مقدار <Cell Sheet3.B1> بازگشت می‌یابد که هیچ اطلاعاتی در مورد این که مقدار درون سلول چیست ارائه نمی‌کند.

می‌بینید که از حلقه for برای کمک به تابع ()range برای اشاره به مقادیر ردیف‌هایی که در ستون 2 مقداری دارند استفاده شده است. اگر این سلول‌های خاص خالی باشند، در این صورت مقدار none بازگشت می‌یابد. اگر می‌خواهید در مورد حلقه‌های for در پایتون بیشتر بدانید، می‌توانید از «آموزش برنامه‌نویسی پایتون» استفاده کنید.

به علاوه تابع‌های خاصی مانند ()get_column_letter و column_index_from_string در پایتون وجود دارند که می‌توانید برای دریافت مقادیر خاص دیگر از آن‌ها استفاده کنید.

نام دو تابع فوق کمابیش نشان می‌دهند که با آن‌ها چه مقادیری را می‌توان دریافت کرد؛ اما اگر بخواهیم قضیه را روشن‌تر بکنیم، باید بگوییم که تابع اول اجازه می‌دهد شما حرف عنوان ستون را دریافت کنید و با استفاده از تابع دوم به طور معکوس، می‌توانید اندیس یک ستون را با ارسال حرف آن به دست بیاورید. طرز کار آن را در مثال زیر مشاهده می‌کنید:

# Import relevant modules from `openpyxl.utils`
from openpyxl.utils import get_column_letter, column_index_from_string

# Return 'A'
get_column_letter(1)

# Return '1'
column_index_from_string('A')

شما قبلاً مقادیر ردیف‌هایی که در ستون خاصی مقدار دارند را دریافت کرده‌اید؛ اما اگر بخواهید ردیف‌های فایل خود را بدون تمرکز صرف روی ستون‌ها نمایش دهید چه باید بکنید؟

در این مورد نیز می‌توان از یک حلقه کمک گرفت. برای مثال فرض کنید می‌خواهیم روی ناحیه‌ای که بین A1 و C3 قرار دارد تمرکز کنیم. دقت کنید که سلول اول گوشه سمت چپ-بالا و سلول دوم گوشه سمت راست-پایین ناحیه مورد نظر را تعیین می‌کند.

این ناحیه همان cellObj است که در خط نخست کد زیر می‌بینید. همچنین فرض کنید می‌خواهیم برای هر سلول که در این ناحیه قرار دارد، مختصات و مقدار درون سلول را نمایش دهیم و پس از رسیدن به ردیف نهایی این پیام نمایش یابد که ردیف cellObj نمایش یافته است.

# Print row per row
for cellObj in sheet['A1':'C3']:
      for cell in cellObj:
              print(cells.coordinate, cells.value)
      print('--- END ---')

در این مورد نیز دقت داشته باشید که انتخاب ناحیه کاملاً شبیه انتخاب، دریافت و اندیس‌گذاری عناصر آرایه NymPy است و از براکت و دونقطه (:) برای نمایش ناحیه‌ای که باید مقادیرش را دریافت کنیم، استفاده می‌شود. به علاوه حلقه فوق به خوبی کاربرد خصوصیت‌های سلول را نمایش می‌دهد. برای این که نتیجه عملی اجرای کد فوق را ببینید باید آن را اجرا کرده و خروجی را هنگامی که اجرای حلقه پایان می‌یابد، مشاهده کنید:

('A1', u'M')
('B1', u'N')
('C1', u'O')
--- END ---
('A2', 10L)
('B2', 11L)
('C2', 12L)
--- END ---
('A3', 14L)
('B3', 15L)
('C3', 16L)
--- END ---

در نهایت باید گفت برخی خصوصیت‌ها مانند max_row و max_column هستند که می‌توانید برای بررسی نتیجه ایمپورت خود استفاده کنید. البته این خصوصیت‌ها روشی کلی برای اطمینان یافتن از بارگذاری صحیح داده‌ها هستند؛ اما در هر صورت می‌توانند مفید واقع شوند.

# Retrieve the maximum amount of rows 
sheet.max_row

# Retrieve the maximum amount of columns
sheet.max_column

همه مواردی که معرفی کردیم مناسب به نظر می‌رسند؛ اما شاید با خود فکر کنید که این روشی بسیار دشوار برای کار با این فایل‌ها محسوب می‌شود به خصوص وقتی که بخواهد آن‌ها را دستکاری نیز بکنید. شاید فکر می‌کنید باید راه آسان‌تری نیز وجود داشته باشد. البته حق با شما است. openpyxl از دیتافریم‌های Pandas نیز پشتیبانی می‌کند. شما می‌توانید از تابع ()DataFrame از بسته Pandas برای قرار دادن مقادیر یک شیت درون یک دیتافریم استفاده کنید:

# Import `pandas` 
import pandas as pd

# Convert Sheet to DataFrame
df = pd.DataFrame(sheet.values)

اگر می‌خواهید هدر و اندیس را نیز تعیین کنید، باید کمی کدنویسی بیشتری انجام دهید:

# Put the sheet values in `data`
data = sheet.values

# Indicate the columns in the sheet values
cols = next(data)[1:]

# Convert your data to a list
data = list(data)

# Read in the data at index 0 for the indices
idx = [r[0] for r in data]

# Slice the data at index 1 
data = (islice(r, 1, None) for r in data)

# Make your DataFrame
df = pd.DataFrame(data, index=idx, columns=cols)

سپس می‌توانید شروع به دستکاری داده‌ها با تابع‌هایی بکنید که بسته Pandas ارائه می‌کند. اما به خاطر داشته باشید که شما در یک محیط مجازی هستید و از این رو اگر بسته هنوز وجود ندارد باید آن را مجدداً با pip نصب کنید.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

برای نوشتن مجدد دیتافریم‌های Pandas در فایل اکسل می‌توانید به سادگی از تابع ()dataframe_to_rows در ماژول utils استفاده کنید:

# Import `dataframe_to_rows`
from openpyxl.utils.dataframe import dataframe_to_rows

# Initialize a workbook 
wb = Workbook()

# Get the worksheet in the active workbook
ws = wb.active

# Append the rows of the DataFrame to your worksheet
for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

البته این‌ها قطعاً همه ظرفیت openpyxl نیستند. این بسته انعطاف‌پذیری زیادی در مورد روش نوشتن مجدد فایل‌های اکسل و تغییر دادن سبک ستون‌ها با استفاده از حالت write-only ارائه می‌کند که آن را به یکی از بسته‌هایی تبدیل می‌کند که هنگام کار با اسپردشیت ها حتماً باید نصب کنید.

برای مطالعه بیشتر در مورد روش‌های تغییر دادن سبک‌های سلول‌ها، تغییر دادن به حالت write-only یا طرز کار با NymPY می‌توانید از این لینک (+) استفاده کنید. اینک به بررسی بسته‌های دیگری که می‌توان برای کار با داده‌های اسپردشیت در پایتون مورد استفاده قرار داد می‌پردازیم. پیش از پایان این بخش باید اشاره کنیم که هیچ گاه پس از پایان کارتان، غیرفعال کردن محیط مجازی پایتون را فراموش نکنید.

خواندن و قالب‌بندی فایل‌های اکسل: xlrd

این بسته در صورتی که بخواهید داده‌ها را از فایل‌های با پسوند .xls یا xlsx. خوانده و قالب‌بندی کنید مناسب خواهد بود.

# Import `xlrd`
import xlrd

# Open a workbook 
workbook = xlrd.open_workbook('example.xls')

# Loads only current sheets to memory
workbook = xlrd.open_workbook('example.xls', on_demand = True)

وقتی نمی‌خواهید کل ورک‌بوک را بررسی کنید، می‌توانید از تابع‌هایی مانند ()sheet_by_name یا ()sheet_by_index برای بازیابی شیت‌هایی مورد نظر در تحلیل‌های خود استفاده کنید.

# Load a specific sheet by name
worksheet = workbook.sheet_by_name('Sheet1')

# Load a specific sheet by index 
worksheet = workbook.sheet_by_index(0)

# Retrieve the value from cell at indices (0,0) 
sheet.cell(0, 0).value

در نهایت باید اشاره کنیم همچنان که می‌بینید شما می‌توانید مقدار را در مختصات معینی که با اندیس‌ها تعیین می‌شوند از شیت بازیابی کنید. برای مطالعه بیشتر در مورد xlwt و xlutils می‌توانید از این لینک (+) استفاده کنید.

نوشتن داده‌ها در فایل‌های اکسل با استفاده از xlwt

اگر می‌خواهید اسپردشیت‌هایی بسازید که داده‌هایتان را در آن‌ها قرار دهید، می‌توانید علاوه بر بسته XlsxWriter، از بسته xlwt استفاده کنید. بسته xlwt برای نوشتن داده‌ها و قالب‌بندی اطلاعات در فایل‌هایی با پسوند xls. مناسب است. فرایند نوشتن فایل در این بسته مانند حالتی است که هنگام نوشتن دستی فایل وجود دارد:

# Import `xlwt` 
import xlwt

# Initialize a workbook 
book = xlwt.Workbook(encoding="utf-8")

# Add a sheet to the workbook 
sheet1 = book.add_sheet("Python Sheet 1") 

# Write to the sheet of the workbook 
sheet1.write(0, 0, "This is the First Cell of the First Sheet") 

# Save the workbook 
book.save("spreadsheet.xls")

اگر می‌خواهید داده‌ها را دریک فایل بنویسید، اما نمی‌خواهید همه مراحل را دستی انجام دهید، می‌توانید از حلقه for برای خودکارسازی بیشتر فرایند کمک بگیرید. در این حالت می‌توانید یک اسکریپت بنویسید که در آن یک ورک‌بوک مقداردهی اولیه می‌شود و سپس به آن یک شیت اضافه کنید. در ادامه فهرستی از ستون‌ها تعیین کرده و ستون مورد نظرتان را در ورک‌بوک با مقادیر دلخواه پر کنید.

سپس باید یک حلقه for داشته باشید تا مطمئن شوید که همه مقادیر در فایل نوشته شده‌اند. فرض کنید در مورد همه مقادیر در محدوده 0 تا 4 (5 شامل نمی‌شود) قرار است کار خاصی را انجام دهید. ابتدا باید مقادیر را ردیف به ردیف پر کنید. بدین منظور باید یک عنصر row تعیین کنید که در هر بار اجرای حلقه تغییر می‌کند. سپس باید حلقه دیگری داشته باشید که روی ستون‌های شیت می‌چرخد. برای مثال برای هر ردیف در شیت به ستون‌های مربوط به آن ردیف رفته و مقداری را در ستون آن ردیف می‌نویسید. زمانی که همه ستون‌های ردیف پر شد، به ردیف دیگر می‌روید تا این که هیچ ردیفی باقی نماند.

# Initialize a workbook
book = xlwt.Workbook()

# Add a sheet to the workbook
sheet1 = book.add_sheet("Sheet1")

# The data
cols = ["A", "B", "C", "D", "E"]
txt = [0,1,2,3,4]

# Loop over the rows and columns and fill in the values
for num in range(5):
      row = sheet1.row(num)
      for index, col in enumerate(cols):
          value = txt[index] + num
          row.write(index, value)

# Save the result
book.save("test.xls")

در تصویر زیر می‌توانید نتیجه اجرای کد فوق را ملاحظه کنید:

اینک که با روش استفاده همزمان از xlrd و xlwt آشنا شدید، زمان آن رسیده است که نگاهی به بسته xlutils داشته باشیم که ارتباط نزدیکی با دو بسته فوق دارد.

مجموعه‌ای ابزارهای کاربردی: xlutils

این بسته اساساً مجموعه‌ای از ابزارهای کاربردی است که به هر دو بسته xlrd و xlwt نیاز دارد و می‌تواند فایل‌های موجود را کپی، اصلاح یا فیلتر کند. البته این کاربردها اینک از سوی openpyxl ارائه می‌شوند. اگر به بخش openpyxl در قسمت‌های قبلی این مقاله مراجعه کنید، می‌توانید اطلاعات بیشتری در مورد طرز کار این بسته برای دریافت داده‌ها در پایتون به دست آورید.

استفاده از pyexcel برای خواندن فایل‌های xls. یا xlsx.

بسته دیگری که برای خواندن داده‌های اسپردشیت در پایتون استفاده می‌شود pyexcel نام دارد. این بسته در واقع پوسته پایتونی است که یک API برای خواندن، دستکاری و نوشتن داده‌ها در فایل‌های csv، .ods، .xls، .xlsx. و xlsm. ارائه می‌دهد. البته در این راهنما ما صرفاً روی فایل‌های xls. و xlsx. متمرکز شده‌ایم.

فیلم آموزش کتابخانه های پایتون برای کار با اکسل در فرادرس

کلیک کنید

برای دریافت داده‌ها در یک آرایه می‌توانید از تابع ()get_array استفاده کنید که در بسته pyexcel قرار دارد:

# Import `pyexcel`
import pyexcel

# Get an array from the data
my_array = pyexcel.get_array(file_name="test.xls")

همچنین می‌توانید داده‌ها را دریک دیکشنری مرتب از فهرست‌ها دریافت کنید. بدین منظور می‌توانید از تابع ()get_dict استفاده کنید:

# Import `OrderedDict` module 
from pyexcel._compact import OrderedDict

# Get your data in an ordered dictionary of lists
my_dict = pyexcel.get_dict(file_name="test.xls", name_columns_by_row=0)

# Get your data in a dictionary of 2D arrays
book_dict = pyexcel.get_book_dict(file_name="test.xls")

با این حال، می‌بینید که اگر بخواهیم یک دیکشنری از آرایه‌های دوبعدی بازگردانیم یا به عبارت دیگر اگر بخواهیم همه شیت های ورک‌بوک را در یک دیکشنری واحد قرار دهیم، باید از تابع ()get_book_dict استفاده کنیم.

باید بدانید که دو ساختمان داده‌ای که در بخش قبلی اشاره کردیم، یعنی آرایه‌ها و دیکشنری اسپردشیت، به شما امکان می‌دهند که با استفاده از ()pd.DataFrame، دیتافریم‌هایی از داده‌های خود بسازید. بدین ترتیب مدیریت داده‌ها تسهیل می‌شود.

در نهایت باید اشاره کرد که شما می‌توانید به کمک تابع ()get_records رکوردها را با pyexcel بازیابی کنید. کافی است آرگومان file_name را به این تابع ارسال کنید تا فهرستی از دیکشنری‌ها را در اختیار شما قرار دهد.

# Retrieve the records of the file
records = pyexcel.get_records(file_name="test.xls")

نوشتن فایل‌ها با استفاده از pyexcel

به همان مقدار که بارگذاری داده‌ها در آرایه‌ها با استفاده از این بسته آسان است، نوشتن مجدد ارائه‌ها در فایل‌های اسپردشیت نیز به سادگی صوت می‌پذیرد. با استفاده از تابع ()save_as و ارسال آرایه و نام فایل مقصد به آرگومان dest_file_name می‌توانید فایل‌های اسپردشیت را بنویسید:

# Get the data
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]

# Save the array to a file
pyexcel.save_as(array=data, dest_file_name="array_data.xls")

توجه داشته باشید که اگر می‌خواهید یک کاراکتر جداساز (delimiter) تعیین کنید، می‌توانید آرگومان dest_delimiter را اضافه کرده و نمادی که می‌خواهید به عنوان جداساز استفاده شود را درون گیومه " " ارسال کنید.

اما اگر یک دیکشنری دارید باید از تابع ()save_book_as استفاده کنید. بدین منظور باید دیکشنری دوبُعدی را به bookdict ارسال کنید و نام فایل را نیز تعیین کنید:

# The data
2d_array_dictionary = {'Sheet 1': [
                                   ['ID', 'AGE', 'SCORE']
                                   [1, 22, 5],
                                   [2, 15, 6],
                                   [3, 28, 9]
                                  ],
                       'Sheet 2': [
                                    ['X', 'Y', 'Z'],
                                    [1, 2, 3],
                                    [4, 5, 6]
                                    [7, 8, 9]
                                  ],
                       'Sheet 3': [
                                    ['M', 'N', 'O', 'P'],
                                    [10, 11, 12, 13],
                                    [14, 15, 16, 17]
                                    [18, 19, 20, 21]
                                   ]}

# Save the data to a file                        
pyexcel.save_book_as(bookdict=2d_array_dictionary, dest_file_name="2d_array_data.xls")

چیزی که باید همواره هنگام استفاده از کد فوق در خاطر داشته باشید، این است که ترتیب داده‌ها در دیکشنری حفظ نمی‌شود. اگر چنین وضعتی مطلوب شما نیست، باید تغییرات اندکی ایجاد کنید. در این مورد در این لینک (+) بیشتر می‌توانید مطالعه کنید.

خواندن و نوشتن فایل‌های csv.

اگر همچنان به دنبال بسته‌هایی به جز Pandas می‌گردید که امکان بارگذاری و نوشتن داده‌ها در فایل‌های csv. را فراهم سازد، می‌توانید از بسته csv استفاده کنید:

# import `csv`
import csv

# Read in csv file 
for row in csv.reader(open('data.csv'), delimiter=','):
      print(row)
      
# Write csv file
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
outfile = open('data.csv', 'w')
writer = csv.writer(outfile, delimiter=';', quotechar='"')
writer.writerows(data)
outfile.close()

توجه داشته باشید که بسته NumPy تابعی به نام ()genfromtxt دارد که امکان بارگذاری داده‌های درون فایل‌های csv. را در آرایه‌هایی که متعاقباً در دیتافریم‌ها قرار می‌گیرند فراهم می‌سازد. در «آموزش تکمیلی برنامه‌نویسی پایتون» در این مورد توضیحات بیشتری ارائه شده است.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

بررسی نهایی داده‌ها

هنگامی که داده‌ها در دسترس قرار گرفتند، نباید گام نهایی را فراموش کنید و آن بررسی این نکته است که آیا داده‌ها به طور صحیحی بارگذاری شده‌اند یا نه. اگر داده‌ها را در یک دیتافریم قرار داده‌اید، می‌توانید به سرعت و سهولت بارگذاری صحیح آن‌ها را با اجرای دستور زیر بررسی کنید:

# Check the first entries of the DataFrame
df1.head()

# Check the last entries of the DataFrame
df1.tail()

زمانی که قصد دارید فایل‌ها را به صورت دیتافریم‌های Pandas بارگذاری کنید، می‌توانید از «راهنمای سریع کتابخانه Numpy» استفاده کنید.

اگر داده‌هایی را در یک آرایه دارید، می‌توانید با استفاده از خصوصیات آرایه‌ای مانند shape، ndim، dtype و موارد دیگر آن‌ها را بررسی کنید:

# Inspect the shape 
data.shape

# Inspect the number of dimensions
data.ndim

# Inspect the data type
data.dtype

سخن پایانی

شما با مطالعه این راهنما تقریباً با همه مباحثی که برای کار با فایل‌های اکسل در پایتون مورد نیاز است، آشنا شده‌اید. اما باید بدانید که ایمپورت کردن داده‌ها تنها یک گام آغازی در فرایند علم داده محسوب می‌شود. زمانی که داده‌ها را از اسپردشیت ها وارد محیط خود کردید، می‌توانید روی آنچه که واقعاً مهم است، یعنی تحلیل داده‌ها متمرکز شوید. اگر داده‌هایتان را در دیتافریم‌ها بارگذاری کرده‌اید، می‌توانید به سادگی با استفاده از دستورهای Pandas آن‌ها را مورد تجزیه و تحلیل قرار دهید.

اگر این مطلب برایتان مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

• ‌مجموعه آموزش های برنامه نویسی پایتون
• آموزش برنامه نویسی پایتون – مقدماتی
• متدهای وهله‌ای، استاتیک و کلاس در پایتون — تفاوت‌های مهمی که باید بدانید
• گنجینه آموزش های اکسل (Microsoft Excel)
• آموزش پایتون (Python) — مجموعه مقالات جامع وبلاگ فرادرس
• گنجینه آموزش های برنامه نویسی پایتون (Python)
• مجموعه آموزش‌های نرم‌افزار مایکروسافت اکسل
• نصب PIP پایتون بر روی ویندوز، مک و لینوکس — از صفر تا صد

==