Creating a dictionary and aggregate the values with the corresponding key

Question

My question is to find the highest value of the 9am humidity (stored in column 12, starting from zero) for each state. The data set looks like this:

Date,State,City,Station Code,Minimum temperature (C),Maximum temperature (C),Rainfall (mm),Evaporation (mm),Sunshine (hours),Direction of maximum wind gust,Speed of maximum wind gust (km/h),9am Temperature (C),9am relative humidity (%),3pm Temperature (C),3pm relative humidity (%)
2017-12-01,NSW,Wollongong,068228,19.0,22.9,0,,,NE,59,21.4,81,21.9,80
2017-12-01,QLD,Brisbane,040913,21.8,28.0,0.2,5.6,12.4,E,26,26.6,65,27.1,64
2017-12-01,VIC,Melbourne,086338,21.5,23.5,1.8,11.4,0.0,N,41,21.8,92,18.9,98
2017-12-02,QLD,Gold Coast,040764,22.4,28.4,0,,,N,44,27.6,69,28.0,72
2017-12-02,NSW,Wollongong,068228,17.7,26.6,0,,,W,87,19.9,82,18.1,83
2017-12-02,QLD,Brisbane,040913,22.3,29.4,0,8.0,9.2,NNE,31,26.9,64,26.8,65
2017-12-02,NSW,Canberra,070351,17.7,19.6,35.2,,,NNW,65,17.8,99,16.0,88
2017-12-02,NSW,Newcastle,061055,19.3,28.2,0,,,NW,54,24.0,100,27.3,90
2017-12-02,VIC,Melbourne,086338,14.7,16.4,20.6,3.0,0.0,SSW,56,15.1,97,16.1,93
2017-12-02,SA,Adelaide,023034,15.0,20.4,0.2,4.6,5.0,SSW,57,17.8,52,18.1,59
2017-12-02,NSW,Albury,072160,18.4,20.6,49.6,,,NNE,39,18.5,96,20.1,76
2017-12-03,VIC,Ballarat,089002,8.1,14.4,13.6,,,S,50,10.1,94,13.3,68
2017-12-03,NSW,Wollongong,068228,16.7,24.7,7.2,,,WNW,44,19.7,63,20.7,70
2017-12-03,VIC,Geelong,087184,11.5,17.5,5.8,,,SSW,50,14.0,93,13.6,86
2017-12-03,VIC,Bendigo,081123,10.5,19.8,2.4,,,SSW,48,13.8,65,17.4,46
2017-12-03,WA,Perth,009021,20.1,36.0,0,15.8,4.6,E,70,29.0,19,34.8,12
2017-12-03,QLD,Toowoomba,041529,17.0,25.5,19.8,,,NNW,31,20.0,80,24.3,55
2017-12-03,NSW,Canberra,070351,12.2,19.9,18.4,,,NW,48,13.8,85,19.3,61
2017-12-03,NSW,Newcastle,061055,16.8,27.2,9.4,,,NNW,52,20.7,100,26.3,78
2017-12-03,QLD,Sunshine C,040861,22.1,28.1,3.0,,,NE,35,26.9,68,24.6,91
2017-12-03,QLD,Gold Coast,040764,22.2,27.8,4.0,,,NNW,41,25.5,78,25.3,86
2017-12-03,SA,Adelaide,023034,12.8,19.9,0.6,6.2,5.7,S,50,16.2,52,15.9,65
2017-12-03,NSW,Albury,072160,13.7,22.3,29.6,,,W,39,14.9,83,21.8,43
2017-12-03,VIC,Melbourne,086338,11.5,17.1,43.8,4.8,2.4,S,44,13.2,79,16.5,64
2017-12-03,QLD,Brisbane,040913,23.0,30.4,0.8,7.0,4.4,NE,22,27.0,60,28.6,64
2017-12-04,QLD,Toowoomba,041529,17.1,20.1,0.2,,,SW,28,18.2,77,18.6,73
2017-12-04,VIC,Geelong,087184,12.1,17.7,3.8,,,S,39,14.8,83,14.9,92
2017-12-04,QLD,Gold Coast,040764,20.6,25.6,0,,,SSE,35,24.0,99,18.7,100
2017-12-04,VIC,Ballarat,089002,9.0,16.0,0,,,S,44,10.7,98,13.3,70
2017-12-04,NSW,Albury,072160,12.0,22.7,0,,,NE,54,16.2,50,20.8,37
2017-12-04,SA,Adelaide,023034,13.4,21.5,0.2,6.6,8.3,SW,46,16.6,57,20.0,45
2017-12-04,QLD,Townsville,032040,23.9,31.3,5.0,,,ENE,35,28.5,72,30.7,62
2017-12-04,NSW,Wollongong,068228,14.7,18.7,4.6,,,SSW,52,16.3,83,15.6,92
2017-12-04,NT,Darwin,014015,24.4,35.3,0.2,0.6,9.4,NW,28,29.3,69,34.6,45
2017-12-04,QLD,Sunshine C,040861,19.5,25.8,16.6,,,SSW,30,25.1,64,21.1,93
2017-12-04,NSW,Newcastle,061055,17.4,19.8,0,,,SSW,50,19.4,100,17.1,100
2017-12-04,NSW,Canberra,070351,10.7,15.7,0.2,,,S,28,11.4,90,12.4,87
2017-12-04,VIC,Melbourne,086338,12.4,17.5,5.4,5.0,0.5,SSE,37,15.9,70,17.5,67
2017-12-04,VIC,Bendigo,081123,10.3,19.1,0.2,,,SSE,44,14.0,64,16.8,47
2017-12-04,WA,Perth,009021,19.5,24.6,5.6,8.4,0.9,S,28,20.1,89,23.1,70
2017-12-04,QLD,Cairns,031011,23.4,31.8,4.0,,,SSE,50,29.6,71,30.8,73
2017-12-04,QLD,Brisbane,040913,20.8,28.0,0,5.2,3.1,W,26,27.0,61,21.2,87
2017-12-05,WA,Perth,009021,19.0,28.0,0,2.8,12.8,WSW,43,24.2,53,26.0,51
2017-12-05,NSW,Wollongong,068228,15.4,21.8,5.0,,,SSW,69,18.2,75,19.3,68
2017-12-05,VIC,Ballarat,089002,6.9,19.3,0,,,SE,44,12.9,86,17.7,63
2017-12-05,QLD,Sunshine C,040861,19.5,26.5,60.6,,,WSW,63,22.7,76,17.8,82
2017-12-05,NSW,Albury,072160,10.6,25.6,1.8,,,S,31,18.4,58,24.7,42
2017-12-05,VIC,Geelong,087184,8.6,20.4,1.4,,,SE,43,16.8,84,20.2,69
2017-12-05,NT,Darwin,014015,28.0,35.5,0,6.4,12.0,NW,28,30.6,64,33.4,55
2017-12-05,VIC,Melbourne,086338,10.8,22.8,1.8,1.2,6.9,ESE,39,16.7,71,21.2,54
2017-12-05,VIC,Bendigo,081123,9.9,24.8,0.2,,,SE,37,17.0,58,24.3,37
2017-12-05,QLD,Cairns,031011,22.0,34.2,19.0,,,SE,33,30.1,64,32.7,39
2017-12-05,QLD,Townsville,032040,22.7,34.7,0,,,ENE,41,29.6,44,29.4,74
2017-12-05,SA,Adelaide,023034,12.2,20.9,0,5.2,10.2,WSW,41,17.6,53,19.7,49
2017-12-05,NSW,Newcastle,061055,16.6,20.5,0.2,,,SSW,56,17.8,100,19.1,100
2017-12-05,QLD,Gold Coast,040764,18.4,26.7,33.8,,,SE,31,22.9,91,26.2,77
2017-12-05,QLD,Toowoomba,041529,15.4,26.0,1.0,,,WSW,39,19.6,72,25.0,34
2017-12-05,NSW,Canberra,070351,11.4,20.0,6.6,,,SE,46,15.5,70,17.6,58
2017-12-06,VIC,Bendigo,081123,12.1,26.9,0,,,W,33,18.7,63,25.6,30
2017-12-06,NSW,Canberra,070351,11.9,20.2,5.2,,,WNW,50,15.3,67,17.8,63
2017-12-06,WA,Perth,009021,16.7,28.9,0,10.6,13.2,SSE,50,21.8,40,28.6,24
2017-12-06,VIC,Melbourne,086338,13.6,21.3,0,5.0,7.1,SSW,33,17.3,70,20.3,63
2017-12-06,QLD,Cairns,031011,21.4,31.6,0.2,,,NE,31,29.3,64,30.7,65
2017-12-06,QLD,Sunshine C,040861,15.5,29.7,8.0,,,NNE,35,24.9,41,28.9,40
2017-12-06,NSW,Newcastle,061055,16.7,27.2,0,,,WNW,54,20.3,100,25.4,
2017-12-06,QLD,Townsville,032040,23.9,33.2,0,,,NE,33,30.0,67,31.9,48
2017-12-06,NT,Darwin,014015,26.4,34.8,0,7.6,10.4,WNW,33,30.9,70,33.8,51
2017-12-06,QLD,Brisbane,040913,17.8,31.8,,5.0,13.0,WSW,30,26.8,32,30.9,23
2017-12-06,NSW,Wollongong,068228,16.9,25.1,0,,,SW,43,19.4,63,20.9,59
2017-12-06,SA,Adelaide,023034,13.7,21.4,0,7.0,13.1,SW,46,17.7,54,21.2,46
2017-12-06,QLD,Toowoomba,041529,12.9,26.7,0,,,WNW,50,20.4,40,25.9,22
2017-12-06,NSW,Albury,072160,12.3,26.6,2.0,,,WNW,44,18.5,57,24.6,38
2017-12-06,QLD,Gold Coast,040764,17.6,30.7,1.0,,,NW,41,26.1,37,30.0,23
2017-12-06,VIC,Ballarat,089002,11.2,21.3,0,,,SE,46,14.9,85,19.9,60
2017-12-06,VIC,Geelong,087184,14.1,21.0,0,,,S,33,17.0,82,20.1,67
2017-12-07,NSW,Wollongong,068228,17.7,30.1,0.4,,,NNW,41,24.8,45,23.4,63
2017-12-07,VIC,Bendigo,081123,9.3,24.6,0,,,WNW,37,18.3,46,22.1,39
2017-12-07,QLD,Townsville,032040,26.6,33.1,0,,,NNE,35,29.8,64,32.0,51
2017-12-07,QLD,Gold Coast,040764,16.0,28.5,0,,,ENE,24,27.3,56,27.0,62
2017-12-07,QLD,Sunshine C,040861,15.7,29.8,0,,,NE,28,27.5,36,28.8,45
2017-12-07,VIC,Melbourne,086338,11.6,21.9,0,4.4,7.0,SSW,39,16.9,65,21.3,48
2017-12-07,VIC,Ballarat,089002,6.6,19.2,0.2,,,WSW,39,13.2,94,18.0,52
2017-12-07,QLD,Brisbane,040913,17.4,33.1,0,10.0,13.1,ENE,24,27.6,37,30.7,32
2017-12-07,QLD,Cairns,031011,22.9,31.5,0,,,NE,30,29.1,71,30.9,67
2017-12-07,VIC,Geelong,087184,9.8,22.7,0,,,SSW,41,15.7,88,18.2,69
2017-12-07,WA,Perth,009021,14.6,29.8,0,12.2,13.2,ESE,46,20.8,45,28.6,21
2017-12-07,NSW,Albury,072160,11.5,29.1,0,,,W,56,20.7,57,28.2,30
2017-12-07,NSW,Canberra,070351,8.7,26.7,0.2,,,WNW,54,18.5,56,25.4,31
2017-12-07,SA,Adelaide,023034,11.5,19.3,1.2,7.4,4.2,SSW,52,14.0,84,18.5,65
2017-12-07,NT,Darwin,014015,28.2,34.5,0,8.0,9.9,NW,35,31.4,67,33.0,58
2017-12-07,QLD,Toowoomba,041529,15.8,29.9,0,,,WNW,30,24.4,33,29.2,20

