Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 manipulating a dataframe - pandas

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
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)


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

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Obtaining Correct Date In Pandas DataFrame eddywinch82 7 386 Yesterday, 04:47 PM
Last Post: sandeep_ganga
  Parse XML String in Pandas Dataframe creedX 2 282 Dec-09-2019, 07:35 PM
Last Post: creedX
  Pandas dataframe to join three tables using like condition among them sandeep_ganga 0 237 Nov-29-2019, 08:30 AM
Last Post: sandeep_ganga
  Pandas Dataframe to Google Big Query Ecniv 2 883 Nov-21-2019, 02:26 PM
Last Post: Ecniv
  Pandas dataframe columns collapsed in Spyder when printing UniKlixX 2 218 Nov-04-2019, 07:00 AM
Last Post: UniKlixX
  pandas dataframe iloc mystery edvvardbrian 2 299 Oct-29-2019, 02:55 PM
Last Post: jefsummers
  How to add a few empty rows into a pandas dataframe python_newbie09 2 1,251 Sep-20-2019, 08:52 AM
Last Post: python_newbie09
  Dropping a column from pandas dataframe marco_ita 6 1,639 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  created a pandas series instead of pandas DataFrame ibaad1406 6 778 Sep-06-2019, 06:23 AM
Last Post: ibaad1406
  Applying operation to a pandas multi index dataframe subgroup Nuovoq 1 472 Sep-04-2019, 10:04 PM
Last Post: Nuovoq

Forum Jump:

Users browsing this thread: 1 Guest(s)