How to put multi array to one csv file with header Python

Question

I have list like this, I loaded from xlsx file

import pandas as pd
travel_df = pd.read_excel('./item.xlsx')
data = travel_df.to_dict('records')

the data like this

data = 
[
    {
        'cat': 'A',
        'subCat': 'a1',
    },
    {
        'cat': 'A',
        'subCat': 'a2',
    },
    {
        'cat': 'B',
        'subCat': 'b1',
    },
    {
        'cat': 'B',
        'subCat': 'b2',
    },
    {
        'cat': 'B',
        'subCat': 'b3',
    },
]

I want to put this into CSV file like this, what is the best and fastest way to do that

A     B
--------
a1    b1
a2    b2
      b3

Anurag Dabas · Accepted Answer · 2021-05-16 10:00:04Z

1

You can do this by DataFrame() method,pivot() method and apply() method:

newdf=pd.DataFrame(data).pivot(columns='cat',values='subCat').apply(lambda x:sorted(x,key=pd.isna))

Finally filter out NaN's:

newdf=newdf[~newdf.isna().all(1)]

Output of newdf:

cat   A     B
0     a1    b1
1     a2    b2
2     NaN   b3

Now if you want to save this in csv file then use to_csv() method

answered May 16, 2021 at 10:00

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

2 Comments

miladjurablu Over a year ago

how to avoid duplicate records?

Anurag Dabas Over a year ago

just use drop_duplicates() method i.e : newdf=newdf.drop_duplicates()

Collectives™ on Stack Overflow

How to put multi array to one csv file with header Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related