You could do it with dictionaries. Use the FIPS as the key. Each value could be a list of tuples with 2 values (date, case_num) After you build the dictionaries sort each list by the date part of the tuple. Then take the top two list elements and subtract the case number. Output data done!
Maybe something like this... (not jupyter notebook)
code:
import csv
with open('case.csv', 'r') as f:
reader = csv.reader(f)
data = list(reader)
data.pop(0)
data = [x[1:] for x in data]
data_dict = {x[1]: [] for x in data}
for row in data:
data_list = data_dict[row[1]]
data_list.append((row[0], row[2]))
data_dict[row[1]] = data_list
for key, value in data_dict.items():
value.sort(reverse=True)
for i in range(0, len(value) - 1):
diff = int(value[1]) - int(value[i + 1][1])
print '{},{},{},{}'.format(key, value[0], value[1], diff)
print '{},{},{},{}'.format(key, value[-1][0], value[-1][1], 0)
csv:
ID,Date,FIPS,Cases
7,5/4/20,001,21
8,5/4/20,002,6
1,5/7/20,001,25
2,5/7/20,002,13
3,5/6/20,001,23
4,5/6/20,002,9
5,5/5/20,001,21
6,5/5/20,002,8
output:
002,5/7/20,13,4
002,5/6/20,9,1
002,5/5/20,8,2
002,5/4/20,6,0
001,5/7/20,25,2
001,5/6/20,23,2
001,5/5/20,21,0
001,5/4/20,21,0
