转置DataFrame制作直方图及解决分类值比率计算代码报错问题

Let’s work through your problems step by step—fixing the code errors, building the structured DataFrame you need, calculating the required ratios, and covering how to transpose for histograms.

1. Fixing the Code Errors #

Error 1: ValueError: The truth value of a DataFrame is ambiguous (Line 5) #

The core issue here is that lps.loc[(...)] returns an entire DataFrame, not a single numeric value. The min() function can’t compare full DataFrames directly, which triggers the ambiguity error. You also don’t need to pass the exact same slice twice to min()—that’s redundant.

Replace line 5 with this (swap 'count' with your actual numeric column name, e.g., the column holding the frequency values):

# Get the subset of data for this ld, type, and BUSINESS RULES category
subset = lps.loc[(lps['num'] == ld) & (lps['let_typ'] == ty) & (lps['num_typ'] == 'BUSINESS RULES')]
# Append the minimum value from your numeric column (or 0 if the subset is empty)
nmrtr.append(subset['count'].min() if not subset.empty else 0)

Error 2: Logical/Syntax Issue (Line 7) #

Your line nmrtr.append(max(sum(nmrtr),1)) adds the sum of the current nmrtr list back into the list itself, which creates messy, unintended values. Instead of manual loops, we’ll use pandas operations once we have the structured DataFrame—this will make your ratio calculation far cleaner.

2. Building Your Target DataFrame #

Forget nested loops—use pandas pivot_table to get exactly the structure you want. Let’s use your sample fake data to demonstrate:

import pandas as pd

# Sample fake data matching your example
fake_data = [
    [121, 'der', 'cat', 7],
    [121, 'der', 'dog', 12],
    [131, 'mnd', 'cat', 7],
    [131, 'mnd', 'dog', 12],
    [141, 'der', 'cat', 7],
    [141, 'der', 'dog', 12]
]
lps = pd.DataFrame(fake_data, columns=['num', 'let_typ', 'animal', 'count'])

# Create the pivot table to get cat/dog counts per num/let_typ
target_df = pd.pivot_table(
    lps,
    index=['num', 'let_typ'],
    columns='animal',
    values='count',
    aggfunc='sum',
    fill_value=0
).reset_index()

print(target_df)

This outputs your desired structure:

3. Calculating the Required Ratios #

With the structured DataFrame, your ratio calculation becomes simple:

Option 1: Sum of min(cat, dog) / sum(cat+dog) (your stated goal) #

# Add a column for the ratio per num/let_typ
target_df['ratio'] = target_df.apply(lambda row: min(row['cat'], row['dog']) / (row['cat'] + row['dog']), axis=1)

# Sum all ratios
total_ratio = target_df['ratio'].sum()
print(f"Total sum of ratios: {total_ratio}")

Option 2: Divide by the original 'num' total count (ld_vl[ld]) #

If ld_vl is a dictionary mapping num to its total count, merge it into the DataFrame first:

# Example ld_vl dictionary
ld_vl = {121: 20, 131: 25, 141: 19}

# Add total count column to the DataFrame
target_df['total_num_count'] = target_df['num'].map(ld_vl)

# Calculate ratio: min(cat, dog) / total_num_count
target_df['ratio_to_total'] = target_df.apply(lambda row: min(row['cat'], row['dog']) / row['total_num_count'], axis=1)

4. Transposing DataFrame for Histograms #

Transposing swaps rows and columns. For your target DataFrame, use this to plot histograms:

# Transpose the DataFrame (exclude non-numeric columns first)
transposed_df = target_df.set_index(['num', 'let_typ']).T

# Plot histogram of cat/dog counts
transposed_df.plot(kind='hist', bins=5, alpha=0.5, title='Histogram of Cat/Dog Counts by Num/Let_Type')

# If you want to plot ratios instead:
target_df[['ratio', 'ratio_to_total']].T.plot(kind='hist', bins=5, title='Histogram of Ratios')

This will generate histograms that visualize the distribution of your counts or ratios across categories.

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