如何在pandas中按日期将行更改为列？

Question

我有一个如下格式的csv数据文件，我想把行改成列，但这种转换需要按股票和日期进行。你知道吗

Ticker,Indicator,Date,Value
STOCK A,ACCRUALS,3/31/2005,-10.44
STOCK A,ACCRUALS,3/31/2006,0.44
STOCK A,AE,3/31/2005,3.97
STOCK A,AE,3/31/2006,3.67
STOCK A,ASETTO,3/31/2005,0.762
STOCK A,ASETTO,3/31/2006,0.9099

输出

Ticker,Date,ACCRUALS,AE,ASETTO
STOCK A,3/31/2005,-10.44,3.97,0.762
STOCK A,3/31/2006,0.44,3.67,0.9099

Answer 1

Ticker,Indicator,Date,Value
STOCK A,ACCRUALS,3/31/2005,-10.44
STOCK A,ACCRUALS,3/31/2006,0.44
STOCK A,AE,3/31/2005,3.97
STOCK A,AE,3/31/2006,3.67
STOCK A,ASETTO,3/31/2005,0.762
STOCK A,ASETTO,3/31/2006,0.9099

假设您的数据位于名为df的数据帧中：

>>> import pandas as pd
>>> df = df.set_index(df['Date'])
>>> for ind in set(df['Indicator']):
...     filtered_df = df[df['Indicator'] == ind]
...     df[ind] = filtered_df['Value']
... 
>>> cols_to_keep = ['Ticker', 'Date'] + list(set(df['Indicator']))
>>> trimmed_df = df[cols_to_keep]
>>> trimmed_df = trimmed_df.drop_duplicates()
>>> trimmed_df
            Ticker       Date  ACCRUALS    AE  ASETTO
Date                                                 
3/31/2005  STOCK A  3/31/2005    -10.44  3.97  0.7620
3/31/2006  STOCK A  3/31/2006      0.44  3.67  0.9099

它应该获取df['Indicator']的每个唯一值，并在df['Value']列中为该特定指示符创建一个新列。你知道吗

您可以使用reset_index()将数据帧的索引设置回零：

>>> trimmed_df.reset_index(drop = True)

而且，您可以不使用cols_to_keep，而是执行以下操作：

>>> trimmed_df.drop("Indicator", axis = 1, inplace = True)