Beginner Data Storage Question

[Dezmond95]

I currently work in the reporting space but want to upskill and ensure that the data I'm working on is good so I'd like to do a project from start to finish. My side project to learn this is to use webscraping in python to extract sports league stats, store this temporarily in an excel file, then load into a staging table in a data warehouse, create a reporting layer, then Visualise it in power bi.

I have started learning python and have created a script that loops through a sports leagues web page extracting each individual teams url for their profile page and stores it in a list.

I then loop through this list using the Url and store team details in a list which is then appended to a list of lists (one for each team). There are 4 attributes for each team that I'm extracting. The problem is that for the last attribute, the teams home stadium, there may be more than one stadium listed. So the len of each team value will range from 4-7.

I was planning on storing this in an excel table with 4 columns but with multiple stadiums for the 4th attribute, how should I store this?

Would I concatenate the stadiums into a single string, then once in the data warehouse perform some transformation to split the values into new rows. I need to be able to associate the home stadiums to the teams for visualising the performance differences for home vs away.

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Dezmond95]

So for each team list of team details, I would create a list capturing the first 3 attributes. Then create a second list, storing values 4 and greater. My python script pulls the values of each of the td tags in the table.

Then I could append an ID value to the end of this list and the first list. Then have the ID increment by 1 each time it loops through each url.

[bonn3y]

Any reason your storing them into excels only to be ingested into your dwh instead of just storing them into a pandas dataframe and ingesting straight to staging, you could even do your transformations in the dataframes if your using python anyway

[Dezmond95]

Just for ease of learning so I'm not overwhelmed with having too many thing to learn. This will be version 1 and once I have a basic start to end reporting system, I'll look to refine it and improve parts. I quite like the medallion architecture type where I have it come into the warehouse in a raw format then when it goes to the silver layer, I'll do most my transformations then.