Determining the correlations between aggregated data and non aggregated data

Question

I have a dataset which is essentially a list of lists produced by a sql query output. Here is what it looks like

[[(datetime.datetime(2017, 12, 1, 0, 0), Decimal('7.9618320610687023')), (datetime.datetime(2018, 1, 1, 0, 0), Decimal('3.8426966292134831')), (datetime.datetime(2018, 2, 1, 0, 0), Decimal('4.4876543209876543')), (datetime.datetime(2018, 3, 1, 0, 0), Decimal('4.7269372693726937')), (datetime.datetime(2018, 4, 1, 0, 0), Decimal('5.3849765258215962')), (datetime.datetime(2018, 5, 1, 0, 0), Decimal('4.0217391304347826')), (datetime.datetime(2018, 6, 1, 0, 0), Decimal('4.1186440677966102')), (datetime.datetime(2018, 7, 1, 0, 0), Decimal('6.2187500000000000')), (datetime.datetime(2018, 8, 1, 0, 0), Decimal('3.2826086956521739')), (datetime.datetime(2018, 9, 1, 0, 0), Decimal('4.4661654135338346')), (datetime.datetime(2018, 10, 1, 0, 0), Decimal('4.9191176470588235')), (datetime.datetime(2018, 11, 1, 0, 0), Decimal('4.0491803278688525')), (datetime.datetime(2018, 12, 1, 0, 0), Decimal('5.3090909090909091'))], [(datetime.datetime(2017, 12, 1, 0, 0), 14.2151145038168), (datetime.datetime(2018, 1, 1, 0, 0), 12.9982584269663), (datetime.datetime(2018, 2, 1, 0, 0), 13.46), (datetime.datetime(2018, 3, 1, 0, 0), 13.0539852398524), (datetime.datetime(2018, 4, 1, 0, 0), 12.9493896713615), (datetime.datetime(2018, 5, 1, 0, 0), 13.115652173913), (datetime.datetime(2018, 6, 1, 0, 0), 12.8800564971751), (datetime.datetime(2018, 7, 1, 0, 0), 13.318125), (datetime.datetime(2018, 8, 1, 0, 0), 13.6523913043478), (datetime.datetime(2018, 9, 1, 0, 0), 14.0972180451128), (datetime.datetime(2018, 10, 1, 0, 0), 14.6723529411765), (datetime.datetime(2018, 11, 1, 0, 0), 14.936393442623), (datetime.datetime(2018, 12, 1, 0, 0), 15.9845454545455)]]

It basically contains two lists each of them with a date and metric column. I need to extract the metric column values of each of the list and find a a correlation between them.

Here the two metrics are quantity and unitprice and the query is basically to find out the the monthly average quantity and unit price for the last 1 year.

Here is what the graph looks like

So here is what I do to get the Pearson and Spearman coefficient in pandas

import pandas as pd
import datetime
from decimal import Decimal

# contains date and average quantity values
data1 = data[0]
# contains date and average unitprice values
data2 = data[1]

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

pearson_coeff = df1.iloc[:,-1].astype('float64').corr(df2.iloc[:,-1].astype('float64'))

spearman_coeff = df1.iloc[:,-1].astype('float64').corr(df2.iloc[:,-1].astype('float64'),method="spearman", min_periods=1)

I get the pearson_coeff value as 0.3416 and spearman_coeff value as 0.2802.

Now I read somewhere that it is not a good idea to find the coorelations on aggregated data. So what I did is made a seperate sql query on each of the metrics but this time without the aggregates.Here is how it looks like

