转换时间序列格式 [英] Convert time series format

问题描述

我有一个时间序列的数据集(df)如下:

I have a dataset (df) of time series as follow:

date          symbol   close
09/01/2018    ACA      132.1
10/01/2018    ACA      134.4
...
28/04/2013    BA       22.12
...
21/01/2016    DIL      180.01
...

我们的想法是将其转换为:

The idea was to convert it as this:

date          ACA      BA      DIL
28/04/2013    NaN      22.12   NaN
...
21/01/2016    NaN      23      180.01
...
...
...
10/01/2018    134.4    32.2    181.3

因此在第一列上花费最长的时间，并匹配其他列的收盘价.我想它可以通过 VLOOKUP() 或 MATCH() 以某种方式完成.

So taking the longest period of time on first column, and match the close price on the others. I guess it can be done with VLOOKUP() or MATCH() somehow.

有什么想法吗?

推荐答案

您可以使用 Powerquery 做到这一点.

You can do this with Powerquery.

第 1 步:在您的数据范围内选择一个填充的单元格 如果是 Excel 2016 之前的版本(并已安装免费插件)或 2016 年的数据选项卡，则转到 PowerQuery 选项卡 > 数据 > 获取 &转换 > 从表

Step 1: Select a populated cell in your data range Goto PowerQuery tab if pre Excel 2016 (and have installed the free-add in) or Data tab in 2016 > Data > Get & Transform > from table

第 2 步:确保 date 列的格式为日期

Step 2: Make sure date column is formatted as Date

第 3 步:按升序排列 date 列

Step 3: Order date column in ascending order

第 4 步:选择 symbol 列 > 转换选项卡 > 透视列

Step 4: Select symbol column > Transform tab > Pivot column

确保值部分使用 Close

第 5 步:根据需要重新排列列

Step 5: Re-arrange columns as required

第 6 步:关闭并加载到页面

Step 6: Close and load to page

注意:您不能用 NaN 替换 Null.导出到工作表时，这些单元格将为空白.

Note: You do not replace Null with NaN. When exported to sheet these cells will be blank.

M 代码:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"symbol", type text}, {"close", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[symbol]), "symbol", "close", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"date", "ACA", "BA", "DIL"})
in
    #"Reordered Columns"

这篇关于转换时间序列格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！