CODEWITHSUNDEEP
pythonpandasdataframe
Zephyr
Zephyr

Reputation: 1352

Replace duplicated values with blank in pandas

I am working on Pandas data frame. The example code will be as follow: ` import pandas as pd

df = pd.DataFrame(
                  {'name1': ['A', 'C', 'A', 'B','C', 'D','D', 'C', 'A', 'B','C', 'A'], 
                   'name2': ['B', 'D', 'C', 'D','B','A','A', 'D', 'C', 'D','D','B'], 
                   'id': [1, 1, 1, 1, 1, 1,2, 2, 2, 2, 2, 2], 
                   'Value1': [1, 2, 3, 4, 5, 6, 0, 2, 4, 6, 3, 5], 
                   'Value2': [0, 2, 4, 6, 3, 5, 1, 2, 3, 4, 5, 6]
                  },
                  columns=['name1','name2','id','Value1','Value2'])`

I can do the agg using the following groupby:

m = df.groupby(['id','name1',])['Value1'].sum()

When I printed m, it will show as follow:

   id  name1
    1   A        4
        B        4
        C        7
        D        6
    2   A        9
        B        6
        C        5
        D        0
    Name: Value1, dtype: int64

When I wrote m it to csv file, it will only contain the value1 as it is a pandas series. Using this series, I want to create a dataframe that is exactly the same as the table below

  id name1  Value1
  1     A      4
  1     B      4
  1     C      7
  1     D      6
  2     A      9
  2     B      6
  2     C      5
  2     D      0

Anyone advise me how to do that? Thanks a lot Zep

Upvotes: 3

Views: 1707

Answers (2)

Pyd
Pyd

Reputation: 6159

simply,

 #reseting the index
 m = m.sort_index().reset_index()
 #masking duplicated value with empty
 m['id']=m['id'].mask(m['id'].duplicated(),"")
 #writing dataframe to a csv file
 m.to_csv("output.csv",index=False)

Upvotes: 3

cs95
cs95

Reputation: 403278

If you need to save to CSV, here's a hack you can use to fix the display before saving.

m = m.sort_index().reset_index()
m['id'] = m['id'].mask(m['id'].ne(m['id'].shift()).cumsum().duplicated(), '')

print(m)
  id name1  Value1
0  1     A       4
1        B       4
2        C       7
3        D       6
4  2     A       9
5        B       6
6        C       5
7        D       0

m.to_csv('file.csv')

Disclaimer; if you're doing anything besides saving, do not run this beforehand.

Upvotes: 3

Related Questions