FAN revenue prediction challenge


Lucy Liu (CDS), Maria Teleczuk (CDS), Clément Chastagnol (Sidetrade),
Gael Varoquaux (Inria, Parietal), Alex Gramfort (Inria, Parietal), Guillaume Lemaitre (Scikit-learn @ Inria Foundation)

Predicting revenue using French Attribution Notices: RAMP studio challenge

Table of Contents

  1. Introduction
  2. Data
  3. Score metric
  4. Data exploration
  5. Predictions
  6. Record linkage
  7. Submission structure
  8. Local testing)
  9. Submitting to RAMP studio)
  10. More information

Introduction

The objective of this challenge is to work with 'dirty data'. Most real data is dirty and the availability of high-quality, open-source ML and data analysis frameworks (such as scikit-learn, pandas...) means that the next frontier for tooling and automation lies in preprocessing. This challenge aims to investigate methodologies to perform statistical analysis directly on the original dirty data.

There are two datasets in this challenge:

  • company_revenue_TRAIN.csv - company revenue declarations.
  • award_notices_RAMP.csv - French Attribution Notices.

Aim

The predictive aim of this challenge is to use company_revenue_TRAIN.csv and award_notices_RAMP.csv to predict the Revenue for each entry in the 'company financial data' dataset. It is advised that you use both datasets, as it improves the prediction (see Score comparison), but using only the company_revenue_TRAIN.csv dataset is also allowed.

Data

Company financial data

This dataset was built from an extract of the National Institute of Statistics and Economic Studies (INSEE) reference database of company revenue declarations from 2013 to 2018. Each row represents the declaration of one company for one year and the following information is provided in the columns:

  • Legal_ID - the reconcilled legal ID of the company
  • Name - the name of the company
  • Activity_code (APE) - 'Activite Principale de l'Entreprise', the main activity of the company - more information in English or in French
  • Address
  • Zipcode
  • City
  • Revenue - in Euros
  • Headcount
  • Fiscal_year_end_date
  • Fiscal_year_duration_in_months
  • Year

There are a few things to note:

  • there are revenue declarations for the same company but different years
  • there is a large reduction in entries for the years 2017 and 2018 due to the Loi Macron law in 2017
  • the 'same company' can have several different entities, resulting in entries where the Legal_ID is different but the Name, Address, City and Zipcode are all the same.
  • Revenue can be negative. A negative revenue may be due to cancelled orders from the previous fiscal year that was recorded in the current fiscal year.

Award notices

Every French public organisation has to issue a call for tenders when buying supplies or services (above a minimum threshold). These are called public procurement contracts. Companies then compete anonymously on these contracts and when a bid is awarded, a notice has to be legally published by the public organization on the BOAMP (historical data is hosted by the DILA). These are called French Attribution Notices (FAN). About 25% of awards are actually electronically published.

Each contract can be divided into a maximum of 5 lots and the same company can win >1 lot of a contract. The award notices dataset comprises award notices from 2017 and 2018. Each row refers to one lot and there can be up to 5 lots referring to the same contract. The following information is provided for each lot:

  • ID_call - ID of the award notice
  • Publication_date of the award notice
  • End_of_call_date of the award notice
  • Departments_of_publication - the department code(s) of the award notice
  • Department_of_provision - the department code(s) where the contract works/goods/services were provided
  • Call_summary - summary of the award notice
  • Call_title - title of the award notice
  • Complete_call_description - description of the award notice
  • Total_amount - total amount of the contract, from all lots, in euros
  • CPV_classes - Common Procurement Vocabulary (CPV), a classification system for public procurement used to describe the subject of procurement contracts (more information can be found here) Columns providing details about the company issuing the contract:
  • Buyer_name
  • Buyer_address
  • Buyer_zipcode
  • Buyer_city
  • Buyer_email

  • Buyer_URL

  • Contract_awarded - whether or not the contact was awarded

Columns providing details about the winner of each lot:

  • ID - unique lot ID
  • awarded - whether or not the contact was awarded
  • description - description of the lot
  • incumbent_name - name of the lot winnter
  • incumbent_address - address of the lot winner
  • incumbent_zipcode - zipcode of the lot winner
  • incumbent_city - city of the lot winner
  • incumbent_country - country of the lot winner
  • number_of_received_bids - number of bids this lot received
  • amount - amount of the lot in euros

Important notes

