Pandas: append row to DataFrame with multiindex in columns

Question

I have a DataFrame with a multiindex in the columns and would like to use dictionaries to append new rows.

Let's say that each row in the DataFrame is a city. The columns contains "distance" and "vehicle". And each cell would be the percentage of the population that chooses this vehicle for this distance.

I'm constructing an index like this:

index_tuples=[]

for distance in ["near", "far"]:
    for vehicle in ["bike", "car"]:
        index_tuples.append([distance, vehicle])

index = pd.MultiIndex.from_tuples(index_tuples, names=["distance", "vehicle"])

Then I'm creating a dataframe:

dataframe = pd.DataFrame(index=["city"], columns = index)

The structure of the dataframe looks good. Although pandas has added Nans as default values ?

Now I would like to set up a dictionary for the new city and add it:

my_home_city = {"near":{"bike":1, "car":0},"far":{"bike":0, "car":1}}
dataframe["my_home_city"] = my_home_city

But this fails:

ValueError: Length of values does not match length of index

Here is the complete error message (pastebin)

UPDATE:

Thank you for all the good answers. I'm afraid I've oversimplified the problem in my example. Actually my index is nested with 3 levels (and it could become more).

So I've accepted the universal answer of converting my dictionary into a list of tuples. This might not be as clean as the other approaches but works for any multiindex setup.

I think the keys of the diction need to be tuples, e.g., {('near', 'bike'): 1, ('near', 'car'): 0 ...} — Paul H
– Paul H, Commented Nov 16, 2017 at 20:17

BENY · Accepted Answer · 2017-11-16 20:50:08Z

4

Multi index is a list of tuple , we just need to modify your dict ,then we could directly assign the value

d = {(x,y):my_home_city[x][y] for x in my_home_city for y in my_home_city[x]}
df.loc['my_home_city',:]=d
df
Out[994]: 
distance     near       far     
vehicle      bike  car bike  car
city          NaN  NaN  NaN  NaN
my_home_city    1    0    0    1

More Info

d
Out[995]: 
{('far', 'bike'): 0,
 ('far', 'car'): 1,
 ('near', 'bike'): 1,
 ('near', 'car'): 0}

df.columns.values
Out[996]: array([('near', 'bike'), ('near', 'car'), ('far', 'bike'), ('far', 'car')], dtype=object)

edited Nov 16, 2017 at 20:50

answered Nov 16, 2017 at 20:28

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

Scott Boston · Accepted Answer · 2017-11-16 20:31:56Z

You can append to you dataframe like this:

my_home_city = {"near":{"bike":1, "car":0},"far":{"bike":0, "car":1}}
dataframe.append(pd.DataFrame.from_dict(my_home_city).unstack().rename('my_home_city'))

Output:

distance     near       far     
vehicle      bike  car bike  car
city          NaN  NaN  NaN  NaN
my_home_city    1    0    0    1

The trick is to create the dataframe row with from_dict then unstack to get structure of your original dataframe with multiindex columns then rename to get index and append.

Or if you don't want to create the empty dataframe first you can use this method to create the dataframe with the new data.

pd.DataFrame.from_dict(my_home_city).unstack().rename('my_home_city').to_frame().T

Output:

              far     near    
             bike car bike car
my_home_city    0   1    1   0

Explained:

pd.DataFrame.from_dict(my_home_city)

      far  near
bike    0     1
car     1     0

Now, let's unstack to create multiindex and get to that new dataframe into the structure of the original dataframe.

pd.DataFrame.from_dict(my_home_city).unstack()

far   bike    0
      car     1
near  bike    1
      car     0
dtype: int64

We use rename to give that series a name which becomes the index label of that dataframe row when appended to the original dataframe.

far   bike    0
      car     1
near  bike    1
      car     0
Name: my_home_city, dtype: int64

Now if you converted that series to a frame and transposed it would look very much like a new row, however, there is no need to do this because, Pandas does intrinsic data alignment, so appending this series to the dataframe will auto-align and add the new dataframe record.

dataframe.append(pd.DataFrame.from_dict(my_home_city).unstack().rename('my_home_city'))
distance     near       far     
vehicle      bike  car bike  car
city          NaN  NaN  NaN  NaN
my_home_city    1    0    0    1

cs95 · Accepted Answer · 2017-11-16 20:09:43Z

2

I don't think you even need to initialise an empty dataframe. With your d, I can get your desired output with unstack and a transpose:

pd.DataFrame(d).unstack().to_frame().T

   far     near    
  bike car bike car
0    0   1    1   0

answered Nov 16, 2017 at 20:09

cs95

406k106 gold badges744 silver badges797 bronze badges

Comments

Alexander · Accepted Answer · 2017-11-16 20:31:58Z

1

Initialize your empty dataframe using MultiIndex.from_product.

distances = ['near', 'far']
vehicles = ['bike', 'car']
df = pd.DataFrame([], columns=pd.MultiIndex.from_product([distances, vehicles]), 
                  index=pd.Index([], name='city'))

Your dictionary results in a square matrix (distance by vehicle), so unstack it (which will result in a Series), then convert it into a dataframe row by calling (to_frame) using the relevant city name and transposing the column into a row.

>>> df.append(pd.DataFrame(my_home_city).unstack().to_frame('my_home_city').T)
              far     near    
             bike car bike car
city                          
my_home_city    0   1    1   0

answered Nov 16, 2017 at 20:31

Alexander

111k32 gold badges212 silver badges208 bronze badges

Comments

Arnav Das · Accepted Answer · 2022-05-06 17:55:48Z

Just to add to all of the answers, this is just another(maybe not too different) simple example, represented in a more reproducible way :

import itertools as it
from IPython.display import display # this is just for displaying output purpose
import numpy as np
import pandas as pd

col_1, col_2 = ['A', 'B'], ['C', 'D']
arr_size = len(col_2)
col = pd.MultiIndex.from_product([col_1, col_2])
tmp_df = pd.DataFrame(columns=col)
display(tmp_df)

for s in range(3):# no of rows to add to tmp_df
    tmp_dict = {x : [np.random.random_sample(1)[0] for i in range(arr_size)] for x in range(arr_size)}
    tmp_ser = pd.Series(it.chain.from_iterable([tmp_dict[x] for x in tmp_dict]), index=col)
    #  display(tmp_dict, tmp_ser)
    tmp_df = tmp_df.append(tmp_ser[tmp_df.columns], ignore_index=True)

display(tmp_df)

Some things to note about above:

The number of items to add should always match len(col_1)*len(col_2), that is the product of element lengths your multi-index is made from.
list(it.chain.from_iterable([[2, 3], [4, 5]])) simply does this [2,3,4,5]

Yanni Papadakis · Accepted Answer · 2018-05-18 13:45:26Z

0

try this workaround

append to dict
then convert to pandas data frame
at the very last step select desired columns to create multi-index with set_index()

d = dict()
for g in predictor_types:
    for col in predictor_types[g]:
        tot = len(ames) - ames[col].count()
        if tot:
            d.setdefault('type',[]).append(g)
            d.setdefault('predictor',[]).append(col)
            d.setdefault('missing',[]).append(tot)
pd.DataFrame(d).set_index(['type','predictor']).style.bar(color='DodgerBlue')

answered May 18, 2018 at 13:45

Yanni Papadakis

1551 silver badge4 bronze badges

Collectives™ on Stack Overflow

Pandas: append row to DataFrame with multiindex in columns

6 Answers 6

Comments

Comments

Comments

Comments

Comments

try this workaround

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

try this workaround

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related