1
$\begingroup$

In some data analysis challenge (so I don't control the data given), I have a dataset made with the price of a commodity in two location (let say Japan and Korea) and at different day to relate to other data, let's say the price of oil and iron on international market.

So a typical row is like

DAY_ID | COUNTRY | PRICE | OIL_PRICE | IRON_PRICE | ...
  1    |   JPN   |  4.35 |    7.22   |    6.55    | 
  1    |   KOR   |  5.32 |    7.22   |    6.55    |
  2    |   JPN   |  3.51 |    6.38   |    4.27    |

As you notice the price of iron is the same for the two first lines since they are in the same day. Also the data are incomplete, some row are missing meaning I can have the row for one day in Japan but not in Korea.

My problem

The DAY_IDis just an identification and does not reflect any chronological order. At the moment I don't know what to do with it so I just drop this column and then train my regression model.

However I feel like that I am erasing some information that I could use since the price in Japan and Korea at the same day are correlated.

How to use the DAY_ID column?

Improve this question
$\endgroup$
29
  • 1
    $\begingroup$ What would be the purpose of the regression? Why are some values missing? $\endgroup$
    – whuber ♦
    Commented Jul 28, 2023 at 19:22
  • 1
    $\begingroup$ I add some information to make it clearer. $\endgroup$
    – EtienneBfx
    Commented Jul 29, 2023 at 7:28
  • 1
    $\begingroup$ It sounds like you are looking for a multivariate regression model in which the response is the vector of (JPN,KOR) values and the model allows for the vector error terms on any given day to be correlated. The duplicate-records tag looks inappropriate to me. $\endgroup$
    – whuber ♦
    Commented Aug 1, 2023 at 14:35
  • 1
    $\begingroup$ "then train my regression model." What is your model training? The price of the commodity as function of the price of iron and the price of oil? What is the use case? Estimation of models in order to figure out which location is having a cheaper commodity? $\endgroup$
    – Sextus Empiricus
    Commented Aug 3, 2023 at 8:25
  • 1
    $\begingroup$ "The DAY_ID is just an identification and does not reflect any chronological order" How can you have time series data where the time is unknown? $\endgroup$
    – Sextus Empiricus
    Commented Aug 3, 2023 at 8:29

1 Answer 1

Reset to default
-1
$\begingroup$

This is a case of insufficient features to explain the target variable. I assume that your target variable in the regression is gonna be price of the commodity. In that case,

  • you can create new features explaining the difference between two countries like amount of natural resources or value of currency etc.
  • try to collect data on what else can influence the price of the commodity, ex: labor cost, transportation cost etc.

Regression results are as good as your features, hope this helps!

Update: Binary encoding or one-hot encoding will help in including these variables in the regression. Reference: https://www.analyticsvidhya.com/blog/2020/08/types-of-categorical-data-encoding/

Improve this answer
$\endgroup$
5
  • $\begingroup$ Could you please explain what is "insufficient" about these features? For instance, if the data hypothetically were that PRICE in JPN is the average of the OIL_PRICE and IRON_PRICE and that the corresponding value in KOR is 1.00 greater on the same day, that would be easy to identify with data like these and would be a perfect fit. $\endgroup$
    – whuber ♦
    Commented Jul 28, 2023 at 19:25
  • $\begingroup$ Just to be clear I make the situation more simple, in my case I have 30 other features. $\endgroup$
    – EtienneBfx
    Commented Jul 28, 2023 at 20:02
  • $\begingroup$ That was pretty evident from the ellipsis in your table. But it doesn't change anything: include COUNTRY as an explanatory factor in your model, and possibly its interactions with the other variables, too. You could even just run separate models for each country, depending on why you are doing this regression. You still need to explain that. $\endgroup$
    – whuber ♦
    Commented Jul 28, 2023 at 20:17
  • $\begingroup$ If its just the two locations JPN and KOR, we can easily include them in the regression using One-Hot-Encoding or Binary Encoding, which essentially is to create another feature {0,1} to indicate if the row belongs to JPN or KOR. Reference: analyticsvidhya.com/blog/2020/08/… $\endgroup$
    – mugndhn
    Commented Jul 31, 2023 at 17:21
  • $\begingroup$ Hello, I think you read to quickly the issue I have. $\endgroup$
    – EtienneBfx
    Commented Aug 3, 2023 at 7:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.