CODEWITHSUNDEEP
python-3.xpandasdataframe
guano
guano

Reputation: 159

Dataframe concatenate columns

I have a dataframe with a multiindex (ID, Date, LID) and columns from 0 to N that looks something like this:

                           0  1  2  3  4
 ID       Date      LID
00112   11-02-2014    I    0  1  5  6  7
00112   11-02-2014    II   2  4  5  3  4
00112   30-07-2015    I    5  7  1  1  2
00112   30-07-2015    II   3  2  8  7  1

I would like to group the dataframe by ID and Date and concatenate the columns to the same row such that it looks like this:

                      0  1  2  3  4  5  6  7  8  9
 ID       Date      
00112   11-02-2014    0  1  5  6  7  2  4  5  3  4 
00112   30-07-2015    5  7  1  1  2  3  2  8  7  1

Upvotes: 2

Views: 90

Answers (2)

piRSquared
piRSquared

Reputation: 294586

Using pd.concat and pd.DataFrame.xs

pd.concat(
    [df.xs(x, level=2) for x in df.index.levels[2]],
    axis=1, ignore_index=True
)

                0  1  2  3  4  5  6  7  8  9
ID  Date                                    
112 11-02-2014  0  1  5  6  7  2  4  5  3  4
    30-07-2015  5  7  1  1  2  3  2  8  7  1

Upvotes: 3

jezrael
jezrael

Reputation: 863801

Use unstack + sort_index:

df = df.unstack().sort_index(axis=1, level=1)
#for new columns names
df.columns = np.arange(len(df.columns))
print (df)
                0  1  2  3  4  5  6  7  8  9
ID  Date                                    
112 11-02-2014  0  1  5  6  7  2  4  5  3  4
    30-07-2015  5  7  1  1  2  3  2  8  7  1

Upvotes: 2

Related Questions