Here I found myself working data collection from City-Data.com where we needed to three variables ('Median year house/condo built', 'Median household income', 'Median house or condo value' and 'Median resident age') for a few hundred zip codes as seen below.
There are several polygons that makeup a zip code, so an average value for all the variable is what we needed.
The raw data collected from the web page is like this:-
The expected output from above that will be used in excel average function is like this:-
This can be done manually, but because there are many of those zip code it will take much effort and even much longer time to complete. So, I decided to use python data wrangling capability to accomplish this.
Here below is a scenario where we have multiple zip codes with possibly many other sub zip codes:-
Each main zip code is separated by '===================='
The expected result is:-
Solution
Some aspects of the data wrangling script that just involve 'find and replace' were done manually since a text editor can easily handle it.
Step 1:
Use text editor to remove unwanted strings from the original data, the resulting output is this...
Note:
Step 2:
Read the column above into pandas dataframe and transpose at each occurrence of 'XXXX' into a column.
Note:
a) Each empty space that represent Sub zip codes are now replaced by 'YYYY'.
b) Each column that has no sub zip code will have 'YYYY' appended manually to its last row.
Step 3:
Read in the updated table above and conduct the data wrangling.
The result of above script is as follow:-
Step 4:
Text file above is now manually cleaned using find and replace tool in text editor to get the final output as seen below.
This can now be added to excel spreadsheet to apply the average function as intended.
That is it!
There are several polygons that makeup a zip code, so an average value for all the variable is what we needed.
The raw data collected from the web page is like this:-
55388
2004
Median household income: $74,048
Median house or condo value: $158,600
Median resident age: 30.8
1976
Median household income: $74,583
Median house or condo value: $305,400
Median resident age: 48
1962
Median household income: $68,958
Median house or condo value: $218,900
Median resident age: 41.7
1994
Median household income: $58,587
Median house or condo value: $172,900
Median resident age: 39.5
1997
Median household income: $94,042
Median house or condo value: $233,300
Median resident age: 36.7
1986
Median household income: $138,917
Median house or condo value: $446,200
Median resident age: 39.9
2000
Median household income: $104,125
Median house or condo value: $263,800
Median resident age: 34.6
The expected output from above that will be used in excel average function is like this:-
55388As you have already notice, all we have to do is to wrap each of 'Median year house/condo built', 'Median house or condo value' and 'Median resident age' together into the function '=AVERAGE(...)'.
=AVERAGE(2004, 1976, 1962, 1994, 1997, 1986, 2000)
=AVERAGE(74048, 74583, 68958, 58587, 94042, 138917, 104125)
=AVERAGE(158600, 305400, 218900, 172900, 233300, 446200, 263800)
=AVERAGE(30.8, 48, 41.7, 39.5, 36.7, 39.9, 34.6)
This can be done manually, but because there are many of those zip code it will take much effort and even much longer time to complete. So, I decided to use python data wrangling capability to accomplish this.
Here below is a scenario where we have multiple zip codes with possibly many other sub zip codes:-
Each main zip code is separated by '===================='
55055
1962
Median household income: $88,018
Median house or condo value: $187,200
Median resident age: 45.6
1973
Median household income: $49,514
Median house or condo value: $164,900
Median resident age: 34.7
1993
Median household income: $59,464
Median house or condo value: $195,800
Median resident age: 43.1
====================
55071
1984
Median household income: $67,768
Median house or condo value: $169,200
Median resident age: 34.7
1965
Median household income: $70,577
Median house or condo value: $156,600
Median resident age: 34.4
2003
Median household income: $127,255
Median house or condo value: $320,500
Median resident age: 31.7
1961
Median household income: $47,434
Median house or condo value: $112,800
Median resident age: 37.1
1973
Median household income: $77,540
Median house or condo value: $182,000
Median resident age: 41.2
====================
55313 B
1995
Median household income: $86,161
Median house or condo value: $251,600
Median resident age: 34.8
2002
Median household income: $73,858
Median house or condo value: $219,700
Median resident age: 42.5
1952
Median household income: $34,453
Median house or condo value: $114,700
Median resident age: 27
1992
Median household income: $77,712
Median house or condo value: $169,900
Median resident age: 43
1983
Median household income: $61,442
Median house or condo value: $201,300
Median resident age: 44.4
1988
Median household income: $88,750
Median house or condo value: $320,200
Median resident age: 36.8
1978
Median household income: $71,000
Median house or condo value: $255,900
Median resident age: 44.9
1976
Median household income: $74,583
Median house or condo value: $305,400
Median resident age: 48
====================
55327
2000
Median household income: $108,214
Median house or condo value: $278,000
Median resident age: 34.5
1979
Median household income: $89,250
Median house or condo value: $247,400
Median resident age: 47.4
1979
Median household income: $72,656
Median house or condo value: $229,400
Median resident age: 38.6
1999
Median household income: $100,208
Median house or condo value: $260,300
Median resident age: 36
====================
55343 D
1951
Median household income: $40,722
Median house or condo value: $435,200
Median resident age: 20.4
1947
Median household income: $88,661
Median house or condo value: $202,100
Median resident age: 52.9
1952
Median household income: $81,250
Median house or condo value: $268,000
Median resident age: 48.3
====================
55344 C
1985
Median household income: $62,344
Median house or condo value: $167,200
Median resident age: 31.9
1982
Median household income: $55,536
Median house or condo value: $93,500
Median resident age: 29.1
1987
Median household income: $50,202
Median house or condo value: $154,300
Median resident age: 31.3
1993
Median household income: $113,371
Median house or condo value: $260,200
Median resident age: 40.1
1983
Median household income: $106,917
Median house or condo value: $369,700
Median resident age: 43.5
1983
Median household income: $77,685
Median house or condo value: $178,100
Median resident age: 42.3
====================
55379 E
1989
Median household income: $54,839
Median house or condo value: $162,100
Median resident age: 36.2
1995
Median household income: $139,946
Median house or condo value: $482,000
Median resident age: 43.4
1998
Median household income: $73,147
Median house or condo value: $175,400
Median resident age: 33.2
2004
Median household income: $136,719
Median house or condo value: $425,900
Median resident age: 39.9
2000
Median household income: $108,272
Median house or condo value: $327,200
Median resident age: 36.8
====================
55403 B
1966
Median household income: $22,313
Median house or condo value: $114,400
Median resident age: 26.5
1973
Median household income: $39,836
Median house or condo value: $116,700
Median resident age: 28.9
1980
Median household income: $37,043
Median house or condo value: $183,400
Median resident age: 49.9
1980
Median household income: $31,159
Median house or condo value: $185,400
Median resident age: 28.8
1968
Median household income: $21,771
Median house or condo value: $257,700
Median resident age: 30.4
1984
Median household income: $24,737
Median house or condo value: $97,400
Median resident age: 37.1
1988
Median household income: $43,167
Median house or condo value: $155,300
Median resident age: 38.2
1988
Median household income: $70,243
Median house or condo value: $155,300
Median resident age: 31.1
1984
Median household income: $43,542
Median house or condo value: $91,300
Median resident age: 40.1
1980
Median household income: $13,244
Median house or condo value: $300,000
Median resident age: 42.2
1939
Median household income: $23,672
Median house or condo value: $722,200
Median resident age: 30.3
====================
55413 C
1939
Median household income: $56,667
Median house or condo value: $179,500
Median resident age: 33.3
1939
Median household income: $33,643
Median house or condo value: $158,900
Median resident age: 24.3
1939
Median household income: $42,604
Median house or condo value: $173,000
Median resident age: 29.1
====================
55414 A
1988
Median household income: $91,500
Median house or condo value: $418,900
Median resident age: 52.4
1939
Median household income: $31,563
Median house or condo value: $166,700
Median resident age: 25.1
2003
Median household income: $34,705
Median house or condo value: $395,500
Median resident age: 30.9
1939
Median household income: $39,107
Median house or condo value: $272,500
Median resident age: 25.9
1939
Median household income: $46,250
Median house or condo value: $155,600
Median resident age: 30.4
1939
Median household income: $53,500
Median house or condo value: $201,200
Median resident age: 24.7
1939
Median household income: $27,530
Median house or condo value: $163,900
Median resident age: 23.3
1939
Median household income: $27,083
Median house or condo value: $332,500
Median resident age: 22.9
1945
Median household income: $32,565
Median house or condo value: $625,000
Median resident age: 25.5
1939
Median household income: $32,683
Median house or condo value: $150,000
Median resident age: 21.6
====================
55439 A
1973
Median household income: $70,074
Median house or condo value: $301,500
Median resident age: 81.7
1970
Median household income: $144,844
Median house or condo value: $661,200
Median resident age: 47.2
1971
Median household income: $150,250
Median house or condo value: $587,000
Median resident age: 43.8
1993
Median household income: $62,308
Median house or condo value: $465,600
Median resident age: 37.4
55439 B
1965
Median household income: $100,988
Median house or condo value: $269,700
Median resident age: 42.4
1970
Median household income: $146,477
Median house or condo value: $618,600
Median resident age: 49.9
1983
Median household income: $79,028
Median house or condo value: $323,300
Median resident age: 60
1975
Median household income: $112,639
Median house or condo value: $344,200
Median resident age: 52.5
1979
Median household income: $125,313
Median house or condo value: $374,900
Median resident age: 54.4
1982
Median household income: $79,250
Median house or condo value: $456,500
Median resident age: 46.1
1956
Median household income: $101,250
Median house or condo value: $371,000
Median resident age: 43.9
1965
Median household income: $135,750
Median house or condo value: $441,200
Median resident age: 51.4
1964
Median household income: $107,083
Median house or condo value: $473,000
Median resident age: 45.2
====================
55441 B
1979
Median household income: $82,605
Median house or condo value: $183,300
Median resident age: 38.5
1962
Median household income: $61,154
Median house or condo value: $212,000
Median resident age: 27.1
1977
Median household income: $55,583
Median house or condo value: $276,200
Median resident age: 38.9
1949
Median household income: $40,156
Median house or condo value: $233,100
Median resident age: 40.3
1982
Median household income: $81,719
Median house or condo value: $258,800
Median resident age: 34.5
1974
Median household income: $56,938
Median house or condo value: $273,300
Median resident age: 44.9
The expected result is:-
55055
=AVERAGE(1962, 1973, 1993)
=AVERAGE(88018, 49514, 59464)
=AVERAGE(187200, 164900, 195800)
=AVERAGE(45.6, 34.7, 43.1)
------------------------------------------
55071
=AVERAGE(1984, 1965, 2003, 1961, 1973)
=AVERAGE(67768, 70577, 127255, 47434, 77540)
=AVERAGE(169200, 156600, 320500, 112800, 182000)
=AVERAGE(34.7, 34.4, 31.7, 37.1, 41.2)
------------------------------------------
55313 B
=AVERAGE(1995, 2002, 1952, 1992, 1983, 1988, 1978, 1976)
=AVERAGE(86161, 73858, 34453, 77712, 61442, 88750, 71000, 74583)
=AVERAGE(251600, 219700, 114700, 169900, 201300, 320200, 255900, 305400)
=AVERAGE(34.8, 42.5, 27, 43, 44.4, 36.8, 44.9, 48)
------------------------------------------
55327
=AVERAGE(2000, 1979, 1979, 1999)
=AVERAGE(108214, 89250, 72656, 100208)
=AVERAGE(278000, 247400, 229400, 260300)
=AVERAGE(34.5, 47.4, 38.6, 36)
------------------------------------------
55343 D
=AVERAGE(1951, 1947, 1952)
=AVERAGE(40722, 88661, 81250)
=AVERAGE(435200, 202100, 268000)
=AVERAGE(20.4, 52.9, 48.3)
------------------------------------------
55344 C
=AVERAGE(1985, 1982, 1987, 1993, 1983, 1983)
=AVERAGE(62344, 55536, 50202, 113371, 106917, 77685)
=AVERAGE(167200, 93500, 154300, 260200, 369700, 178100)
=AVERAGE(31.9, 29.1, 31.3, 40.1, 43.5, 42.3)
------------------------------------------
55379 E
=AVERAGE(1989, 1995, 1998, 2004, 2000)
=AVERAGE(54839, 139946, 73147, 136719, 108272)
=AVERAGE(162100, 482000, 175400, 425900, 327200)
=AVERAGE(36.2, 43.4, 33.2, 39.9, 36.8)
------------------------------------------
55403 B
=AVERAGE(1966, 1973, 1980, 1980, 1968, 1984, 1988, 1988, 1984, 1980, 1939)
=AVERAGE(22313, 39836, 37043, 31159, 21771, 24737, 43167, 70243, 43542, 13244, 23672)
=AVERAGE(114400, 116700, 183400, 185400, 257700, 97400, 155300, 155300, 91300, 300000, 722200)
=AVERAGE(26.5, 28.9, 49.9, 28.8, 30.4, 37.1, 38.2, 31.1, 40.1, 42.2, 30.3)
------------------------------------------
55413 C
=AVERAGE(1939, 1939, 1939)
=AVERAGE(56667, 33643, 42604)
=AVERAGE(179500, 158900, 173000)
=AVERAGE(33.3, 24.3, 29.1)
------------------------------------------
55414 A
=AVERAGE(1988, 1939, 2003, 1939, 1939, 1939, 1939, 1939, 1945, 1939)
=AVERAGE(91500, 31563, 34705, 39107, 46250, 53500, 27530, 27083, 32565, 32683)
=AVERAGE(418900, 166700, 395500, 272500, 155600, 201200, 163900, 332500, 625000, 150000)
=AVERAGE(52.4, 25.1, 30.9, 25.9, 30.4, 24.7, 23.3, 22.9, 25.5, 21.6)
------------------------------------------
55439 A
=AVERAGE(1973, 1970, 1971, 1993)
=AVERAGE(70074, 144844, 150250, 62308)
=AVERAGE(301500, 661200, 587000, 465600)
=AVERAGE(81.7, 47.2, 43.8, 37.4)
55439 B
=AVERAGE(1965, 1970, 1983, 1975, 1979, 1982, 1956, 1965, 1964)
=AVERAGE(100988, 146477, 79028, 112639, 125313, 79250, 101250, 135750, 107083)
=AVERAGE(269700, 618600, 323300, 344200, 374900, 456500, 371000, 441200, 473000)
=AVERAGE(42.4, 49.9, 60, 52.5, 54.4, 46.1, 43.9, 51.4, 45.2)
------------------------------------------
55441 B
=AVERAGE(1979, 1962, 1977, 1949, 1982, 1974)
=AVERAGE(82605, 61154, 55583, 40156, 81719, 56938)
=AVERAGE(183300, 212000, 276200, 233100, 258800, 273300)
=AVERAGE(38.5, 27.1, 38.9, 40.3, 34.5, 44.9)
Some aspects of the data wrangling script that just involve 'find and replace' were done manually since a text editor can easily handle it.
Step 1:
Use text editor to remove unwanted strings from the original data, the resulting output is this...
55055
1962
88018
187200
45.6
1973
49514
164900
34.7
1993
59464
195800
43.1
XXXX
55071
1984
67768
169200
34.7
1965
70577
156600
34.4
2003
127255
320500
31.7
1961
47434
112800
37.1
1973
77540
182000
41.2
XXXX
55313 B
1995
86161
251600
34.8
2002
73858
219700
42.5
1952
34453
114700
27
1992
77712
169900
43
1983
61442
201300
44.4
1988
88750
320200
36.8
1978
71000
255900
44.9
1976
74583
305400
48
XXXX
55327
2000
108214
278000
34.5
1979
89250
247400
47.4
1979
72656
229400
38.6
1999
100208
260300
36
XXXX
55343 D
1951
40722
435200
20.4
1947
88661
202100
52.9
1952
81250
268000
48.3
XXXX
55344 C
1985
62344
167200
31.9
1982
55536
93500
29.1
1987
50202
154300
31.3
1993
113371
260200
40.1
1983
106917
369700
43.5
1983
77685
178100
42.3
XXXX
55379 E
1989
54839
162100
36.2
1995
139946
482000
43.4
1998
73147
175400
33.2
2004
136719
425900
39.9
2000
108272
327200
36.8
XXXX
55403 B
1966
22313
114400
26.5
1973
39836
116700
28.9
1980
37043
183400
49.9
1980
31159
185400
28.8
1968
21771
257700
30.4
1984
24737
97400
37.1
1988
43167
155300
38.2
1988
70243
155300
31.1
1984
43542
91300
40.1
1980
13244
300000
42.2
1939
23672
722200
30.3
XXXX
55413 C
1939
56667
179500
33.3
1939
33643
158900
24.3
1939
42604
173000
29.1
XXXX
55414 A
1988
91500
418900
52.4
1939
31563
166700
25.1
2003
34705
395500
30.9
1939
39107
272500
25.9
1939
46250
155600
30.4
1939
53500
201200
24.7
1939
27530
163900
23.3
1939
27083
332500
22.9
1945
32565
625000
25.5
1939
32683
150000
21.6
XXXX
55439 A
1973
70074
301500
81.7
1970
144844
661200
47.2
1971
150250
587000
43.8
1993
62308
465600
37.455439 B
1965
100988
269700
42.4
1970
146477
618600
49.9
1983
79028
323300
60
1975
112639
344200
52.5
1979
125313
374900
54.4
1982
79250
456500
46.1
1956
101250
371000
43.9
1965
135750
441200
51.4
1964
107083
473000
45.2
XXXX
55441 B
1979
82605
183300
38.5
1962
61154
212000
27.1
1977
55583
276200
38.9
1949
40156
233100
40.3
1982
81719
258800
34.5
1974
56938
273300
44.9
Note:
a) 'XXXX' was used to separate zip codes
b) Sub zip codes are separate by space
Step 2:
Read the column above into pandas dataframe and transpose at each occurrence of 'XXXX' into a column.
z_df = pd.read_excel(r'C:\Users\Yusuf_08039508010\Desktop\Working_Files\...\Sub ZipCode DataInPut\raw_Book.xlsx')
z_df = z_df.fillna('YYYY')
# z_df
# ================
zList = z_df['T6'].tolist()
zList
# ================
size = len(zList)
idx_list = [idx + 1 for idx, val in enumerate(zList) if val == 'XXXX']
idx_list
# ================
res = [zList[i: j] for i, j in zip([0] + idx_list, idx_list + ([size] if idx_list[-1] != size else []))]
# res
# ================
res_df = pd.DataFrame(res).T
res_df
Note:
a) Each empty space that represent Sub zip codes are now replaced by 'YYYY'.
b) Each column that has no sub zip code will have 'YYYY' appended manually to its last row.
Step 3:
Read in the updated table above and conduct the data wrangling.
z_df = pd.read_csv(r"C:\Users\Yusuf_08039508010\Documents\Jupyter_Notebook\2019\EXAMS Notebook\T66.csv")
for column in z_df.columns:
print('Working on DF...', column)
# Column zipcode - PROCESSING
zList = z_df[column].tolist()
# zList
zList = [x for x in zList if str(x) != 'nan']
size = len(zList)
idx_list = [idx + 1 for idx, val in enumerate(zList) if val == 'YYYY']
# idx_list
res = [zList[i: j] for i, j in zip([0] + idx_list, idx_list + ([size] if idx_list[-1] != size else []))]
# res
res_df = pd.DataFrame(res, dtype=int).T
# res_df
# WRITE TO TXT file....
sourceFile = open('zipcode_processed_data66.txt', 'a')
print('------------------------------------------', file = sourceFile)
sourceFile.close()
# =============================================
for col in res_df.columns:
# print(col)
# Column SUB_ZIP_Code PROCESSING
resList0 = res_df[col].tolist()
resList0 = list(filter(None, resList0))
try:
target_index = resList0.index('YYYY')
except Exception:
target_index = None
# target_index
resList1 = resList0[:target_index]
# resList1
# res_df.to_csv('TXXXXX.csv', index=None)
first_ele = resList1[0]
resList1.remove(resList1[0])
resList1
# -------------------------------
# Split list into sublists of 3 chunks of elements
n = 4
l = [resList1[i:i+n] for i in range(0, len(resList1), n)]
# l
# ------------------
# FOMART data
year = []
house = []
age = []
income = []
for x in l:
try:
year.append(x[0])
income.append(x[1])
house.append(x[2])
age.append(x[3])
data = first_ele, year, income, house, age
except Exception:
data = first_ele, 'Nil', 'Nil', 'Nil', 'Nil'
# ------------------
# WRITE TO TXT file....
sourceFile = open('zipcode_processed_data66.txt', 'a')
print(data, file = sourceFile)
sourceFile.close()
print(' DONE FOR ....', col)
The result of above script is as follow:-
------------------------------------------
('55055', ['1962', '1973', '1993'], ['88018', '49514', '59464'], ['187200', '164900', '195800'], ['45.6', '34.7', '43.1'])
------------------------------------------
('55071', ['1984', '1965', '2003', '1961', '1973'], ['67768', '70577', '127255', '47434', '77540'], ['169200', '156600', '320500', '112800', '182000'], ['34.7', '34.4', '31.7', '37.1', '41.2'])
------------------------------------------
('55313 B', ['1995', '2002', '1952', '1992', '1983', '1988', '1978', '1976'], ['86161', '73858', '34453', '77712', '61442', '88750', '71000', '74583'], ['251600', '219700', '114700', '169900', '201300', '320200', '255900', '305400'], ['34.8', '42.5', '27', '43', '44.4', '36.8', '44.9', '48'])
------------------------------------------
('55327', ['2000', '1979', '1979', '1999'], ['108214', '89250', '72656', '100208'], ['278000', '247400', '229400', '260300'], ['34.5', '47.4', '38.6', '36'])
------------------------------------------
('55343 D', ['1951', '1947', '1952'], ['40722', '88661', '81250'], ['435200', '202100', '268000'], ['20.4', '52.9', '48.3'])
------------------------------------------
('55344 C', ['1985', '1982', '1987', '1993', '1983', '1983'], ['62344', '55536', '50202', '113371', '106917', '77685'], ['167200', '93500', '154300', '260200', '369700', '178100'], ['31.9', '29.1', '31.3', '40.1', '43.5', '42.3'])
------------------------------------------
('55379 E', ['1989', '1995', '1998', '2004', '2000'], ['54839', '139946', '73147', '136719', '108272'], ['162100', '482000', '175400', '425900', '327200'], ['36.2', '43.4', '33.2', '39.9', '36.8'])
------------------------------------------
('55403 B', ['1966', '1973', '1980', '1980', '1968', '1984', '1988', '1988', '1984', '1980', '1939'], ['22313', '39836', '37043', '31159', '21771', '24737', '43167', '70243', '43542', '13244', '23672'], ['114400', '116700', '183400', '185400', '257700', '97400', '155300', '155300', '91300', '300000', '722200'], ['26.5', '28.9', '49.9', '28.8', '30.4', '37.1', '38.2', '31.1', '40.1', '42.2', '30.3'])
------------------------------------------
('55413 C', ['1939', '1939', '1939'], ['56667', '33643', '42604'], ['179500', '158900', '173000'], ['33.3', '24.3', '29.1'])
------------------------------------------
('55414 A', ['1988', '1939', '2003', '1939', '1939', '1939', '1939', '1939', '1945', '1939'], ['91500', '31563', '34705', '39107', '46250', '53500', '27530', '27083', '32565', '32683'], ['418900', '166700', '395500', '272500', '155600', '201200', '163900', '332500', '625000', '150000'], ['52.4', '25.1', '30.9', '25.9', '30.4', '24.7', '23.3', '22.9', '25.5', '21.6'])
------------------------------------------
('55439 A', ['1973', '1970', '1971', '1993'], ['70074', '144844', '150250', '62308'], ['301500', '661200', '587000', '465600'], ['81.7', '47.2', '43.8', '37.4'])
('55439 B', ['1965', '1970', '1983', '1975', '1979', '1982', '1956', '1965', '1964'], ['100988', '146477', '79028', '112639', '125313', '79250', '101250', '135750', '107083'], ['269700', '618600', '323300', '344200', '374900', '456500', '371000', '441200', '473000'], ['42.4', '49.9', '60', '52.5', '54.4', '46.1', '43.9', '51.4', '45.2'])
------------------------------------------
('55441 B', ['1979', '1962', '1977', '1949', '1982', '1974'], ['82605', '61154', '55583', '40156', '81719', '56938'], ['183300', '212000', '276200', '233100', '258800', '273300'], ['38.5', '27.1', '38.9', '40.3', '34.5', '44.9'])
Step 4:
Text file above is now manually cleaned using find and replace tool in text editor to get the final output as seen below.
55055
=AVERAGE(1962, 1973, 1993)
=AVERAGE(88018, 49514, 59464)
=AVERAGE(187200, 164900, 195800)
=AVERAGE(45.6, 34.7, 43.1)
------------------------------------------
55071
=AVERAGE(1984, 1965, 2003, 1961, 1973)
=AVERAGE(67768, 70577, 127255, 47434, 77540)
=AVERAGE(169200, 156600, 320500, 112800, 182000)
=AVERAGE(34.7, 34.4, 31.7, 37.1, 41.2)
------------------------------------------
55313 B
=AVERAGE(1995, 2002, 1952, 1992, 1983, 1988, 1978, 1976)
=AVERAGE(86161, 73858, 34453, 77712, 61442, 88750, 71000, 74583)
=AVERAGE(251600, 219700, 114700, 169900, 201300, 320200, 255900, 305400)
=AVERAGE(34.8, 42.5, 27, 43, 44.4, 36.8, 44.9, 48)
------------------------------------------
55327
=AVERAGE(2000, 1979, 1979, 1999)
=AVERAGE(108214, 89250, 72656, 100208)
=AVERAGE(278000, 247400, 229400, 260300)
=AVERAGE(34.5, 47.4, 38.6, 36)
------------------------------------------
55343 D
=AVERAGE(1951, 1947, 1952)
=AVERAGE(40722, 88661, 81250)
=AVERAGE(435200, 202100, 268000)
=AVERAGE(20.4, 52.9, 48.3)
------------------------------------------
55344 C
=AVERAGE(1985, 1982, 1987, 1993, 1983, 1983)
=AVERAGE(62344, 55536, 50202, 113371, 106917, 77685)
=AVERAGE(167200, 93500, 154300, 260200, 369700, 178100)
=AVERAGE(31.9, 29.1, 31.3, 40.1, 43.5, 42.3)
------------------------------------------
55379 E
=AVERAGE(1989, 1995, 1998, 2004, 2000)
=AVERAGE(54839, 139946, 73147, 136719, 108272)
=AVERAGE(162100, 482000, 175400, 425900, 327200)
=AVERAGE(36.2, 43.4, 33.2, 39.9, 36.8)
------------------------------------------
55403 B
=AVERAGE(1966, 1973, 1980, 1980, 1968, 1984, 1988, 1988, 1984, 1980, 1939)
=AVERAGE(22313, 39836, 37043, 31159, 21771, 24737, 43167, 70243, 43542, 13244, 23672)
=AVERAGE(114400, 116700, 183400, 185400, 257700, 97400, 155300, 155300, 91300, 300000, 722200)
=AVERAGE(26.5, 28.9, 49.9, 28.8, 30.4, 37.1, 38.2, 31.1, 40.1, 42.2, 30.3)
------------------------------------------
55413 C
=AVERAGE(1939, 1939, 1939)
=AVERAGE(56667, 33643, 42604)
=AVERAGE(179500, 158900, 173000)
=AVERAGE(33.3, 24.3, 29.1)
------------------------------------------
55414 A
=AVERAGE(1988, 1939, 2003, 1939, 1939, 1939, 1939, 1939, 1945, 1939)
=AVERAGE(91500, 31563, 34705, 39107, 46250, 53500, 27530, 27083, 32565, 32683)
=AVERAGE(418900, 166700, 395500, 272500, 155600, 201200, 163900, 332500, 625000, 150000)
=AVERAGE(52.4, 25.1, 30.9, 25.9, 30.4, 24.7, 23.3, 22.9, 25.5, 21.6)
------------------------------------------
55439 A
=AVERAGE(1973, 1970, 1971, 1993)
=AVERAGE(70074, 144844, 150250, 62308)
=AVERAGE(301500, 661200, 587000, 465600)
=AVERAGE(81.7, 47.2, 43.8, 37.4)
55439 B
=AVERAGE(1965, 1970, 1983, 1975, 1979, 1982, 1956, 1965, 1964)
=AVERAGE(100988, 146477, 79028, 112639, 125313, 79250, 101250, 135750, 107083)
=AVERAGE(269700, 618600, 323300, 344200, 374900, 456500, 371000, 441200, 473000)
=AVERAGE(42.4, 49.9, 60, 52.5, 54.4, 46.1, 43.9, 51.4, 45.2)
------------------------------------------
55441 B
=AVERAGE(1979, 1962, 1977, 1949, 1982, 1974)
=AVERAGE(82605, 61154, 55583, 40156, 81719, 56938)
=AVERAGE(183300, 212000, 276200, 233100, 258800, 273300)
=AVERAGE(38.5, 27.1, 38.9, 40.3, 34.5, 44.9)
This can now be added to excel spreadsheet to apply the average function as intended.
That is it!
No comments:
Post a Comment