[[(datetime.datetime(2017, 12, 1, 0, 0), 272), (datetime.datetime(2017, 12, 1, 0, 0), -16), (datetime.datetime(2017, 12, 1, 0, 0), 80), (datetime.datetime(2017, 12, 1, 0, 0), 38), (datetime.datetime(2017, 12, 1, 0, 0), -2), (datetime.datetime(2017, 12, 1, 0, 0), 79), (datetime.datetime(2017, 12, 1, 0, 0), -10), (datetime.datetime(2017, 12, 1, 0, 0), 12), (datetime.datetime(2017, 12, 1, 0, 0), 32), (datetime.datetime(2017, 12, 1, 0, 0), -1), (datetime.datetime(2017, 12, 1, 0, 0), 1), (datetime.datetime(2017, 12, 1, 0, 0), 6), (datetime.datetime(2017, 12, 1, 0, 0), 4), (datetime.datetime(2017, 12, 1, 0, 0), -12), (datetime.datetime(2017, 12, 1, 0, 0), 2), (datetime.datetime(2017, 12, 1, 0, 0), 3), (datetime.datetime(2017, 12, 1, 0, 0), 5), (datetime.datetime(2017, 12, 1, 0, 0), 52), (datetime.datetime(2017, 12, 1, 0, 0), 16), (datetime.datetime(2018, 1, 1, 0, 0), -4), (datetime.datetime(2018, 1, 1, 0, 0), 4), (datetime.datetime(2018, 1, 1, 0, 0), 12), (datetime.datetime(2018, 1, 1, 0, 0), -23), (datetime.datetime(2018, 1, 1, 0, 0), 16), (datetime.datetime(2018, 1, 1, 0, 0), 48), (datetime.datetime(2018, 1, 1, 0, 0), 5), (datetime.datetime(2018, 1, 1, 0, 0), -1), (datetime.datetime(2018, 1, 1, 0, 0), 1), (datetime.datetime(2018, 1, 1, 0, 0), 3), (datetime.datetime(2018, 1, 1, 0, 0), 17), (datetime.datetime(2018, 1, 1, 0, 0), -7), (datetime.datetime(2018, 1, 1, 0, 0), 11), (datetime.datetime(2018, 1, 1, 0, 0), -6), (datetime.datetime(2018, 1, 1, 0, 0), 7), (datetime.datetime(2018, 1, 1, 0, 0), 10), (datetime.datetime(2018, 1, 1, 0, 0), 8), (datetime.datetime(2018, 1, 1, 0, 0), -13), (datetime.datetime(2018, 1, 1, 0, 0), -9), (datetime.datetime(2018, 1, 1, 0, 0), -3), (datetime.datetime(2018, 1, 1, 0, 0), -2), (datetime.datetime(2018, 1, 1, 0, 0), 32), (datetime.datetime(2018, 1, 1, 0, 0), 6), (datetime.datetime(2018, 1, 1, 0, 0), 2), (datetime.datetime(2018, 2, 1, 0, 0), -7), (datetime.datetime(2018, 2, 1, 0, 0), 12), (datetime.datetime(2018, 2, 1, 0, 0), 32), (datetime.datetime(2018, 2, 1, 0, 0), 3), (datetime.datetime(2018, 2, 1, 0, 0), 11), (datetime.datetime(2018, 2, 1, 0, 0), 1), (datetime.datetime(2018, 2, 1, 0, 0), -3), (datetime.datetime(2018, 2, 1, 0, 0), -2), (datetime.datetime(2018, 2, 1, 0, 0), -1), (datetime.datetime(2018, 2, 1, 0, 0), -4), (datetime.datetime(2018, 2, 1, 0, 0), 48), (datetime.datetime(2018, 2, 1, 0, 0), 4), (datetime.datetime(2018, 2, 1, 0, 0), 16), (datetime.datetime(2018, 2, 1, 0, 0), 24), (datetime.datetime(2018, 2, 1, 0, 0), -5), (datetime.datetime(2018, 2, 1, 0, 0), 72), (datetime.datetime(2018, 2, 1, 0, 0), 2), (datetime.datetime(2018, 2, 1, 0, 0), 6), (datetime.datetime(2018, 3, 1, 0, 0), -3), (datetime.datetime(2018, 3, 1, 0, 0), 8), (datetime.datetime(2018, 3, 1, 0, 0), 24), (datetime.datetime(2018, 3, 1, 0, 0), 3), (datetime.datetime(2018, 3, 1, 0, 0), 16), (datetime.datetime(2018, 3, 1, 0, 0), 150), (datetime.datetime(2018, 3, 1, 0, 0), -23), (datetime.datetime(2018, 3, 1, 0, 0), -2), (datetime.datetime(2018, 3, 1, 0, 0), 27), (datetime.datetime(2018, 3, 1, 0, 0), -9), (datetime.datetime(2018, 3, 1, 0, 0), -5), (datetime.datetime(2018, 3, 1, 0, 0), 14), (datetime.datetime(2018, 3, 1, 0, 0), 15), (datetime.datetime(2018, 3, 1, 0, 0), 48), (datetime.datetime(2018, 3, 1, 0, 0), 4), (datetime.datetime(2018, 3, 1, 0, 0), 13), (datetime.datetime(2018, 3, 1, 0, 0), 7), (datetime.datetime(2018, 3, 1, 0, 0), -7), (datetime.datetime(2018, 3, 1, 0, 0), -6), (datetime.datetime(2018, 3, 1, 0, 0), 20), (datetime.datetime(2018, 3, 1, 0, 0), 6), (datetime.datetime(2018, 3, 1, 0, 0), 10), (datetime.datetime(2018, 3, 1, 0, 0), 12), (datetime.datetime(2018, 3, 1, 0, 0), 1), (datetime.datetime(2018, 3, 1, 0, 0), 32), (datetime.datetime(2018, 3, 1, 0, 0), -1), (datetime.datetime(2018, 3, 1, 0, 0), 2), (datetime.datetime(2018, 3, 1, 0, 0), -48), (datetime.datetime(2018, 3, 1, 0, 0), -8), (datetime.datetime(2018, 3, 1, 0, 0), 5), (datetime.datetime(2018, 3, 1, 0, 0), -10), (datetime.datetime(2018, 3, 1, 0, 0), 17), (datetime.datetime(2018, 4, 1, 0, 0), 36), (datetime.datetime(2018, 4, 1, 0, 0), 4), (datetime.datetime(2018, 4, 1, 0, 0), 11), (datetime.datetime(2018, 4, 1, 0, 0), 60), (datetime.datetime(2018, 4, 1, 0, 0), 2), (datetime.datetime(2018, 4, 1, 0, 0), -3), (datetime.datetime(2018, 4, 1, 0, 0), -2), (datetime.datetime(2018, 4, 1, 0, 0), -8), (datetime.datetime(2018, 4, 1, 0, 0), 6), (datetime.datetime(2018, 4, 1, 0, 0), 8), (datetime.datetime(2018, 4, 1, 0, 0), 1), (datetime.datetime(2018, 4, 1, 0, 0), 22), (datetime.datetime(2018, 4, 1, 0, 0), -11), (datetime.datetime(2018, 4, 1, 0, 0), 150), (datetime.datetime(2018, 4, 1, 0, 0), -1), (datetime.datetime(2018, 4, 1, 0, 0), 5), (datetime.datetime(2018, 4, 1, 0, 0), 3), (datetime.datetime(2018, 4, 1, 0, 0), 7), (datetime.datetime(2018, 4, 1, 0, 0), 10), (datetime.datetime(2018, 4, 1, 0, 0), 32), (datetime.datetime(2018, 4, 1, 0, 0), 14), (datetime.datetime(2018, 4, 1, 0, 0), 16), (datetime.datetime(2018, 4, 1, 0, 0), 48), (datetime.datetime(2018, 4, 1, 0, 0), 12), (datetime.datetime(2018, 4, 1, 0, 0), 24), (datetime.datetime(2018, 5, 1, 0, 0), -1), (datetime.datetime(2018, 5, 1, 0, 0), 20), (datetime.datetime(2018, 5, 1, 0, 0), 16), (datetime.datetime(2018, 5, 1, 0, 0), 32), (datetime.datetime(2018, 5, 1, 0, 0), 5), (datetime.datetime(2018, 5, 1, 0, 0), 6), (datetime.datetime(2018, 5, 1, 0, 0), 120), (datetime.datetime(2018, 5, 1, 0, 0), 3), (datetime.datetime(2018, 5, 1, 0, 0), 8), (datetime.datetime(2018, 5, 1, 0, 0), -3), (datetime.datetime(2018, 5, 1, 0, 0), 36), (datetime.datetime(2018, 5, 1, 0, 0), -2), (datetime.datetime(2018, 5, 1, 0, 0), 24), (datetime.datetime(2018, 5, 1, 0, 0), 4), (datetime.datetime(2018, 5, 1, 0, 0), 1), (datetime.datetime(2018, 5, 1, 0, 0), 2), (datetime.datetime(2018, 5, 1, 0, 0), 10), (datetime.datetime(2018, 5, 1, 0, 0), -14), (datetime.datetime(2018, 5, 1, 0, 0), 14), (datetime.datetime(2018, 5, 1, 0, 0), 12), (datetime.datetime(2018, 5, 1, 0, 0), -9), (datetime.datetime(2018, 6, 1, 0, 0), 3), (datetime.datetime(2018, 6, 1, 0, 0), -1), (datetime.datetime(2018, 6, 1, 0, 0), 39), (datetime.datetime(2018, 6, 1, 0, 0), 5), (datetime.datetime(2018, 6, 1, 0, 0), 17), (datetime.datetime(2018, 6, 1, 0, 0), 11), (datetime.datetime(2018, 6, 1, 0, 0), 16), (datetime.datetime(2018, 6, 1, 0, 0), 10), (datetime.datetime(2018, 6, 1, 0, 0), 2), (datetime.datetime(2018, 6, 1, 0, 0), -4), (datetime.datetime(2018, 6, 1, 0, 0), 4), (datetime.datetime(2018, 6, 1, 0, 0), 32), (datetime.datetime(2018, 6, 1, 0, 0), 7), (datetime.datetime(2018, 6, 1, 0, 0), 120), (datetime.datetime(2018, 6, 1, 0, 0), 1), (datetime.datetime(2018, 6, 1, 0, 0), 12), (datetime.datetime(2018, 6, 1, 0, 0), -2), (datetime.datetime(2018, 6, 1, 0, 0), 6), (datetime.datetime(2018, 7, 1, 0, 0), -6), (datetime.datetime(2018, 7, 1, 0, 0), 7), (datetime.datetime(2018, 7, 1, 0, 0), 72), (datetime.datetime(2018, 7, 1, 0, 0), 6), (datetime.datetime(2018, 7, 1, 0, 0), 192), (datetime.datetime(2018, 7, 1, 0, 0), 10), (datetime.datetime(2018, 7, 1, 0, 0), 12), (datetime.datetime(2018, 7, 1, 0, 0), 32), (datetime.datetime(2018, 7, 1, 0, 0), 112), (datetime.datetime(2018, 7, 1, 0, 0), 3), (datetime.datetime(2018, 7, 1, 0, 0), -2), (datetime.datetime(2018, 7, 1, 0, 0), 5), (datetime.datetime(2018, 7, 1, 0, 0), 13), (datetime.datetime(2018, 7, 1, 0, 0), 22), (datetime.datetime(2018, 7, 1, 0, 0), -1), (datetime.datetime(2018, 7, 1, 0, 0), 1), (datetime.datetime(2018, 7, 1, 0, 0), 4), (datetime.datetime(2018, 7, 1, 0, 0), 15), (datetime.datetime(2018, 7, 1, 0, 0), 16), (datetime.datetime(2018, 7, 1, 0, 0), 8), (datetime.datetime(2018, 7, 1, 0, 0), 2), (datetime.datetime(2018, 8, 1, 0, 0), 7), (datetime.datetime(2018, 8, 1, 0, 0), 30), (datetime.datetime(2018, 8, 1, 0, 0), 20), (datetime.datetime(2018, 8, 1, 0, 0), 2), (datetime.datetime(2018, 8, 1, 0, 0), 6), (datetime.datetime(2018, 8, 1, 0, 0), 8), (datetime.datetime(2018, 8, 1, 0, 0), -3), (datetime.datetime(2018, 8, 1, 0, 0), 16), (datetime.datetime(2018, 8, 1, 0, 0), 9), (datetime.datetime(2018, 8, 1, 0, 0), 5), (datetime.datetime(2018, 8, 1, 0, 0), -2), (datetime.datetime(2018, 8, 1, 0, 0), -150), (datetime.datetime(2018, 8, 1, 0, 0), 1), (datetime.datetime(2018, 8, 1, 0, 0), -1), (datetime.datetime(2018, 8, 1, 0, 0), 11), (datetime.datetime(2018, 8, 1, 0, 0), 3), (datetime.datetime(2018, 8, 1, 0, 0), 64), (datetime.datetime(2018, 8, 1, 0, 0), 10), (datetime.datetime(2018, 8, 1, 0, 0), 12), (datetime.datetime(2018, 8, 1, 0, 0), 32), (datetime.datetime(2018, 8, 1, 0, 0), 4), (datetime.datetime(2018, 9, 1, 0, 0), 2), (datetime.datetime(2018, 9, 1, 0, 0), 40), (datetime.datetime(2018, 9, 1, 0, 0), 16), (datetime.datetime(2018, 9, 1, 0, 0), -3), (datetime.datetime(2018, 9, 1, 0, 0), 5), (datetime.datetime(2018, 9, 1, 0, 0), 4), (datetime.datetime(2018, 9, 1, 0, 0), 1), (datetime.datetime(2018, 9, 1, 0, 0), -7), (datetime.datetime(2018, 9, 1, 0, 0), 3), (datetime.datetime(2018, 9, 1, 0, 0), 6), (datetime.datetime(2018, 9, 1, 0, 0), -2), (datetime.datetime(2018, 9, 1, 0, 0), -1), (datetime.datetime(2018, 9, 1, 0, 0), 32), (datetime.datetime(2018, 10, 1, 0, 0), 2), (datetime.datetime(2018, 10, 1, 0, 0), 8), (datetime.datetime(2018, 10, 1, 0, 0), 17), (datetime.datetime(2018, 10, 1, 0, 0), 3), (datetime.datetime(2018, 10, 1, 0, 0), 5), (datetime.datetime(2018, 10, 1, 0, 0), 9), (datetime.datetime(2018, 10, 1, 0, 0), 120), (datetime.datetime(2018, 10, 1, 0, 0), -1), (datetime.datetime(2018, 10, 1, 0, 0), 6), (datetime.datetime(2018, 10, 1, 0, 0), -6), (datetime.datetime(2018, 10, 1, 0, 0), 40), (datetime.datetime(2018, 10, 1, 0, 0), 16), (datetime.datetime(2018, 10, 1, 0, 0), 20), (datetime.datetime(2018, 10, 1, 0, 0), -3), (datetime.datetime(2018, 10, 1, 0, 0), 1), (datetime.datetime(2018, 10, 1, 0, 0), 4), (datetime.datetime(2018, 10, 1, 0, 0), 32), (datetime.datetime(2018, 10, 1, 0, 0), 7), (datetime.datetime(2018, 11, 1, 0, 0), 48), (datetime.datetime(2018, 11, 1, 0, 0), 4), (datetime.datetime(2018, 11, 1, 0, 0), 16), (datetime.datetime(2018, 11, 1, 0, 0), 80), (datetime.datetime(2018, 11, 1, 0, 0), 32), (datetime.datetime(2018, 11, 1, 0, 0), 12), (datetime.datetime(2018, 11, 1, 0, 0), 10), (datetime.datetime(2018, 11, 1, 0, 0), 5), (datetime.datetime(2018, 11, 1, 0, 0), -24), (datetime.datetime(2018, 11, 1, 0, 0), 6), (datetime.datetime(2018, 11, 1, 0, 0), 72), (datetime.datetime(2018, 11, 1, 0, 0), 2), (datetime.datetime(2018, 11, 1, 0, 0), -3), (datetime.datetime(2018, 11, 1, 0, 0), 13), (datetime.datetime(2018, 11, 1, 0, 0), -12), (datetime.datetime(2018, 11, 1, 0, 0), 3), (datetime.datetime(2018, 11, 1, 0, 0), 17), (datetime.datetime(2018, 11, 1, 0, 0), -1), (datetime.datetime(2018, 11, 1, 0, 0), 1), (datetime.datetime(2018, 11, 1, 0, 0), -5), (datetime.datetime(2018, 12, 1, 0, 0), -6), (datetime.datetime(2018, 12, 1, 0, 0), 5), (datetime.datetime(2018, 12, 1, 0, 0), 3), (datetime.datetime(2018, 12, 1, 0, 0), 12), (datetime.datetime(2018, 12, 1, 0, 0), 16), (datetime.datetime(2018, 12, 1, 0, 0), 8), (datetime.datetime(2018, 12, 1, 0, 0), 4), (datetime.datetime(2018, 12, 1, 0, 0), 128), (datetime.datetime(2018, 12, 1, 0, 0), 10), (datetime.datetime(2018, 12, 1, 0, 0), 6), (datetime.datetime(2018, 12, 1, 0, 0), 2), (datetime.datetime(2018, 12, 1, 0, 0), -1), (datetime.datetime(2018, 12, 1, 0, 0), 13), (datetime.datetime(2018, 12, 1, 0, 0), 1)], [(datetime.datetime(2017, 12, 1, 0, 0), 12.72), (datetime.datetime(2017, 12, 1, 0, 0), 25.49), (datetime.datetime(2017, 12, 1, 0, 0), 20.38), (datetime.datetime(2017, 12, 1, 0, 0), 10.95), (datetime.datetime(2017, 12, 1, 0, 0), 9.95), (datetime.datetime(2017, 12, 1, 0, 0), 12.75), (datetime.datetime(2017, 12, 1, 0, 0), 8.5), (datetime.datetime(2018, 1, 1, 0, 0), 8.5), (datetime.datetime(2018, 1, 1, 0, 0), 25.49), (datetime.datetime(2018, 1, 1, 0, 0), 12.75), (datetime.datetime(2018, 1, 1, 0, 0), 24.96), (datetime.datetime(2018, 1, 1, 0, 0), 9.95), (datetime.datetime(2018, 1, 1, 0, 0), 10.95), (datetime.datetime(2018, 1, 1, 0, 0), 19.96), (datetime.datetime(2018, 1, 1, 0, 0), 0.0), (datetime.datetime(2018, 2, 1, 0, 0), 12.75), (datetime.datetime(2018, 2, 1, 0, 0), 24.96), (datetime.datetime(2018, 2, 1, 0, 0), 10.95), (datetime.datetime(2018, 2, 1, 0, 0), 8.5), (datetime.datetime(2018, 2, 1, 0, 0), 19.96), (datetime.datetime(2018, 2, 1, 0, 0), 9.95), (datetime.datetime(2018, 3, 1, 0, 0), 24.96), (datetime.datetime(2018, 3, 1, 0, 0), 9.95), (datetime.datetime(2018, 3, 1, 0, 0), 10.95), (datetime.datetime(2018, 3, 1, 0, 0), 9.86), (datetime.datetime(2018, 3, 1, 0, 0), 4.0), (datetime.datetime(2018, 3, 1, 0, 0), 12.75), (datetime.datetime(2018, 3, 1, 0, 0), 19.96), (datetime.datetime(2018, 3, 1, 0, 0), 8.5), (datetime.datetime(2018, 4, 1, 0, 0), 19.96), (datetime.datetime(2018, 4, 1, 0, 0), 8.5), (datetime.datetime(2018, 4, 1, 0, 0), 9.95), (datetime.datetime(2018, 4, 1, 0, 0), 12.75), (datetime.datetime(2018, 4, 1, 0, 0), 24.96), (datetime.datetime(2018, 4, 1, 0, 0), 10.95), (datetime.datetime(2018, 5, 1, 0, 0), 24.96), (datetime.datetime(2018, 5, 1, 0, 0), 19.96), (datetime.datetime(2018, 5, 1, 0, 0), 9.95), (datetime.datetime(2018, 5, 1, 0, 0), 12.75), (datetime.datetime(2018, 5, 1, 0, 0), 10.95), (datetime.datetime(2018, 5, 1, 0, 0), 5.0), (datetime.datetime(2018, 6, 1, 0, 0), 12.75), (datetime.datetime(2018, 6, 1, 0, 0), 4.0), (datetime.datetime(2018, 6, 1, 0, 0), 8.5), (datetime.datetime(2018, 6, 1, 0, 0), 10.95), (datetime.datetime(2018, 6, 1, 0, 0), 19.96), (datetime.datetime(2018, 6, 1, 0, 0), 9.95), (datetime.datetime(2018, 6, 1, 0, 0), 19.95), (datetime.datetime(2018, 6, 1, 0, 0), 24.96), (datetime.datetime(2018, 7, 1, 0, 0), 19.96), (datetime.datetime(2018, 7, 1, 0, 0), 8.5), (datetime.datetime(2018, 7, 1, 0, 0), 24.96), (datetime.datetime(2018, 7, 1, 0, 0), 10.95), (datetime.datetime(2018, 7, 1, 0, 0), 9.95), (datetime.datetime(2018, 7, 1, 0, 0), 12.75), (datetime.datetime(2018, 8, 1, 0, 0), 10.95), (datetime.datetime(2018, 8, 1, 0, 0), 24.96), (datetime.datetime(2018, 8, 1, 0, 0), 19.96), (datetime.datetime(2018, 8, 1, 0, 0), 9.95), (datetime.datetime(2018, 8, 1, 0, 0), 8.5), (datetime.datetime(2018, 8, 1, 0, 0), 12.75), (datetime.datetime(2018, 9, 1, 0, 0), 10.95), (datetime.datetime(2018, 9, 1, 0, 0), 24.96), (datetime.datetime(2018, 9, 1, 0, 0), 9.95), (datetime.datetime(2018, 9, 1, 0, 0), 12.75), (datetime.datetime(2018, 10, 1, 0, 0), 12.75), (datetime.datetime(2018, 10, 1, 0, 0), 24.96), (datetime.datetime(2018, 10, 1, 0, 0), 9.95), (datetime.datetime(2018, 10, 1, 0, 0), 10.95), (datetime.datetime(2018, 11, 1, 0, 0), 12.75), (datetime.datetime(2018, 11, 1, 0, 0), 32.04), (datetime.datetime(2018, 11, 1, 0, 0), 24.96), (datetime.datetime(2018, 11, 1, 0, 0), 10.95), (datetime.datetime(2018, 12, 1, 0, 0), 32.04), (datetime.datetime(2018, 12, 1, 0, 0), 12.75), (datetime.datetime(2018, 12, 1, 0, 0), 10.95), (datetime.datetime(2018, 12, 1, 0, 0), 24.96)]]