I have tried this so far:

humidity = {}
states= {}
is_first_line = True

for row in open("climate_data_Dec2017.csv"):
  if is_first_line:
    is_first_line = False
  else:
    values = row.split(",")
    humidity_at_nine = values[12]
    states = values[1]
  print(states, ":", float(humidity_at_nine))

So I have created a dictionary for states and humidity, but I can't get to find the maximum humidity value, and then correspond it with a state. I have also done the if function to clear the first line, and then splitted all the values up to find the humidity and state values through indexing. I have tried doing max(humidity_at_nine) but it says it doesnt work because it is not iterable or something, or a float/str?

My desired answer is:

NSW : 100.0
NT : 70.0
QLD : 99.0
SA : 84.0
VIC : 98.0
WA : 89.0

I think I should find max_humidity = max(humidity_at_nine) and then put that into the print, whilst printing the corresponding state? — D.Ronald, May 08 '18 at 05:21
yea find the maximum humidity for each state as in the deesired answer — D.Ronald, May 08 '18 at 05:28

Shivam Singh · Accepted Answer · 2018-05-08T05:46:25.727

1

One of the simplest ways would be to construct a dictionary with state as key, and corresponding array of humidities as a value, see this code.

states= {}
is_first_line = True
humidity_at_nine = 0

