Python Count Unique value in Row csv

user_10293576 0
Viewed 6183

I have CSV, which is in a list. Example:

[[R2C1,R01,API_1,801,API_TEST01],
[R2C1,R01,API_1,802,API_TEST02],
[R2C1,R01,API_1,801,API_TEST03]]

Like to find out the all the unique in i[3] and count them. results:

[{num: 801, count: 2}, {num: 802, count: 1}]

so that I can call dict key for another test.

Code:

    for row in data[1:]:
    vnum = row[3]
    ipcount.append({"num":vnum,"count": count})
    if row[3] not in ipcount:
        ipcount.append({"num":vlan})
csv python
3 Answers

If you use the pandas library:

import pandas as pd
# Open your file using pd.read_csv() or from your list of lists
df = pd.DataFrame([['R2C1','R01','API_1',801,'API_TEST01'],
                   ['R2C1','R01','API_1',802,'API_TEST02'],
                   ['R2C1','R01','API_1',801,'API_TEST03']])
print(df)
      0    1      2    3           4
0  R2C1  R01  API_1  801  API_TEST01
1  R2C1  R01  API_1  802  API_TEST02
2  R2C1  R01  API_1  801  API_TEST03

Here you can use .value_counts() to get the number of each value in column 3, then using a dictionary comprehension transform this into the form you need:

[{'num': k, 'count': v} for k, v in dict(df[3].value_counts()).items()]
[{'num': 801, 'count': 2}, {'num': 802, 'count': 1}]
user_32797160

You can do this using a dictionary in order to group list items by num element. The last step is using a list comprehension in order to achieve your desired result.

dict = {}
for elem in data:
  if elem[3] not in dict:
    dict[elem[3]] = 0
  dict[elem[3]] = dict[elem[3]] + 1

final_list = [{'num' : elem, 'count': dict[elem]} for elem in dict]

Output

[{'num': 801, 'count': 2}, {'num': 802, 'count': 1}]
user_65831400

here a pure pandas approach without any loops

import pandas as pd 

# define path to data
PATH = u'path\to\data.csv'

# create panda datafrmae
df = pd.read_csv(PATH, usecols = [0,1,2,3], header = 0, names = ['a', 'b', 'c','num'])

# Add count to column of interest
df['count'] = df.groupby('num')['num'].transform('count')

# only keep unique values in column of interest
df.drop_duplicates(subset=['num'], inplace = True)

# create dict from bowth columns
your_output = dict(zip(df.num, df.count))
user_60600830
Related