Wsrtjtyk

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})

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}]

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}]

here a pure pandas approach without any loops

import pandas as pd 



# define path to data

PATH = u'pathtodata.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))