Chuyển từ Excel sang Python. Các chức năng cần thiết để phân tích dữ liệu SEO

Học cách viết mã, cho dù với Python, JavaScript hay ngôn ngữ lập trình khác, đều có rất nhiều lợi ích, bao gồm khả năng làm việc với tập dữ liệu lớn hơn và tự động hóa các tác vụ lặp đi lặp lại

Nhưng bất chấp những lợi ích, nhiều chuyên gia SEO vẫn chưa thực hiện chuyển đổi – và tôi hoàn toàn hiểu tại sao. Đó không phải là một kỹ năng cần thiết cho SEO và tất cả chúng ta đều là những người bận rộn

Nếu bạn bị ép thời gian và bạn đã biết cách hoàn thành một tác vụ trong Excel hoặc Google Trang tính, thì việc thay đổi chiến thuật có thể giống như phát minh lại bánh xe

Khi tôi mới bắt đầu viết mã, ban đầu tôi chỉ sử dụng Python cho các tác vụ mà tôi không thể hoàn thành trong Excel – và phải mất vài năm tôi mới biết đó là lựa chọn thực tế của tôi để xử lý dữ liệu

Nhìn lại, tôi vô cùng vui mừng vì mình đã kiên trì, nhưng đôi khi đó là một trải nghiệm khó chịu, với nhiều giờ dành cho việc quét các luồng trên Stack Overflow

Bài đăng này được thiết kế để giúp các chuyên gia SEO khác chịu chung số phận

Trong đó, chúng tôi sẽ đề cập đến các tính năng và công thức Excel tương đương được sử dụng phổ biến nhất trong Python để phân tích dữ liệu SEO – tất cả đều có sẵn trong sổ ghi chép Google Colab được liên kết trong phần tóm tắt

Cụ thể, bạn sẽ học tương đương với

• LEN
• Thả bản sao
• Văn bản thành Cột
• TÌM/TÌM
• NỐI
• Tìm và thay thế
• TRÁI/GIỮA/PHẢI
• NẾU
• NẾU
• VLOOKUP
• COUNTIF/SUMIF/AVERAGEIF
• Bảng tổng hợp

Thật ngạc nhiên, để hoàn thành tất cả những điều này, chúng tôi sẽ chủ yếu sử dụng một thư viện duy nhất – Pandas – với một chút trợ giúp từ người anh lớn của nó, NumPy

điều kiện tiên quyết

Vì mục đích ngắn gọn, có một số điều chúng tôi sẽ không đề cập đến hôm nay, bao gồm

• Cài đặt Python
• Pandas cơ bản, như nhập CSV, lọc và xem trước khung dữ liệu

Nếu bạn không chắc chắn về bất kỳ điều gì trong số này, thì hướng dẫn của Hamlet về phân tích dữ liệu Python cho SEO là tài liệu hoàn hảo

Bây giờ, không cần phải quảng cáo thêm, hãy bắt đầu

LEN

LEN cung cấp số lượng ký tự trong một chuỗi văn bản

Đối với SEO cụ thể, trường hợp sử dụng phổ biến là đo độ dài của thẻ tiêu đề hoặc mô tả meta để xác định xem chúng có bị cắt bớt trong kết quả tìm kiếm hay không

Trong Excel, nếu chúng ta muốn đếm ô thứ hai của cột A, chúng ta sẽ nhập

df2 = df.drop_duplicates(subset='column')

Python không quá khác biệt, vì chúng ta có thể dựa vào hàm len có sẵn, có thể được kết hợp với loc[] của Pandas để truy cập một hàng dữ liệu cụ thể trong một cột

len(df['Title'].loc[0])

Trong ví dụ này, chúng tôi đang lấy độ dài của hàng đầu tiên trong cột "Tiêu đề" của khung dữ liệu của chúng tôi

Tuy nhiên, việc tìm độ dài của một ô không hữu ích cho SEO. Thông thường, chúng tôi muốn áp dụng một hàm cho toàn bộ cột

Trong Excel, điều này có thể đạt được bằng cách chọn ô công thức ở góc dưới cùng bên phải và kéo ô đó xuống hoặc bấm đúp vào

