Python Forum
Simple pandas dataframe question
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).
[Image: Screen-Shot-2018-12-30-at-9-59-18-PM.png]

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

Possibly Related Threads…
Thread Author Replies Views Last Post
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 155 Jan-11-2021, 09:53 PM
Last Post: Azureaus
  Pandas question new2datasci 0 140 Jan-10-2021, 01:29 AM
Last Post: new2datasci
  Comparing results within a list and appending to pandas dataframe Aryagm 1 182 Dec-17-2020, 01:08 PM
Last Post: palladium
  How to search for specific string in Pandas dataframe Coding_Jam 1 253 Nov-02-2020, 09:35 AM
Last Post: PsyPy
  PANDAS: DataFrame | White Spaces & Special Character Removal traibr 1 558 Sep-10-2020, 07:02 PM
Last Post: eddywinch82
  No Output In Pandas DataFrame Query eddywinch82 1 403 Aug-17-2020, 09:25 PM
Last Post: eddywinch82
  strange error from pandas dataframe djf123 1 916 Jul-27-2020, 05:25 AM
Last Post: scidam
  Pandas DataFrame not updating HelpMePlease 3 600 Jul-11-2020, 07:19 PM
Last Post: jefsummers
  Pandas DataFrame visual Truman 8 819 Jul-10-2020, 06:11 AM
Last Post: hussainmujtaba
  Pandas DataFrame and unmatched column sritsv19 0 618 Jul-07-2020, 12:52 PM
Last Post: sritsv19

Forum Jump:

User Panel Messages

Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020