How to merge columns, value count them and then plot the results?

Question

How do I get from a dataframe with multiple columns that have similar values and need to be merged:

df1 = pd.DataFrame({'firstcolumn':['ab', 'ca', 'da', 'ta','la'],
'secondcolumn':['ab', 'ca', 'ta', 'da', 'sa'], 'index':[2011,2012,2011,2012,2012]})

To a crosstab that tells me for each year how many values were collected?

Index ab ca da ta sa la
2011 2  0  1  1  0  0
2012 0  2  1  1  1  1

Also, how could then plot the table?

Answer 1

It can be done like:

import pandas as pd
melted = pd.melt(df1, id_vars=["index"], var_name="Var", value_name="Score").dropna()
table=pd.crosstab(index=melted['index'], columns=melted['Score'])
%matplotlib inline
table.plot.bar() #for simple axes subplots