Khi làm việc với khung dữ liệu Pandas, chúng ta có thể sử dụng str. len để tính độ dài của các hàng trong một chuỗi, sau đó lưu kết quả vào một cột mới

df['Length'] = df['Title'].str.len()

Str. len là một hoạt động 'vectơ hóa', được thiết kế để áp dụng đồng thời cho một loạt giá trị. Chúng tôi sẽ sử dụng rộng rãi các hoạt động này trong suốt bài viết này, vì chúng hầu như kết thúc nhanh hơn một vòng lặp

Một ứng dụng phổ biến khác của LEN là kết hợp với SUBSTITUTE để đếm số từ trong một ô

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Trong Pandas, chúng ta có thể đạt được điều này bằng cách kết hợp str. tách và str. chức năng len cùng nhau

df['No. Words'] = df['Title'].str.split().str.len()

Chúng tôi sẽ bao gồm str. phân tách chi tiết hơn sau, nhưng về cơ bản, những gì chúng tôi đang làm là phân tách dữ liệu của mình dựa trên các khoảng trắng trong chuỗi, sau đó đếm số phần thành phần

Thả các bản sao

Tính năng 'Xóa các bản sao' của Excel cung cấp một cách dễ dàng để xóa các giá trị trùng lặp trong tập dữ liệu, bằng cách xóa hoàn toàn các hàng trùng lặp (khi tất cả các cột được chọn) hoặc xóa các hàng có cùng giá trị trong các cột cụ thể

Trong Pandas, chức năng này được cung cấp bởi drop_duplicates

Để loại bỏ các hàng trùng lặp trong một loại khung dữ liệu

len(df['Title'].loc[0])

Để loại bỏ các hàng dựa trên các bản sao trong một cột duy nhất, hãy bao gồm tham số tập hợp con

len(df['Title'].loc[0])

Hoặc chỉ định nhiều cột trong danh sách

len(df['Title'].loc[0])

Một bổ sung ở trên đáng để gọi là sự hiện diện của tham số tại chỗ. Bao gồm inplace=True cho phép chúng tôi ghi đè lên khung dữ liệu hiện có của mình mà không cần tạo một khung dữ liệu mới

Tất nhiên, có những lúc chúng tôi muốn bảo tồn dữ liệu thô của mình. Trong trường hợp này, chúng ta có thể gán khung dữ liệu đã loại trừ của mình cho một biến khác

df2 = df.drop_duplicates(subset='column')

Văn bản thành cột

Một tính năng thiết yếu hàng ngày khác, tính năng 'văn bản thành cột' có thể được sử dụng để phân tách chuỗi văn bản dựa trên dấu phân cách, chẳng hạn như dấu gạch chéo, dấu phẩy hoặc khoảng trắng

Ví dụ: tách một URL thành miền của nó và các thư mục con riêng lẻ

Khi xử lý một khung dữ liệu, chúng ta có thể sử dụng str. chức năng phân tách, tạo danh sách cho từng mục trong một chuỗi. Điều này có thể được chuyển đổi thành nhiều cột bằng cách đặt tham số mở rộng thành True

len(df['Title'].loc[0])

Như thường lệ, các URL của chúng tôi trong hình trên đã được chia thành các cột không nhất quán vì chúng không có cùng số lượng thư mục

Điều này có thể khiến mọi thứ trở nên khó khăn khi chúng tôi muốn lưu dữ liệu của mình trong một khung dữ liệu hiện có

Chỉ định tham số n giới hạn số lần phân tách, cho phép chúng tôi tạo một số cột cụ thể

len(df['Title'].loc[0])

Một tùy chọn khác là sử dụng cửa sổ bật lên để xóa cột của bạn khỏi khung dữ liệu, thực hiện phân tách và sau đó thêm lại cột bằng chức năng nối

len(df['Title'].loc[0])

Sao chép URL vào một cột mới trước khi tách cho phép chúng tôi giữ nguyên URL đầy đủ. Sau đó chúng ta có thể đổi tên các cột mới. 🐆

