python：根据索引事件将时间间隔数据分成两天的块

huangapple
go评论96阅读模式
英文:

python: sorting time interval data into two days chucks based on index event

问题

  1. 我有以下数据：

df =
id date_medication medication index_date
1 2000-01-01 A 2000-01-04
1 2000-01-02 A 2000-01-04
1 2000-01-05 B 2000-01-04
1 2000-01-06 B 2000-01-04
2 2000-01-01 A 2000-01-05
2 2000-01-03 B 2000-01-05
2 2000-01-06 A 2000-01-05
2 2000-01-10 B 2000-01-05

  2. 我想将数据转换为围绕索引事件（IE）的两天时间段。创建新的列代表时间间隔，如下：

df =
id -4 -2 0 2 4 6
1 A A IE B 0 0
2 A B IE A A B

  2. <details>
  3. <summary>英文:</summary>
  5. I have the following data:

df =
id date_medication medication index_date
1 2000-01-01 A 2000-01-04
1 2000-01-02 A 2000-01-04
1 2000-01-05 B 2000-01-04
1 2000-01-06 B 2000-01-04
2 2000-01-01 A 2000-01-05
2 2000-01-03 B 2000-01-05
2 2000-01-06 A 2000-01-05
2 2000-01-10 B 2000-01-05

  2. and I would like to transform the data into two days&#39; chucks around the index event (IE). That is creating new columns representing the time intervals such as:

df =
id -4 -2 0 2 4 6
1 A A IE B 0 0
2 A B IE A A B

  3. </details>
  6. # 答案1
  7. **得分**: 2
  9. ```python
  10. #将列转换为日期时间
  11. df['date_medication'] = pd.to_datetime(df['date_medication'])
  12. df['index_date'] = pd.to_datetime(df['index_date'])
  14. #获取2天的时间段
  15. s = df['date_medication'].sub(df['index_date']).dt.days // 2 * 2
  16. #对大于等于0的值添加2天
  17. s.loc[s.ge(0)] += 2
  19. #透视列
  20. df1 = df.assign(g=s).pivot(index='id', columns='g', values='medication')
  21. #添加0列
  22. df1.loc[:, 0] = 'IE'
  23. #添加0列
  24. df1 = (df1.rename_axis(columns=None)
  25.          .reindex(columns=range(df1.columns.min(), df1.columns.max() + 2, 2), fill_value=0)
  26.          .fillna(0)
  27.          .reset_index())
  28.    id -4 -2   0  2  4  6
  29. 0   1  A  A  IE  B  B  0
  30. 1   2  A  B  IE  A  0  B
  32. print(s)
  33. 0   -4
  34. 1   -2
  35. 2    2
  36. 3    4
  37. 4   -4
  38. 5   -2
  39. 6    2
  40. 7    6
  41. dtype: int64
英文:

Use:

  1. #convert columns to datetimes
  2. df[&#39;date_medication&#39;] = pd.to_datetime(df[&#39;date_medication&#39;])
  3. df[&#39;index_date&#39;] = pd.to_datetime(df[&#39;index_date&#39;])
  5. #get 2 days chunks
  6. s = df[&#39;date_medication&#39;].sub(df[&#39;index_date&#39;]).dt.days // 2 * 2
  7. #add 2 days for greater/equal values 0 
  8. s.loc[s.ge(0)] += 2
  10. #pivoting columns
  11. df1 = df.assign(g = s).pivot(index=&#39;id&#39;, columns=&#39;g&#39;, values=&#39;medication&#39;)
  12. #added 0 column
  13. df1.loc[:, 0] = &#39;IE&#39;
  14. #added 0 column
  15. df1 = (df1.rename_axis(columns=None)
  16.          .reindex(columns=range(df1.columns.min(), df1.columns.max() + 2, 2), fill_value=0)
  17.          .fillna(0)
  18.          .reset_index())
  19.    id -4 -2   0  2  4  6
  20. 0   1  A  A  IE  B  B  0
  21. 1   2  A  B  IE  A  0  B

Details:

  1. print (s)
  2. 0   -4
  3. 1   -2
  4. 2    2
  5. 3    4
  6. 4   -4
  7. 5   -2
  8. 6    2
  9. 7    6
  10. dtype: int64

答案2

得分: 2

  1. # 计算差值
  2. days = df['date_medication'].sub(df['index_date']).dt.days
  4. # 创建所需的区间和标签
  5. bins = np.arange(days.min() - days.min() % 2, days.max() + days.max() % 2 + 1, 2)
  6. lbls = bins[bins != 0]  # 排除0
  7. df['interval'] = pd.cut(days, bins, labels=lbls, include_lowest=True, right=False)
  9. # 重塑数据框
  10. out = (df.pivot(index='id', columns='interval', values='medication')
  11.          .reindex(bins, fill_value='IE', axis=1).fillna(0)
  12.          .rename_axis(columns=None).reset_index())
英文:

You can use:

  1. # Compute the delta
  2. days = df[&#39;date_medication&#39;].sub(df[&#39;index_date&#39;]).dt.days
  4. # Create desired bins and labels
  5. bins = np.arange(days.min() - days.min() % 2, days.max() + days.max() % 2 + 1, 2)
  6. lbls = bins[bins != 0]  # Exclude 0
  7. df[&#39;interval&#39;] = pd.cut(days, bins, labels=lbls, include_lowest=True, right=False)
  9. # Reshape your dataframe
  10. out = (df.pivot(index=&#39;id&#39;, columns=&#39;interval&#39;, values=&#39;medication&#39;)
  11.          .reindex(bins, fill_value=&#39;IE&#39;, axis=1).fillna(0)
  12.          .rename_axis(columns=None).reset_index())

Output:

  1. &gt;&gt;&gt; out
  2.    id -4 -2   0  2  4  6
  3. 0   1  A  A  IE  B  B  0
  4. 1   2  A  B  IE  A  0  B

huangapple
  • 本文由 发表于 2023年3月9日 18:17:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/75683209.html
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定