如何用Python对含重复字段组的Excel(.xls)做行列转换并提取关键词？

Got it, let's work through this problem together. Excel's built-in transpose is too rigid for your case—since you've got over 1000 repeated field groups (Name/Age/Gender/Job/Location), we need a tailored approach to restructure the data properly, plus add keyword extraction. Here's how to do it with Python using pandas (the go-to library for tabular data) and some keyword extraction tools.

Step 1: Install Dependencies #

First, make sure you have the required packages installed. Open your terminal and run:

pip install pandas openpyxl jieba  # jieba for Chinese keywords; use nltk for English

• pandas: Handles data loading, restructuring, and saving.
• openpyxl: Lets pandas read/write Excel files.
• jieba: For Chinese keyword extraction. If you're working with English text, replace it with nltk instead.

Step 2: Full Code Implementation #

Case 1: Field Groups Are Arranged Vertically #

This is the most common scenario if your raw Excel looks like this (each field group takes 5 consecutive rows):

Name
XXX1
Age
YYY1
Gender
ZZZ1
Job
AAA1
Location
BBB1
Name
XXX2
...

Here's the code to restructure it and extract keywords:

import pandas as pd
import jieba.analyse

# Load the raw xls file (no header in the original data)
raw_df = pd.read_excel('your_input_file.xls', header=None)

# Define the structure of each field group
group_size = 5
field_names = ['Name', 'Age', 'Gender', 'Job', 'Location']

# Split the raw data into chunks of 5 rows (one chunk per field group)
field_groups = [raw_df.iloc[i:i+group_size, 0].values for i in range(0, len(raw_df), group_size)]

# Convert groups into a structured DataFrame (this is our "custom transpose")
structured_df = pd.DataFrame(field_groups, columns=field_names)

# Function to extract keywords (adjust for English if needed)
def get_keywords(text, top_k=3):
    if pd.isna(text):
        return ""
    # Use TF-IDF to extract top N keywords
    return ', '.join(jieba.analyse.extract_tags(str(text), topK=top_k))

# Add keyword columns for relevant fields
structured_df['Job_Keywords'] = structured_df['Job'].apply(get_keywords)
structured_df['Location_Keywords'] = structured_df['Location'].apply(get_keywords)

# Save the final result to a new Excel file
structured_df.to_excel('transposed_with_keywords.xlsx', index=False)
print("Done! Check the output file for your structured data and keywords.")

Case 2: Field Groups Are Arranged Horizontally #

If your raw Excel has field groups side-by-side (each group takes 5 consecutive columns):

Name  Age  Gender  Job  Location  Name  Age  Gender  Job  Location  ...
XXX1  YYY1 ZZZ1    AAA1 BBB1      XXX2  YYY2 ZZZ2    AAA2 BBB2      ...

Use this modified code instead:

import pandas as pd
import jieba.analyse

# Load the raw file (assuming the first row is field names)
raw_df = pd.read_excel('your_input_file.xls')

group_size = 5
field_names = ['Name', 'Age', 'Gender', 'Job', 'Location']

# Split columns into groups of 5
column_groups = [raw_df.iloc[:, i:i+group_size] for i in range(0, raw_df.shape[1], group_size)]

# Rename columns for each group and concatenate into one DataFrame
structured_df = pd.concat(
    [group.rename(columns=dict(zip(group.columns, field_names))) for group in column_groups],
    ignore_index=True
)

# Same keyword extraction as before
def get_keywords(text, top_k=3):
    if pd.isna(text):
        return ""
    return ', '.join(jieba.analyse.extract_tags(str(text), topK=top_k))

structured_df['Job_Keywords'] = structured_df['Job'].apply(get_keywords)
structured_df['Location_Keywords'] = structured_df['Location'].apply(get_keywords)

structured_df.to_excel('transposed_with_keywords.xlsx', index=False)
print("Processing complete! Output file ready.")

Step 3: Notes for English Text #

If you're working with English keywords, replace the jieba part with NLTK's TF-IDF:

1. Install NLTK: pip install nltk
2. Download required resources:

   import nltk
   nltk.download('punkt')
   nltk.download('stopwords')
   

3. Update the keyword function:

   from sklearn.feature_extraction.text import TfidfVectorizer
   from nltk.corpus import stopwords
   
   def get_english_keywords(text, top_k=3):
       if pd.isna(text):
           return ""
       stop_words = set(stopwords.words('english'))
       vectorizer = TfidfVectorizer(stop_words=stop_words)
       tfidf_matrix = vectorizer.fit_transform([str(text)])
       feature_names = vectorizer.get_feature_names_out()
       scores = tfidf_matrix.toarray()[0]
       top_indices = scores.argsort()[-top_k:][::-1]
       return ', '.join([feature_names[i] for i in top_indices if scores[i] > 0])
   

内容的提问来源于stack exchange，提问作者liule123