How can I reindex the data in the Dataframe.groupby.size() with pandas?

Question

I was puzzled by the issue just like as below. DataFrame= i have the DataFrame data and using the DataFrame.groupby(["Month","Repair_type","Repair_State"]).size() and the result is just like below graph.

enter image description here

Noted: I wanted to add the data as "Repair_type[just_for_sample]-->Repair_State[End of process]" in January, example as below

enter image description here

Month=['January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'January', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'February', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'March', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'April', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'May', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June', 'June']
Repair_type=['units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'just_for_sample', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'just_for_sample', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'just_for_sample', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'just_for_sample', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'units_return', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_ship', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'pre_unit_apply', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'nor_unit_ship', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit', 'quick_repari_unit']
Repair_State=['obsolete', 'Overhauled', 'To be shipped', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'draft', '445', 'obsolete', 'Received goods', 'To be reviewed', 'To be returned', 'Under maintenance', 'End of process', 'obsolete', 'To be warehoused', 'Goods to be prepared', 'To be reviewed', 'Receiving spare parts', 'End of process', '235', '245', 'Warehoused', 'obsolete', 'Goods to be prepared', 'To be reviewed', 'To be returned', 'Under maintenance', 'End of process', '635', 'obsolete', 'To be returned', 'Warehoused', 'Under maintenance', 'End of process', 'draft', 'obsolete', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'Spare parts to be repaired', 'obsolete', 'Received goods', 'Overhauled', 'To be returned', 'End of process', 'obsolete', 'To be reviewed', 'Receiving spare parts', 'End of process', '235', 'Warehoused', 'obsolete', 'To be returned', 'End of process', '635', 'Warehoused', 'obsolete', 'To be returned', 'End of process', 'Under maintenance', 'obsolete', 'Received goods', 'Overhauled', 'To be reviewed', 'To be returned', 'Goods to be received', 'End of process', 'draft', 'obsolete', 'Received goods', 'To be returned', 'Under maintenance', 'End of process', 'obsolete', 'Goods to be received', 'Receiving spare parts', 'End of process', '235', 'Warehoused', 'obsolete', 'To be returned', 'Under maintenance', 'End of process', 'draft', '635', '645', 'Warehoused', 'obsolete', 'To be returned', 'Under maintenance', 'End of process', 'Warehoused', 'obsolete', 'Received goods', 'Overhauled', 'To be shipped\n', 'To be reviewed', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'obsolete', 'Received goods', 'Overhauled', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'obsolete', 'To be reviewed', 'Goods to be received', 'Receiving spare parts', 'End of process', 'Warehoused', 'obsolete', 'To be warehoused', 'To be reviewed', 'To be returned', 'Under maintenance', 'End of process', 'Warehoused', 'obsolete', 'To be returned', 'Under maintenance', 'End of process', 'Under maintenance', '1010', 'obsolete', 'Overhauled', 'To be reviewed', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'draft', 'obsolete', 'Received goods', 'To be shipped\n', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'obsolete', 'To be overhauled', 'Receiving spare parts', 'End of process', 'Warehoused', 'obsolete', 'To be reviewed', 'To be returned', 'Warehoused', 'Under maintenance', 'End of process', 'Warehoused', 'obsolete', 'To be returned', 'Under maintenance', 'End of process', 'Warehoused', 'obsolete', 'Received goods', 'Overhauled', 'To be shipped\n', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'obsolete', 'Received goods', 'To be returned', 'Goods to be received', 'Under maintenance', 'End of process', 'Prepare spare parts', 'obsolete', 'Goods to be received', 'Receiving spare parts', 'End of process', 'Warehoused', 'obsolete', 'To be returned', 'Under maintenance', 'End of process', 'Warehoused', 'obsolete', 'To be reviewed', 'To be returned', 'Under maintenance', 'End of process', 'draft']
count=[6, 4, 1, 13, 175, 2, 15, 1, 1, 2, 2, 2, 9, 1, 14, 3, 1, 1, 4, 17, 30, 16, 1, 35, 5, 3, 1, 43, 9, 81, 8, 2, 22, 1, 2, 53, 1, 8, 7, 174, 3, 8, 1, 1, 7, 1, 3, 9, 1, 5, 12, 4, 5, 31, 2, 34, 29, 6, 1, 1, 6, 39, 1, 8, 2, 2, 6, 4, 242, 15, 2, 1, 10, 8, 2, 6, 5, 1, 21, 24, 3, 52, 4, 69, 6, 62, 1, 5, 1, 3, 1, 21, 8, 82, 1, 26, 1, 2, 1, 1, 12, 255, 3, 26, 1, 15, 1, 9, 1, 1, 11, 4, 2, 1, 27, 35, 40, 12, 2, 1, 76, 4, 123, 4, 4, 13, 4, 94, 1, 1, 15, 3, 1, 8, 227, 5, 20, 1, 1, 12, 1, 7, 1, 3, 8, 6, 1, 25, 27, 34, 6, 1, 65, 1, 5, 90, 40, 7, 22, 12, 37, 1, 11, 1, 3, 1, 5, 213, 3, 14, 1, 10, 6, 1, 2, 8, 1, 2, 2, 54, 27, 11, 6, 66, 6, 133, 66, 4, 1, 21, 2, 7, 2]
df1 = pd.DataFrame({'Month': Month,
                    'Repair_type': Repair_type,
                    'Repair_State': Repair_State,
                    'count':count})
print(df1.groupby(['Month','Repair_type','Repair_State']).size())

and the result show as below:

      Month Repair_type Repair_State    count
January                    587
    nor_unit_ship          194
        235                16
        245                1
        End of process     81
        Goods to be prepared    3
        obsolete            5
        To be returned      43
        To be reviewed      1
        Under maintenance   9
        Warehoused          35
    pre_unit_apply          56
    pre_unit_ship           31
    quick_repari_unit       89
    units_return            217
February                    399
    just_for_sample         1
        Under maintenance   1
    nor_unit_ship           101
        235 5
        End of process      29
        obsolete            2
        To be returned      34
        Warehoused          31
    pre_unit_apply          22
    pre_unit_ship           22
    quick_repari_unit       53
    units_return            200

I need to add the just_for_sample data Under maintenance in January as February.

Use `DataFrame.groupby(["Month","Repair_type","Repair_State"]).size().reset_index(name='count')` — jezrael, Jan 10 '22 at 07:36
i run it but the result has no add the Repair_type [just_for_sample] element to it. January["nor_unit_ship" "pre_unit_apply" "pre_unit_ship" "quick_repari_unit" "units_return"] — Felix_Fu, Jan 10 '22 at 07:43
Welcome to Stackoverflow. Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on [how to ask a good question](http://stackoverflow.com/help/how-to-ask) may also be useful. — jezrael, Jan 10 '22 at 07:44
[Please don't post images of data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) — jezrael, Jan 10 '22 at 07:45
thinks to your reminding.run the DataFrame.groupby(["Month","Repair_type","Repair_State"]).size().reset_index(name='count') ,but the January still has not the just_for_sample . — Felix_Fu, Jan 10 '22 at 08:22
hmmm, it seems no `just_for_sample` in original dataFrame for `January` — jezrael, Jan 10 '22 at 08:23
yes, so i using the reindex before to add, but only can add the Repair_State the data ,not Repair_type. However,i want to add the series like "{just_for_sample :[Under maintenance:1]"} — Felix_Fu, Jan 10 '22 at 08:31
I think you need [this](https://stackoverflow.com/questions/70621701/how-to-reindex-multiindex-dataframe-on-multiple-levels/70621819#70621819), only change `df.columns` to `df.index` — jezrael, Jan 10 '22 at 08:32
thanks,and i try it to find that is not what i need. I have to change the way to solve, add the Row which including the Month、Repair_type、and the Repair_State to excel row data, and others column value can ignore, should you have some suggestion to do it ? thanks in advanced. — Felix_Fu, Jan 10 '22 at 09:22

How can I reindex the data in the Dataframe.groupby.size() with pandas?

0 Answers0