使用 read_csv 将财务数据导入 Python Pandas

问题描述

我有一个具有以下结构的 .csv:

I have a .csv with the following structure:

date_begin,date_end,name,name_code,active_accounts,transaction_amount,transaction_count
1/1/2008,1/31/2008,Name_1,1001,"123,456","$7,890,123.45","67,890"
2/1/2008,2/29/2008,Name_1,1001,"43,210","$987,654.32","109,876"
3/1/2008,3/31/2008,Name_1,1001,"485,079","$1,265,789,433.98","777,888"
...
12/1/2008,12/31/2008,Name_1,1001,"87,543","$432,098,987","87,987"
1/1/2008,1/31/2008,Name_2,1002,"268,456","$890,123.45","97,890"
2/1/2008,2/29/2008,Name_2,1002,"53,210","$987,654.32","109,876"
...
etc

我正在尝试使用以下代码将它们读入熊猫数据帧:

I am trying to read them into into a pandas dataframe by using the following code:

import pandas as pd

data = pd.read_csv('my_awesome_csv.csv'),parse_dates=[[0,1]],
                   infer_datetime_format=True)

这很好用，只是我想控制每一列中的数据类型.当我在解释器中运行以下代码时，我发现引号中的数字不会被识别为数字，无论是美元还是其他.

This works just fine except that I would like to control the data types in each column. When I run the following code in the interpreter I discover that the numbers in quotes do not get recognized as numbers, either dollars or otherwise.

In [10]: data.dtypes
Out[10]:
date_begin_date_end       object
name                      object
name_code                  int64
active_accounts           object  # Problem, I want this to be a number
transaction_amount        object  # Ditto, I want this to be a number (it's a dollar amount)
transaction_count         object  # Still a number!
dtype: object

我在 中做了一些窥探Pandas csv 文档 但还没有找到我正在寻找的关于声明类型的内容，当它们在csv中保存为带有逗号和美元符号的字符串时.我在这里的最终目标是能够对这些列中的值进行一些算术运算.

I have done some snooping around in the Pandas csv documentation but haven't found what I'm looking for about declaring types that are amounts when they are saved as strings with commas and dollar signs in the csv. My ultimate goal here is to be able to do some arithmetic operations on the values in these columns.

有什么想法吗?

推荐答案

你可以使用 矢量化字符串方法在调用后解析这些列read_csv:

You could use vectorized string methods to parse those columns after the call toread_csv:

import pandas as pd
import decimal
D = decimal.Decimal

data = pd.read_csv('data', parse_dates=[[0,1]], infer_datetime_format=True)

for col in ('active_accounts', 'transaction_count'):
    data[col] = data[col].str.replace(r',', '').astype(int)

data['transaction_amount'] = (data['transaction_amount']
                              .str.replace(r'[^-+d.]', '').astype(D))


print(data.dtypes)
# date_begin_date_end    object
# name                   object
# name_code               int64
# active_accounts         int64
# transaction_amount     object
# transaction_count       int64
# dtype: object

print(data)

收益

    date_begin_date_end    name  name_code  active_accounts
0    1/1/2008 1/31/2008  Name_1       1001           123456
1    2/1/2008 2/29/2008  Name_1       1001            43210
2    3/1/2008 3/31/2008  Name_1       1001           485079
3  12/1/2008 12/31/2008  Name_1       1001            87543
4    1/1/2008 1/31/2008  Name_2       1002           268456
5    2/1/2008 2/29/2008  Name_2       1002            53210

  transaction_amount  transaction_count
0         7890123.45              67890
1          987654.32             109876
2      1265789433.98             777888
3          432098987              87987
4          890123.45              97890
5          987654.32             109876

附注.read_csv 确实有一个 转换器参数您可以使用它提供一个函数来解析有问题的列.这些为每个字符串调用一次函数.如果你有很多行，那可能需要很多 Python 函数调用.使用矢量化字符串方法处理列，如上所示应该快得多.

PS. read_csv does have a convertersparameterwith which you could supply a function to parse the problematic columns. Thesefunctions are called once for each string. If you have a lot of rows, that could be require a lot of Python functioncalls. Handling the columns using vectorized string methods, as shown aboveshould be far quicker.

import pandas as pd
import re
import decimal
D = decimal.Decimal

def make_parser(cls):
    def parse_commas(text):
        return cls(re.sub(r'[^-+d.]', '', text))
    return parse_commas

to_int = make_parser(int)
to_decimal = make_parser(D)

data = pd.read_csv('data', parse_dates=[[0,1]], infer_datetime_format=True
                   , converters={4: to_int, 5: to_decimal, 6: to_int})

print(data)

收益

    date_begin_date_end    name  name_code  active_accounts
0    1/1/2008 1/31/2008  Name_1       1001           123456
1    2/1/2008 2/29/2008  Name_1       1001            43210
2    3/1/2008 3/31/2008  Name_1       1001           485079
3  12/1/2008 12/31/2008  Name_1       1001            87543
4    1/1/2008 1/31/2008  Name_2       1002           268456
5    2/1/2008 2/29/2008  Name_2       1002            53210

  transaction_amount  transaction_count
0         7890123.45              67890
1          987654.32             109876
2      1265789433.98             777888
3          432098987              87987
4          890123.45              97890
5          987654.32             109876

transaction_amount 列中的值是小数.小数:

and the values in the transaction_amount column are decimal.Decimals:

In [64]: data.loc[0, 'transaction_amount']
Out[64]: Decimal('7890123.45')

这篇关于使用 read_csv 将财务数据导入 Python Pandas的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！