for row in open("test.csv"):
    if is_first_line:
        is_first_line = False
    else:
        values = row.split(",")
        humidity_at_nine = values[12]
        state = values[1]
        if state in states.keys():
            states[state].append(float(humidity_at_nine))
        else:
            states[state] = [float(humidity_at_nine)]

# sort keys in alphabetical order    
order_list = sorted(states.keys())

for item in order_list:
    print(item,max(states[item]))

Output:

NSW 100.0
NT 70.0
QLD 99.0
SA 84.0
VIC 98.0
WA 89.0

edited May 08 '18 at 05:46

answered May 08 '18 at 05:30

Shivam Singh

1,584
1
10
9

How do you sort the output? – D.Ronald May 08 '18 at 05:34
In the particualr order in the desired answer, becuase i will neet to use a sort method somewhere – D.Ronald May 08 '18 at 05:35
See https://stackoverflow.com/questions/613183/how-do-i-sort-a-dictionary-by-value – IonicSolutions May 08 '18 at 05:36
haha also im a noob, so i dont understand the code if state in states.keys(): states[state].append(float(humidity_at_nine)) else: states[state] = [float(humidity_at_nine)] for k,v in states.items(): print(k,max(v)) - How K and V is allocated to whatever values it is printed out – D.Ronald May 08 '18 at 05:38
Thats okay, what is your order of output based on? You need sorting on which parameter? Or it is just the particular order mentioned in the question? – Shivam Singh May 08 '18 at 05:42
sorted by the key @ShivamSingh so i assumed its by the states – D.Ronald May 08 '18 at 05:43
It looks like the sattes are also in alphabetical order – D.Ronald May 08 '18 at 05:44

