Format Data using panadas groupBy such that it groups by one column

Sourabh Raj 0
Viewed 34

I have the data in below format in an csv :-

enter image description here

However, the format in which I required is below :-

enter image description here

I have written below code, but somehow the groupby is not working for me.

def grouping():
    df = pd.read_csv("final_data_6.csv")
    df['n'] = df.apply(lambda x: (x['data'], x['Period']), axis=1)
    df.groupby(['data','Period'])['n'].apply(list).reset_index()
    df.to_csv("final_data_9.csv", encoding="utf-8", index=False)
group-by pandas python-3.x
1 Answers

Use GroupBy.agg with create dictionaries filled by list:

def grouping():
    df = pd.read_csv("final_data_6.csv")
    
    df['n'] = [x for x in zip(df['positions'], df['Period'])]
    df=df.groupby('data')['n'].agg(lambda x:{'entities':list(x)}).reset_index(name='entity')
    
    df.to_csv("final_data_9.csv", encoding="utf-8", index=False)

Sample data test:

print (df)
  data positions        Period
0  abc     37,41       disease
1  abc     10,16         drugs
2  def      4,14    indication
3  def     78,86  intervention

df['n'] = [x for x in zip(df['positions'], df['Period'])]
print (df)
  data positions        Period                      n
0  abc     37,41       disease       (37,41, disease)
1  abc     10,16         drugs         (10,16, drugs)
2  def      4,14    indication     (4,14, indication)
3  def     78,86  intervention  (78,86, intervention)


df=df.groupby('data')['n'].agg(lambda x:{'entities':list(x)}).reset_index(name='entity')

print (df)
  data                                             entity
0  abc  {'entities': [('37,41', 'disease'), ('10,16', ...
1  def  {'entities': [('4,14', 'indication'), ('78,86'...
jezrael0
Related