- Intro
: 'Or' " , as my understanding this is type of data issue (expect is int , but the current is str] , right ? And in the tutorial you solved it by "all_data = all_data[all_data['Order Date'].str[] != 'Or']" , can you please help explain this ?
_df["Month"] = c_df["Order Date"].str[]<ipython-input-56-07e71ed795ed>:2: SettingWithCopyWarning:A value is trying to be set on a copy of a slice from a DataFrame.Try using .loc[row_indexer,col_indexer] = value instead
: i was able to avoid the warning with df = df.groupby('Order ID').agg({'Product': ', '.join}).reset_index(). I look forward to more of practical projects.Thanks
The error can be avoided if you declare df to be a copy of all_data using .copy()
The groupby function sorts by months I think so that will be [], same as the new month variable
- Downloading the Data
- Getting started with the code (Jupyter Notebook)
csvs into a single dataframe ()
- Read single CSV file
At when using os.listdir("'./"), this returns a list alread. So using [file for file in os.listdir(...)] is redundant.
- List all files in a directory
How would I do the code at around in the video without list using list comprehension?
- Concatenating files
'' instead of the rows containing 'or' error @ 21:46. Also, when i merged the data my merged data has blank rows in between . Anybody else having the same issue?
If you are working with your own data and watching this video and if you do not have a date, consider adding the below. I needed to break down my data by week, so I did:
my approach was quite not elegant: cities = sorted(str(all_data["City"].unique()).split(" ' "))[] hahaha
nd line appears to me on all columns NaN, instead of "Bose SoundSport...". My doubt is about minute of the video. Does someone have the same problem?
- Reading in Updated dataframe
: Add a Month column ()
- Parse string in Pandas cell (.str)
, u can solve that problem with pd.to_datime(column), actually without format because it can be produced to the date time, and then u can actually find all month that u need, good luck!
At when I am doing : all_data.Month = all_data['Order Date'].str[:2]. I get a warning and month column does not get added up to the dataframe. Warning I get is as follows : UserWarning: Pandas doesn't allow columns to be created via a new attribute name.Can someone please suggest how do I add my column.
- Drop NaN values from df
: how the products command is working. [product for product, df in product_group] can you please explain the same. Or anyone can let me understand please.
- Remove rows based on condition
I think is more reliable to parse column of dates as datetime type to avoid all these problems
that duplication was because of the header rows in each of the files. I've dealt with this a lot. You would have had had to have excluded those header rows on each file before you concatenated all of them together to resolve this.
AtI am getting a following error:
: Add a sales column ()
- Another way to convert a column to numeric (ints & floats)
-Even after applying pd.to_numeric type of Price Each remains object.How do I fix this issue?
: What was the best month for sales? ()
At , sales also have values from January 2020 dates too. So, the values for Jan should be lesser than what's shown.
- Visualizing our results with bar chart in matplotlib
For this task: - Visualizing our results with bar chart in matplotlibInstead of using the datetime library I used the calendar library. This code helps me change the month number to month name:
-- Sales amount for Dec month should be 1.289987e+11, when [quantity order * price each]If multiply 28114 * 4.588415e+06 = 1.289987e+11Why Sales column is not generating the correct result or I am mistaken somewhere?Can anyone suggest here?
i was wondering during the result of my Y axis is in E notation. how can i convert it into real numbers? would really appreciate if anybody would help to solve this problem!
you have created months list to pass it to plt.bar() out of thin air, in current scenario as our data is coming in sorted way by month so no issue is coming else it would have plotted Sales against wrong month. Instead I tried this, please let me know if I'm wrong about it?all_data.groupby('Month')['Daily Sale'].sum().plot(kind='bar')plt.show()
: What city sold the most product? ()
Pro tip: go to command mode (press Esc) and press 'b' to make cells below current cell or 'a' to make cells above
- Add a city column
For you can also use the following:df['Purchase Address'].str.split( ',' , n=2, expand=True)
Instead of using lambda function you can also you can also use split function directly on the series like this:
I can already anticipate issues with cities in different states having the same name and being grouped together. Really need to combine city+state to compare different cities.
- Using the .apply() method (super useful!!)
, wouldn't it be easier if the "City' column is obtained through the zipcodes of the 'Purchase Address'?
Hey Keith, thank you for this amazing video. I just wanted to raise a query on an error I'm encountering when we are trying to split the 'Purchase Address' column to identify the City at !I'm following the tutorial step by step and the piece of apply() code that works for you in throwing an error for me -Code: all_data['City'] = all_data['Purchase Address'].apply(lambda x: x.split(',')[1])IndexError: list index out of range
For anyone wondering, at , you can also just do .apply(get_city) without adding the () so that the function doesn't get called immediately, and the pandas apply method will know to pass in each value of the series to the function, so you can also avoid using the lambda to call the get_city function!
I believe you can also just refer to the function by name instead of using the lambda.
Great video Keith, thanks! At you use .apply(lambda x:get_city(x)) and I wonder, since you have already defined the get_city function separately, couldn't we just use .apply(get_city)? Cheers
- Why do we use the lambda x ?
At , you don't need to use the lambda function when you use the apply method.df['City'] = df['Purchase Address'].apply(get_city)
Just a tip, you actually don't need to use lambda to grab a cells contents like you said at around . Lambda is just a tool to allow you to use functions like apply(and others) on one line, without having to define a custom function beforehand to pass in. If you have already defined a function like get_city(address), then you can just pass that function itself into apply. So the full line would look like this: all_data['Column'] = all_data['Purchase Address'].apply(get_city)
At you don't need to use the lambda function, even if you want to access a cell content. If you simply pass the reference to a function, by default the *args will be passed. Example:
- Dropping a column
edit: Ahh you noticed it too :)
Great Video. At you could've used this address.split(',')[2][1:3] instead of using split again.
I keep getting an error "TypeError: 'builtin_function_or_method' object is not subscriptable" when trying to use the .apply() method at .
- Answering the question (using groupby)
@ can we use data from results to plot a bar graph [city, Sales]?
- Plotting our results
hey Keith, how do you remove the scientific notation and display your yticks in "millions" at ?
At Why do we need to find the unique value when the group by (''city") will provide the value unique itself. I simply didsales_by_city = merged_df.groupby("city").sum().reset_index()
Hey Galli, I am following your tutorials... Why don't you useresults.index() orlist(results.index()) to get name of cities....
At you ran into the issue where you had the names in the wrong order:
I have a question! At , when you go through using the list comprehension to match the cities with sales in the plot, couldn't you have just used the column from that very "results" df you created which has the unique cities and their respective sales sums?
For the issue at I just use results.index instead of creating a unique list of cities from the all_data.plt.bar(results.index, results['Sales'])
, use result.index as x values and x ticks.
- 51:00We can also use the index of the previous dataframe which contains the city names :)cities = <name_of_the_previous_table>.index
At for anyone who wants to use .unique(), when you calculate the sales for each city make sure to throw in a .reset_index() in there, it will reset the indexes and your bar is going to be alright.
Really like this tutorial. Very helpful. I tried to work ahead as you instructed, and I think I found an alternative method for .
In for cities: can always use the index values from 'results' DF:cities = results.index.valuesinstead of a for loop
Hey Keith, great work! Could you please give a "one line-ish" explanation for the trick in ? Keep on rockin'! cheers
In instead of doing comprehension workaround, you could simply use results.index.
- Instead of looping, we can just pass results.index which solved my problem
What i did at is :
On that part where you groupby cities then plot the total sales, then go through a complicated plotting procedure ( ) .That's not necessary.
at you can just do city=results.index to print the city in order with sales
cities = result.Sales.keys() works as expected. great tutorial, tks!
I tried using cities=result.index instead of the list comprehension. Thanks for the video.
At we can get the list of cities by just passing list(results.index)
Instead of using a confusing for loop and comprehension list for sorting values of cities for X-axis. You can just sort the values of the city column beforehand like this:
At is it possible to sort the bar chart, so that the cities are ordered from left to right in ascending order with respect to their sales? When I try to use sort_values("Total", ascending = False) the order gets mixed.
in instead of using that list comprehension , we can simply change the "city " index to columns in result data frame throgh this -> results.reset_index(level=["city"]) and simply use the city column as x axis and sales column as y axis in barplot
: What time should we display advertisements to maximize the likelihood of purchases? ()
And here isdf['hour'] = df.loc[:, 'Order Date'].map(lambda x: x.hour )plt.figure(figsize=(18,10))sns.barplot(df.groupby('hour').sum().sort_values(by='sales', ascending=False).index,df.groupby('hour').sum().sort_values(by='sales', ascending=False)['sales'])
- Using to_datetime() method
When parsing a column into datetime, specifying the format manually will decrease the execution time significantly:all_data['Order Date'] = pd.to_datetime(all_data['Order Date'], format='%m/%d/%y %H:%M')
- Creating hour & minute columns
- Matplotlib line graph to plot our results
Extremely helpful video Keith! So happy that I found this channel. Also, voice crack at :)
I heard that
Hi Keith! Actually I have problems executing where plt.plot(hours, all_data.groupby([‘Hour’]).count()). It turns out to be a InvalidIndexError. I checked the syntax and accuracy but still it does not work on my notebook. Is there anyways to tackle this problem?
The analysis at (advertising time) is incorrect, you need to group by order ids as well, otherwise orders with many different products in them have more "weight" than ones with single products. Also, perhaps not use count and instead use order value so they can be weighted by amount, since more expensive orders are more valuable for the business.
: What products are most often sold together? ()
(Which set of items are mostly bought together) at I thought of an alternate solutionbought_together = all_data.groupby(by='Order ID').count() # Because this will count the number of times each order has been boughtmax_order = bought_together['Product'].idxmax() # Gives the order ID of the product which was bought most frequently (The count after groupby is the maximum)most_bought_together = all_data[all_data['Order ID'] == max_order]['Product'] # Gives the product name where Order ID is same as the max_order
- Finding duplicate values in our DataFrame
- Use transform() method to join values from two rows into a single row
...I used the .apply function here .. worked pretty much the same ... I think it worked better since it removed all duplicates from the returned series unlike transform where the returned series was of the same length as the passed dataframe
- Dropping rows with duplicate values
- Counting pairs of products (itertools, collections)
For the section: - Counting pairs of products (itertools, collections)
Nice and useful video Keith. At around when you want to count the pairs of products wouldn't be simpler to just do df['Grouped'].value_counts() ? This gives me same answers (order) as you but the total count is different for some reason: iPhone,Lightning Charging Cable 882 ; Google Phone,USB-C Charging Cable 856 ; iPhone,Wired Headphones 361. Thanks again !
Stackoverflow link used at : https://stackoverflow.com/questions/52195887/counting-unique-pairs-of-numbers-into-a-python-dictionary
: What product sold the most? Why do you think it did? ()
- Graphing data
- Overlaying a second Y-axis on existing chart