score 1 · Answer 2 · answered May 08 '18 at 05:30

Building on your code, the following will give you the desired output and make use of the dictionary you created:

states = {}
is_first_line = True

for row in open("climate_data_Dec2017.csv"):
    if is_first_line:
        is_first_line = False
    else:
        values = row.split(",")
        humidity_at_nine = float(values[12])
        state = values[1]

        try:
            states[state].append(humidity_at_nine)
        except KeyError:
            states[state] = [humidity_at_nine]

for state, humidity in states.items():
    print("%s: %0.2f" % (state, max(humidity)))

Miraj50 · Answer 3 · 2018-05-08T05:56:19.480

You can achieve what you want in much less code than you think.

d = {} # we shall use a dictionary

with open("climate_data_Dec2017.csv",'r') as f:
    next(f) # skip the first line
    for line in f:
        l = line.split(',')
        if l[1] in d: # if the state is there update it if needed
            if float(l[12]) > d[l[1]]:
                d[l[1]] = float(l[12]) # remember the floats are strings now after the split operation
        else: # otherwise make a new key value pair for the new state
            d[l[1]] = float(l[12])

for i,j in sorted(d.items()):
    print("{} : {}".format(i,j)) # string formatting to get the desired format

Output:

NSW : 100.0
NT : 70.0
QLD : 99.0
SA : 84.0
VIC : 98.0
WA : 89.0

