使用Pandas数据框的索引操作列

Question

使用Pandas数据框的索引操作列

3

这应该相对容易。我有一个Pandas数据框（Dates）：

    A   B   C
1/8/2017    1/11/2017   1/20/2017   1/25/2017
1/9/2017    1/11/2017   1/20/2017   1/25/2017
1/10/2017   1/11/2017   1/20/2017   1/25/2017
1/11/2017   1/20/2017   1/25/2017   1/31/2017
1/12/2017   1/20/2017   1/25/2017   1/31/2017
1/13/2017   1/20/2017   1/25/2017   1/31/2017

我希望对比Dates.index和Dates的区别。输出结果应该如下：

    A   B   C
1/8/2017     3   12      17 
1/9/2017     2   11      16 
1/10/2017    1   10      15 
1/11/2017    9   14      20 
1/12/2017    8   13      19 
1/13/2017    7   12      18

当然，我尝试了这个：

Dates - Dates.index

但我收到了这个美妙的TypeError错误：

TypeError: Could not operate DatetimeIndex...with block values ufunc subtract cannot use operands with types dtype('<M8[ns]') and dtype('O')

相反，我已经编写了一个循环逐列进行操作，但这似乎很愚蠢。有没有人能建议一种pythonic的方法来做到这一点？

编辑

In [1]: import pandas as pd
import numpy as np
import datetime
dates = pd.date_range('20170108',periods=6)
df = pd.DataFrame(np.empty([len(dates),3]),index=dates,columns=list('ABC'))
df['A'].loc[0:3] = datetime.date(2017, 1, 11)
df['B'].loc[0:3] = datetime.date(2017, 1, 20)
df['C'].loc[0:3] = datetime.date(2017, 1, 25)
df['A'].loc[3:6] = datetime.date(2017, 1, 20)
df['B'].loc[3:6] = datetime.date(2017, 1, 25)
df['C'].loc[3:6] = datetime.date(2017, 1, 31)

In [2]: print(df)
                     A           B           C
2017-01-08  2017-01-11  2017-01-20  2017-01-25
2017-01-09  2017-01-11  2017-01-20  2017-01-25
2017-01-10  2017-01-11  2017-01-20  2017-01-25
2017-01-11  2017-01-20  2017-01-25  2017-01-31
2017-01-12  2017-01-20  2017-01-25  2017-01-31
2017-01-13  2017-01-20  2017-01-25  2017-01-31

In [3]: df = df.sub(df.index.to_series(),axis=0)

ValueError: operands could not be broadcast together with shapes (18,) (6,)

- trob

2个回答

0

我认为更明确和优雅的方法是简单地使用 apply。

df = df.apply(pd.to_datetime，axis="columns") # 确保值为日期时间 df.apply(lambda x: x - df.index.to_series()，axis="rows)

- ehudk

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jezrael · Accepted Answer

您需要首先将所有列转换为日期时间格式，然后使用sub函数：

#if dtypes of all columns are datetime, omit it
date_cols = list('ABC')
for col in df.columns:
    df[col] = pd.to_datetime(df[col])

df = df.sub(df.index.to_series(),axis=0)
print (df)
                A       B       C
2017-01-08 3 days 12 days 17 days
2017-01-09 2 days 11 days 16 days
2017-01-10 1 days 10 days 15 days
2017-01-11 9 days 14 days 20 days
2017-01-12 8 days 13 days 19 days
2017-01-13 7 days 12 days 18 days

你需要使用dtypes和datetime64相关技术：

dates = pd.date_range('20170108',periods=6)
df = pd.DataFrame(index=dates)
df.loc[0:3, 'A'] = pd.Timestamp(2017, 1, 11)
df.loc[0:3, 'B'] = pd.Timestamp(2017, 1, 20)
df.loc[0:3, 'C'] = pd.Timestamp(2017, 1, 25)
df.loc[3:6, 'A'] = pd.Timestamp(2017, 1, 20)
df.loc[3:6, 'B'] = pd.Timestamp(2017, 1, 25)
df.loc[3:6, 'C'] = pd.Timestamp(2017, 1, 31)
print (df)
                    A          B          C
2017-01-08 2017-01-11 2017-01-20 2017-01-25
2017-01-09 2017-01-11 2017-01-20 2017-01-25
2017-01-10 2017-01-11 2017-01-20 2017-01-25
2017-01-11 2017-01-20 2017-01-25 2017-01-31
2017-01-12 2017-01-20 2017-01-25 2017-01-31
2017-01-13 2017-01-20 2017-01-25 2017-01-31

print (df.dtypes)
A    datetime64[ns]
B    datetime64[ns]
C    datetime64[ns]
dtype: object

df = df.sub(df.index.to_series(),axis=0)
print (df)
                A       B       C
2017-01-08 3 days 12 days 17 days
2017-01-09 2 days 11 days 16 days
2017-01-10 1 days 10 days 15 days
2017-01-11 9 days 14 days 20 days
2017-01-12 8 days 13 days 19 days
2017-01-13 7 days 12 days 18 days