len(df['Title'].loc[0])

NỐI

Hàm CONCAT cho phép người dùng kết hợp nhiều chuỗi văn bản, chẳng hạn như khi tạo danh sách từ khóa bằng cách thêm các công cụ sửa đổi khác nhau

Trong trường hợp này, chúng tôi đang thêm “mens” và khoảng trắng vào danh sách các loại sản phẩm của cột A

len(df['Title'].loc[0])

Giả sử chúng ta đang xử lý các chuỗi, điều tương tự cũng có thể đạt được trong Python bằng cách sử dụng toán tử số học

len(df['Title'].loc[0])

Hoặc chỉ định nhiều cột dữ liệu

len(df['Title'].loc[0])

Pandas có chức năng concat chuyên dụng, nhưng điều này hữu ích hơn khi cố gắng kết hợp nhiều khung dữ liệu với cùng một cột

Chẳng hạn, nếu chúng tôi có nhiều lần xuất từ ​​công cụ phân tích liên kết yêu thích của mình

len(df['Title'].loc[0])

TÌM/TÌM

Các công thức TÌM KIẾM và TÌM cung cấp cách định vị một chuỗi con trong một chuỗi văn bản

Các lệnh này thường được kết hợp với ISNUMBER để tạo cột Boolean giúp lọc tập dữ liệu, điều này có thể cực kỳ hữu ích khi thực hiện các tác vụ như phân tích tệp nhật ký,. e. g

len(df['Title'].loc[0])

Sự khác biệt giữa TÌM KIẾM và TÌM KIẾM là find phân biệt chữ hoa chữ thường

Hàm Pandas tương đương, str. chứa, phân biệt chữ hoa chữ thường theo mặc định

len(df['Title'].loc[0])

Không phân biệt chữ hoa chữ thường có thể được bật bằng cách đặt tham số chữ hoa chữ thường thành Sai

len(df['Title'].loc[0])

Trong cả hai trường hợp, bao gồm na=False sẽ ngăn các giá trị null được trả về trong cột Boolean

Một lợi thế lớn của việc sử dụng Pandas ở đây là, không giống như Excel, regex được chức năng này hỗ trợ nguyên bản – giống như trong Google trang tính thông qua REGEXMATCH

Xâu chuỗi nhiều chuỗi con lại với nhau bằng cách sử dụng ký tự ống dẫn, còn được gọi là toán tử OR

df['Length'] = df['Title'].str.len()

Tìm và thay thế

Tính năng “Tìm và Thay thế” của Excel cung cấp một cách dễ dàng để thay thế riêng lẻ hoặc hàng loạt một chuỗi con bằng một chuỗi con khác

Khi xử lý dữ liệu cho SEO, rất có thể chúng tôi sẽ chọn toàn bộ cột và “Thay thế tất cả. ”

Công thức SUBSTITUTE cung cấp một tùy chọn khác ở đây và rất hữu ích nếu bạn không muốn ghi đè lên cột hiện có

Ví dụ: chúng tôi có thể thay đổi giao thức của một URL từ HTTP thành HTTPS hoặc xóa giao thức đó bằng cách thay thế bằng không có gì

Khi làm việc với các khung dữ liệu trong Python, chúng ta có thể sử dụng str. thay thế

df['Length'] = df['Title'].str.len()

Hoặc

df['Length'] = df['Title'].str.len()

Một lần nữa, không giống như Excel, regex có thể được sử dụng – như với REGEXREPLACE của Google Trang tính

df['Length'] = df['Title'].str.len()

Ngoài ra, nếu bạn muốn thay thế nhiều chuỗi con bằng các giá trị khác nhau, bạn có thể sử dụng phương thức thay thế của Python và cung cấp một danh sách

Điều này giúp bạn không phải xâu chuỗi nhiều str. thay thế chức năng

df['Length'] = df['Title'].str.len()

TRÁI/GIỮA/PHẢI

Việc trích xuất một chuỗi con trong Excel yêu cầu sử dụng các hàm LEFT, MID hoặc RIGHT, tùy thuộc vào vị trí của chuỗi con trong một ô

