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?