Both data sets are very dirty. There is a lot of missing data and the column descriptions provided above are a guide only. Further, the award notices dataset is much smaller than the company revenue declarations dataset. Therefore, it is expected that many companies in the company revenue declarations dataset are not present in the award notices dataset.

Training and test

The company revenue training dataset has been split into 'training' and 'test' subsets. The shapes are:

  • training: (1 495 948, 11)
  • test: (520 966, 11)

Your model will be tested on a completely separate company revenue dataset stored on the RAMP server. This dataset has a shape of (702 181, 11). This test dataset will also be dirty but we can guarantee that the following columns will (only) be of numerical data type:

  • Legal_ID
  • Headcount
  • Fiscal_year_duration_in_months
  • Year

Score metric

A unique score is used:

$$score = |max(5,log_{10}(max(1,y\_true))) - max(5,log_{10}(max(1,y\_pred)))| $$

Score interpretation:

  • A lower score is better
  • Any y_true or y_pred value less than 1 is 'taken' as 1
  • If both the y_true and y_pred are less than 100 000, the score would be 0.
  • The score is the same regardless of the order of y_true and y_pred in the equation.
  • If the difference in raw y_true and y_pred values is the same, the score is greater for smaller magnitudes of y_true and y_pred.

Data exploration

In [ ]:
%matplotlib inline
import imp
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import seaborn

Company financial data

In [ ]:
from problem import get_train_data

X_df, y_array = get_train_data()
In [ ]:
X_df.head(3)
In [ ]:
X_df['Fiscal_year_end_date'] = pd.to_datetime(X_df['Fiscal_year_end_date'], format='%Y-%m-%d')
In [ ]:
X_df.shape
In [ ]:
# proportion of NaN values
X_df.isna().sum() / X_df.shape[0]
In [ ]:
# number of unique values
X_df.nunique()
In [ ]:
X_df.dtypes
In [ ]:
X_df.describe()

Distribution of Revenue

In [ ]:
logRev = np.log10(np.clip(y_array, a_min=1, a_max=None))
seaborn.kdeplot(logRev)

Award notices

This dataset is present in the data/ directory and is added by default to all submissions when submitting to RAMP studio. When testing your submission locally however, it is important to copy this file into the submission directory of the submission you wish to test.

For example, the starting kit submission directory (submissions/starting_kit) contains a copy of this dataset as it is required for RAMP to work locally. You will not need to upload this data file when you are making a submission. See for more information.

In [ ]:
award = pd.read_csv('data/award_notices_RAMP.csv.zip', compression='zip')
award.head(3)
In [ ]:
award.shape
In [ ]:
award.dtypes
In [ ]:
award.describe()
In [ ]:
# proportion of NA values
award.isna().sum() / award.shape[0]

Predictions

Company revenue only

First, let's predict using only the comp dataset:

  1. Create a transformer that deals with missing values:
In [ ]:
X_df.dtypes
In [ ]:
# list of numerical columns
num_cols = ['Legal_ID', 'Headcount', 'Fiscal_year_duration_in_months', 'Year']
In [ ]:
from sklearn.impute import SimpleImputer
from sklearn.pipeline import Pipeline

numeric_transformer = Pipeline(steps=[
    ('impute', SimpleImputer(strategy='median'))
])
  1. Create a transformer to split Fiscal_year_end_date into separate year, month, day columns:
In [ ]:
from sklearn.preprocessing import FunctionTransformer

def process_date(X):
    date = pd.to_datetime(X['Fiscal_year_end_date'], format='%Y-%m-%d')
    return np.c_[date.dt.year, date.dt.month, date.dt.day]

date_transformer = FunctionTransformer(process_date, validate=False)
  1. Process the Activity_code (APE) column. At the moment the Scikit learn OrdinalEncoder() does not have a handle_unknown argument. This means that it would not be able to handle any values in Activity_code (APE) which appear in 'train' but do not appear in 'test'. Here we will simply get around this by using the first 2 characters of APE, which are always numbers. The first 2 numbers give the broad category the companies activities fall under (e.g. 'AGRICULTURE'). This column is then converted to numeric data type, so missing values can be dealt with by using the median value (with SimpleImputer()).
In [ ]:
def process_APE(X):
    APE = X['Activity_code (APE)'].str[:2]
    return pd.to_numeric(APE, errors='coerce').values[:, np.newaxis]

