How to concatanate 35 columns at a time in pandas within a single dataframe?

user_11586653 0
Viewed 65

Need to solve this using pandas if possible:

I have a large data frame (1000 columns and 100 rows) with each column containing either a 1 or 0.

I want to combine the values in the first 5 columns into a single column, then combine the following 5 values into a second column etc... until each column contains 5 values.

This needs to be done for each row also.

I have also tried solving this without using pandas, the output results in a string and contains \n at the end of each row. 

z = pd.read_csv('number_file')
length = len(z) // 5 * 5
for i in range(0, length,5):
    (a[i:i + 5])
x = number_file[length:]

Input file (the numbers along the top refer to the columns)

1 2 3 4 5 6 7 8 9 10...
0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1...
0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1...
0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0...
0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0...

Desired Output

 col1  col2 col3   col4  col5  col6
00000 10000 00010 00000 01000 00001
00000 10000 00010 00000 01000 00001
00010 00000 00000 00100 00000 00000
00100 00000 10000 00010 00000 00000
pandas python
3 Answers

groupby on axis=1

a = np.arange(df.shape[1]) // 5 + 1
df.astype(str).groupby(a, axis=1).agg(''.join).add_prefix('col')

    col1   col2   col3   col4   col5   col6
0  00000  10000  00010  00000  01000  00001
1  00000  10000  00010  00000  01000  00001
2  00010  00000  00000  00100  00000  00000
3  00100  00000  10000  00010  00000  00000
user_23366540

Just to add some documentation to @piRSquared's good answer:

import pandas as pd
import numpy as np

# Reconstructing your dataframe with copy/pasta:

row1 = '0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1'.split(' ')
row2 = '0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1'.split(' ')
row3 = '0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0'.split(' ')
row4 = '0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0'.split(' ')

df = pd.DataFrame(pd.Series(row) for row in [row1,row2,row3,row4])

# The idea is to create a categorical label "mask" with group labels (1 thru 6) to
# pass into the pandas groupby function next:

group_labels = np.arange(df.shape[1]) // 5 + 1  # Floor division, not a JS comment!
# We floor divide into 5 categories, but then add one to the whole array to be 1-indexed.

# So now group_labels is a list of integers, a sort of column-mask:
# [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6]

# Now "group_labels" can guide pandas in grouping on columns (axis=1).
# agg(''.join) takes the grouped df and performs a string concat with no spaces.
# add_prefix('col') is fairly self-explanatory.
answer = df.groupby(group_labels, axis=1).agg(''.join).add_prefix('col')

# answer.values == 
#  [['00000', '10000', '00010', '00000', '01000', '00001'],
#   ['00000', '10000', '00010', '00000', '01000', '00001'],
#   ['00010', '00000', '00000', '00100', '00000', '00000'],
#   ['00100', '00000', '10000', '00010', '00000', '00000']]
user_104495520

I want to try on numpy solution:

a = df.astype(str).values.reshape(df.shape[0],-1,5)
data = np.chararray.sum(a, axis=-1)

Out[1827]:
array([['00000', '10000', '00010', '00000', '01000', '00001'],
       ['00000', '10000', '00010', '00000', '01000', '00001'],
       ['00010', '00000', '00000', '00100', '00000', '00000'],
       ['00100', '00000', '10000', '00010', '00000', '00000']],
      dtype=object)


cols = ['col'+ str(i) for i in range(1, data.shape[1]+1)]    
pd.DataFrame(data, columns=cols)

Out[1838]:
    col1   col2   col3   col4   col5   col6
0  00000  10000  00010  00000  01000  00001
1  00000  10000  00010  00000  01000  00001
2  00010  00000  00000  00100  00000  00000
3  00100  00000  10000  00010  00000  00000
user_101892140
Related