Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to display a dataframe, with columns showing twice in a row

Tags:

python

pandas

For example:

df_test=pd.DataFrame([('tokyo',123),('london',11),('sydney',22),('taiwan',33),('hongkong',23),('la', 32)], columns=['city','count'])

       city  count
0     tokyo    123
1    london     11
2    sydney     22
3    taiwan     33
4  hongkong     23
5        la     32

I want it look like this, as the rows are too much than columns, making it easy to read.

       city  count    city     count
0     tokyo    123    taiwan     33
1    london     11    hongkong   23
2    sydney     22    la         32

like image 728
DennisLi Avatar asked Jan 24 '23 14:01

DennisLi


1 Answers

You can use modulo and integer division by index values and reshape by DataFrame.unstack, last sorting second level and for avoid duplicated columns names flatten MultiIndex in columns:

df = (df_test.set_index([df_test.index % 2, df_test.index // 2])
             .unstack()
             .sort_index(axis=1, level=1, sort_remaining=False))
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
print (df)
    city0  count0   city1  count1
0   tokyo     123  sydney      22
1  london      11  taiwan      33

If possible not default RangeIndex is possible use:

df_test=pd.DataFrame([('tokyo',123),('london',11),('sydney',22),('taiwan',33)], 
                     columns=['city','count'], 
                     index=list('abcd'))
print (df_test)
     city  count
a   tokyo    123
b  london     11
c  sydney     22
d  taiwan     33

arr = np.arange(len(df_test))
df = (df_test.set_index([arr % 2, arr // 2])
             .unstack()
             .sort_index(axis=1, level=1, sort_remaining=False))
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
print (df)
    city0  count0   city1  count1
0   tokyo     123  sydney      22
1  london      11  taiwan      33

EDIT: Solution for always 4 columns for general data with count N by length of rows:

N = len(df_test)  % 2 + len(df_test)  // 2

arr = np.arange(len(df_test))
df = (df_test.set_index([arr % N, arr // N])
             .unstack()
             .sort_index(axis=1, level=1, sort_remaining=False))
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
print (df)
    city0  count0     city1  count1
0   tokyo     123    taiwan      33
1  london      11  hongkong      23
2  sydney      22        la      32
like image 188
jezrael Avatar answered Jan 27 '23 02:01

jezrael