APE_transformer = FunctionTransformer(process_APE, validate=False)
  1. Only keep the numbers in the Zipcode column:
In [ ]:
def zipcodes(X):
    zipcode_nums = pd.to_numeric(X['Zipcode'], errors='coerce')
    return zipcode_nums.values[:, np.newaxis]

zipcode_transformer = FunctionTransformer(zipcodes, validate=False)

Finally the full pipeline is assembled.

  • for missing values in numerical columns, the 'median' is used.
  • the date column Fiscal_year_end_date is transformed into separated year month and day columns.
  • Activity_code (APE) is dealt with as described above.
  • the columns Name, Address and City are all dropped.
In [ ]:
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import make_pipeline

zipcode_col = ['Zipcode']
date_cols = ['Fiscal_year_end_date']
drop_cols = ['Name', 'Address', 'City']
APE_col = ['Activity_code (APE)']


preprocessor_comp = ColumnTransformer(
    transformers=[
        ('zipcode', make_pipeline(zipcode_transformer, SimpleImputer(strategy='median')), zipcode_col),
        ('num', numeric_transformer, num_cols),
        ('date', make_pipeline(date_transformer, SimpleImputer(strategy='median')), date_cols),
        ('APE', make_pipeline(APE_transformer, SimpleImputer(strategy='median')), APE_col),
        ('drop cols', 'drop', drop_cols),
    ])
In [ ]:
# make sure it works
preprocessor_comp.fit_transform(X_df)

We will use a Random Forest Regressor model:

In [ ]:
from sklearn.ensemble import RandomForestRegressor

regressor = RandomForestRegressor(n_estimators=10)

Before we can test our model, we need to define our unique scoring function:

In [ ]:
from sklearn.metrics import make_scorer

def loss(y_true, y_pred):
    
    if isinstance(y_true, pd.Series):
        y_true = y_true.values

    true = np.maximum(5., np.log10(np.maximum(1., y_true)))
    pred = np.maximum(5., np.log10(np.maximum(1., y_pred)))
    
    loss = np.mean(np.abs(true - pred))
    
    return loss
    
fan_loss = make_scorer(loss, greater_is_better=False)

Now we can test our model. Note that we use GroupShuffleSplit using Legal_ID as the group so that the same company ('Legal_ID') only appears in either 'train' or 'test' but does not appear both in 'train' and 'test'.

This reflects the same conditions of this challenge where, the private 'test' data (on RAMP) does not contain any company that also appears in the public 'train' dataset you have access to. This is because Revenue for the same company is often very similar.

In [ ]:
from sklearn.model_selection import cross_val_score
from sklearn.model_selection import GroupShuffleSplit

clf = Pipeline(steps=[
    ('preprocessing', preprocessor_comp),
    ('classifier', regressor)])

cv = GroupShuffleSplit(n_splits=8, test_size=0.25)

scores_Xdf = -cross_val_score(clf, X_df, y_array, cv=cv, scoring=fan_loss, groups=X_df['Legal_ID'], n_jobs=2)

print("mean: %e (+/- %e)" % (scores_Xdf.mean(), scores_Xdf.std()))

Naive merge

Now let us predict using a naive merge of award and comp datasets.

The naive merge will only use the name of the company. To aid the merging we will convert the name to all lower case and remove punctuation and white space.

In [ ]:
award['Name_processed'] = award['incumbent_name'].str.lower()
award['Name_processed'] = award['Name_processed'].str.replace('[^\w]','')

For each company, extract 2 features:

  • the number of award lots won
  • the total sum of award lots won
In [ ]:
award_features = award.groupby(['Name_processed'])['amount'].agg(['count','sum'])
award_features.head(3)

Next we will perform a naive merge of X_df and award_features.

Be careful in this step to ensure that the order of X_df is not changed.

In [ ]:
def merge_naive(X):
    X['Name'] = X['Name'].str.lower()     
    X['Name'] = X['Name'].str.replace('[^\w]','')
    df = pd.merge(X, award_features, left_on='Name', right_on='Name_processed', how='left')
    return df[['count','sum']]
merge_transformer = FunctionTransformer(merge_naive, validate=False)

Implement the same feature transformation steps as above and include the 'merge' step.

In [ ]:
zipcode_col = ['Zipcode']
date_cols = ['Fiscal_year_end_date']
drop_cols = ['Address', 'City']
APE_col = ['Activity_code (APE)']
merge_col = ['Name']

