How to remove duplicate values from multi-index pandas dataframe when saving to csv

Question

I have a dataframe like this:

df=pd.DataFrame([[1,"10/1/2019","I",2879],
                 [1,"10/1/2019","O",196],
                 [1,"10/2/2019","I",2840],
                 [1,"10/2/2019","O",189],
                 [2,"10/1/2019","I",2907],
                 [2,"10/1/2019","O",195]],
                columns=["A","B","C","D"])
df.set_index(["A","B","C"],inplace=True)

when I display the contents they look more or less like this:

                    D
A   B           C   
1   10/1/2019   I   2879
                O   196
    10/2/2019   I   2840
                O   189
2   10/1/2019   I   2907
                O   195

So my question is how to generate a csv file which contents look like it shows on the notebook where "A" and "B" values become empty on subsequent rows. In other words, this is how I need the csv file output to look like:

A,B,C,D
1,10/1/2019,I,2879
,,O,196
,10/2/2019,I,2840
,,O,189
2,10/1/2019,I,2907
,,O,195

David Erickson · Accepted Answer · 2020-10-16 01:11:07Z

2

Don't set the index, and create some series that return True or False and where True make the values blank:

df=pd.DataFrame([[1,"10/1/2019","I",2879],
                 [1,"10/1/2019","O",196],
                 [1,"10/2/2019","I",2840],
                 [1,"10/2/2019","O",189],
                 [2,"10/1/2019","I",2907],
                 [2,"10/1/2019","O",195]],
                columns=["A","B","C","D"])
s1 = df.duplicated(subset=['A'])
s2 = df.duplicated(subset=['A','B'])
df['A'] = df['A'].where(~s1,'')
df['B'] = df['B'].where(~s2,'')
df
Out[1]: 
   A          B  C     D
0  1  10/1/2019  I  2879
1                O   196
2     10/2/2019  I  2840
3                O   189
4  2  10/1/2019  I  2907
5                O   195

answered Oct 16, 2020 at 1:11

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Erickson Over a year ago

@codeninja2c4u no problem also kindly feel free to upvot if helpful. Thanks for accepting!

Collectives™ on Stack Overflow

How to remove duplicate values from multi-index pandas dataframe when saving to csv

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related