CODEWITHSUNDEEP
pythonsqlpython-3.xpandasnumpy
satoshi
satoshi

Reputation: 439

Pandas appending across columns (or SQL)

I have the following dataframe where - and $ is data

A B C 01/01/18 01/08/18
- - -     $        $
- - -     $        $
- - -     $        $

I would like to transform into the following

A B C  Period   Cost
- - -  01/01/18 $
- - -  01/01/18 $
- - -  01/01/18 $
- - -  01/08/18 $
- - -  01/08/18 $
- - -  01/08/18 $

I know I can create two dataframes for each period df1 and df2 then total = df2.append(df1) but I would have to create 52 dataframes for the year. Is there an easier way to do this using some fancy transpose?

Updated Approach

I am trying to do the following df.grouby(['A','B',C']) but I need to df.transpose() first I believe. If someone can help me out with this approach even if its in SQL I would appreciate it.

Upvotes: 0

Views: 65

Answers (1)

akuiper
akuiper

Reputation: 214957

That's what pandas.melt does:

pd.melt(df, id_vars=['A', 'B', 'C'], var_name='Period', value_name='Cost')

#   A  B  C    Period Cost
#0  -  -  -  01/01/18    $
#1  -  -  -  01/01/18    $
#2  -  -  -  01/01/18    $
#3  -  -  -  01/08/18    $
#4  -  -  -  01/08/18    $
#5  -  -  -  01/08/18    $

You can also set unchanged columns as index and then stack the other columns:

ids = ['A', 'B', 'C']
df.set_index(ids).stack().rename_axis(ids + ['Period']).rename('Cost').reset_index()

#   A  B  C    Period Cost
#0  -  -  -  01/01/18    $
#1  -  -  -  01/08/18    $
#2  -  -  -  01/01/18    $
#3  -  -  -  01/08/18    $
#4  -  -  -  01/01/18    $
#5  -  -  -  01/08/18    $

Upvotes: 1

Related Questions