Python Forum
Simple pandas dataframe question
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Simple pandas dataframe question
#1
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?

Thanks,

Allen
Reply
#2
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)
print(before)
after = before.groupby(['Country','Index']).sum()
after = after.sort_values(by=['Shares'])
print(after)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  Use pandas to obtain cartesian product between a dataframe of int and equations? haihal 0 1,091 Jan-06-2023, 10:53 PM
Last Post: haihal
  pandas df inside a df question mbaker_wv 4 1,152 Dec-25-2022, 01:11 AM
Last Post: mbaker_wv
  Pandas usecols question rsearing 1 1,218 Aug-20-2022, 10:10 PM
Last Post: jefsummers
  Pandas Dataframe Filtering based on rows mvdlm 0 1,396 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Pandas dataframe: calculate metrics by year mcva 1 2,269 Mar-02-2022, 08:22 AM
Last Post: mcva
  Pandas dataframe comparing anto5 0 1,243 Jan-30-2022, 10:21 AM
Last Post: anto5
  PANDAS: DataFrame | Replace and others questions moduki1 2 1,758 Jan-10-2022, 07:19 PM
Last Post: moduki1
  PANDAS: DataFrame | Saving the wrong value moduki1 0 1,527 Jan-10-2022, 04:42 PM
Last Post: moduki1
  Simple question (I guess) tchadrack 1 1,294 Jan-08-2022, 06:36 AM
Last Post: buran

Forum Jump:

User Panel Messages

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