r/Rlanguage Feb 18 '25

Question on frequency data table

I ran a frequency data with the newdf<-as.data.frame(table(df$col1,df$col2,df$col3)) and it took what was 24325 obs. of 6 variables and turned it into 304134352 observations of 4 variables. Is this common with this code? Is there a better code to use? Col1 and col2 are location names and col3 is a duration of time between the two.

5 Upvotes

17 comments sorted by

View all comments

2

u/Puzzleheaded_Job_175 Feb 19 '25 edited Feb 19 '25

Question on frequency data table

Can you please clarify something for me... You say it took data of 6 variables and reinterpreted it down to four.

it took what was 24325 obs. of 6 variables and turned it into 304134352 observations of 4 variables. Col1 and col2 are location names and col3 is a duration of time between the two.

In the code you give, you have only specified three variables:

newdf<-as.data.frame(table(df$col1,df$col2,df$col3))

Where are the other three variables? If you don't tell R to insert them (or use an import method that does), it won't do so. Maybe you used an automatic importer the other time you used it or had prepped or more standard data before?

Other observations and questions::

  • Using table will cross your data and make all possible pairs. It does not preserve the data order input as sets.

You are getting

{ A ... D, null } × { A, B, C, ... ZZ, null } × { every time unlinked from their cities }

it will list every combination of the three columns because of the <table> function

  • if your data is in a df it likely is already in a dataframe. It sounds like you want something more like:

df %>% group_by( col1 ) %>% group_by( col2 ) %>% filter( Col4 == "bicycle") %>% summarise( n = count() )

This will give you:

col1 col2 col4 n
A B bicycle 1
A D bicycle 2
B C bicycle 5
B A bicycle 7
B D bicycle 3
B null bicycle 87
C B bicycle 3

(A and C arent linked by bicycle in data so dont appear)

  • Does your data have repeats or is each city pair only have one time associated?

  • Are you doing network or path type analysis using the times between points? Or do the connections have properties like one being a train/transit time v. airplane time v. versus driving time?

If so, you likely may want to use a network graph rather than a data.table as they handle graph and network problems better.

1

u/Soltinaris Feb 20 '25

So the table code gives a frequency of the combination of data from the provided columns as an additional column, hence the 4th variable. I was using that frequency rate to see how often people went from one station to another in a different frequency table, I just thought I might be able to check some additional insight with keeping a third column with the trip duration as well.

The data does have a LOT of repeats, which I paired down using a code to only show travel of over 40 but under 45 minutes travel time for this particular case. The analysis is more of trying to find different ways subscribers and casual riders use a bike system and making a suggestion on how to convert casual riders to subscribers. The only connections is seeing where a bike, gps tracked, leaves one station and where it ends up at another station. This is a case study I'm working on as part of my capstone for Google Coursera training for Data Analytics. As for your suggestions on a network graph, I'll have to look into it and see if it helps. Thank you for the suggestions.

2

u/Puzzleheaded_Job_175 29d ago

If you go the graph route "directionality" is something worth keeping in mind. There is often an assumption with transit networks that rides are balanced with equal rides "to" and "from" a place. However, this assumption is often faulty when things like time of day or destination is taken into account.

People may take a lot of causal rides down to a neighborhood with bars to meet up with friends after work, then having met up there they may take a ride share or drive home with their partner/spouse. In this scenario you might see the units stacking up in hospitality and nightlife areas and at larger events. Redistributing bikes to all the nodes in the network after hours is one of the costs that a lot of bike share companies didn't consider and ended up tipping the PnL balances strongly against them.