不带pand的CSV文件中重复键的求和值

Question

我有一个大的数据集，如下所示

party,cp,qualifier,amount
ABC,DEF,GOOGLE_2,100
ABC,DEF,GOOGLE_2,200
GHI,JKL,FACEBOOK_1,500
GHI,JKL,FACEBOOK_1,-600

我想输出：

ABC,DEF,GOOGLE,300
GHI,JKL,FACEBOOK,-100

以下是我迄今为止的python代码：

headers = ["valuation_date","party_group_name","type","party_name","cp_group_name","cp_name","qualifier","amount"]
data = {}
with open(t1file,'rb') as f:
        reader = csv.reader(f)
        headers = reader.next()
        for row in reader:
                party = row[headers.index('party')]
                cp = row[headers.index('cp')]
                qualifier = row[headers.index('qualifier')]
                amount = row[headers.index('amount')]
                if row[headers.index('type')] == "Equity":
                        new_qualifier = qualifier.split("_")[0]
                        if party in data.keys():
                                if cp in data.keys():
                                        if new_qualifier in data.keys():
                                                data[party][cp][new_qualifier] += float(amount)
                                        else:
                                                data[party][cp][qualifier][amount] = data[party][cp][new_qualifier][amount]
                                else:
                                        data[cp] = cp
                        else:
                             data[party] = party

运行上述代码时，出现以下错误：

  data[party][cp][qualifier][amount] = data[party][cp][new_qualifier][amount]
TypeError: string indices must be integers, not str

非常生疏与Python道歉，如果它是明显的讣告，但任何见解，我做错了什么？ 谢谢！你知道吗

Answer 1

可以使用pandas.drop_duplicates删除多个列的副本，并将其与pandas.groupby()&；sum组合以获得所需的结果

>>>import pandas as pd
>>>#read file using pandas.read_csv()
>>>df
  party   cp   qualifier  amount
0   ABC  DEF    GOOGLE_2     100
1   ABC  DEF    GOOGLE_2     200
2   GHI  JKL  FACEBOOK_1     500
3   GHI  JKL  FACEBOOK_1    -600

>>>df['Total'] = df.groupby(['party','cp','qualifier'])['amount'].transform('sum')
>>>print(df.drop_duplicates(subset=['party','cp','qualifier'], keep='last'))

  party   cp   qualifier  amount  Total
1   ABC  DEF    GOOGLE_2     200    300
3   GHI  JKL  FACEBOOK_1    -600   -100