+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: manipulating a dataframe - pandas (/thread-22453.html)
manipulating a dataframe - pandas - nsx200 - Nov-13-2019
Hello,
Whilst this is an assignment, I thought I would post in this forum page as it is pandas related...
I have a rubbish dataframe to use that lists sales amounts over a 6 month period. There are just two columns - a 'Country' column listing the names of countries that produced sales and a second column 'Sales Amount' that simply has an integer value for the number of sales for that country.
It is quite rubbish in that the countries appear multiple times (some more than others) and I have the task of creating a new dataframe with the unique country names as the index, but the second column must be a sum of the total number of sales for each country. So if England appears 3 times in the list with a 1, a 2 and a 3 being the integers in the three rows. I would need to return England in the first column with 6 in the second.
I have no issue getting the unique values, but my brain is stumped on producing the second column based on a total sum of each unique countries sales amounts.
So if I had the following basic dataframe...
df = pd.DataFrame{'England':1,'England':2,'England':3,'Wales':2,'Wales':7,'Wales':4}Giving the output of...England 1
England 2
England 3
Wales 2
Wales 7
Wales 4
I would need to return a new dataframe (not a pivot table or other structure - the assignment says it must be a dataframe) looking like this...
Country Sales Amount
England 6
Wales 13
Any pointers - not answers - they would be greatly appreciated.
I have tried to use a groupby() to populate the second column, but I just seem to get either 123 as the sales for England rather than 6, or I get 3 which is the number of times England appears in the Country column.
Apologies for the lack of code given, I am on my mobile and it is not easy to enter code with my very old and small phone screen.
Kind Regards
RE: manipulating a dataframe - pandas - ThomasL - Nov-13-2019
Difficult to give you hints/pointers without giving the answer.
So, with .groupby() you were on the right track but didn´t take next step.
import pandas as pd
data = [['England', 1], ['England', 2], ['England', 3], ['Wales', 2], ['Wales', 7], ['Wales', 4]]
df = pd.DataFrame(data, columns=["country", "sales"])
new_df = df.groupby("country").sum()
new_df.reset_index(level=0, inplace=True)
print(new_df)
print(type(new_df))
RE: manipulating a dataframe - pandas - nsx200 - Nov-14-2019
ThomasL
Thanks. Good to know I was nearly there initially.
I will perservere and look at the example you have given to make it work.
Thanks for your help