熊猫read_csv和过滤器列与usecols

I have a csv file which isn't coming in correctly with pandas.read_csv when I filter the columns with usecols and use multiple indexes.

我有一个csv文件不能正确地输入到熊猫。当我使用usecols过滤列并使用多个索引时，读取_csv。

import pandas as pd
csv = r"""dummy,date,loc,x
   bar,20090101,a,1
   bar,20090102,a,3
   bar,20090103,a,5
   bar,20090101,b,1
   bar,20090102,b,3
   bar,20090103,b,5"""

f = open('foo.csv', 'w')
f.write(csv)
f.close()

df1 = pd.read_csv('foo.csv',
        header=0,
        names=["dummy", "date", "loc", "x"], 
        index_col=["date", "loc"], 
        usecols=["dummy", "date", "loc", "x"],
        parse_dates=["date"])
print df1

# Ignore the dummy columns
df2 = pd.read_csv('foo.csv', 
        index_col=["date", "loc"], 
        usecols=["date", "loc", "x"], # <----------- Changed
        parse_dates=["date"],
        header=0,
        names=["dummy", "date", "loc", "x"])
print df2

I expect that df1 and df2 should be the same except for the missing dummy column, but the columns come in mislabeled. Also the date is getting parsed as a date.

我期望df1和df2应该是相同的，除了缺少假列之外，但是列的标签是错误的。这个日期也被解析为一个日期。

In [118]: %run test.py
               dummy  x
date       loc
2009-01-01 a     bar  1
2009-01-02 a     bar  3
2009-01-03 a     bar  5
2009-01-01 b     bar  1
2009-01-02 b     bar  3
2009-01-03 b     bar  5
              date
date loc
a    1    20090101
     3    20090102
     5    20090103
b    1    20090101
     3    20090102
     5    20090103

Using column numbers instead of names give me the same problem. I can workaround the issue by dropping the dummy column after the read_csv step, but I'm trying to understand what is going wrong. I'm using pandas 0.10.1.

使用列号而不是名称也给了我同样的问题。我可以通过在read_csv步骤之后删除假列来解决这个问题，但是我正在尝试理解哪里出错了。我用熊猫0.10.1。

edit: fixed bad header usage.

编辑:修正头的错误用法。

4 个解决方案

#1

The answer by @chip completely misses the point of two keyword arguments.

@chip的答案完全忽略了两个关键字参数的要点。

• names is only necessary when there is no header and you want to specify other arguments using column names rather than integer indices.
• 只有在没有头时才需要名称，并且您希望使用列名而不是整数索引来指定其他参数。
• usecols is supposed to provide a filter before reading the whole DataFrame into memory; if used properly, there should never be a need to delete columns after reading.
• usecols应该在将整个数据aframe读入内存之前提供一个过滤器;如果使用得当，应该永远不需要在阅读后删除列。

This solution corrects those oddities:

这个解决方案纠正了那些奇怪的地方:

import pandas as pd
from StringIO import StringIO

csv = r"""dummy,date,loc,x
bar,20090101,a,1
bar,20090102,a,3
bar,20090103,a,5
bar,20090101,b,1
bar,20090102,b,3
bar,20090103,b,5"""

df = pd.read_csv(StringIO(csv),
        header=0,
        index_col=["date", "loc"], 
        usecols=["date", "loc", "x"],
        parse_dates=["date"])

Which gives us:

这给我们:

                x
date       loc
2009-01-01 a    1
2009-01-02 a    3
2009-01-03 a    5
2009-01-01 b    1
2009-01-02 b    3
2009-01-03 b    5

#2

This code achieves what you want --- also its weird and certainly buggy:

这段代码实现了你想要的——当然，它也很奇怪，而且有问题:

I observed that it works when:

我发现当:

a) you specify the index_col rel. to the number of columns you really use -- so its three columns in this example, not four (you drop dummy and start counting from then onwards)

a)您将index_col rel.指定到实际使用的列数——因此在本例中它有三列，而不是四列(您删除假列，然后开始计数)

b) same for parse_dates

parse_dates b)相同

c) not so for usecols ;) for obvious reasons

c) usecols不是这样;)原因显而易见

d) here I adapted the names to mirror this behaviour

我在这里修改了名字以反映这种行为。

import pandas as pd
from StringIO import StringIO

csv = """dummy,date,loc,x
bar,20090101,a,1
bar,20090102,a,3
bar,20090103,a,5
bar,20090101,b,1
bar,20090102,b,3
bar,20090103,b,5
"""

df = pd.read_csv(StringIO(csv),
        index_col=[0,1],
        usecols=[1,2,3], 
        parse_dates=[0],
        header=0,
        names=["date", "loc", "", "x"])

print df

which prints

的打印

                x
date       loc   
2009-01-01 a    1
2009-01-02 a    3
2009-01-03 a    5
2009-01-01 b    1
2009-01-02 b    3
2009-01-03 b    5

#3

If your csv file contains extra data, columns can be deleted from the DataFrame after import.

如果您的csv文件包含额外的数据，则可以在导入后从DataFrame中删除列。

import pandas as pd
from StringIO import StringIO

csv = r"""dummy,date,loc,x
bar,20090101,a,1
bar,20090102,a,3
bar,20090103,a,5
bar,20090101,b,1
bar,20090102,b,3
bar,20090103,b,5"""

df = pd.read_csv(StringIO(csv),
        index_col=["date", "loc"], 
        usecols=["dummy", "date", "loc", "x"],
        parse_dates=["date"],
        header=0,
        names=["dummy", "date", "loc", "x"])
del df['dummy']

Which gives us:

这给我们:

                x
date       loc
2009-01-01 a    1
2009-01-02 a    3
2009-01-03 a    5
2009-01-01 b    1
2009-01-02 b    3
2009-01-03 b    5

#4

import csv first and use csv.DictReader its easy to process...

先导入csv并使用csv。它很容易处理……