preprocessor_merge = ColumnTransformer(
    transformers=[
        ('zipcode', make_pipeline(zipcode_transformer, SimpleImputer(strategy='median')), zipcode_col),
        ('num', numeric_transformer, num_cols),
        ('date', make_pipeline(date_transformer, SimpleImputer(strategy='median')), date_cols),
        ('APE', make_pipeline(APE_transformer, SimpleImputer(strategy='median')), APE_col),
        ('merge', make_pipeline(merge_transformer, SimpleImputer(strategy='median')), merge_col),
        ('drop cols', 'drop', drop_cols),
    ])
In [ ]:
# check it works
preprocessor_merge.fit_transform(X_df)

This example is implemented in RAMP within the files in the folder submissions/starting_kit.

The transformation steps above are implemented in the file submissions/starting_kit/feature_extractor.py (a copy of this file is shown below). This file needs to define a class called FeatureExtractor which requires a fit() and transform() function. The fit() function takes both X_df and y_array as parameters, meaning that you are able to engineer new features using y_array (e.g. target encoding). The transform() function only takes X_df. We only use the transform() function in our simple example below.

Note that the award dataset is being read in from the submission folder (submissions/starting_kit). This means that when testing locally, each submission folder should contain a copy of the award dataset award_notices_RAMP.csv.

In [ ]:
import os
import pandas as pd
import numpy as np
from sklearn.impute import SimpleImputer
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import FunctionTransformer
from sklearn.pipeline import Pipeline


class FeatureExtractor(object):
    def __init__(self):
        pass

    def fit(self, X_df, y_array):
        pass

    def transform(self, X_df):
        X_encoded = X_df

        path = os.path.dirname(__file__)
        award = pd.read_csv(os.path.join(path, 'award_notices_RAMP.csv.zip', compression='zip'),
                            low_memory=False)
        # obtain features from award
        award['Name_processed'] = award['incumbent_name'].str.lower()
        award['Name_processed'] = award['Name_processed'].str.replace('[^\w]','')
        award_features = award.groupby(['Name_processed'])['amount'].agg(['count','sum'])

        def zipcodes(X):
            zipcode_nums = pd.to_numeric(X['Zipcode'], errors='coerce')
            return zipcode_nums.values[:, np.newaxis]
        zipcode_transformer = FunctionTransformer(zipcodes, validate=False)

        numeric_transformer = Pipeline(steps=[
            ('impute', SimpleImputer(strategy='median'))])

        def process_date(X):
            date = pd.to_datetime(X['Fiscal_year_end_date'], format='%Y-%m-%d')
            return np.c_[date.dt.year, date.dt.month, date.dt.day]
        date_transformer = FunctionTransformer(process_date, validate=False)
        
        def process_APE(X):
            APE = X['Activity_code (APE)'].str[:2]
            return pd.to_numeric(APE).values[:, np.newaxis]
        APE_transformer = FunctionTransformer(process_APE, validate=False)

        def merge_naive(X):
            X['Name'] = X['Name'].str.lower()     
            X['Name'] = X['Name'].str.replace('[^\w]','')
            df = pd.merge(X, award_features, left_on='Name', 
                          right_on='Name_processed', how='left')
            return df[['count','sum']]
        merge_transformer = FunctionTransformer(merge_naive, validate=False)

        num_cols = ['Legal_ID', 'Headcount', 
                    'Fiscal_year_duration_in_months', 'Year']
        zipcode_col = ['Zipcode']
        date_cols = ['Fiscal_year_end_date']
        APE_col = ['Activity_code (APE)']
        merge_col = ['Name']
        drop_cols = ['Address', 'City']

        preprocessor = ColumnTransformer(
            transformers=[
                ('zipcode', make_pipeline(zipcode_transformer, SimpleImputer(strategy='median')), zipcode_col),
                ('num', numeric_transformer, num_cols),
                ('date', make_pipeline(date_transformer, SimpleImputer(strategy='median')), date_cols),
                ('APE', make_pipeline(APE_transformer, SimpleImputer(strategy='median')), APE_col),
                ('merge', make_pipeline(merge_transformer, SimpleImputer(strategy='median')), merge_col),
                ('drop cols', 'drop', drop_cols),
                ])

        X_array = preprocessor.fit_transform(X_encoded)
        return X_array

We will use a Random Forest Regressor model again:

In [ ]:
regressor = RandomForestRegressor(n_estimators=10)

