Skip to main content
 首页 » 编程设计

python之如何转换存储为两列(开始、结束)的日期范围以创建新的行索引并为值创建累积率

2024年10月24日15yyy_WW

我想知道如何转换存储为两列(开始、结束)的日期范围以创建新的行索引?例如我想转换以下数据:

    end         start     value 
0   2000-01-04  2000-01-02  6 
1   2000-01-05  2000-01-03  9 

收件人:

date      rate 
2000-01-02  2 
2000-01-03  5 
2000-01-04  5 
2000-01-05  3 

注意:

开始和结束显示范围,比率是在时间范围内分布的值,我正在寻找每天所有比率的总和

请您参考如下方法:

import pandas as pd 
import numpy as np 
import io 
 
temp=u"""end,start,value 
2000-01-04,2000-01-02,6 
2000-01-05,2000-01-03,9""" 
 
df = pd.read_csv(io.StringIO(temp), parse_dates = [0,1]) 
print df 
#change ordering for filling date from start to end 
df = df[['start', 'end', 'value']] 
 
#value divided difference of start and end, but it cant count first day, so has to be added 
df['value'] = df['value']/(df['end'] + pd.Timedelta('1 days')- df['start']).astype('timedelta64[D]') 
 
df['Id'] = df.index 
#reshape datetimes from rows to columns 
df = pd.melt(df, id_vars=[ 'value','Id'], var_name=['D'], value_name='Date') 
#remove unnecessary column D 
del df['D'] 
print df 
#   value  Id       Date 
#0      2   0 2000-01-02 
#1      3   1 2000-01-03 
#2      2   0 2000-01-04 
#3      3   1 2000-01-05 
 
#set multiindex 
df = df.set_index(['Id', 'Date' ]) 
 
#fill gap between start and end dates 
f = lambda df: df.asfreq("D", method='ffill') 
df = df.reset_index(level=0).groupby('Id').apply(f) 
 
del df['Id'] 
df = df.reset_index() 
print df 
#   Id       Date  value 
#0   0 2000-01-02      2 
#1   0 2000-01-03      2 
#2   0 2000-01-04      2 
#3   1 2000-01-03      3 
#4   1 2000-01-04      3 
#5   1 2000-01-05      3 
 
#sum column value to column rate 
df['rate'] = df.groupby('Date')['value'].transform('sum') 
#delete unnecessary columns 
df = df.drop(['Id', 'value'], axis=1 ) 
#drop duplicity 
df = df.drop_duplicates() 
print df 
# 
#        Date  rate 
#0 2000-01-02     2 
#1 2000-01-03     5 
#2 2000-01-04     5 
#5 2000-01-05     3