Hello All, If anyone would be kind to assist me with this issue. As per my last post, I am a beginner with R Studio. Just resuming the case study by Google data analytics Course: Case study 1.
Currently stuck on creating a new column by subtracting the ended_at - started_at. which gives me the ride_length. I did some research on my own and did the following:
which gives me the following error:
ride_lenght<-(end_time-start_time)
startz
July 25, 2024, 8:00pm
2
These lines set the variables to strings. You can't subtract strings. You want to put in numbers. Like
end_time <- 7
start_time <-3
1 Like
ohhhh, so how can make it a string since each column end_time and start_time are the time frame that each cyclist started and ended. Sorry for all the questions and thanks
startz
July 25, 2024, 8:17pm
4
Suppose they are columns of a dataframe named df. Then you could go
df.ride_lenght < -df.end_time - df.start_time
1 Like
Going to try this, thank you so much!
Unfortunately, I was not able to resolve the issue. I tried the following:
rm(end_time,start_time)
startz
July 25, 2024, 9:19pm
7
Can you show us the output from
str(Cycling_Months)
str(Cycling_Months) :'data.frame': 710721 obs. of 13 variables:
.. ride_id : chr [1:710721] "CDE6023BE6B11D2F" "462B48CD292B6A18" "9CFB6A858D23ABF7" "6365EFEB64231153" ... rideable_type : chr [1:710721] "electric_bike" "electric_bike" "electric_bike" "electric_bike" ...
.. started_at : chr [1:710721] "2024-06-11 17:20:06.289" "2024-06-11 17:19:21.567" "2024-06-11 17:25:27.089" "2024-06-11 11:53:50.769" ... ended_at : chr [1:710721] "2024-06-11 17:21:39.464" "2024-06-11 17:19:36.377" "2024-06-11 17:30:13.035" "2024-06-11 12:08:13.382" ...
.. start_station_name: chr [1:710721] "" "" "" "" ... start_station_id : chr [1:710721] "" "" "" "" ...
.. end_station_name : chr [1:710721] "" "" "" "" ... end_station_id : chr [1:710721] "" "" "" "" ...
.. start_lat : num [1:710721] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:710721] -87.7 -87.7 -87.7 -87.6 -87.6 ...
.. end_lat : num [1:710721] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:710721] -87.7 -87.7 -87.7 -87.6 -87.6 ...
.. member_casual : chr [1:710721] "casual" "casual" "casual" "casual" ... :'data.frame': 609493 obs. of 13 variables:
.. ride_id : chr [1:609493] "7D9F0CE9EC2A1297" "02EC47687411416F" "101370FB2D3402BE" "E97E396331ED6913" ... rideable_type : chr [1:609493] "classic_bike" "classic_bike" "classic_bike" "electric_bike" ...
.. started_at : chr [1:609493] "2024-05-25 15:52:42" "2024-05-14 15:11:51" "2024-05-30 17:46:04" "2024-05-17 20:21:54" ... ended_at : chr [1:609493] "2024-05-25 16:11:50" "2024-05-14 15:22:00" "2024-05-30 18:09:16" "2024-05-17 20:40:32" ...
.. start_station_name: chr [1:609493] "Streeter Dr & Grand Ave" "Sheridan Rd & Greenleaf Ave" "Streeter Dr & Grand Ave" "Streeter Dr & Grand Ave" ... start_station_id : chr [1:609493] "13022" "KA1504000159" "13022" "13022" ...
.. end_station_name : chr [1:609493] "Clark St & Elm St" "Sheridan Rd & Loyola Ave" "Wabash Ave & 9th St" "Sheffield Ave & Wellington Ave" ... end_station_id : chr [1:609493] "TA1307000039" "RP-009" "TA1309000010" "TA1307000052" ...
.. start_lat : num [1:609493] 41.9 42 41.9 41.9 41.9 ... start_lng : num [1:609493] -87.6 -87.7 -87.6 -87.6 -87.6 ...
.. end_lat : num [1:609493] 41.9 42 41.9 41.9 41.9 ... end_lng : num [1:609493] -87.6 -87.7 -87.6 -87.7 -87.6 ...
.. member_casual : chr [1:609493] "casual" "casual" "member" "member" ... :'data.frame': 415025 obs. of 13 variables:
.. ride_id : chr [1:415025] "743252713F32516B" "BE90D33D2240C614" "D47BBDDE7C40DD61" "6684E760BF9EA9B5" ... rideable_type : chr [1:415025] "classic_bike" "electric_bike" "classic_bike" "classic_bike" ...
.. started_at : chr [1:415025] "2024-04-22 19:08:21" "2024-04-11 06:19:24" "2024-04-20 11:13:13" "2024-04-04 18:39:20" ... ended_at : chr [1:415025] "2024-04-22 19:12:56" "2024-04-11 06:22:21" "2024-04-20 11:29:31" "2024-04-04 18:43:06" ...
.. start_station_name: chr [1:415025] "Aberdeen St & Jackson Blvd" "Aberdeen St & Jackson Blvd" "Sheridan Rd & Montrose Ave" "Aberdeen St & Jackson Blvd" ... start_station_id : chr [1:415025] "13157" "13157" "TA1307000107" "13157" ...
.. end_station_name : chr [1:415025] "Desplaines St & Jackson Blvd" "Desplaines St & Jackson Blvd" "Ashland Ave & Belle Plaine Ave" "Desplaines St & Jackson Blvd" ... end_station_id : chr [1:415025] "15539" "15539" "13249" "15539" ...
.. start_lat : num [1:415025] 41.9 41.9 42 41.9 42 ... start_lng : num [1:415025] -87.7 -87.7 -87.7 -87.7 -87.7 ...
.. end_lat : num [1:415025] 41.9 41.9 42 41.9 41.9 ... end_lng : num [1:415025] -87.6 -87.6 -87.7 -87.6 -87.6 ...
.. member_casual : chr [1:415025] "member" "member" "member" "member" ... :'data.frame': 301687 obs. of 13 variables:
.. ride_id : chr [1:301687] "64FBE3BAED5F29E6" "9991629435C5E20E" "E5C9FECD5B71BEBD" "4CEA3EC8906DAEA8" ... rideable_type : chr [1:301687] "electric_bike" "electric_bike" "electric_bike" "electric_bike" ...
.. started_at : chr [1:301687] "2024-03-05 18:33:11" "2024-03-06 17:15:14" "2024-03-06 17:16:36" "2024-03-03 22:55:54" ... ended_at : chr [1:301687] "2024-03-05 18:51:48" "2024-03-06 17:16:04" "2024-03-06 17:19:28" "2024-03-03 22:58:08" ...
.. start_station_name: chr [1:301687] "" "" "" "" ... start_station_id : chr [1:301687] "" "" "" "" ...
.. end_station_name : chr [1:301687] "" "" "" "" ... end_station_id : chr [1:301687] "" "" "" "" ...
.. start_lat : num [1:301687] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:301687] -87.7 -87.6 -87.6 -87.6 -87.7 ...
.. end_lat : num [1:301687] 42 41.9 41.9 41.9 41.9 ... end_lng : num [1:301687] -87.7 -87.6 -87.6 -87.6 -87.7 ...
.. member_casual : chr [1:301687] "member" "member" "member" "member" ... :'data.frame': 223164 obs. of 13 variables:
.. ride_id : chr [1:223164] "FCB05EB1758F85E8" "7FB986AD5D3DE9D6" "40CA13E15B5B470D" "D47A1660919E8861" ... rideable_type : chr [1:223164] "classic_bike" "classic_bike" "electric_bike" "classic_bike" ...
.. started_at : chr [1:223164] "2024-02-03 14:14:18" "2024-02-05 21:10:06" "2024-02-05 15:10:44" "2024-02-15 12:40:34" ... ended_at : chr [1:223164] "2024-02-03 14:21:00" "2024-02-05 21:15:44" "2024-02-05 15:12:32" "2024-02-15 12:44:24" ...
.. start_station_name: chr [1:223164] "Clark St & Newport St" "Michigan Ave & Washington St" "Leavitt St & Armitage Ave" "Southport Ave & Waveland Ave" ... start_station_id : chr [1:223164] "632" "13001" "TA1309000029" "13235" ...
.. end_station_name : chr [1:223164] "Southport Ave & Waveland Ave" "Wabash Ave & Grand Ave" "Milwaukee Ave & Wabansia Ave" "Southport Ave & Belmont Ave" ... end_station_id : chr [1:223164] "13235" "TA1307000117" "13243" "13229" ...
.. start_lat : num [1:223164] 41.9 41.9 41.9 41.9 41.8 ... start_lng : num [1:223164] -87.7 -87.6 -87.7 -87.7 -87.6 ...
.. end_lat : num [1:223164] 41.9 41.9 41.9 41.9 41.8 ... end_lng : num [1:223164] -87.7 -87.6 -87.7 -87.7 -87.6 ...
.. member_casual : chr [1:223164] "member" "member" "member" "member" ... :'data.frame': 144873 obs. of 13 variables:
.. ride_id : chr [1:144873] "C1D650626C8C899A" "EECD38BDB25BFCB0" "F4A9CE78061F17F7" "0A0D9E15EE50B171" ... rideable_type : chr [1:144873] "electric_bike" "electric_bike" "electric_bike" "classic_bike" ...
.. started_at : chr [1:144873] "2024-01-12 15:30:27" "2024-01-08 15:45:46" "2024-01-27 12:27:19" "2024-01-29 16:26:17" ... ended_at : chr [1:144873] "2024-01-12 15:37:59" "2024-01-08 15:52:59" "2024-01-27 12:35:19" "2024-01-29 16:56:06" ...
.. start_station_name: chr [1:144873] "Wells St & Elm St" "Wells St & Elm St" "Wells St & Elm St" "Wells St & Randolph St" ... start_station_id : chr [1:144873] "KA1504000135" "KA1504000135" "KA1504000135" "TA1305000030" ...
.. end_station_name : chr [1:144873] "Kingsbury St & Kinzie St" "Kingsbury St & Kinzie St" "Kingsbury St & Kinzie St" "Larrabee St & Webster Ave" ... end_station_id : chr [1:144873] "KA1503000043" "KA1503000043" "KA1503000043" "13193" ...
.. start_lat : num [1:144873] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:144873] -87.6 -87.6 -87.6 -87.6 -87.7 ...
.. end_lat : num [1:144873] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:144873] -87.6 -87.6 -87.6 -87.6 -87.6 ...
.. member_casual : chr [1:144873] "member" "member" "member" "member" ... :'data.frame': 224073 obs. of 13 variables:
.. ride_id : chr [1:224073] "C9BD54F578F57246" "CDBD92F067FA620E" "ABC0858E52CBFC84" "F44B6F0E8F76DC90" ... rideable_type : chr [1:224073] "electric_bike" "electric_bike" "electric_bike" "electric_bike" ...
.. started_at : chr [1:224073] "2023-12-02 18:44:01" "2023-12-02 18:48:19" "2023-12-24 01:56:32" "2023-12-24 10:58:12" ... ended_at : chr [1:224073] "2023-12-02 18:47:51" "2023-12-02 18:54:48" "2023-12-24 02:04:09" "2023-12-24 11:03:04" ...
.. start_station_name: chr [1:224073] "" "" "" "" ... start_station_id : chr [1:224073] "" "" "" "" ...
.. end_station_name : chr [1:224073] "" "" "" "" ... end_station_id : chr [1:224073] "" "" "" "" ...
.. start_lat : num [1:224073] 41.9 41.9 41.9 42 41.9 ... start_lng : num [1:224073] -87.7 -87.7 -87.6 -87.7 -87.6 ...
.. end_lat : num [1:224073] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:224073] -87.7 -87.6 -87.6 -87.7 -87.6 ...
.. member_casual : chr [1:224073] "member" "member" "member" "member" ... :'data.frame': 362518 obs. of 13 variables:
.. ride_id : chr [1:362518] "4EAD8F1AD547356B" "6322270563BF5470" "B37BDE091ECA38E0" "CF0CA5DD26E4F90E" ... rideable_type : chr [1:362518] "electric_bike" "electric_bike" "electric_bike" "classic_bike" ...
.. started_at : chr [1:362518] "2023-11-30 21:50:05" "2023-11-03 09:44:02" "2023-11-30 11:39:44" "2023-11-08 10:01:45" ... ended_at : chr [1:362518] "2023-11-30 22:13:27" "2023-11-03 10:17:15" "2023-11-30 11:40:08" "2023-11-08 10:27:05" ...
.. start_station_name: chr [1:362518] "Millennium Park" "Broadway & Sheridan Rd" "State St & Pearson St" "Theater on the Lake" ... start_station_id : chr [1:362518] "13008" "13323" "TA1307000061" "TA1308000001" ...
.. end_station_name : chr [1:362518] "Pine Grove Ave & Waveland Ave" "Broadway & Sheridan Rd" "State St & Pearson St" "Theater on the Lake" ... end_station_id : chr [1:362518] "TA1307000150" "13323" "TA1307000061" "TA1308000001" ...
.. start_lat : num [1:362518] 41.9 42 41.9 41.9 41.9 ... start_lng : num [1:362518] -87.6 -87.7 -87.6 -87.6 -87.6 ...
.. end_lat : num [1:362518] 41.9 42 41.9 41.9 41.9 ... end_lng : num [1:362518] -87.6 -87.6 -87.6 -87.6 -87.6 ...
.. member_casual : chr [1:362518] "member" "member" "member" "member" ... :'data.frame': 537113 obs. of 13 variables:
.. ride_id : chr [1:537113] "4449097279F8BBE7" "9CF060543CA7B439" "667F21F4D6BDE69C" "F92714CC6B019B96" ... rideable_type : chr [1:537113] "classic_bike" "electric_bike" "electric_bike" "classic_bike" ...
.. started_at : chr [1:537113] "2023-10-08 10:36:26" "2023-10-11 17:23:59" "2023-10-12 07:02:33" "2023-10-24 19:13:03" ... ended_at : chr [1:537113] "2023-10-08 10:49:19" "2023-10-11 17:36:08" "2023-10-12 07:06:53" "2023-10-24 19:18:29" ...
.. start_station_name: chr [1:537113] "Orleans St & Chestnut St (NEXT Apts)" "Desplaines St & Kinzie St" "Orleans St & Chestnut St (NEXT Apts)" "Desplaines St & Kinzie St" ... start_station_id : chr [1:537113] "620" "TA1306000003" "620" "TA1306000003" ...
.. end_station_name : chr [1:537113] "Sheffield Ave & Webster Ave" "Sheffield Ave & Webster Ave" "Franklin St & Lake St" "Franklin St & Lake St" ... end_station_id : chr [1:537113] "TA1309000033" "TA1309000033" "TA1307000111" "TA1307000111" ...
.. start_lat : num [1:537113] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:537113] -87.6 -87.6 -87.6 -87.6 -87.6 ...
.. end_lat : num [1:537113] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:537113] -87.7 -87.7 -87.6 -87.6 -87.6 ...
.. member_casual : chr [1:537113] "member" "member" "member" "member" ... :'data.frame': 666371 obs. of 13 variables:
.. ride_id : chr [1:666371] "011C1903BF4E2E28" "87DB80E048A1BF9F" "7C2EB7AF669066E3" "57D197B010269CE3" ... rideable_type : chr [1:666371] "classic_bike" "classic_bike" "electric_bike" "classic_bike" ...
.. started_at : chr [1:666371] "2023-09-23 00:27:50" "2023-09-02 09:26:43" "2023-09-25 18:30:11" "2023-09-13 15:30:49" ... ended_at : chr [1:666371] "2023-09-23 00:33:27" "2023-09-02 09:38:19" "2023-09-25 18:41:39" "2023-09-13 15:39:18" ...
.. start_station_name: chr [1:666371] "Halsted St & Wrightwood Ave" "Clark St & Drummond Pl" "Financial Pl & Ida B Wells Dr" "Clark St & Drummond Pl" ... start_station_id : chr [1:666371] "TA1309000061" "TA1307000142" "SL-010" "TA1307000142" ...
.. end_station_name : chr [1:666371] "Sheffield Ave & Wellington Ave" "Racine Ave & Fullerton Ave" "Racine Ave & 15th St" "Racine Ave & Belmont Ave" ... end_station_id : chr [1:666371] "TA1307000052" "TA1306000026" "13304" "TA1308000019" ...
.. start_lat : num [1:666371] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:666371] -87.6 -87.6 -87.6 -87.6 -87.6 ...
.. end_lat : num [1:666371] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:666371] -87.7 -87.7 -87.7 -87.7 -87.7 ...
.. member_casual : chr [1:666371] "member" "member" "member" "member" ... :'data.frame': 771693 obs. of 13 variables:
.. ride_id : chr [1:771693] "903C30C2D810A53B" "F2FB18A98E110A2B" "D0DEC7C94E4663DA" "E0DDDC5F84747ED9" ... rideable_type : chr [1:771693] "electric_bike" "electric_bike" "electric_bike" "electric_bike" ...
.. started_at : chr [1:771693] "2023-08-19 15:41:53" "2023-08-18 15:30:18" "2023-08-30 16:15:08" "2023-08-30 16:24:07" ... ended_at : chr [1:771693] "2023-08-19 15:53:36" "2023-08-18 15:45:25" "2023-08-30 16:27:37" "2023-08-30 16:33:34" ...
.. start_station_name: chr [1:771693] "LaSalle St & Illinois St" "Clark St & Randolph St" "Clark St & Randolph St" "Wells St & Elm St" ... start_station_id : chr [1:771693] "13430" "TA1305000030" "TA1305000030" "KA1504000135" ...
.. end_station_name : chr [1:771693] "Clark St & Elm St" "" "" "" ... end_station_id : chr [1:771693] "TA1307000039" "" "" "" ...
.. start_lat : num [1:771693] 41.9 41.9 41.9 41.9 41.9 ... start_lng : num [1:771693] -87.6 -87.6 -87.6 -87.6 -87.6 ...
.. end_lat : num [1:771693] 41.9 41.9 41.9 41.9 41.9 ... end_lng : num [1:771693] -87.6 -87.6 -87.6 -87.6 -87.7 ...
.. member_casual : chr [1:771693] "member" "member" "member" "member" ... :'data.frame': 767650 obs. of 13 variables:
.. ride_id : chr [1:767650] "9340B064F0AEE130" "D1460EE3CE0D8AF8" "DF41BE31B895A25E" "9624A293749EF703" ... rideable_type : chr [1:767650] "electric_bike" "classic_bike" "classic_bike" "electric_bike" ...
.. started_at : chr [1:767650] "2023-07-23 20:06:14" "2023-07-23 17:05:07" "2023-07-23 10:14:53" "2023-07-21 08:27:44" ... ended_at : chr [1:767650] "2023-07-23 20:22:44" "2023-07-23 17:18:37" "2023-07-23 10:24:29" "2023-07-21 08:32:40" ...
.. start_station_name: chr [1:767650] "Kedzie Ave & 110th St" "Western Ave & Walton St" "Western Ave & Walton St" "Racine Ave & Randolph St" ... start_station_id : chr [1:767650] "20204" "KA1504000103" "KA1504000103" "13155" ...
.. end_station_name : chr [1:767650] "Public Rack - Racine Ave & 109th Pl" "Milwaukee Ave & Grand Ave" "Damen Ave & Pierce Ave" "Clinton St & Madison St" ... end_station_id : chr [1:767650] "877" "13033" "TA1305000041" "TA1305000032" ...
.. start_lat : num [1:767650] 41.7 41.9 41.9 41.9 42 ... start_lng : num [1:767650] -87.7 -87.7 -87.7 -87.7 -87.7 ...
.. end_lat : num [1:767650] 41.7 41.9 41.9 41.9 42 ... end_lng : num [1:767650] -87.7 -87.6 -87.7 -87.6 -87.6 ...
.. member_casual : chr [1:767650] "member" "member" "member" "member" ...
startz
July 25, 2024, 9:58pm
9
It's very hard to read that output. Not your fault, as the website sometimes formats things awkwardly. It looks like there isn't a column in Cycling_Months called ended_at.
what could i possibly do?
startz
July 25, 2024, 10:32pm
11
Try View(Cycling_Months). That should open a spreadsheet-like view. Then scroll if necessary until the column end_at is in view and take screenshot to post here. If you can't find the column, then it's not in the data.
keithn
July 25, 2024, 11:15pm
12
I can see the two columns in question
Note that these are character type ("chr")
So first you want to convert these to datetime objects before you can subtract them. The lubridate function ymd_hms() can help with this. Load lubridate with library(lubridate). Then you will want to wrap each of your columns with this function before you subtract them.
Secondly, to access a column of data in R, where the data frame is called df and the column is called foo, then the notation you want is df$foo, and not df.foo.
I would give you the code to work this out, but I'm typing on my phone and it's tricky to write code, so if you can have a go at piecing these two hints together and reporting back your findings, you'll save me from logging on to a computer.
Good luck.
1 Like
keithn
July 25, 2024, 11:23pm
13
Still on my phone so haven't tested this code but something like this is what I'm describing
library(lubridate)
Cycling_Months$ride_length <- ymd_hms(Cycling_Months$ended_at) - ymd_hms(Cycling_Months$started_at)
Need you to check in those dates stamps that month comes before day because I can't be sure from that snippet. If it goes year, day, month, then the function you would want is ydm_hms
Edit: correction to lubridate function. Should have been ymd_hms, thanks to @startz
2 Likes
startz
July 25, 2024, 11:24pm
14
@keithn is certainly right about the dollar sign rather than the period. I must have been in stupid-mode. That also explains the error message.
startz
July 25, 2024, 11:28pm
15
I think you want ymd_hms() rather than dmy_hms().
1 Like
keithn
July 25, 2024, 11:30pm
16
Yep you are correct. I'll edit my example code to prevent future confusion
Sorry for my late response. I will try these solutions right away
Thank you so much, It certainly worked!
system
October 24, 2024, 2:49am
19
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.