9

Suppoose df.bun (df is a Pandas dataframe)is a multi-index(date and name) with variable being category values written in string,

date      name             values
20170331  A122630          stock-a
          A123320          stock-a
          A152500          stock-b
          A167860          bond
          A196030          stock-a
          A196220          stock-a
          A204420          stock-a
          A204450          curncy-US
          A204480          raw-material
          A219900          stock-a

How can I make this to represent total counts in the same date and its percentage to make table like below with each of its date,

date           variable    counts     Percentage
20170331          stock         7           70%
                   bond         1           10%
           raw-material         1           10%
                 curncy         1           10%

I have done print(df.groupby('bun').count()) as a resort to this question but it lacks..

cf) Before getting df.bun I used the following code to import nested dictionary to Pandas dataframe.

import numpy as np
import pandas as pd

result = pd.DataFrame()
origDict = np.load("Hannah Lee.npy")
for item in range(len(origDict)):
    newdict = {(k1, k2):v2 for k1,v1 in origDict[item].items() for k2,v2 in origDict[item][k1].items()}
    df = pd.DataFrame([newdict[i] for i in sorted(newdict)],
                      index=pd.MultiIndex.from_tuples([i for i in sorted(newdict.keys())]))
    print(df.bun)
hildensia
  • 1,650
  • 2
  • 12
  • 24
Hannah Lee
  • 383
  • 2
  • 7
  • 19
  • What is `df`? If it has a tag, can you edit your question to add that, and if it doesn't, can you edit your question to link to what `df` is supposed to be? – Mike 'Pomax' Kamermans May 04 '18 at 07:21
  • dataframe is not enough. Which package do you use. I suppose it is pandas, but it's not clear by your question. Also, it is usually better to show (minimal) code, that is actually running – hildensia May 04 '18 at 07:23

1 Answers1

15

I believe need SeriesGroupBy.value_counts:

g = df.groupby('date')['values']
df = pd.concat([g.value_counts(), 
                g.value_counts(normalize=True).mul(100)],axis=1, keys=('counts','percentage'))
print (df)
                       counts  percentage
date     values                          
20170331 stock-a            6        60.0
         bond               1        10.0
         curncy-US          1        10.0
         raw-material       1        10.0
         stock-b            1        10.0

Another solution with size for counts and then divide by new Series created by transform and sum:

df2 = df.reset_index().groupby(['date', 'values']).size().to_frame('count')
df2['percentage'] = df2['count'].div(df2.groupby('date')['count'].transform('sum')).mul(100)
print (df2)
                       count  percentage
date     values                         
20170331 bond              1        10.0
         curncy-US         1        10.0
         raw-material      1        10.0
         stock-a           6        60.0
         stock-b           1        10.0

Difference between solutions is first sort by values per groups and second sort MultiIndex.

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252