Pandas: transpose dataframe

Question

I have df:

ID  url visits  count_sec   buys
0012ea90a6deb4eeb2924fb13e844136    aliexpress.com  3438    33067   25
0012ea90a6deb4eeb2924fb13e844136    ebay.com    9   44  
001342afb153e2775649dc5ae0460605    ozon.ru 1   6   
0019b08bc9bb8da21f3b8ecc945a67d3    aliexpress.com  24  2196    
0019b08bc9bb8da21f3b8ecc945a67d3    bonprix.ru  42  1378

I need to transponse it and get

ID                                         url
                                    visits  count_sec   buys
                                  aliexpress.com         ebay.com            ozon.ru     aliexpress.com      bonprix.ru
0012ea90a6deb4eeb2924fb13e844136   3438 33067 25       9    44    0        0    0    0        0   0   0          0   0   0
001342afb153e2775649dc5ae0460605    0   0   0           0   0   0          1    6    0        0   0   0          0   0   0
0019b08bc9bb8da21f3b8ecc945a67d3    0   0   0           0   0   0          0   0    0       24  2196  0         42  1378   0

How can I do that?

Do you need top url in Multiindex in columns?

jezrael
– jezrael

2016-07-25 12:54:59 +00:00
Commented Jul 25, 2016 at 12:54 — jezrael
– jezrael, Commented Jul 25, 2016 at 12:54

jezrael · Accepted Answer · 2016-07-25 14:36:22Z

3

You can use pivot_table, but there is missing top url in MultiIndex in columns. Also values are only random, because it is not clear which each column contain values:

#new column with value `url`
df['url1'] = 'url'
df1 = df.pivot_table(index='ID', values=['visits','count_sec','buys'],columns=['url1','url'])
#swap first and second level in MultiIndex in columns
df1.columns = df1.columns.swaplevel(0,1)
#remove columns names
df1 = df1.rename_axis((None,None,None), axis=1)
print (df1)
                                            url                              \
                                         visits                               
                                 aliexpress.com bonprix.ru ebay.com ozon.ru   
ID                                                                            
0012ea90a6deb4eeb2924fb13e844136         3438.0        NaN      NaN     NaN   
001342afb153e2775649dc5ae0460605            NaN        NaN      NaN     NaN   
0019b08bc9bb8da21f3b8ecc945a67d3            NaN        NaN      NaN     NaN   

                                                                             \
                                      count_sec                               
                                 aliexpress.com bonprix.ru ebay.com ozon.ru   
ID                                                                            
0012ea90a6deb4eeb2924fb13e844136        33067.0        NaN      9.0     NaN   
001342afb153e2775649dc5ae0460605            NaN        NaN      NaN     1.0   
0019b08bc9bb8da21f3b8ecc945a67d3           24.0       42.0      NaN     NaN   


                                           buys                              
                                 aliexpress.com bonprix.ru ebay.com ozon.ru  
ID                                                                           
0012ea90a6deb4eeb2924fb13e844136           25.0        NaN     44.0     NaN  
001342afb153e2775649dc5ae0460605            NaN        NaN      NaN     6.0  
0019b08bc9bb8da21f3b8ecc945a67d3         2196.0     1378.0      NaN     NaN

edited Jul 25, 2016 at 14:36

answered Jul 25, 2016 at 12:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

NineWasps Over a year ago

Is it real to do url at the top and visits, count_sec, buys under that?

NineWasps Over a year ago

And I asked question about merge df. you asked me result = pd.merge(short, short1, on='ID', how='outer') . but how can I merge pivot table?

jezrael Over a year ago

If need merge with another dataframe by print (pd.merge(df1.reset_index(), short, on='ID', how='outer')), you need remove Multiindex, because warning:

C:\Anaconda3\lib\site-packages\pandas\tools\merge.py:205: UserWarning: merging between different levels can give an unintended result (3 levels on the left, 1 on the right)   warnings.warn(msg, UserWarning)

Collectives™ on Stack Overflow

Pandas: transpose dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related