在DataFrame中为CSV气象数据添加关联纬度与月份的N列

Here's how you can efficiently add the N column to your DataFrame using pandas, with two approaches depending on your dataset size:

Step 1: Setup & Read Input Data #

First, import pandas and read your CSV file (note the separator is /):

import pandas as pd

# Read the original dataset
df = pd.read_csv('your_input.csv', sep='/')

Step 2: Create the Lookup Table #

We'll first define the lookup table as a pandas DataFrame where the index represents months (1-12) and columns represent latitudes:

# Define the N value lookup table
n_lookup_data = {
    17: [25, 22, 8, 4, 23, 16, 26, 3, 21, 19, 28, 21],
    18: [29, 11, 13, 14, 30, 4, 5, 16, 16, 30, 18, 14],
    19: [13, 1, 10, 16, 8, 7, 10, 2, 23, 10, 12, 26],
    20: [13, 16, 21, 10, 8, 5, 25, 27, 8, 28, 6, 3],
    21: [2, 23, 8, 13, 18, 29, 28, 2, 7, 20, 8, 8]
}

# Create DataFrame with months (1-12) as index
n_lookup = pd.DataFrame(n_lookup_data, index=range(1, 13))

Approach 1: Merge (Best for Large Datasets) #

Reshape the lookup table to a long format, then merge it with your original DataFrame. This is efficient for big datasets:

# Reshape lookup table to long format (month, lan, N)
n_lookup_long = n_lookup.reset_index().melt(
    id_vars='index',
    var_name='lan',
    value_name='N'
).rename(columns={'index': 'month'})

# Fix latitude sign (if your input has negative latitudes like -17 instead of 17)
df['lan'] = df['lan'].abs()

# Merge to add the N column
df_with_N = pd.merge(df, n_lookup_long, on=['lan', 'month'], how='left')

Approach 2: Apply (Simpler for Small Datasets) #

Use apply to look up values row-by-row. This is easier to read but slower for large data:

# Function to get N value for a row
def get_n_value(row):
    month = row['month']
    latitude = abs(row['lan'])  # Handle negative latitudes
    return n_lookup.loc[month, latitude]

# Add N column to DataFrame
df['N'] = df.apply(get_n_value, axis=1)

Step 3: Save the Result #

Finally, save the updated DataFrame to a CSV file (using / as separator):

# Save to output CSV
df_with_N.to_csv('your_output.csv', sep='/', index=False)

Example Output #

For your sample input row -17/18/1990/1/0.4, the code will map lan=17 (absolute value) and month=1 to N=25, resulting in -17/18/1990/1/0.4/25 as expected.

Content of the question来源于stack exchange，提问作者KaSan