作者:泥泥的春天_565_576 | 来源:互联网 | 2023-02-07 06:55
我尝试按照一天中的天和小时(X->天,Y->小时)从熊猫数据帧生成热图.结果应该是这样的:
数据源是postgres中的一个表:
id | created_at
---------+------------------------
2558145 | 2017-03-02 11:31:15+01
2558146 | 2017-03-02 11:31:46+01
2558147 | 2017-03-02 11:32:28+01
2558148 | 2017-03-02 11:32:57+01
....
这是我的代码按小时重新组合数据.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:postgres@localhost:5432/bla')
import datetime
import matplotlib.pyplot as plt
import numpy as np
%matplotlib inline
from matplotlib.dates import date2num
import seaborn as sns
df = pd.read_sql_query("""
SELECT created_at, 1 as print
FROM foo
WHERE created_at > '2017-02-01'
AND created_at <'2017-03-01'""", con=engine)
df['created_at'] = pd.to_datetime(df['created_at'])
df.index = df['created_at']
df = df.resample('H')['print'].sum()
df.fillna(0, inplace=True)
print(df.head())
created_at
2017-02-01 07:00:00+00:00 1.0
2017-02-01 08:00:00+00:00 152.0
2017-02-01 09:00:00+00:00 101.0
2017-02-01 10:00:00+00:00 92.0
2017-02-01 11:00:00+00:00 184.0
Freq: H, Name: print, dtype: float64
结果看起来很好,但我无法弄清楚如何绘制这个数据帧?
1> ImportanceOf..:
热图是二维图,它将x和y对映射到一个值.这意味着热图的输入必须是2D数组.
在这里,您希望将数组的列表示天数,将行表示为小时.作为第一步,我们需要在数据帧的两个不同列中包含数天和数小时.然后可以将这些列重新整形为2D数组,这需要知道有多少天和几小时.如果还要求实际上每天/每小时对都有一个条目.
如果没有这个限制,我们可以使用a pivot_table
来聚合表中的值.这在以下解决方案中显示.
import pandas as pd
import numpy as np; np.random.seed(0)
import seaborn.apionly as sns
import matplotlib.pyplot as plt
# create dataframe with datetime as index and aggregated (frequency) values
date = pd.date_range('2017-02-23', periods=10*12, freq='2h')
freq = np.random.poisson(lam=2, size=(len(date)))
df = pd.DataFrame({"freq":freq}, index=date)
# add a column hours and days
df["hours"] = df.index.hour
df["days"] = df.index.map(lambda x: x.strftime('%b-%d'))
# create pivot table, days will be columns, hours will be rows
piv = pd.pivot_table(df, values="freq",index=["hours"], columns=["days"], fill_value=0)
#plot pivot table as heatmap using seaborn
ax = sns.heatmap(piv, square=True)
plt.setp( ax.xaxis.get_majorticklabels(), rotation=90 )
plt.tight_layout()
plt.show()
对于绘图,您还可以使用matplotlib imshow图,如下所示:
fig, ax = plt.subplots()
im = ax.imshow(piv, cmap="Greens")
fig.colorbar(im, ax=ax)
ax.set_xticks(range(len(piv.columns)))
ax.set_yticks(range(len(piv.index)))
ax.set_xticklabels(piv.columns, rotation=90)
ax.set_yticklabels(piv.index)
ax.set_xlabel("Days")
ax.set_ylabel("Hours")
plt.tight_layout()
plt.show()