pandas TypeError:仅对DatetimeIndex，TimedeltaIndex或PeriodIndex有效，但具有"Int64Index"的实例 [英] Pandas TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Int64Index'

问题描述

我有一些要分析的订单数据. 当前感兴趣的是:在哪个月份购买了多少SKU?

I've got some order data that I want to analyse. Currently of interest is: How often has which SKU been bought in which month?

这里有个小例子:

import datetime
import pandas as pd
import numpy as np

d = {'sku': ['RT-17']}
df_skus = pd.DataFrame(data=d)
print(df_skus)

d = {'date': ['2017/02/17', '2017/03/17', '2017/04/17', '2017/04/18', '2017/05/02'], 'item_sku': ['HT25', 'RT-17', 'HH30', 'RT-17', 'RT-19']}
df_orders = pd.DataFrame(data=d)
print(df_orders)

for i in df_orders.index:
    print("\n toll")
    df_orders.loc[i,'date']=pd.to_datetime(df_orders.loc[i, 'date'])

df_orders = df_orders[df_orders["item_sku"].isin(df_skus["sku"])]
monthly_sales = df_orders.groupby(["item_sku", pd.Grouper(key="date",freq="M")]).size()
monthly_sales = monthly_sales.unstack(0) 

print(monthly_sales)

这很好，但是如果我使用真实订单数据(来自CSV)，则几分钟后会得到:

That works fine, but if I use my real order data (from CSV) I get after some minutes:

TypeError:仅对DatetimeIndex，TimedeltaIndex或PeriodIndex有效，但有一个'Int64Index'实例

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Int64Index'

问题出在那一行:

monthly_sales = df_orders.groupby(["item_sku"，pd.Grouper(key ="date"，freq ="M")]).size()

monthly_sales = df_orders.groupby(["item_sku", pd.Grouper(key="date",freq="M")]).size()

是否可以跳过该错误? 我尝试了一个尝试，除了块:

Is it possible to skip over the error? I tried a try except block:

try:
    monthly_sales = df_orders.groupby(["item_sku", pd.Grouper(key="date",freq="M")]).size()
    monthly_sales = monthly_sales.unstack(0) 
except:
    print "\n Here seems to be one issue"

然后我得到印刷品(monthly_sales)

Then I get for the print(monthly_sales)

空DataFrame
列:[txn_id，日期，item_sku，数量]
索引:[]

Empty DataFrame
Columns: [txn_id, date, item_sku, quantity]
Index: []

那么我的数据中的某些内容可能会清空或阻止分组? 如何清除"我的数据?
还是我会在这里和那里丢失销售数据就可以了，如果我可以跳过"错误，这有可能吗?

So something in my data empties or brakes the grouping it seems like? How can I 'clean' my data?
Or I'd be even fine with loosing the data of a sale here and there if I can just 'skip' over the error, is this possible?

推荐答案

读取CSV时，请使用parse_dates参数-

When reading your CSV, use the parse_dates argument -

df_order = pd.read_csv('file.csv', parse_dates=['date'])

自动将date转换为日期时间.如果这不起作用，则需要将其作为字符串加载，然后将errors='coerce'参数与pd.to_datetime-

Which automatically converts date to datetime. If that doesn't work, then you'll need to load it in as a string, and then use the errors='coerce' argument with pd.to_datetime -

df_order['date'] = pd.to_datetime(df_order['date'], errors='coerce')

请注意，您可以将系列对象(除其他外)传递给pd.to_datetime`.

Note that you can pass series objects (amongst other things) to pd.to_datetime`.

接下来，按照您的操作进行过滤和分组，它应该可以正常工作.

Next, filter and group as you've been doing, and it should work.

df_orders[df_orders["item_sku"].isin(df_skus["sku"])]\
     .groupby(['item_sku', pd.Grouper(key='date', freq='M')]).size()

item_sku  date      
RT-17     2017-03-31    1
          2017-04-30    1

这篇关于 pandas TypeError:仅对DatetimeIndex，TimedeltaIndex或PeriodIndex有效，但具有"Int64Index"的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！