# Treating multiple observations per object

I am working on a project whose aim is to analyze the relationship between machine elements and their price.

My data consists of thousands of machine elements, their price, as well as technical and non-technical attributes. Most of the data is categorical

The technical data contains information such as weight, dimensions, material. Each element (identified by unique code) has distinct technical attributes.

The non-technical data contain information such as supplier name, contract type, INCOTERMS, first day of validity of the contract, last day of validity, minimum order quantity, etc. In some cases the prices depend on the order quantity.

One of the problems I am facing is that often each element has a different price depending on the financial data, i.e. element X will cost 100 dollars if Incoterms is A, 200 dollars if Incoterms is B, etc. In other words, there are rows that contain price information on the same element, but one of the columns has a different value and so the price is different.

In other cases the price is 100 if 50-100 elements are ordered, 80 if 100-200 elements are ordered, 60 if 200-500, etc.

I am planning to do some correlations as well as regression. I will probably also try data mining (using Rattle and R).

I need advice on how to treat the (rather) similar observations that each object has in case of correlation, regression and in general for data mining. Should I try to select a single observation per element or do the analysis for “all in”. I guess the last option won’t work for correlation at all.

Perhaps including a sample screenshot of the data with clarify my problem. So what is visible in the picture happens quite often: one object, different attributes, different price. I am wondering whether I should (try to) have only one row per object with only one set of attributes (we don’t need to discuss the criteria) or whether I should keep the multiple rows for the same object when running a correlation analysis or a regression.

What is the response variable in your regression? And for what purpose are you finding correlations between these things? My apologies, I'm not entirely sure if I understand your situation.

I'm going to assume here (please correct me if this is incorrect) that you are attempting to find the relationship behind different things (like building material, size, country of origin, etc.) and the price of the object. I'm also going to assume you're fairly new to R. If this is incorrect, please excuse the unnecessary explanation.

So let's take for example a microservo, and we want to know if the price is different for three different building materials -- steel, iron, and titanium. Do those make a difference, and are those material differences dependant on (say) the size of the microservo. I would create a categorical variable from my materials (steel = 0, iron = 1, titanium = 2, for arbitrary reasons) and let's assume two levels of size small (0) and large (1). Now, we use a regression model to see if these things predict the price. We will also include an interaction term to see if they modify each other's influence on each other.

We first read in your excel (presumably) sheet, taking tab (\t) as the seperator value, and including the column headers in the file (header = true). We then do a linear regression (try ?lm and ?glm in your R console), and then read the output.

yourdatatable <- read.table("your_excel_table_here.xls", sep = '\t', header = T)
y <- lm(price ~ size*material, data = yourdatatable, na.rm=T)
summary(y)


This will give you the three levels of predictors predicting your price. Firstly it will show how size affects price and the associated P value, and how material affects price at the associated P value. The coefficient will be the increase in price per 1 unit increase in your predictor variable. For example, as size goes from small to large (0 to 1), you may have a coefficient of 5. This would indicate that your price increases by an average of \$5 when your size goes from small to large. An interpretation of this nature for the materials scale would need a ranked list of materials, which I did not provide.

The interaction line (size*material) will tell you if the size of the microservo affects the increase in price caused by materials. For example, if it doesn't matter that your microservo is tiny or huge if it is made out of copper because your materials are reasonably inexpensive, then the P value associated with this will be insignificant. If, however, the increase in size really matters because you're making your microservo out of titanium, then this interaction term will be significant, because the size of the microservo is directly affecting the increase in price associated with material. Take a look on google for some articles explaining the interaction term.

Let's do a quick example using an example data set called mtcars. You can copy paste this into R.

ex <- lm(mpg~hp*wt, data = mtcars, na.rm=T)
summary(ex)


This gives the output

                Estimate Std. Error t value Pr(>|t|)
(Intercept) 49.80842    3.60516  13.816 5.01e-14 ***
hp          -0.12010    0.02470  -4.863 4.04e-05 ***
wt          -8.21662    1.26971  -6.471 5.20e-07 ***
hp:wt        0.02785    0.00742   3.753 0.000811 ***


The response variable here was miles/gallon of gas, and our two predictors were horsepower and weight. So we can see that, yes, both of these have an effect on the miles/gallon of the car. We can also see our interaction term (hp:wt) is significant, which means that if a car is much heavier it will affect the contribution of horsepower to the miles/gallon.

I hope this helped. If I really misunderstood, I apologize, please correct me.

• Thank you for your reply. Indeed I am trying to find the relationship between different parameters and the price of machine parts. But I am also trying to find objects that are too expensive for their properties. I am not completely new to R but I am more familiar with Stata I know how to read tables in R and how to interpret coefficients :) But thank you very much for the time you spend to write your answer, I really appreciate it! Regarding the correlations, I also wanted to look at the pairwise correlations between the price and the element properties. Please see my updated post – Henrik Aug 20 '14 at 19:11