CODEWITHSUNDEEP
pythonpandasdataframemulti-index
doomdaam
doomdaam

Reputation: 783

How to append a multindex to my dataframe in python?

I would like to add a multiindex to my dataframe. I looked into the pandas.pydata [documentation][1] without any luck.

My dataframe looks like this where the Name is the index, and the date include the speed for the vessels:

                     27/02/2020
Name                      
MAERSK EDMONTON       19.1
MAERSK EMDEN           0.0
MAERSK ENPING          0.0
MAERSK ESSEN           0.0
MAGLEBY MAERSK         0.1
METTE MAERSK          17.7
MSC AMBITION           0.0
MSC ARIANE            17.4
MSC CAMILLE            0.0

The desired output would look like this. The vessels belong to a given alliance, in this case 2M Alliance. 

2M_Alliance
                     27/02/2020
Name                      
MAERSK EDMONTON       19.1
MAERSK EMDEN           0.0
MAERSK ENPING          0.0
MAERSK ESSEN           0.0
MAGLEBY MAERSK         0.1
METTE MAERSK          17.7
MSC AMBITION           0.0
MSC ARIANE            17.4
MSC CAMILLE            0.0

Here's what I've got so far.

df_alli = df[df.Name.isin(["MAERSK EMDEN", "METTE MAERSK", "MAGLEBY MAERSK", "MSC ARIANE", "MAERSK EDMONTON", "MAERSK ENPING", "MSC AMBITION", "MSC CAMILLE", "MAERSK ESSEN"])]
df_name = pd.DataFrame()

df_name["Name"] = df_alli["Name"]
df_name[date] = df_alli[date]

#df_name = df_name.set_index("Name")

arrys = [["2M_Alliance"], df_name["Name"]]
s = pd.MultiIndex.from_product(arrys, names=["Alliance", "Name"])
s = pd.Series(df_name[date], index=s)
s = pd.DataFrame(s)
print(s)

The output looks fine, but the NaN are not filled out, and I'm not sure why. Outputs:

                            27/02/2020
Alliance    Name                      
2M_Alliance MAERSK EDMONTON        NaN
            MAERSK EMDEN           NaN
            MAERSK ENPING          NaN
            MAERSK ESSEN           NaN
            MAGLEBY MAERSK         NaN
            METTE MAERSK           NaN
            MSC AMBITION           NaN
            MSC ARIANE             NaN
            MSC CAMILLE            NaN

Upvotes: 1

Views: 32

Answers (1)

jezrael
jezrael

Reputation: 862771

Create new column by DataFrame.assign and then use DataFrame.set_index:

df_name = df_alli.assign(Alliance = '2M_Alliance').set_index(['Alliance','Name'])
print (df_name)
                             27/02/2020
Alliance    Name                       
2M_Alliance MAERSK EDMONTON        19.1
            MAERSK EMDEN            0.0
            MAERSK ENPING           0.0
            MAERSK ESSEN            0.0
            MAGLEBY MAERSK          0.1
            METTE MAERSK           17.7
            MSC AMBITION            0.0
            MSC ARIANE             17.4
            MSC CAMILLE             0.0

Upvotes: 1

Related Questions