Using python in daily corporate tasks would be a great idea for a series. Thanks for this vid, I will use it in my project later :)

@gustavojuantorena

2 жыл бұрын

Agree! 👍

@yusufrumi1626

2 жыл бұрын

Oh yes please

@uberkhan

Жыл бұрын

Think the same.

Thank you John! More pandas and Excel/Google Sheets related content please ☺ love your videos and long term subscriber ♥

@JohnWatsonRooney

2 жыл бұрын

Thank you!

Thanks for this video John! I've been enjoying your data scraping videos A LOT. And this new video about local data automation is very interesting too! Being able to grab data from all kinds of sources and then produce something valuable with that is just amazing. The CTRL+D / CMD+D PyCharm duplicate shortcut was the cherry on top.

You always create a video about a subject I'm working on. Perfect timing. Keep them coming!

@JohnWatsonRooney

2 жыл бұрын

Thanks Kyo appreciate it!

Always happy to be a subscriber. This is quality

I was thinking about this yesterday, thank you !

Hey John , first time coming across your video and I'm really impressed on how flexible you are with pandas on data..and just discovering the glob method also ..hopefully I get to learn more from your videos ....Thank you and have a wonderful day

@JohnWatsonRooney

2 жыл бұрын

Thank you very kind I’m glad you enjoyed it!

Nice video, really like pandas. Hope we will see more pandas videos from you.

Great, as usually

Thanks for this type of Content please keep updating us

@JohnWatsonRooney

2 жыл бұрын

Thanks!

Excel covers full stack i.e. UI, automation and Database. What you demonstrated is only for python to cover automation - you will need additional learning curve on Python UI and Database read write to replace excel use cases with python.

Brilliant Video!! Please make moree videos on excel automation please

Hi John, Thankyou Can you make more videos on excel automation using python

Brilliant as usual 😉 i have scrapped a property website and it was containing many prop types( apartment, villa..etc every type in a single csv) and I wanted to concatenate them.

@JohnWatsonRooney

2 жыл бұрын

Thanks! I’m glad you liked it!

Thank you John.. Please add visualization of the data as well

Thank you John it was awesome .Also can you tell how we can automate product schema in python

Thank you for the video.

@JohnWatsonRooney

2 жыл бұрын

Thanks for watching!

I do like Python a lot and a few year ago that might have been the more effective way to do it. But since Excel include PowerQuery doing the same thing inside PowerQuery might be more easy for most people to reach the same result all without Excel and with a saved recipe that can be scheduled to have automated refreshed report.

@JohnWatsonRooney

2 жыл бұрын

Sure I totally understand that for this simplified example using power query would be much easier for most people. I wanted to show people what can be achieved using Python so those that are learning might find new personal projects to create to grow their learning and understanding. Thanks for your comment I appreciate good honest feedback like this

@snipelite94

2 жыл бұрын

That's my first thought! Thanks for saying it! VBA and Power Query are already there to be used, with an enormous amount of customisable power R or Python require further installations that would have to be OK'd by the IT security dept

@jeanchindeko5477

2 жыл бұрын

@@snipelite94 that true, many organisation have strict policies that restrict usage or installation of software like Python

@catferatu9736

2 жыл бұрын

I love PowerQuery and find it really userfriendly but it's still nice to know how it would work in Python so I absolutely appreciate the tutorials.

@kevinl.9657

Жыл бұрын

I also have this same sentiment. Though, if you look at the bigger picture, depending on what you are doing, using Excel's ecosystem including VBA, PowerQuery, etc, can only do so much. A simple example would be, if you improve your workflow little by little, using VBA, PowerQuery, etc, say for a year or so or however long it take, you'd definitely hit a wall that it gets so complicated. It's easier to implement each time you want to improve something compared to Python. But, had you started using Python from the start, yes it would maybe take more time to implement at first, but in the long run, using a proper programming language can do so much more. But yeah, I'm not saying using Excel's ecosystem is bad, it's actually really good. Arguably the best thing Microsoft has ever done. But if you want to make your workflow, or better, your team's workflow, better, IMHO, using a proper programming language such as Python is the right choice in the long run.

Interesting, I was using groupby function to do the same, I'll start using pivottable!. Regarding datetime conversion, I prefer to use parse_dates from read_csv parameter, specially when my setup is in spanish and I'm opening csv in US/UK date format. I would have find interesting also to show how you can create calculated fields with lambda functions. Also creating highlighting rules in the excel format is very interesting. Thanks!

A super useful follow up vid would be xlwings. After all the data manipulation and calculations, to output it in a way properly formatted and looking nice, xlwings is a pretty good choice to add on to the automation.

@JohnWatsonRooney

2 жыл бұрын

Good idea thank you!

I thought I knew Pandas well, and I thought I knew PyCharm well. CTRL+D, and pd.pivot_table were knew to me.... Good job. PS. SHIFT+F10, or CMD+R in Mac is the run shortcut in PyCharm.

@JohnWatsonRooney

2 жыл бұрын

CtrlD was new to me too I guess you don’t know if no one shows you! Thanks for the nice comment

Another great video

@JohnWatsonRooney

Жыл бұрын

Thanks glad you think so

I noticed that your previous videos were mostly done on VS code. Did you move away from VS Code to PyCharm? By the way, your videos are very nicely done! All are straight to the point and without the fluff that are often found in other channels. :)

@JohnWatsonRooney

2 жыл бұрын

Thanks! Yes I mostly use PyCharm now, however I do still use vs code sometimes

Randomly came across your vids and think it was the best thing ever, one thing I would like to ask though I have to match point of sale receipts (banked) to a the transactions(sales) the references are in some cases the same and some not( human intervention), could I create this matching using python? It kills me everyday I have to do it manually.

