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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With