How to format Pandas DataFrame with different array sizes

Question

I need to create an Excel spreadsheet dynamically that looks something like this:

 _______________________________________________________
| Name | Start Date | Title | Companies | Sub-companies |
|-------------------------------------------------------|
| Jim  | 01/01/2010 |  CTO  |  Google   |   YouTube     |
|-------------------------------------------------------|
|      |            |       |           |    FitBit     |
|-------------------------------------------------------|
|      |            |       |   Apple   |    NextVR     |
|-------------------------------------------------------|
|      |            |       |           |    Beats      |
|-------------------------------------------------------|
| Tom  | 12/12/2020 |  CEO  |  Amazon   |   Audible     |
|-------------------------------------------------------|
|      |            |       |           |    Zappos     |
|-------------------------------------------------------|
|      |            |       |  Samsung  |  Biologics    |
|-------------------------------------------------------|
|      |            |       |           |    Cheil      |
|-------------------------------------------------------|

This is my code so far:

import pandas as pd

df = pd.DataFrame({
    "Name": ["Jim", "Tom"],
    "Start Date": ["01/01/2010", "12/12/2020"],
    "Companies": ["Google", "Apple", "Amazon", "Samsung"],
    "Sub-companies": [
         "YouTube", "FitBit", "NextVR", "Beats", 
         "Audible", "Zappos", "Biologics", "Cheil"
         ]
})

print(df)

df.to_excel("output.xlsx", sheet_name="Webtoons Comic Data")

This doesn't work because "all arrays must be same length" How can I go about doing this? My current plan is to make it line-by-line by adding empty spaces to each column array. Is there a better way to do it? I don't need help populating the arrays, just structuring the DF itself. Thanks for your help.

@Simon How can I do that? My current idea is to have an array like [None, None, None, "Google", "Youtube"], then add each item to each column — Mick
– Mick, Commented Apr 3, 2021 at 1:56
@Mick, create an empty dataframe with header first. then insert row by row. — Simon
– Simon, Commented Apr 3, 2021 at 1:57

Simon · Accepted Answer · 2021-04-03 02:14:55Z

1

if you want to insert yourself, good indentation'd be helpful

import pandas as pd
import numpy as np
df = pd.DataFrame(
    columns=[
        'Name', 'Start Date',   'Title', 'Companies',   'Sub-companies',
        ],
    data = np.array([[
        'Jim',  '01/01/2010',   'CTO',  'Google',       'YouTube',
        ], [
        '',     '',             '',     '',             'FitBit',
        ], [
        '',     '',             '',     'Apple',        'NextVR',
    ]]),
    )

edited Apr 3, 2021 at 2:14

answered Apr 3, 2021 at 2:12

Simon

4311 gold badge6 silver badges13 bronze badges

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

1 Comment

Mick Over a year ago

This solution will do the trick, it's not a oneliner like i was hoping but it gives me the flexibility to change it depending on the clients requirements. Thanks.

BENY · Accepted Answer · 2021-04-03 02:00:09Z

1

As I mentioned in comments , we can do it by try with set the index

df = pd.DataFrame({'Name':["Jim","Jim","Jim","Jim","Tom","Tom","Tom","Tom"],
"Companies": ["Google","Google", "Apple","Apple", "Amazon", "Amazon","Samsung","Samsung"],
    "Sub-companies": [
         "YouTube", "FitBit", "NextVR", "Beats", 
         "Audible", "Zappos", "Biologics", "Cheil"
         ]
})
df.set_index(['Name','Companies'])
               Sub-companies
Name Companies              
Jim  Google          YouTube
     Google           FitBit
     Apple            NextVR
     Apple             Beats
Tom  Amazon          Audible
     Amazon           Zappos
     Samsung       Biologics
     Samsung           Cheil
#df.set_index(['Name','Companies']).to_excel()

answered Apr 3, 2021 at 2:00

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

Mick Over a year ago

This is helpful, but really the appearance of the data in the spreadsheet is important, is there a way make the company only show once?

Collectives™ on Stack Overflow

How to format Pandas DataFrame with different array sizes

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related