1

Using a data-set, I am trying to group the values on the basis of a key(order_id) using the groupby(). However, the return type being groupby object, I am not able to convert into a data frame which is the requirement.

I have tried using groupby to aggregate on the basis of a key. I also tried converting into a list.

import pandas as pd
table=pd.read_csv("chiporders.csv", sep='\t')
df_chiporders= pd.DataFrame(table)
type(df_chiporders)
type(df_chiporders['item_name'])
df_chiporders.item_name
df_grp=df_chiporders.groupby('order_id')['quantity'].apply(list)
print df_grp

Expected result : The result in a data Frame.

please refrain using Count(), Sum() because my answer should have the actual values in a dataframe

snair.stack
  • 405
  • 4
  • 13
john_carter
  • 21
  • 4
  • Use `df_grp=df_chiporders.groupby('order_id')['quantity'].apply(list).reset_index()` – jezrael Sep 23 '19 at 10:41
  • OUTPUT : print df_grp order_id 1 [1, 1, 1, 1] 2 [2] 3 [1, 1] 4 [1, 1] 5 [1, 1] .... 1825 [1, 1, 1, 1, 1, 1] 1826 [1, 1, 1, 1] 1827 [1, 1, 1, 1, 1] 1828 [1, 1, 1] 1829 [1, 1, 1] 1830 [1, 1] 1831 [1, 1, 1] 1832 [1, 1] 1833 [1, 1] 1834 [1, 1, 1] Name: quantity, Length: 1834, dtype: object print type(df_grp) – john_carter Sep 23 '19 at 10:41
  • Thanks, @jezrael .. It works. What is the L? in the data frame ! can we remove it ? – john_carter Sep 23 '19 at 10:43
  • `L` ? Where is it? – jezrael Sep 23 '19 at 10:44
  • Hi, @jezrael, it is not similar to the other question, i need to use the groupby(), this returns the groupby object, this object has to be converted to a data frame – john_carter Sep 23 '19 at 11:01
  • Can you create [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) ? – jezrael Sep 23 '19 at 11:05
  • Using public dataset available here: [link]https://raw.githubusercontent.com/mathcoder3141/blog-data-files/master/Congress_White_House.csv `import pandas as pd; df = pd.read_csv('white_house_salary.csv'); df.columns; df_grp = df.groupby('Position Title'); new_df = pd.DataFrame(df_grp); new_df.to_csv("out.csv", sep = "\t");` This should be able to convert the groupby object to dataframe and then write to a csv file. Hope it helps. – snair.stack Sep 23 '19 at 12:27

0 Answers0