I performed the same operations of loading the data into pandas, extracting the columns from each of the two dataframe and finding a correlation between them.

Now without the aggreagtes, I get pearson_coeff value as 0.0189 and spearman_coeff as 0.0395.

But it seems rather strange to me that the values have actually come down so drastically. For example the Pearson coefficient value has come down from 0.34 to 0.01 and Spearman coefficent value has come down from 0.28 to 0.03.

I am not sure why would there be such a sharp decline. If we look at the graph, the two metrics does seem to be somewhat getting along with each other in a positive way and I was expecting much larger value for the correlation.

How do I know which one to pick to determine the correlation? The correlations between the aggregated metrics or the coorelation between the non aggregated metrics? How do I verify if the result I am getting is valid?

Answer 1

There are several effects that could lead to this kind of results.

1. Your raw data may have different sequences because you have multiple values for the same index and the database may return the data in different order for different variables. You should aggregate on the day level (or more precisely on the level on which you can get the timestamp) because correlation requires the same order for both variables.

2. There could be a correlation on a monthly level and no correlation on a much lower level. Like December is a peak for both variables, but quantity and unit price happen on different days (e.g. different products). This happens especially when the variables have lots of zeros.

3. Your data shows only the times with non-zero data which is different for two variables. You need to insert the missing zeros before correlation.

4. You have negative values. If it is quantity it could be returns. I would recommend removing all negative values because they can completely destroy correlation. Returns appear several days after the buy and when you have only a negative value for the day then it does not give you any meaningful information about sales quantity during this day.

There is a possibility that you have all 4 problems.