# Preprocess list data

I got question about preparation data for my ML algorithm. Raw data has format similar to:

{
"finances": [
{
"assets": 1230.39,
"investments": 3245.39,
"netProfit": 8765.45,
"year": 2017
},
{
"assets": 111.11,
"investments": 222.22,
"netProfit": 333.33,
"year": 2016
},
{
"assets": 1111.11,
"investments": 2222.22,
"netProfit": 3333.33,
"year": 2015
}
],
"someValue": 123.45,
"title": "Hello!"
}


And I am wondering what is best way to pass data about finances to my algorithm (Number of years can differ from 0 to 8).

I was thinking about making every parameter for every year new attribute, but this would lead to have many not null values for some cases, because not every record has 8 years of financial history.

My question is: What is best way to handle such data in Input data (In fact every of finances elements would have more values in it (around 10-15).

• Welcome to the site! The data seem to be in JSON format, which makes it easy to parse into a data frame. About choosing the right attributes: What do you want to do with your ML algorithm? – Elias Strehle Feb 6 '18 at 20:03
• I would like to some some loan approval predictions, I'm kinda newbie in ML. so sorry if my question is silly. I'm just wonering what is the best way to pass finances to my ML algorithm and how to handle null values if there will be any if given solution. – Blejwi Feb 6 '18 at 20:06

The ultimate end goal of your modeling is going to affect the way you want to format your data. It's a good practice whenever you start a machine learning project to ask yourself, what is the precise question you want to answer, because whatever model you generate, it's only going to make sense if used in the context of the question asked.

If in your case you want to predict loan approvals, then first you need to check whether that information is even present in the data you have. The JSON you have just shows historical data, but do you know the outcome for each data point? Is that another feature, maybe recorded in the "someValue", "title" area of the file? Without it, you really can't do anything.

If you can get that information, then it's perfectly fine to generate a variable for each year: assets_year1, assets_year2, ... assets_year8, investments_year1, ... etc. True, year8 might have a lot of Null values, but that's not necessarily bad. For example, most models working with text data consist of really sparse training matrices, yet they do very well in practice. Depending on the algorithm, it might weed out those variables anyways. With Null values, you just have to try imputing the Null values differently, and you can also create an indicator variable for whether the column has missing values, and see how different settings change performance.

To take it a step further, you can also create additional features to encapsulate the nuances that are occurring over the years. Create features like the historical average, average change per year, total number of years of history, etc. Creative feature engineering is the hardest part, but can lead to big changes in performance.

• Great answer, thank you! Are there other ways of handling yearly variables? For example, counting total_assets with wages where last year has the highest wage, and 8 years ago has lower weight? Also, I know that null values are not allowed in some algorithms, so It is OK to replace them with mean values? – Blejwi Feb 7 '18 at 8:05
• Since you have domain context, you can probably come up with a lot of valuable features for the years. Give that example a shot and let the algorithm work it out. For the nulls, most algorithms require imputing for the math to work, but there are different choices on how to do so. The simplest way is to impute the mean/median, but that can get tricky if you're concerned about data leakage. You want to impute the whole data with the mean of the training data, not the mean of the whole data. Imputing with a constant value like 0 is easier, but neither is better, it just depends on the data. – dbaghern Feb 7 '18 at 15:04