@JohnWatsonRooney

2 жыл бұрын

Hey thanks for watching! When you say in some cases they are not the same - that could cause some issues. You could try fuzzy matching in Python and see if that could help but generally I’d say it could be done!

Hey man. There is a store I m trying to scrape, but you have to scroll in order to see all the products. When I scroll, I can t see any get requests in the network xhr tab. What should I do to make the program go to the bottom and take all the information? I m using scrapy

Thank You John once again. Just one question, if i were to merge lets say a 100 files, the processing time would be considerable. Can we apply the logic of conc.futures here. If Yes, a little help will be appreciated. Im not even sure if thats possible so if not please avoid my stupid question. Would love to hear from yoh in either scenarios John. Happy Teaching us❤

Thanks for sharing Can you provide the CSV data files?

hello john can you make a video on scrapping world population data website please i tried but failed beacuse the span tag is constandly changing

Exactly what I'm searching for. Where can I get the csv's for practice?

@JohnWatsonRooney

2 жыл бұрын

Great thanks! I get all my fake data from mockaroo - I don’t think I have these exact ones saved I’m afraid sorry!

@mikekaspari1357

2 жыл бұрын

@@JohnWatsonRooney Thanks for the quick response. i didn't macharoo before. thanks for the tipp, so I can create my own fake date.

It seems like what you described could be performed just as easily in an Excel workbook (called, say, "Recurring Report Summary") using VBA. Do you disagree? What advantages do you see Python has over Excel VBA?

question for you, for a site that returns "you need to enable javascript to experience this site", is it still possible to use python 'requests' to do this? What's the work around? Or am I forced to switch to 'requests-html' for this task. Would prefer staying with 'requests'. Thanks.

@JohnWatsonRooney

2 жыл бұрын

Hey, I’m afraid not, JavaScript is run in the browser so you’ll need to have the page rendered out, using something like requests-html, playwright or splash

@kevinl.9657

Жыл бұрын

Use a headless browser to load the page.

*can I use garden snake*

Let's say you had a report with a column that has buy or sold value, how would you go about splitting that into multiple reports, one for buy and one for sell?

@JohnWatsonRooney

2 жыл бұрын

Hi Alan, sure you could split it up, create the data frame with all then create one for buy and one for sell - assuming there is some way to determine which is which

@alan_tucker

2 жыл бұрын

@@JohnWatsonRooney As an example let's say there was a column called 'type' with a value of either Buy or Sell.

@xpcalc446

2 жыл бұрын

@@alan_tucker if you want 2 dataframes df_buy = df[df['Type'] == 'Buy' ] df_sell = df[df['Type'] == 'Sell' ]

Hi John , can you explain ways to bypass captha?

@JohnWatsonRooney

2 жыл бұрын

You generally have to use a captcha solving service, if you google you’ll find some that will work with the captchas you are encountering (recaptcha for example) it has a cost though, the captcha itself is really there to provide a “cost” to getting the data

@divyasukumar295

2 жыл бұрын

Hi There. I have used cloudscraper to bypass captcha in some cases.

when i can get this data ?

for example u have 1 excel sheet and it consist of 10000 data in it. Later when we import that excel file in pycharm or jupiter notebook. if i run that file i will get an Index range also know as Row labels. my python code should be able to read that ten thousand row labels and should be able to separate / split into 10 different excel sheet files which will have 1000 data in each of the 10 saperated sheet. other example is, if there is 9999 data in 1 sheet then my python code should divide 9000 data in 9 sheet and other 999 in other sheet without any mistakes. i am asking this because in my data there is not any unique values for my code to split the files using .unique plz help i have search the whole YT , stackoverflow, and github tooo from 3 days

I'm a python web scraper and I have good grip on python, so, Should I go for VBA or continue with the python to handle excel problems?

@JohnWatsonRooney

2 жыл бұрын

For everyday stuff I still use excel but for things that I need to do over and over, and for larger datasets I always use pandas. I always put my scraped data into a database, then pull it into pandas for analysis. I never really used much VBA though

@kevinl.9657

Жыл бұрын

I don't think you need it. But for curiosity's sake, you might want to learn it. Or just for the purpose of having a conversation with someone who does VBA. But for solely using VBA for tasks, maybe not.

@casual_gamer1413

Жыл бұрын

@@JohnWatsonRooney thank you I will continue with python

Which python Software you use for coding

@JohnWatsonRooney

2 жыл бұрын

PyCharm almost exclusively now, occasionally vs code but rarely!

That's not really a pivot table though. In a pivot you would have countries as columns in the example.

Or just use power query, way simpler… and user friendly.

@kevinl.9657

Жыл бұрын

Yeah. Use the right tool for the right task. Though, if you want a very scalable system, use a proper programming language such as Python.

What they won’t share here is that pandas and python corrupts excel sheets …

To be honest I don't see the theme of the video being interesting for a lot of people, compare to you previous ones. I mean "are you doing repetitive tasks on the excel? Automate it with python! " isn't really a revelation. Maybe there is a lot of people who isnt like me and they didn't try this from the start, but from my point of view - not as helpful or interesting theme of the video compare to your other videos.

@JohnWatsonRooney

2 жыл бұрын

This is the hard part of content creation. I need to appeal to a wider audience which means that some people aren’t going to find certain videos as interesting or useful. Just like if I were to put out more technical content it alienates those who aren’t as proficient. It’s a tough balance. The idea of this video is to show people who are new and learning what can be achieved to get them more interested and excited to learn more Python. This one doesn’t appeal to you specifically I get that, thanks for your comment though I appreciate the feedback

yikes; this is so much more verbose than data.table syntax in R!

@JohnWatsonRooney

2 жыл бұрын

Yeah R is specifically designed for this sort of thing