Giả sử chúng ta muốn trích xuất tên miền gốc và tên miền phụ từ một URL

df['Length'] = df['Title'].str.len()

Sử dụng kết hợp MID và nhiều hàm FIND, ít nhất phải nói rằng công thức này rất xấu – và mọi thứ trở nên tồi tệ hơn rất nhiều đối với các trích xuất phức tạp hơn

Một lần nữa, Google Trang tính làm điều này tốt hơn Excel, vì nó có REGEXEXTRACT

Thật đáng tiếc khi bạn cung cấp cho nó bộ dữ liệu lớn hơn, nó sẽ tan chảy nhanh hơn Babybel trên bộ tản nhiệt nóng

Rất may, Pandas cung cấp str. giải nén, hoạt động theo cách tương tự

df['Length'] = df['Title'].str.len()

Kết hợp với fillna để ngăn các giá trị null, như bạn làm trong Excel với IFERROR

df['Length'] = df['Title'].str.len()

Nếu

Các câu lệnh IF cho phép bạn trả về các giá trị khác nhau, tùy thuộc vào việc một điều kiện có được đáp ứng hay không

Để minh họa, giả sử rằng chúng tôi muốn tạo nhãn cho các từ khóa đang xếp hạng trong ba vị trí hàng đầu

Thay vì sử dụng Pandas trong trường hợp này, chúng ta có thể dựa vào NumPy và chức năng where (hãy nhớ nhập NumPy, nếu bạn chưa nhập)

df['Length'] = df['Title'].str.len()

Có thể sử dụng nhiều điều kiện cho cùng một đánh giá bằng cách sử dụng toán tử AND/OR và đặt các tiêu chí riêng lẻ trong dấu ngoặc tròn

df['Length'] = df['Title'].str.len()

Ở phần trên, chúng tôi đang trả lại "3 từ khóa hàng đầu" cho bất kỳ từ khóa nào có xếp hạng nhỏ hơn hoặc bằng ba, loại trừ bất kỳ từ khóa nào xếp hạng ở vị trí 0

NẾU

Đôi khi, thay vì chỉ định nhiều điều kiện cho cùng một đánh giá, bạn có thể muốn nhiều điều kiện trả về các giá trị khác nhau

Trong trường hợp này, giải pháp tốt nhất là sử dụng IFS

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Một lần nữa, NumPy cung cấp cho chúng tôi giải pháp tốt nhất khi làm việc với các khung dữ liệu, thông qua chức năng chọn của nó

Với select, chúng ta có thể tạo danh sách các điều kiện, lựa chọn và giá trị tùy chọn khi tất cả các điều kiện đều sai

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Cũng có thể có nhiều điều kiện cho mỗi đánh giá

Giả sử chúng ta đang làm việc với một nhà bán lẻ thương mại điện tử có trang danh sách sản phẩm (PLP) và trang hiển thị sản phẩm (PDP) và chúng ta muốn gắn nhãn loại xếp hạng trang có thương hiệu trong 10 kết quả hàng đầu

Giải pháp đơn giản nhất ở đây là tìm kiếm các mẫu URL cụ thể, chẳng hạn như thư mục con hoặc tiện ích mở rộng, nhưng nếu đối thủ cạnh tranh có mẫu tương tự thì sao?

Trong kịch bản này, chúng ta có thể làm một cái gì đó như thế này

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Ở trên, chúng tôi đang sử dụng str. chứa để đánh giá xem một URL trong top 10 có phù hợp với mẫu thương hiệu của chúng tôi hay không, sau đó sử dụng cột “Xếp hạng thương hiệu” để loại trừ mọi đối thủ cạnh tranh

Trong ví dụ này, dấu ngã (~) biểu thị kết quả khớp phủ định. Nói cách khác, chúng tôi đang nói rằng chúng tôi muốn mọi URL thương hiệu không khớp với mẫu cho “PDP” hoặc “PLP” khớp với tiêu chí cho 'Khác. ’

Cuối cùng, Không có giá trị nào được đưa vào vì chúng tôi muốn các kết quả không phải thương hiệu trả về giá trị null

