Bottom Page

Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Simple pandas dataframe question
Hi all,

I am new to pandas and have a real simple question for all of you but it will make my day to day presentation more intuitive.

I am trying to merge few common key value into one (please see below picture).

This is similar to excel function "Merge and Centre" where values in different cells can be combined into 1 single cell/value. I know how to do it while concating multiple dataframes but in my case it is a single one.

Do you have any clues?


The below code will do exactly what you seek.

To explain it, I created 3 series and assigned them to before_data.

I assigned the before_data as the data for a DataFrame called before.

I assigned the now grouped by (Country and Index) DataFrame before to after and used .sum() to aggregate as there is technically no summing going on here in these values.

I then applied a sort on the Shares column so that numerically it matched the original dataframe.

import pandas as pd
import numpy as np

before_data = {'Country' : pd.Series(['Australia','Australia', 'Japan', 'Japan','Japan','Japan', 'Hong Kong', 'Hong Kong', 'Hong Kong'], index=range(9)),
               'Index' : pd.Series(["ASX 200","MSCI Australia","N225","Topix","Mother","MSCI Japan", "HSI", "HSCEI", "MSCI Hong Kong"]),
               'Shares' : pd.Series([10,20,30,40,50,60,70,80,90])}

before = pd.DataFrame(before_data)
after = before.groupby(['Country','Index']).sum()
after = after.sort_values(by=['Shares'])

FORTITUDE likes this post

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Pandas merge question smw10c 2 2,714 35 minutes ago
Last Post: hussainmujtaba
  Pandas DataFrame Concatenate problems Kristenl2784 1 95 Yesterday, 01:28 AM
Last Post: hussainmujtaba
  Difference of two columns in Pandas dataframe zinho 2 349 Jun-17-2020, 03:36 PM
Last Post: zinho
  error bars with dataframe and pandas Hucky 4 353 Apr-27-2020, 02:02 AM
Last Post: Hucky
  Python Pandas DataFrame Help AmericanEagle1989 1 221 Apr-12-2020, 12:37 PM
Last Post: AmericanEagle1989
  Python & Dataframe basic question georgialtr 0 271 Apr-06-2020, 03:59 PM
Last Post: georgialtr
  How does pyplot know what was plotted by the output of pandas.DataFrame(...).cumprod( codeowl 2 245 Mar-28-2020, 08:27 AM
Last Post: j.crater
  Ordering of pandas DataFrame new_to_python 5 308 Mar-15-2020, 06:08 PM
Last Post: new_to_python
  Pandas dataframe merge snmmat 1 316 Mar-09-2020, 06:56 PM
Last Post: jefsummers
  Transform Facebook Graph API insights JSON to pandas dataframe usman 0 346 Mar-03-2020, 05:14 AM
Last Post: usman

Forum Jump:

Users browsing this thread: 1 Guest(s)