基于Python 2.7的CSV批量ECPM推荐值计算需求

Here's an efficient, Python 2.7-compatible approach using pandas to compute your recommended ECPM values, handling missing data and large datasets (10k+ rows) effectively:

import pandas as pd
import numpy as np

# Read the CSV and parse dates correctly (DD/MM/YYYY format)
df = pd.read_csv('Cliente_x_Pais_Sitio.csv', sep=',', parse_dates=['Fecha'], dayfirst=True)

# Pivot data to group by your key columns and get ECPM for each required date
pivot_df = df.pivot_table(
    index=['Cliente', 'Auth_domain', 'Sitio', 'Country'],
    columns='Fecha',
    values='ECPM_medio',
    aggfunc='first'  # Assumes one entry per group-date combination
).reset_index()

# Rename date columns to simpler, easier-to-reference labels
date_mapping = {
    pd.Timestamp('2017-01-15'): 'ecpm_201701',
    pd.Timestamp('2017-12-15'): 'ecpm_201712',
    pd.Timestamp('2018-01-15'): 'ecpm_201801'
}
pivot_df = pivot_df.rename(columns=date_mapping)

# Fill missing date values with 0 as specified
pivot_df = pivot_df.fillna(0)

# Vectorized calculation (faster for large datasets) of recommended ECPM
# First condition: 15/12/2017 ECPM ≤ 15/01/2018 ECPM
cond1 = pivot_df['ecpm_201712'] <= pivot_df['ecpm_201801']
subcond1 = pivot_df['ecpm_201712'] * 0.8 >= pivot_df['ecpm_201701']
result_cond1 = np.where(subcond1, pivot_df['ecpm_201701'], pivot_df['ecpm_201712'] * 0.8)

# Else condition (15/12/2017 ECPM > 15/01/2018 ECPM)
subcond2 = pivot_df['ecpm_201801'] >= pivot_df['ecpm_201701']
result_cond2 = np.where(subcond2, pivot_df['ecpm_201701'], pivot_df['ecpm_201801'])

# Combine results into final recommendation column
pivot_df['Recomendation_ECPM'] = np.where(cond1, result_cond1, result_cond2)

# Extract only the required columns and save to CSV
final_df = pivot_df[['Cliente', 'Auth_domain', 'Sitio', 'Country', 'Recomendation_ECPM']]
final_df.to_csv('recommended_ecpm.csv', index=False)

Key Notes: #

• Date Handling: The dayfirst=True parameter ensures pandas correctly parses your DD/MM/YYYY date format.
• Pivoting: This restructures your data so each unique group (Cliente/Auth_domain/Sitio/Country) has a single row with columns for each required date's ECPM, simplifying conditional logic application.
• Missing Values: fillna(0) automatically replaces any missing date entries with 0, as requested.
• Performance: Using vectorized operations with numpy.where instead of row-by-row apply makes the code significantly faster for large datasets, which is critical for 10k+ rows.
• Output: The final CSV contains exactly the columns you specified, with no extra index data.

Testing with Your Sample Data: #

For the group (FF, @ff, ff_Color, Alemania):

• ecpm_201701 = 0.34, ecpm_201712 = 0.38, ecpm_201801 = 0.37
• Since 0.38 > 0.37, we check if 0.37 ≥ 0.34 (yes), so the recommendation is 0.34.

For (FF, @ff, ff_Color, Afganistán):

• ecpm_201701 = 0, ecpm_201712 = 0.53, ecpm_201801 = 0.5
• Since 0.53 > 0.5, we check if 0.5 ≥ 0 (yes), so the recommendation is 0.

Both results align perfectly with your specified logic.

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