It seems you want the output sorted by the keys, isn't it? So you can use the sorted function on d.items(), which will return a list of keys from the dictionary.

[`why dict.get instead of dict[key]`](https://stackoverflow.com/questions/11041405/why-dict-getkey-instead-of-dictkey) — Patrick Artner, May 08 '18 at 05:50
@PatrickArtner Thats why I have checked first if the value is there in d or not. Otherwise I could also have used a default dict. But I guess the OP needs a simple answer. — Miraj50, May 08 '18 at 05:52

score 1 · Answer 4 · answered May 08 '18 at 05:52

Using the csv modules csv.DictReader.

Demo:

import csv
with open(filename) as infile:
    reader = csv.DictReader(infile, delimiter=",")
    d = {}
    for i in reader:
        if i["State"] not in d:
            d[i["State"]] = []
        d[i["State"]].append(float(i["9am relative humidity (%)"]))
print(dict((k, max(v)) for k,v in d.items()))

Output:

{'VIC': 98.0, 'WA': 89.0, 'QLD': 99.0, 'NT': 70.0, 'SA': 84.0, 'NSW': 100.0}

score 1 · Answer 5 · answered May 08 '18 at 05:52

If you can use pandas library then, you can do it in a line;

df.groupby(['State'])['9am relative humidity (%)'].max().to_dict()

Full code:

In [1]: from io import StringIO

In [2]: import pandas as pd

In [3]: date_str = '''
        ....
   ...: 2017-12-07,NT,Darwin,014015,28.2,34.5,0,8.0,9.9,NW,35,31.4,67,33.0,58
   ...: 2017-12-07,QLD,Toowoomba,041529,15.8,29.9,0,,,WNW,30,24.4,33,29.2,20
   ....
        '''

In [4]: df = pd.read_csv(StringIO(data_str))

In [5]: df.groupby(['State'])['9am relative humidity (%)'].max().to_dict()
Out[5]: {'NSW': 100, 'NT': 70, 'QLD': 99, 'SA': 84, 'VIC': 98, 'WA': 89}

Patrick Artner · Answer 6 · 2018-05-08T05:53:31.980

To create the dict, you can set dictionary values with the max of your current temp and the already stored temp.

To avoid ValueErrors, use dict.get(key,defaultValue) (see here) like so:

humidity = {}
is_first_line = True
humidity_at_nine = '0'

for row in open("climate_data_Dec2017.csv"):
    if is_first_line:
        is_first_line = False
    else: 
        values = row.split(",")
        humidity_at_nine = float(values[12]) # make it float
        states = values[1]

        # set the keys value to max of itself and humidity_at_nine
        humidity[states] = max(humidity.get(states,humidity_at_nine),humidity_at_nine)

# sort by state name (dict are unordered, they might come out as you wish
# this makes it explicit. The lambda is strictly speeking not nessessary,
# tuples get sorted by 0-index, 1-index (on tie of 0-index), etc anyway

for k,v in sorted(humidity.items(), key = lambda x:x[0]):
        print(f'{k} : {v}')

Output:

NSW : 100.0
NT : 70.0
QLD : 99.0
SA : 84.0
VIC : 98.0
WA : 89.0

score 0 · Answer 7 · answered May 08 '18 at 06:02

Dump the data to array and find all states. Dump states to states aray and create humidity to each state. Find maximum for each state with following algorithm:

arr =[]
states=[]

lines = open("weather.txt").readlines()

i = 0

for line in lines:
    if i>0:
        arr.append(line.split(","))
        states.append(line.split(",")[1])
    i += 1

states = sorted(list(set(states)))

hum = [float(0)]*len(states)

for state in states:
    for l in arr:
        l_hum = float(l[-3])
        state_idx = states.index(state)
        l_state = l[1]
        if l_state == state and l_hum > hum[state_idx]:
            hum[state_idx] = l_hum

for i in range(0,len(states)):
    print(states[i], hum[i])

Creating a dictionary and aggregate the values with the corresponding key

7 Answers7