idx.index = idx['date']
时间: 2023-10-16 09:08:43 浏览: 32
This line of code is creating a new index for a pandas DataFrame called `idx`. The new index is being set to the values in the `date` column of the DataFrame. This allows for easier and more efficient indexing and filtering of the data based on the dates.
相关问题
帮我优化以下sql select s.* from ifare.structred_rule s right join ( select MAX(r.VERSION) vers, r.status, r.tbl_no, r.live_from, r.data_type, r. source from ifare.structred_rule r where (r.status in ('8', '')) group by r.status, r.tbl_no, r.live_from, r.data_type, r. source) tp on s.version = tp.vers and s.tbl_no = tp.tbl_no and s.status = tp.status and s.live_from = tp.live_from and s.data_type = tp.data_type and s.SOURCE = tp.source and s.status <> '-1' where s.version ='00001' and s.remark = 'D' and s.audited_date >= '20211010' and s.audited_date <='20211011' order by s.db_date asc, s.tbl_no asc, s.version desc
首先,可以将右连接转换为内连接,因为在右连接的情况下,我们只对右表进行了聚合,而不是左表。这可能会影响查询性能。其次,可以将子查询的结果存储到临时表中,以便后续查询使用。最后,可以为联接条件创建索引以提高查询性能。
下面是优化后的SQL:
```
CREATE TEMPORARY TABLE tmp_structred_rule AS
SELECT MAX(r.VERSION) AS vers, r.status, r.tbl_no, r.live_from, r.data_type, r.source
FROM ifare.structred_rule r
WHERE r.status IN ('8', '')
GROUP BY r.status, r.tbl_no, r.live_from, r.data_type, r.source;
SELECT s.*
FROM ifare.structred_rule s
JOIN tmp_structred_rule tp
ON s.version = tp.vers
AND s.tbl_no = tp.tbl_no
AND s.status = tp.status
AND s.live_from = tp.live_from
AND s.data_type = tp.data_type
AND s.SOURCE = tp.source
WHERE s.status <> '-1'
AND s.version = '00001'
AND s.remark = 'D'
AND s.audited_date >= '20211010'
AND s.audited_date <= '20211011'
ORDER BY s.db_date ASC, s.tbl_no ASC, s.version DESC;
```
同时,为联接条件创建以下索引:
```
CREATE INDEX idx_structred_rule_version ON ifare.structred_rule (version);
CREATE INDEX idx_structred_rule_tbl_no ON ifare.structred_rule (tbl_no);
CREATE INDEX idx_structred_rule_status ON ifare.structred_rule (status);
CREATE INDEX idx_structred_rule_live_from ON ifare.structred_rule (live_from);
CREATE INDEX idx_structred_rule_data_type ON ifare.structred_rule (data_type);
CREATE INDEX idx_structred_rule_source ON ifare.structred_rule (source);
```
这些优化措施应该能够提高查询性能。
df = pd.read_csv('stock_comments_analyzed.csv', parse_dates=['created_time']) grouped = df['polarity'].groupby(df.created_time.dt.date) def BI_Simple_func(row): pos = row[row == 1].count() neg = row[row == 0].count() return (pos-neg)/(pos+neg) BI_Simple_index = grouped.apply(BI_Simple_func) def BI_func(row): pos = row[row == 1].count() neg = row[row == 0].count() bi = np.log(1.0 * (1+pos) / (1+neg)) return bi BI_index = grouped.apply(BI_func) sentiment_idx = pd.concat([BI_index.rename('BI'), BI_Simple_index.rename('BI_Simple')], axis=1) quotes = pd.read_csv('./data/sh000001.csv', parse_dates=['date']) quotes.set_index('date', inplace=True) sentiment_idx.index = pd.to_datetime(sentiment_idx.index) merged = pd.merge(sentiment_idx, quotes, how='left', left_index=True, right_index=True) merged.fillna(method='ffill', inplace=True) merged['BI_MA'] = merged['BI'].rolling(window=10, center=False).mean() merged['BI_Simple_MA'] = merged['BI_Simple'].rolling(window=10, center=False).mean() merged.to_csv('merged_sentiment_idx.csv')
这段代码是关于股票情绪指数的计算和处理,包括读取股票评论数据并按日期分组,定义了两个函数用于计算简单的情绪指数和 BI 指数,然后将这些指数与股票行情数据进行合并并计算移动平均数,最后将结果保存到 csv 文件中。其中,BI 指数是一种用于衡量市场情绪的指标,通过计算买方和卖方之间的力量平衡来反映市场的情绪状态。