This is implemented in the file submissions/starting_kit/regressor.py (a copy of this file is shown below). The Regressor class must have a fit() and predict() function. If you are using a scikit-learn function, this can be done by simply calling fit() and predict() on the regressor, as seen below.

In [ ]:
from sklearn.ensemble import RandomForestRegressor
from sklearn.base import BaseEstimator


class Regressor(BaseEstimator):
    def __init__(self):
        self.reg = RandomForestRegressor(n_estimators=5)

    def fit(self, X, y):
        self.reg.fit(X, y)

    def predict(self, X):
        return self.reg.predict(X)

We can test our model within this notebook, using preprocessor_merge and regressor defined above, in a Pipeline().

Once you are happy with a solution, you can transfer your solution to feature_extractor.py and regressor.py files and test your submission using RAMP (see 'Submissions').

In [ ]:
clf = Pipeline(steps=[
    ('preprocessing', preprocessor_merge),
    ('classifier', regressor)])

cv = GroupShuffleSplit(n_splits=8, test_size=0.25)

scores_merge = -cross_val_score(clf, X_df, y_array, cv=cv, scoring=fan_loss, groups=X_df['Legal_ID'],
                               n_jobs=2)

print("mean: %e (+/- %e)" % (scores_merge.mean(), scores_merge.std()))

Score comparison

You can see in the below plot of the scores that using the merged data always results in better scores (smaller is better):

In [ ]:
scores = pd.DataFrame({'Xdf': scores_Xdf, 'merge': scores_merge})
scores.boxplot()

Record linkage

The problem of trying to match 'records' referring to the same entity across different data sources is called record linkage. We used a very naive way to match companies above but there are much more sophiisticated methods and packages implementing these methods. To get you started here are two Python packages:

Submission structure

Each of your submissions should be in it's own folder within the submissions folder (e.g. submissions/my_submission). The submission directory should contain 3 files:

  • a copy of the award_notices_RAMP.csv dataset. Note that you will not need to upload this file when making a submission in RAMP studio as this is done automatically by RAMP studio.
  • feature_extractor.py - this should merge the X_df and the award dataset and any feature transformation you wish
  • regressor.py - this should implement a regressor with a fit() and predict() function

See submissions/starting_kit for an example.

You can also test your submissions using RAMP-workflow before submitting:

Local testing (before submission)

It is important that you test your submission files before submitting them. For this we provide a unit test - ramp_test_submission.

First, ensure that ramp-worflow is installed (see the github repo for installation instructions).

Now you can use ramp_test_submission. This command will test on files in submissions/starting_kit by default. To specify testing on a different folder use the flag --submission. For example to run the test on submissions/solution1 use: ramp_test_submission --submission solution1.

If it runs and print training and test errors on each fold, then you can submit the code.

For example, below we test the starting kit:

In [ ]:
!ramp_test_submission

Submitting to ramp.studio

Once you found a good model, you can submit it to ramp.studio. First, if it is your first time using RAMP, sign up, otherwise log in. Then sign up for the 'FAN_revenue_prediction' challenge and the 'Saclay M2 Data Camp 2019/20' event. Both signups are controlled by RAMP administrators, so there can be a delay between asking for signup and being able to submit.

Once your signup request is accepted, you can go to your sandbox. Here you can either edit and save the code files on the left hand side of the page or upload your feature_extractor.py and regressor.py files on the right hand side of the page, then give your submission a name and click 'submit'. The submission is trained and tested on our backend in the same way as ramp_test_submission does it locally. While your submission is waiting in the queue and being trained, you can find it in the "New submissions (pending training)" table in my submissions. Once it is trained, you get an email, and your submission shows up on the public leaderboard. If there is an error (despite having tested your submission locally with ramp_test_submission), it will show up in the "Failed submissions" table in my submissions. You can click on the error to see part of the trace.

After submission, do not forget to give credits to the previous submissions you reused or integrated into your submission.

The data set we use at the backend is usually different from what you find in the starting kit, so the score may be different.

The usual way to work with RAMP is to explore solutions, add feature transformations, select models, perhaps do some AutoML/hyperopt, etc., locally then check that your submission works locally with ramp_test_submission. The script will print mean cross-validation scores if your submission works.

The official score in this RAMP (the first score column after "historical contributivity" on the leaderboard) is FAN error.

More information

You can find more information in the README of the ramp-workflow library.