Pandas多列批量fillna(0)并求和的优化实现及报错解决问询

Let's fix your batch processing issue and streamline the workflow for your task. Here's a clean, efficient solution that avoids repetitive code and the indexing error you encountered:

Step-by-Step Solution Code #

import pandas as pd
import numpy as np

# Load the dataset with proper missing value handling
da1 = pd.read_csv('terror.csv', sep=',', header=0, encoding='latin', na_values=['Missing', ' '])

# Define the target integer columns
attack_columns = ["attacktype1", "attacktype2", "attacktype3"]

# Batch fill missing values with 0, and cast to integer type (optional but recommended)
da1[attack_columns] = da1[attack_columns].fillna(0).astype(int)

# Generate the Total_attacks column by summing the three attack type rows
da1["Total_attacks"] = da1[attack_columns].sum(axis=1)

# Filter for India records and calculate the total sum of Total_attacks
india_total_attacks = da1[da1["country_txt"] == "India"]["Total_attacks"].sum()
print(f"Total attacks recorded in India: {india_total_attacks}")

Why Your Previous Batch Method Failed #

The error ValueError: Cannot index with multidimensional key happened because you tried to use a DataFrame (store) as the column argument in loc. The loc method expects column names (a list/array of strings) or a boolean mask for columns, not a full DataFrame. Your store variable was the processed version of the three attack columns, which is a 2D structure—hence the indexing conflict.

Key Improvements Over Your Initial Approach #

• Batch Processing: Instead of calling fillna(0) on each column individually, we apply it to the entire subset of columns in one line. This scales much better if you ever need to add more columns to the calculation.
• Type Consistency: The .astype(int) ensures the columns remain integer type after filling (since fillna(0) can convert integer columns to float if there were missing values).
• Cleaner Summation: Using .sum(axis=1) on the column subset directly computes the row-wise sum without repeating column names.
• Simpler Filtering: Direct boolean indexing (da1[da1["country_txt"] == "India"]) is more readable than query for straightforward filters, though both work perfectly well.

Alternative: Using assign for Chaining (Optional) #

If you prefer a chained workflow (no in-place modifications), you can write it like this:

da1 = (pd.read_csv('terror.csv', sep=',', header=0, encoding='latin', na_values=['Missing', ' '])
       .assign(**{col: lambda df: df[col].fillna(0).astype(int) for col in attack_columns})
       .assign(Total_attacks=lambda df: df[attack_columns].sum(axis=1)))

india_total_attacks = da1[da1["country_txt"] == "India"]["Total_attacks"].sum()

This keeps all transformations in a single chain, which is useful for maintaining immutability of your original data.

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