Pandas合并CSV后Bidfloor列赋值出现NaN问题求助

Hey there, let's figure out why your Bidfloor column is filling up with NaNs instead of your target floorprice values. Here's a breakdown of the common issues and how to fix them step by step:

First, Let's Identify the Root Causes #

The most likely reasons your merge is returning all NaNs are:

• Mismatched merge keys: The columns you're using to join (Sitio, Country or Sitio, Espacio, Country) don't have matching values between df_g and df_seg. This could be from case differences (like "Mexico" vs "mexico"), hidden whitespace, or even different data types.
• No matching rows in df_seg: If df_seg doesn't have any entries that line up with the key combinations from df_g, a left merge will automatically fill those spots with NaNs.
• Index misalignment: Directly assigning the merged Precio column might not sync up with df_g's original rows, leading to misplaced NaNs.

Step-by-Step Fixes #

1. Clean & Normalize Your Merge Keys #

First, eliminate any case or whitespace issues that could break the match:

# Strip whitespace and convert to lowercase for all merge columns
for col in ['Sitio', 'Espacio', 'Country']:
    df_g[col] = df_g[col].str.strip().str.lower()
    df_seg[col] = df_seg[col].str.strip().str.lower()

# Check if there are any overlapping key pairs between the two DataFrames
matches = pd.merge(df_g[['Sitio', 'Espacio', 'Country']], 
                   df_seg[['Sitio', 'Espacio', 'Country']], 
                   how='inner')
print(f"Number of matching key combinations: {len(matches)}")

If this count is 0, that means there are no matches at all—you'll need to adjust your merge columns or fix the data in df_seg to align with df_g.

2. Merge Properly to Avoid Index Misalignment #

Instead of directly assigning the merged column, do a full merge and then map the values correctly:

# Merge df_g with only the necessary columns from df_seg
merged = df_g.merge(
    df_seg[['Sitio', 'Espacio', 'Country', 'Precio']],
    on=['Sitio', 'Espacio', 'Country'],
    how='left'
)

# Assign Precio to Bidfloor, and fill any remaining NaNs with your target floorprice
df_g['Bidfloor'] = merged['Precio'].fillna(floorprice)

This ensures every row in df_g gets the correct matching Precio value, and any rows without a match get your specified floorprice instead of NaN.

3. Verify Data Types of Merge Columns #

Make sure the key columns are the same data type in both DataFrames (e.g., both strings, not one string and one category):

print("df_g key column types:\n", df_g[['Sitio', 'Espacio', 'Country']].dtypes)
print("\ndf_seg key column types:\n", df_seg[['Sitio', 'Espacio', 'Country']].dtypes)

If there's a mismatch, convert them to the same type:

# Convert to string type (adjust if you need a different type)
df_g['Sitio'] = df_g['Sitio'].astype(str)
df_seg['Sitio'] = df_seg['Sitio'].astype(str)

4. Double-Check df_seg Has the Precio Column #

It sounds obvious, but make sure df_seg actually has the Precio column with valid values:

print("Columns in df_seg:", df_seg.columns.tolist())
print("\nSample Precio values:\n", df_seg['Precio'].head())

If Precio is missing or has all NaNs, that's a clear reason your Bidfloor is filling with NaNs.

Full Revised Code #

Here's how your code should look after implementing these fixes:

import pandas as pd

floorprice = 0.17
df_g = pd.read_csv('este_mes.csv')
df_g = df_g[df_g.Subastas > 1000]

df_seg = pd.read_csv('o...')  # Replace with your actual file path

# Clean merge keys
for col in ['Sitio', 'Espacio', 'Country']:
    df_g[col] = df_g[col].str.strip().str.lower()
    df_seg[col] = df_seg[col].str.strip().str.lower()

# Merge and assign Bidfloor
merged_df = df_g.merge(
    df_seg[['Sitio', 'Espacio', 'Country', 'Precio']],
    on=['Sitio', 'Espacio', 'Country'],
    how='left'
)
df_g['Bidfloor'] = merged_df['Precio'].fillna(floorprice)

# Save the updated DataFrame
df_g.to_csv('aaaa.csv', index=False)

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