python-2.7 - CSV 文件中的讨论

Question

我有一个包含如下三列的 CSV 文件，我想计算每种类型和日期的“AT”和“AT + LH”的值，然后将其放在另一个 CSV 文件的单独列中。

输入 CSV 文件:

╔═══════╦═══════╦═══════╗
║ Date  ║ Type  ║ Value ║
╠═══════╬═══════╬═══════╣
║ Nov-2 ║ M2222 ║ AT    ║
║ Nov-2 ║ M2222 ║ LH    ║
║ Nov-3 ║ M2222 ║ LH    ║
║ Nov-3 ║ M2223 ║ LH    ║
║ Nov-3 ║ M2223 ║ AT    ║
╚═══════╩═══════╩═══════╝

我需要这样的输出:

输出 CSV 文件:

╔═══════╦═══════╦═════════════════════════╦═════════════╗
║ Date  ║ Type  ║ Total (Total = AT + LH) ║ Value of AT ║
╠═══════╬═══════╬═════════════════════════╬═════════════╣
║ Nov-2 ║ M2222 ║                       2 ║           1 ║
║ Nov-3 ║ M2222 ║                       1 ║           0 ║
║ Nov-3 ║ M2223 ║                       2 ║           1 ║
╚═══════╩═══════╩═════════════════════════╩═════════════╝

我已经尝试为此使用词典，但无法获得所需的输出。但在输出中，我将 Date 和 Type 的列合并到一个列中。

代码:

from collections import Counter
import csv
from collections import defaultdict
from collections import OrderedDict
import collections
import pandas as pd

with open(r'file1','wb') as crack,open(r'file2','wb') as crack1:

  w = csv.writer(crack,delimiter=',', quoting = csv.QUOTE_ALL)
  w1 = csv.writer(crack1,delimiter=',', quoting = csv.QUOTE_ALL)

  for row in zip(['DATE & BASIC TYPE'],['Total Triggerred']):
    w.writerow(row)

  for row in zip(['DATE & BASIC TYPE'],['No of LOH']):
   w1.writerow(row)

 with open(r'inputfile, 'rb') as infile:
  reader = csv.reader(infile)
  next(reader, None)
  dict1=defaultdict(list)

  for row in reader:
   dict1[(row[0],row[1])].append(int(row[3]))

  for key, value in dict1.iteritems():
   for row in zip([key], [sum(dict1[key])]):
    #print key, sum(dict1[key])
      w.writerow(row)

 with open(r'inputfile', 'rb') as infile:
   reader = csv.reader(infile)
   next(reader, None)
   dict2=defaultdict(list)

 for row in reader:
   if row[2]=='AUTO RELEASE':
      dict2[(row[0],row[1])].append(int(row[3]))

 for key, value in dict2.iteritems():
   for row in zip([key], [sum(dict2[key])]):
    #print key, sum(dict2[key])
     w1.writerow(row)

 a = pd.read_csv(r'file1.csv')
 b = pd.read_csv(r'file2.csv')
 merged = a.merge(b, on='DATE & BASIC TYPE')
 merged.to_csv(r'outputfile.csv', index=False)

我得到这样的输出:

╔═══════════════════╦═══════╦═════════════╗
║    Date & Type    ║ Total ║ Value of AT ║
╠═══════════════════╬═══════╬═════════════╣
║ ('Nov-2','M2222') ║     2 ║           1 ║
║ ('Nov-3','M2222') ║     1 ║           0 ║
║ ('Nov-3','M2223') ║     2 ║           1 ║
╚═══════════════════╩═══════╩═════════════╝

Answer 1

我认为这可以帮助你:

import pandas as pd

df = pd.read_csv('input_csv.csv', sep=';')
print df.groupby(['Date','Type'])['Type','Value'].count()

---

这是 Albe 最初在评论中提交的附加代码。

import pandas as pd

#read csv file using data frame 
df = pd.read_csv('input_csv.csv', sep=',')

#count how many "AT" values in a data frame 
df['Value AT'] = (df['Value'] == 'AT').astype(int)

#sum "AT" and "LH" of a data frame
df['TOT'] = (df['Value'] == 'AT').astype(int) + (df['Value'] == 'LH').astype(int)

#grouping by data and type 
print df.groupby(['Date', 'Type'])['TOT', 'Value AT'].sum()

...最后您可以添加这段代码来创建 csv 输出文件:

#print df_grouped 
df_grouped.to_csv(r'outputfile.csv', index=True)