VLOOKUP

VLOOKUP là một công cụ cần thiết để kết hợp hai bộ dữ liệu riêng biệt trên một cột chung

Trong trường hợp này, thêm các URL trong cột N vào dữ liệu từ khóa, vị trí và lượng tìm kiếm trong các cột A-C, sử dụng cột “Từ khóa” được chia sẻ

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Để làm điều gì đó tương tự với Pandas, chúng ta có thể sử dụng hợp nhất

Sao chép chức năng của phép nối SQL, hợp nhất là một chức năng cực kỳ mạnh mẽ hỗ trợ nhiều loại phép nối khác nhau

Đối với mục đích của chúng tôi, chúng tôi muốn sử dụng phép nối trái, thao tác này sẽ duy trì khung dữ liệu đầu tiên của chúng tôi và chỉ hợp nhất các giá trị phù hợp từ khung dữ liệu thứ hai của chúng tôi

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Một lợi thế bổ sung khi thực hiện hợp nhất trên VLOOKUP là bạn không cần phải chia sẻ dữ liệu trong cột đầu tiên của tập dữ liệu thứ hai, như với XLOOKUP mới hơn

Nó cũng sẽ lấy nhiều hàng dữ liệu thay vì kết quả khớp đầu tiên trong kết quả tìm thấy

Một vấn đề phổ biến khi sử dụng hàm là các cột không mong muốn bị trùng lặp. Điều này xảy ra khi tồn tại nhiều cột được chia sẻ, nhưng bạn cố gắng so khớp bằng một cột

Để ngăn chặn điều này – và cải thiện độ chính xác của các kết quả khớp – bạn có thể chỉ định một danh sách các cột

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Trong một số trường hợp nhất định, bạn có thể chủ động muốn đưa các cột này vào. Chẳng hạn, khi cố gắng hợp nhất nhiều báo cáo xếp hạng hàng tháng

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Đoạn mã trên thực thi hai lần hợp nhất để kết hợp ba khung dữ liệu có cùng cột - đó là thứ hạng của chúng tôi cho tháng 11, tháng 10 và tháng 9

Bằng cách gắn nhãn các tháng trong các tham số hậu tố, chúng ta sẽ có một khung dữ liệu rõ ràng hơn nhiều, hiển thị tháng rõ ràng, trái ngược với các giá trị mặc định của _x và _y đã thấy trong ví dụ trước đó

COUNTIF/SUMIF/AVERAGEIF

Trong Excel, nếu bạn muốn thực hiện một hàm thống kê dựa trên một điều kiện, bạn có thể sử dụng COUNTIF, SUMIF hoặc AVERAGEIF

Thông thường, COUNTIF được sử dụng để xác định số lần một chuỗi cụ thể xuất hiện trong tập dữ liệu, chẳng hạn như URL

Chúng tôi có thể thực hiện điều này bằng cách khai báo cột 'URL' làm phạm vi của chúng tôi, sau đó là URL trong một ô riêng lẻ làm tiêu chí của chúng tôi

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Trong Pandas, chúng ta có thể đạt được kết quả tương tự bằng cách sử dụng chức năng nhóm

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Ở đây, cột được khai báo trong ngoặc tròn biểu thị các nhóm riêng lẻ và cột được liệt kê trong ngoặc vuông là nơi tập hợp (i. e. , số đếm) được thực hiện

Tuy nhiên, đầu ra mà chúng tôi nhận được không hoàn hảo cho trường hợp sử dụng này vì nó đã hợp nhất dữ liệu

Thông thường, khi sử dụng Excel, chúng tôi sẽ có số lượng URL nội tuyến trong tập dữ liệu của mình. Sau đó, chúng tôi có thể sử dụng nó để lọc các URL được liệt kê thường xuyên nhất

Để làm điều này, hãy sử dụng biến đổi và lưu trữ đầu ra trong một cột

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

Bạn cũng có thể áp dụng các hàm tùy chỉnh cho các nhóm dữ liệu bằng cách sử dụng hàm lambda (ẩn danh)

