Turning Table Data Into Columns And Counting By Frequency

I have a dataframe in the following form: shape is 2326 x 1271 Column names are just serialized from 0-1269 while rows are categories that could repeat like 'apple' in the example

Solution 1:

Use stack along with crosstab to compute the frequency counts:

Data:

index= ['Apple', 'Orange', 'Apple', 'Banana', 'Kiwi']
data = [['AA', 'DD', 'RR', ''], ['DD', 'PP', '', ''], 
        ['AA', 'RR', 'TT', 'SS'], ['EE', 'NN', '',''], ['NN', 'WW','', '']]
frame = pd.DataFrame(data, index, columns=np.arange(4))
frame

Operations:

df = frame.stack().reset_index(0, name='values')
df = pd.crosstab(df['level_0'], df['values']).drop('', axis=1).replace(0, '')
df.index.name=None; df.columns.name=None
df