1

I have a dataframe which has flags 0/1 for multiple products along with accounts and which zipcode they belong to. My Goal is to count the 1's in columns which have been created as flags.

    Zip     acc     A   B
    32123   214124  1   0
    32123   124124  0   0
    32123   124124  1   1
    32123   124124  1   1
    12333   112424  1   1
    12333   123131  1   0
    12333   214135  1   0
    12333   123145  1   0

My expected output is in the following format

Zip     Pro #acc
32123   A   3
        B   2
12333   A   4
        B   1

What might be the best way to get to this? I have tried using pd.crosstab/groupby functions but max got to this

g.groupby(['ZIP','A','B']).agg({'ACC':'count'})
c.set_index(['ZIP','A','B'])

Zip     A   B   acc
32123   0   0   1
12333   0   0   2
m2rik
  • 135
  • 7
  • 1
    Can you please post your data in text format? This way your question is more inclusive and is easier for everyone to try different things. – aaossa Feb 23 '22 at 20:33
  • you may start with `df.melt(id_vars=['Zip', 'acc'], var_name='Pro')` – Raymond Kwok Feb 24 '22 at 03:45

1 Answers1

1

First, you can .groupby "Zip" and sum those values to get the number you want:

>>> df = df.groupby("Zip").sum()
          acc  A  B
Zip                
12333  572835  4  1
32123  586496  3  2

Then, pd.melt the data by using "Zip" as id and extracting the values from both "A" and "B" (now the sum from your previous step) to place in your new dataframe:

>>> df = df.reset_index().melt(id_vars=["Zip"], value_vars=["A", "B"], var_name="Pro", value_name="#acc")
     Zip Pro  #acc
0  12333   A     4
1  32123   A     3
2  12333   B     1
3  32123   B     2

You can also use both "Zip" and "Pro" as index columns if you want:

>>> df = df.set_index(["Zip", "Pro"])
           #acc
Zip   Pro      
12333 A       4
32123 A       3
12333 B       1
32123 B       2
aaossa
  • 3,763
  • 2
  • 21
  • 34
  • 1
    Excellent! I did try the pd.melt but was confused with the results. This helps a lot! Kudos. – m2rik Feb 25 '22 at 20:00