Reconstruct a dataframe from a contingency table in Python [duplicate]

Question

I would like to reconstruct a dataframe from a contingency table stored as dataframe. For example from ctab I would like to build df1 or df2. Is there a command to do that or do I need a loop?

import pandas as pd
ctab = pd.DataFrame([[1,2], [2, 1]], columns=["A", "B"], index=["A", "B"])
print(ctab)
df1 = pd.DataFrame([["A","A", 1], ["A","B", 2], ["B","A", 2], ["B","B", 1]], columns=["col", "index", "freq"])
print(df1)
df2 = pd.DataFrame([["A","A"], ["A","B"], ["A","B"], ["B","A"], ["B","A"], ["B","B"]], columns=["col", "index"])
print(df2)

mozway · Accepted Answer · 2023-04-20 06:41:33Z

2

You can use rename_axis, stack, and reset_index:

out = ctab.rename_axis(index='index', columns='col').stack().reset_index(name='freq')

Output:

  index col  freq
0     A   A     1
1     A   B     2
2     B   A     2
3     B   B     1

For the second one, replicate the rows with Index.repeat:

out = ctab.rename_axis(index='index', columns='col').stack().reset_index(name='freq')

out = out.loc[out.index.repeat(out.pop('freq'))]

Output:

  index col
0     A   A
1     A   B
1     A   B
2     B   A
2     B   A
3     B   B

edited Apr 20, 2023 at 6:41

answered Apr 20, 2023 at 6:35

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reconstruct a dataframe from a contingency table in Python [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related