Effective Python: Munging Fitbit Data with Pandas
programming
Python
effective python
Pain lies at the intersection of the computer and the real-world. Real-world data is messy, missing, inconsistent. Transforming or munging it into an analysis dataset is and art and a science and often a pain.
This short post shows how I transformed an extract of my Fitbit exercise into a pandas DataFrame. There where several obstacles!
Sample data set
txt ='''Date Activity Steps Distance Duration Calories Deets
Jun 28, 16:43 Walk 5,690 3.43 miles 48:00 385 cals View Details
Jun 27, 16:24 Walk 5,828 3.41 miles 48:17 398 cals View Details
Jun 27, 13:56 Workout Edit 608 N/A 26:02 140 cals View Details
Jun 26, 16:49 Walk 5,883 3.47 miles 49:41 424 cals View Details
Jun 25, 15:09 Walk 5,936 3.34 miles 50:25 406 cals View Details
Jun 24, 15:40 Walk 5,705 3.49 miles 48:15 408 cals View Details
Jun 23, 14:39 Walk 5,818 3.54 miles 48:32 411 cals View Details
Jun 23, 13:03 Workout Edit 447 N/A 21:56 111 cals View Details
Jun 22, 14:42 Walk 5,875 3.7 miles 49:04 403 cals View Details
Jun 21, 16:04 Walk 5,879 3.59 miles 49:35 412 cals View Details
Jun 21, 13:48 Workout Edit 443 N/A 23:02 99 cals View Details
Jun 20, 19:37 Walk Edit 2,048 N/A 23:03 160 cals View Details
Jun 20, 17:46 Walk Edit 1,651 N/A 17:05 119 cals View Details
Jun 20, 14:17 Walk 6,617 3.62 miles 1:00:46 458 cals View Details
Jun 20, 09:15 Walk Edit 1,497 N/A 19:38 119 cals View Details
Jun 19, 13:26 Walk Edit 3,297 N/A 32:25 232 cals View Details
Jun 18, 12:51 Walk Edit 6,656 N/A 1:09:58 502 cals View Details
Jun 17, 16:15 Workout Edit 485 N/A 21:51 106 cals View Details
Jun 17, 14:30 Walk 6,131 3.59 miles 51:54 412 cals View Details
Jun 16, 15:25 Walk 6,012 3.88 miles 42:50 524 cals View Details
Jun 15, 16:39 Walk 5,725 3.24 miles 47:53 404 cals View Details
Jun 15, 14:36 Workout Edit 322 N/A 21:16 90 cals View Details
Jun 14, 17:09 Walk 5,843 3.53 miles 49:32 419 cals View Details
Jun 14, 14:12 Walk Edit 2,019 N/A 27:19 156 cals View Details
Jun 13, 15:06 Workout Edit 530 N/A 22:19 107 cals View Details
Jun 13, 13:03 Walk 5,992 3.45 miles 50:39 423 cals View Details
Jun 12, 16:03 Walk Edit 2,434 N/A 25:36 174 cals View Details
Jun 11, 11:20 Walk Edit 7,162 N/A 1:22:46 526 cals View Details
Jun 10, 17:01 Walk Edit 3,601 N/A 36:43 259 cals View Details
Jun 10, 13:06 Walk Edit 3,253 N/A 46:06 267 cals View Details
Jun 9, 19:02 Walk Edit 2,676 N/A 27:18 191 cals View Details
Jun 8, 15:07 Walk 5,789 3.44 miles 48:25 428 cals View Details
Jun 8, 12:37 Workout Edit 536 N/A 22:56 107 cals View Details
Jun 7, 14:59 Walk 5,778 3.49 miles 48:14 409 cals View Details
Jun 6, 16:08 Workout Edit 493 N/A 23:24 109 cals View Details
Jun 6, 14:11 Walk 5,872 3.41 miles 49:17 416 cals View Details
Jun 6, 13:07 Walk Edit 1,617 N/A 22:12 140 cals View Details
Jun 5, 15:13 Walk Edit 4,881 N/A 50:22 360 cals View Details
Jun 4, 11:05 Walk Edit 6,547 N/A 1:05:43 464 cals View Details
Jun 3, 15:45 Walk 6,072 3.45 miles 52:01 414 cals View Details
Jun 3, 13:30 Workout Edit 438 N/A 21:40 98 cals View Details
Jun 2, 13:48 Walk Edit 7,023 N/A 1:05:43 527 cals View Details
Jun 2, 11:54 Walk Edit 2,447 N/A 34:09 198 cals View Details
Jun 1, 15:25 Workout Edit 534 N/A 22:07 115 cals View Details
May 31, 11:52 Walk Edit 7,685 N/A 1:11:41 626 cals View Details
May 30, 17:07 Workout Edit 387 N/A 21:33 88 cals View Details
May 30, 09:58 Walk Edit 6,732 N/A 1:06:34 487 cals View Details
May 29, 20:50 Walk Edit 1,143 N/A 15:22 96 cals View Details
May 29, 13:52 Walk Edit 2,173 N/A 31:34 173 cals View Details
May 29, 12:34 Walk Edit 1,007 N/A 17:05 93 cals View Details
May 28, 16:39 Workout Edit 439 N/A 22:15 97 cals View Details
May 28, 10:46 Walk Edit 7,773 N/A 1:18:31 576 cals View Details
May 27, 15:12 Walk 6,563 3.58 miles 59:12 439 cals View Details
May 26, 18:28 Walk Edit 2,609 N/A 26:27 190 cals View Details
May 26, 15:03 Workout Edit 465 N/A 22:00 122 cals View Details
May 25, 18:58 Walk Edit 3,515 N/A 49:30 307 cals View Details
May 25, 12:57 Walk Edit 1,556 N/A 17:56 111 cals View Details
May 25, 09:49 Walk Edit 2,778 N/A 35:51 245 cals View Details
May 24, 15:14 Walk Edit 3,003 N/A 40:59 245 cals View Details
May 23, 15:01 Workout Edit 348 N/A 20:44 113 cals View Details
May 23, 13:41 Walk 5,953 3.46 miles 50:44 407 cals View Details
May 22, 18:44 Walk Edit 1,752 N/A 32:26 153 cals View Details
May 21, 10:33 Walk Edit 6,802 N/A 1:14:14 506 cals View Details
May 20, 14:30 Workout Edit 419 N/A 21:19 124 cals View Details
May 20, 12:55 Walk 5,936 3.49 miles 50:33 407 cals View Details
May 20, 09:31 Walk Edit 3,562 N/A 41:49 299 cals View Details
May 19, 15:38 Walk Edit 8,855 N/A 1:56:04 697 cals View Details
May 19, 14:57 Walk Edit 1,368 N/A 15:21 103 cals View Details
May 18, 16:51 Workout Edit 343 N/A 21:01 86 cals View Details
May 18, 15:13 Walk 6,445 3.48 miles 56:27 435 cals View Details
May 18, 14:10 Walk Edit 2,973 N/A 46:06 259 cals View Details
May 17, 18:00 Walk Edit 7,071 N/A 1:12:33 513 cals View Details
May 17, 12:19 Walk Edit 1,647 N/A 24:45 138 cals View Details
May 17, 10:41 Walk Edit 1,700 N/A 27:19 148 cals View Details
May 16, 16:15 Workout Edit 314 N/A 21:46 91 cals View Details
May 15, 15:12 Walk Edit 3,699 N/A 46:56 290 cals View Details
May 15, 13:48 Walk Edit 5,749 N/A 1:01:28 423 cals View Details
May 14, 15:34 Walk Edit 1,559 N/A 18:46 125 cals View Details
May 13, 15:30 Walk 6,017 3.41 miles 51:53 396 cals View Details
May 12, 15:45 Walk Edit 4,519 N/A 49:31 338 cals View Details
May 12, 13:00 Walk 6,233 3.47 miles 53:14 418 cals View Details
May 11, 14:35 Walk 6,050 3.52 miles 51:25 441 cals View Details
May 10, 15:07 Walk Edit 4,246 N/A 51:12 325 cals View Details
May 9, 13:47 Walk 5,934 3.39 miles 51:03 446 cals View Details
May 6, 15:42 Walk Edit 1,491 N/A 19:37 121 cals View Details
May 5, 16:51 Walk 7,665 3.9 miles 1:36:37 624 cals View Details
May 5, 16:09 Walk Edit 1,402 N/A 18:47 112 cals View Details
May 5, 13:05 Walk 6,273 2.76 miles 1:29:22 498 cals View Details
May 4, 09:45 Walk Edit 3,710 N/A 46:57 285 cals View Details
May 3, 13:17 Workout Edit 2,199 N/A 31:29 228 cals View Details
May 3, 11:41 Walk 1,722 0.76 miles 1:00:40 195 cals View Details
Apr 29, 18:17 Walk Edit 2,646 N/A 33:18 215 cals View Details
Apr 29, 14:02 Walk Edit 3,022 N/A 43:31 269 cals View Details
Apr 26, 10:15 Walk 18,751 10.38 miles 4:03:44 1,420 cals View Details
Apr 25, 14:04 Walk Edit 1,540 N/A 27:17 141 cals View Details
Apr 25, 10:18 Walk 15,301 7.08 miles 2:59:56 1,244 cals View Details
Apr 24, 09:50 Walk 11,350 6.02 miles 2:23:44 887 cals View Details
Apr 23, 13:56 Walk 5,466 3 miles 1:03:21 411 cals View Details
Apr 22, 17:29 Walk 3,765 2.04 miles 42:52 314 cals View Details
Apr 22, 13:29 Walk Edit 2,465 N/A 33:17 193 cals View Details
Apr 21, 09:40 Walk 13,921 6.95 miles 2:58:31 1,133 cals View Details
Apr 20, 14:41 Walk Edit 4,443 N/A 1:07:27 377 cals View Details
Apr 20, 13:55 Walk Edit 1,561 N/A 20:29 143 cals View Details
Apr 20, 10:05 Walk 11,047 5.62 miles 2:14:28 853 cals View Details
Apr 19, 11:27 Walk 12,282 5.9 miles 3:02:11 975 cals View Details
Apr 18, 17:39 Walk 3,361 1.51 miles 42:23 254 cals View Details
Apr 17, 11:42 Walk 9,046 5.25 miles 2:09:35 686 cals View Details
Apr 17, 10:57 Walk Edit 1,553 N/A 16:13 108 cals View Details
Apr 16, 15:46 Walk 2,291 1.17 miles 26:06 166 cals View Details
Apr 16, 14:58 Walk 1,198 0.61 miles 14:16 91 cals View Details
Apr 16, 11:45 Walk 10,836 5.24 miles 2:32:07 835 cals View Details
Apr 15, 18:35 Walk 4,738 2.48 miles 58:18 343 cals View Details
Apr 15, 14:21 Walk Edit 5,489 N/A 1:06:34 415 cals View Details
Apr 14, 15:59 Walk Edit 3,355 N/A 41:49 269 cals View Details
Apr 14, 10:38 Walk Edit 4,402 N/A 54:37 327 cals View Details
Apr 11, 15:24 Walk Edit 1,643 N/A 18:47 126 cals View Details
Apr 11, 11:12 Walk 5,388 2.65 miles 1:02:35 407 cals View Details
Apr 10, 13:49 Walk Edit 3,554 N/A 43:32 270 cals View Details
Apr 10, 11:46 Walk Edit 1,719 N/A 18:46 114 cals View Details
Apr 9, 17:05 Walk Edit 1,874 N/A 20:29 138 cals View Details
Apr 9, 14:57 Walk Edit 6,109 N/A 1:19:23 476 cals View Details
Apr 9, 13:17 Walk Edit 5,282 N/A 1:18:33 455 cals View Details
Apr 9, 11:28 Walk Edit 1,600 N/A 16:14 107 cals View Details
Apr 8, 22:02 Walk Edit 1,775 N/A 19:38 135 cals View Details
Apr 8, 18:57 Walk Edit 1,409 N/A 15:22 99 cals View Details
Apr 8, 15:19 Walk 5,050 2.49 miles 1:15:27 391 cals View Details
Apr 8, 12:04 Walk Edit 3,518 N/A 36:44 252 cals View Details
Apr 8, 10:04 Walk Edit 3,335 N/A 34:08 227 cals View Details
Apr 7, 14:11 Walk Edit 2,636 N/A 35:50 193 cals View Details
Apr 7, 11:14 Walk Edit 3,945 N/A 47:46 282 cals View Details
Apr 5, 13:08 Walk 5,699 3.43 miles 48:03 410 cals View Details
Apr 4, 15:48 Workout Edit 354 N/A 23:00 111 cals View Details
Apr 4, 13:36 Walk 5,889 3.4 miles 50:48 436 cals View Details
Apr 3, 09:02 Workout Edit 6,219 N/A 55:14 564 cals View Details
Apr 2, 14:19 Walk Edit 2,566 N/A 27:19 187 cals View Details
Apr 2, 13:06 Walk Edit 5,088 N/A 53:47 370 cals View Details
Apr 1, 13:20 Walk 5,879 3.41 miles 49:59 422 cals View Details
Mar 31, 14:45 Walk Edit 4,462 N/A 47:50 327 cals View Details
Mar 31, 12:32 Walk Edit 2,946 N/A 29:54 238 cals View Details
Mar 30, 14:07 Workout Edit 472 N/A 21:36 107 cals View Details
Mar 30, 12:28 Walk 5,888 3.08 miles 51:28 443 cals View Details
Mar 29, 13:04 Walk 5,932 3.41 miles 50:33 420 cals View Details
Mar 28, 16:03 Workout Edit 495 N/A 24:04 130 cals View Details
Mar 28, 10:43 Walk 6,033 3.42 miles 51:26 448 cals View Details
Mar 26, 13:28 Walk Edit 6,600 N/A 1:10:49 483 cals View Details
Mar 25, 16:42 Workout Edit 330 N/A 21:55 124 cals View Details
Mar 25, 13:45 Walk 5,903 3.1 miles 50:54 400 cals View Details
Mar 24, 13:30 Walk Edit 1,341 N/A 15:22 109 cals View Details
Mar 23, 16:37 Workout Edit 425 N/A 23:37 138 cals View Details
Mar 23, 13:56 Walk 5,912 3.45 miles 50:33 430 cals View Details
Mar 22, 13:03 Walk 5,929 3.39 miles 50:51 429 cals View Details
Mar 21, 15:31 Workout Edit 429 N/A 22:35 107 cals View Details
Mar 21, 13:44 Walk 5,713 3.4 miles 49:10 417 cals View Details
Mar 20, 12:39 Walk Edit 4,261 N/A 42:40 307 cals View Details
Mar 20, 09:01 Workout Edit 6,395 N/A 56:38 534 cals View Details
Mar 19, 11:45 Walk Edit 6,389 N/A 1:07:26 483 cals View Details
Mar 18, 16:31 Workout Edit 500 N/A 22:21 127 cals View Details
Mar 18, 14:22 Walk 5,764 3.44 miles 49:28 429 cals View Details
Mar 17, 14:35 Walk 5,861 3.43 miles 49:37 420 cals View Details
Mar 16, 14:13 Workout Edit 355 N/A 22:11 110 cals View Details
Mar 16, 13:04 Walk 5,857 3.48 miles 51:46 429 cals View Details
Mar 15, 15:30 Walk 5,877 3.43 miles 50:15 446 cals View Details
Mar 14, 16:41 Workout Edit 440 N/A 22:32 116 cals View Details
Mar 14, 14:00 Walk 5,840 3.41 miles 49:36 434 cals View Details
Mar 13, 14:25 Walk Edit 4,214 N/A 43:31 333 cals View Details
Mar 13, 08:59 Workout Edit 6,336 N/A 57:16 539 cals View Details
Mar 11, 17:00 Workout Edit 506 N/A 22:05 129 cals View Details
Mar 11, 13:37 Walk Edit 2,387 N/A 38:24 224 cals View Details
Mar 10, 14:14 Walk 5,904 3.52 miles 50:14 415 cals View Details
Mar 9, 16:07 Workout Edit 1,009 N/A 33:40 201 cals View Details
Mar 8, 14:08 Walk 5,834 3.41 miles 49:21 419 cals View Details
Mar 7, 16:47 Workout Edit 380 N/A 21:55 109 cals View Details
Mar 7, 14:44 Walk Edit 3,448 N/A 36:42 244 cals View Details
Mar 7, 13:05 Walk Edit 2,860 N/A 29:52 211 cals View Details
Mar 6, 13:33 Walk Edit 4,317 N/A 43:32 337 cals View Details
Mar 6, 09:02 Workout Edit 6,303 N/A 57:01 552 cals View Details
Mar 5, 12:17 Walk Edit 7,051 N/A 1:16:48 522 cals View Details
Mar 4, 16:08 Workout Edit 353 N/A 20:36 96 cals View Details
Mar 4, 12:52 Walk 6,030 3.42 miles 51:14 427 cals View Details
Mar 3, 13:34 Walk 5,833 3.39 miles 49:33 415 cals View Details
Mar 2, 14:51 Walk 5,936 3.44 miles 50:41 413 cals View Details
Mar 2, 13:03 Workout Edit 337 N/A 21:47 92 cals View Details
Mar 1, 13:44 Walk 5,934 3.46 miles 49:40 447 cals View Details
Feb 28, 16:04 Workout Edit 393 N/A 21:52 120 cals View Details
Feb 28, 13:43 Walk 5,956 3.41 miles 49:58 427 cals View Details
Feb 27, 09:01 Workout Edit 6,227 N/A 56:34 571 cals View Details
Feb 26, 12:40 Walk Edit 6,837 N/A 1:14:14 523 cals View Details
Feb 25, 16:42 Workout Edit 445 N/A 21:59 105 cals View Details
Feb 25, 14:05 Walk 6,078 3.41 miles 51:28 434 cals View Details
Feb 24, 14:32 Walk 6,086 3.63 miles 51:59 421 cals View Details
Feb 23, 16:32 Workout Edit 496 N/A 36:43 132 cals View Details
Feb 23, 11:19 Walk 6,072 3.26 miles 57:52 464 cals View Details
Feb 22, 13:03 Walk 5,459 3.18 miles 51:25 385 cals View Details
Feb 21, 16:47 Workout Edit 495 N/A 22:30 107 cals View Details
Feb 21, 13:14 Walk 5,935 3.41 miles 51:38 425 cals View Details
Feb 20, 09:09 Workout Edit 6,328 N/A 56:38 582 cals View Details
Feb 19, 14:07 Walk Edit 6,441 N/A 1:04:52 476 cals View Details
Feb 18, 16:18 Workout Edit 445 N/A 22:24 115 cals View Details
Feb 18, 14:58 Walk Edit 2,215 N/A 35:51 200 cals View Details
Feb 17, 14:07 Walk 5,846 3.36 miles 49:49 431 cals View Details
Feb 16, 14:24 Walk 5,910 3.41 miles 50:00 416 cals View Details
Feb 15, 16:09 Workout Edit 415 N/A 20:41 114 cals View Details
Feb 15, 13:56 Walk 6,170 3.51 miles 53:35 415 cals View Details
Feb 14, 16:53 Walk Edit 1,378 N/A 17:05 110 cals View Details
Feb 12, 14:10 Walk 7,046 3.4 miles 1:13:38 511 cals View Details
Feb 11, 16:39 Workout Edit 452 N/A 33:31 143 cals View Details
Feb 10, 13:54 Walk 5,910 3.45 miles 50:50 416 cals View Details
Feb 9, 15:50 Walk Edit 1,764 N/A 25:35 145 cals View Details
Feb 8, 14:38 Walk 5,757 3.37 miles 49:12 421 cals View Details
Feb 6, 14:32 Walk 5,886 3.41 miles 50:30 435 cals View Details
Feb 5, 15:06 Walk 5,901 3.42 miles 50:29 425 cals View Details
Jan 30, 09:53 Workout Edit 6,307 N/A 56:06 603 cals View Details
Jan 28, 16:40 Workout Edit 977 N/A 33:48 219 cals View Details
Jan 27, 14:50 Walk 5,810 3.45 miles 48:44 422 cals View Details
Jan 26, 16:35 Workout Edit 506 N/A 20:05 143 cals View Details
Jan 26, 15:05 Walk 5,872 3.45 miles 49:54 426 cals View Details
Jan 25, 15:01 Walk 5,936 3.66 miles 49:44 420 cals View Details
Jan 24, 14:20 Walk Edit 5,801 N/A 51:14 427 cals View Details
Jan 23, 09:35 Workout Edit 6,380 N/A 57:30 592 cals View Details
Jan 22, 15:07 Walk Edit 5,585 N/A 49:30 471 cals View Details
Jan 20, 14:17 Walk Edit 5,768 N/A 52:06 438 cals View Details
Jan 19, 14:06 Walk Edit 5,953 N/A 52:55 446 cals View Details
Jan 18, 17:10 Workout Edit 941 N/A 47:21 269 cals View Details
Jan 18, 16:08 Walk Edit 1,376 N/A 16:13 109 cals View Details
Jan 17, 10:12 Walk Edit 3,715 N/A 36:41 311 cals View Details
Jan 16, 09:01 Workout Edit 9,751 N/A 1:27:59 892 cals View Details
Jan 14, 16:00 Workout Edit 965 N/A 32:06 204 cals View Details
Jan 13, 14:34 Walk Edit 6,260 N/A 55:30 447 cals View Details
Jan 12, 14:49 Walk Edit 6,061 N/A 54:38 431 cals View Details
Jan 12, 11:06 Walk Edit 1,995 N/A 30:44 191 cals View Details
Jan 10, 16:15 Workout Edit 910 N/A 31:52 207 cals View Details
Jan 9, 09:05 Workout Edit 9,718 N/A 1:24:07 802 cals View Details
Jan 8, 13:35 Walk Edit 7,194 N/A 1:27:02 531 cals View Details
Jan 7, 16:51 Workout Edit 932 N/A 33:46 181 cals View Details
Jan 6, 13:48 Walk Edit 5,989 N/A 54:36 424 cals View Details
Jan 5, 13:37 Walk Edit 7,247 N/A 1:08:18 549 cals View Details
Jan 4, 16:17 Workout Edit 861 N/A 31:41 167 cals View Details
Jan 3, 14:04 Walk 5,765 2.89 miles 49:07 383 cals View Details
Jan 2, 09:00 Workout Edit 6,329 N/A 56:02 550 cals View Details
Jan 1, 16:19 Workout Edit 500 N/A 21:21 116 cals View Details'''
from io import StringIO
sio = StringIO(txt)
df = pd.read_csv(sio, sep='\t')
df.Date = pd.to_datetime([i.replace(',', ' 2022,') for i in df.Date], format='%b %d %Y, %H:%M')
df.Steps = df.Steps.str.replace(',','').astype(int)
df.Distance = pd.to_numeric(df.Distance.str.replace(' miles', ''))
df.Calories = pd.to_numeric(df.Calories.str.replace(',| cals', ''))
df.Duration = pd.to_timedelta([f'00:{i}' if len(i) == 5 else i for i in df.Duration]).astype('timedelta64[s]') / 60
print(df.dtypes)
df = df.drop(columns=['Deets'])
w = df.query("Activity == 'Walk' and 3. <= Distance <= 3.9")
display(w)
w = w.drop(index=[85, 97, 204])
w = w.set_index('Date')
print(w.shape)
import matplotlib.dates as mdates
# coding: utf-8
f, axs = smfig(2, 2, (14.0, 10.0), )
axi = iter(axs.flat)
for c in w.select_dtypes(exclude=object).columns:
ax = next(axi)
w[c].plot(ax=ax, lw=0.25, linestyle=':', marker='o', fillstyle='none', rot=0)
yr = ax.get_ylim()
yr = [yr[1]/2, yr[1]*1.1]
ax.set(title=c, ylim=yr)
ax.grid(lw=.25, c='w')
loc = mdates.AutoDateLocator(minticks=21, maxticks=27)
# loc.intervald[DAILY] = [7]
# fm = mdates.AutoDateFormatter(loc )
fm = mdates.ConciseDateFormatter(loc, show_offset=False)
ax.xaxis.set_major_locator(loc)
ax.xaxis.set_major_formatter(fm)
if ax is ax2:
ax.axhline(50, c='k', lw=1)
f, axs = smfig(2, 2, (14.0, 10.0), )
axi = iter(axs.flat)
ax = next(axi)
ax.plot(w.Distance, w.Steps, 'x')
ax.set(xlabel='Distance', ylabel='Steps')
ax = next(axi)
ax.plot(w.Distance, w.Duration, 'x')
ax.set(xlabel='Distance', ylabel='Duration')
ax = next(axi)
ax.plot(w.Distance, w.Steps, 'x')
ax.set(xlabel='Distance', ylabel='Steps')
ax = next(axi)
ax.plot(w.Steps, w.Duration, 'x')
ax.set(xlabel='Steps', ylabel='Duration')