df['No. Words'] = df['Title'].str.split().str.len()

Trong các ví dụ của chúng tôi cho đến nay, chúng tôi đã sử dụng cùng một cột để nhóm và tổng hợp của mình, nhưng chúng tôi không phải. Tương tự như COUNTIFS/SUMIFS/AVERAGEIFS trong Excel, có thể nhóm bằng cách sử dụng một cột, sau đó áp dụng hàm thống kê của chúng tôi cho cột khác

Quay trở lại ví dụ về trang kết quả của công cụ tìm kiếm (SERP) trước đó, chúng tôi có thể muốn đếm tất cả các PDP xếp hạng trên cơ sở từng từ khóa và trả lại số này cùng với dữ liệu hiện có của chúng tôi

df['No. Words'] = df['Title'].str.split().str.len()

Mà theo cách nói của Excel, sẽ trông giống như thế này

df['No. Words'] = df['Title'].str.split().str.len()

Bảng tổng hợp

Cuối cùng, nhưng không kém phần quan trọng, đã đến lúc nói về các bảng tổng hợp

Trong Excel, bảng tổng hợp có thể là cổng gọi đầu tiên của chúng tôi nếu chúng tôi muốn tóm tắt một tập dữ liệu lớn

Ví dụ: khi làm việc với dữ liệu xếp hạng, chúng tôi có thể muốn xác định URL nào xuất hiện thường xuyên nhất và vị trí xếp hạng trung bình của chúng

Xin nhắc lại, Pandas có các bảng tổng hợp tương đương của riêng nó – nhưng nếu tất cả những gì bạn muốn là đếm các giá trị duy nhất trong một cột, thì điều này có thể được thực hiện bằng cách sử dụng hàm value_counts

df['No. Words'] = df['Title'].str.split().str.len()

Sử dụng groupby cũng là một tùy chọn

Trước đó trong bài viết, thực hiện một nhóm tổng hợp dữ liệu của chúng tôi không phải là điều chúng tôi muốn – nhưng đó chính xác là những gì được yêu cầu ở đây

df['No. Words'] = df['Title'].str.split().str.len()

Hai hàm tổng hợp đã được áp dụng trong ví dụ trên, nhưng điều này có thể dễ dàng được mở rộng và có sẵn 13 loại khác nhau

Tất nhiên, có những lúc chúng ta muốn sử dụng pivot_table, chẳng hạn như khi thực hiện các hoạt động đa chiều

Để minh họa điều này có nghĩa là gì, hãy sử dụng lại các nhóm xếp hạng mà chúng tôi đã tạo bằng cách sử dụng các câu lệnh có điều kiện và cố gắng hiển thị số lần một URL được xếp hạng trong mỗi nhóm

df['No. Words'] = df['Title'].str.split().str.len()

Đây không phải là định dạng tốt nhất để sử dụng vì nhiều hàng đã được tạo cho mỗi URL

Thay vào đó, chúng ta có thể sử dụng pivot_table, sẽ hiển thị dữ liệu trong các cột khác nhau

df['No. Words'] = df['Title'].str.split().str.len()

Suy nghĩ cuối cùng

Cho dù bạn đang tìm kiếm nguồn cảm hứng để bắt đầu học Python hay đã tận dụng nó trong quy trình SEO của mình, tôi hy vọng rằng các ví dụ trên sẽ giúp bạn trong suốt hành trình của mình

Như đã hứa, bạn có thể tìm thấy sổ ghi chép Google Colab chứa tất cả các đoạn mã tại đây

Trên thực tế, chúng tôi hầu như chưa vạch ra được bề nổi của những gì có thể, nhưng việc hiểu những kiến ​​thức cơ bản về phân tích dữ liệu Python sẽ giúp bạn có cơ sở vững chắc để xây dựng

Cái nào tốt hơn để phân tích dữ liệu Excel hay Python?

Khi nào tôi nên sử dụng Python thay vì Excel?

Excel và Python có phải là công cụ phân tích dữ liệu thường được sử dụng không?

Python có hoạt động tốt với Excel không?