Hey there! Let's fix up your code and get that friend phone data added to your Excel sheet. First, let's address a few small issues in your existing code, then build out the functionality you need.

Issues in the Original Code #

• Typo: import panda as pd should be import pandas as pd (missing an 's' in pandas)
• Missing Quotes: Filenames like input.json and exl.xlsx need to be wrapped in quotes when passing to file functions
• Unnecessary Step: pd.read_json(json_data) isn't needed—json.load() already gives you a Python dictionary, so we can work directly with that

Complete Working Code #

Here's the corrected and expanded code that does exactly what you're asking for:

import json
import pandas as pd

# Load the JSON data into a Python dictionary
with open("input.json", "r") as json_file:
    friend_phone_data = json.load(json_file)

# Load your Excel file into a DataFrame
excel_df = pd.read_excel("exl.xlsx", na_filter=False, header=0)

# Step 1: Identify all unique friend keys from the JSON (e.g., friend1, friend2, etc.)
all_friend_columns = set()
for entry in friend_phone_data.values():
    all_friend_columns.update(entry.keys())
# Sort the columns for a consistent order (optional but clean)
all_friend_columns = sorted(all_friend_columns)

# Step 2: Add these friend columns to the Excel DataFrame, defaulting to empty strings
for col in all_friend_columns:
    excel_df[col] = ""

# Step 3: Fill in the phone numbers for each name
for idx, row in excel_df.iterrows():
    name = row["name"]
    # Check if the name exists in the JSON data
    if name in friend_phone_data:
        # Get the friend phone entries for this name
        name_friends = friend_phone_data[name]
        # Fill each friend column if the data exists
        for col in all_friend_columns:
            if col in name_friends:
                excel_df.at[idx, col] = name_friends[col]

# Step 4: Save the updated DataFrame back to Excel (optional but recommended)
excel_df.to_excel("updated_exl.xlsx", index=False)

What This Code Does #

1. Loads Data: Correctly loads both the JSON and Excel files into usable formats
2. Discovers Columns: Automatically finds all unique friend keys from your JSON, so you don't have to hardcode them (great if you add more friend types later)
3. Adds Columns: Creates empty columns in the Excel DataFrame for each friend type
4. Fills Data: Matches each name from the Excel to the JSON, filling in phone numbers where available and leaving empty cells where there's no data
5. Saves Result: Writes the updated data to a new Excel file (updated_exl.xlsx) so you don't overwrite your original file

Example Output #

After running the code, your updated Excel file will look like this:

Performance Note #

If you're working with a very large Excel file (thousands of rows), using iterrows() can be slow. For those cases, you can use pd.Series.map() or a lambda function with apply() for faster processing. But for small datasets like your example, iterrows() works perfectly fine.

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