<p>也许我的解释不够清楚。从源csv我需要一个与我显示的完全一样的透视表,但是每个星期都有一个关于源记录的范围。
终于找到了解决办法,任何改进都是欢迎的。可能对其他人有用:</p>
<pre><code>def week_range( date ):
""" Utility function. Returns start and end dates for a given date (starting Monday) """
year, week, dow = date.isocalendar()
if dow == 7:
start_date = date
else:
start_date = date - timedelta( dow )
end_date = start_date + timedelta( 6 )
return (start_date, end_date)
# Load and get first date (fecha) in the sorted by date csv file
df = pd.read_csv("data.csv")
nextDate = df.head(1)
nextDate = pd.to_datetime(nextDate["fecha"].values[0])
continueNextWeek = True
while continueNextWeek:
# Get week start and end dates for the current date
datefrom, dateto = week_range( nextDate )
fmindate = (df.fecha.astype( 'datetime64[ns]' ) >= datefrom )
fmaxdate = (df.fecha.astype( 'datetime64[ns]' ) <= dateto )
# Get a by date filtered dataframe
dfFiltered = df[ (df.tipo == "Saliente") & (fmindate & fmaxdate) ]
# If there are records for that week, generate pivot table
if dfFiltered.shape[0]:
txt = pd.pivot_table(
dfFiltered,
columns=[ "fecha" ],
index=[ "org", "tipo", "estado" ],
values=[ "destino", "duracion_total", "duracion_media" ],
aggfunc={ "destino": len, "duracion_total": total_secs_inTimeSerie, "duracion_media": mean_secs_inTimeSerie },
fill_value="", margins=True
)
# Write it to disk
report_name = "report-{}-{}.html".format(datefrom.strftime('%Y-%m-%d'),dateto.strftime('%Y-%m-%d'))
with open(os.path.join("out",report_name), "w") as text_file:
text_file.write ( txt.to_html() )
# Stop if the week end date is greater than last record in set
if dateto > df.tail(1)["fecha"].astype( 'datetime64[ns]').values[0]:
continueNextWeek = False
else:
nextDate = dateto + timedelta(1)
</code></pre>