Organise and create new dataframe from existing one in Python

Question

I have a list like this.

['9_1_152', '9_2_129', '9_3_22', '9_3_140', '10_3_28', '10_3_134', '10_3_147', '10_5_15', '11_3_18', '11_3_32', '11_3_137', '11_4_150', '12_2_13', '12_3_25', '12_3_151', '12_4_138', '13_4_13', '13_4_27', '13_5_139', '13_5_151', '14_4_16', '14_4_30', '14_4_134', '14_4_146', '15_1_92', '15_2_25', '15_2_122', '15_3_11', '15_4_40', '15_4_73', '15_5_197', '15_6_60', '15_6_103', '15_6_210', '16_1_19', '16_1_34', '16_2_8', '16_2_161', '16_4_51', '16_4_61', '16_4_85', '16_4_109', '16_5_73', '16_7_208', '16_8_213', '17_2_77', '17_4_5', '17_4_44', '17_5_30', '17_5_59', '17_5_97', '17_5_111', '17_5_157', '17_6_177', '17_6_189', '17_9_217', '18_1_22', '18_2_177', '18_2_205', '18_3_163', '18_5_11', '18_5_78', '18_5_107', '18_6_55', '18_6_65', '18_6_89', '18_6_98', '19_1_16', '19_1_68', '19_1_121', '19_1_155', '19_2_181', '19_3_77', '19_3_101', '19_4_37', '19_4_89', '19_5_54', '20_1_22', '20_1_131', '20_1_145', '20_2_172', '20_3_49', '20_6_84', '20_6_159', '20_6_217', '21_2_25', '21_2_139', '21_3_66', '21_4_40', '21_4_191', '21_5_204', '21_6_93', '21_6_108', '22_1_49', '22_1_61', '22_1_134', '22_1_160', '22_1_181', '22_4_1', '22_4_93', '22_5_102', '22_5_211', '22_6_196', '22_6_203', '22_7_12', '22_8_22', '23_3_192', '23_5_92', '23_6_122', '23_6_182', '24_1_87', '24_1_137', '24_2_111', '24_4_76', '24_5_1', '24_6_41', '24_7_12', '24_8_22', '25_1_101', '25_1_137', '25_2_10', '25_2_91', '25_4_165', '25_5_68', '25_6_79', '25_6_113', '25_8_217', '26_2_34', '26_2_66', '26_2_82', '26_2_106', '26_2_117', '26_2_214', '26_4_97', '26_6_172', '26_9_197', '26_10_201', '27_2_34', '27_2_86', '27_4_9', '27_5_49', '27_5_63', '27_5_163', '27_5_190', '27_9_209', '27_10_213', '28_1_205', '28_2_17', '28_2_151', '28_4_58', '28_4_113', '28_4_124', '28_5_169', '28_6_69', '29_1_34', '29_1_81', '29_1_134', '29_1_155', '29_1_173', '29_2_51', '29_6_8', '29_6_21', '30_1_8', '30_1_37', '30_1_126', '30_1_164', '30_2_151', '30_4_65', '30_5_83', '30_5_176', '30_6_50', '31_1_19', '31_1_141', '31_2_58', '31_3_81', '31_5_116', '31_6_45', '32_2_45', '32_2_71', '32_2_97', '32_5_87', '32_5_121', '32_6_21', '32_6_166', '33_1_30', '33_1_55', '33_2_17', '33_2_102', '33_2_166', '33_5_6', '33_5_44', '33_6_117', '34_1_4', '34_1_16', '34_1_43', '34_1_75', '34_1_107', '34_1_116', '34_2_139', '34_5_30', '34_5_183', '35_1_12', '35_3_1', '35_3_39', '35_3_52', '35_3_63', '35_3_73', '35_3_91', '35_3_109', '35_3_118', '35_3_159', '35_3_198', '35_3_210', '35_4_82', '35_4_100', '35_4_131', '35_4_171', '35_4_184', '35_4_222', '35_4_229', '35_5_25', '35_5_145', '37_1_145', '37_1_197', '37_2_132', '37_3_8', '37_3_42', '37_3_56', '37_3_85', '37_3_94', '37_3_112', '37_3_122', '37_3_172', '37_3_186', '37_3_204', '37_3_224', '37_4_103', '37_4_160', '37_4_216', '37_5_25', '37_6_74', '39_1_169', '39_2_157', '39_2_189', '39_3_4', '39_3_15', '39_3_70', '39_3_88', '39_3_97', '39_3_115', '39_3_126', '39_3_179', '39_4_54', '39_4_106', '39_4_142', '39_4_198', '39_4_210', '39_5_39', '42_1_30', '42_1_96', '42_1_141', '42_1_189', '42_2_154', '42_2_197', '42_3_4', '42_3_15', '42_3_46', '42_3_59', '42_3_105', '42_3_166', '42_3_217', '42_4_69', '42_4_79', '42_4_117', '42_4_177', '42_4_204', '42_6_129', '53_3_130', '53_3_143', '53_4_34', '53_4_47', '53_4_156', '53_5_20', '54_4_121', '54_6_13', '54_6_36', '54_6_135', '54_6_147', '55_1_112', '55_2_28', '55_2_143', '55_3_156', '55_5_127', '55_7_3', '55_8_14', '56_3_35', '56_4_20', '56_5_133', '56_6_153', '57_2_21', '57_2_125', '57_2_135', '57_2_147', '57_5_35', '58_2_40', '58_4_23', '58_4_127', '58_4_153', '58_6_141', '166_1_149', '166_2_30', '175_6_17', '175_6_31', '176_6_26', '180_1_26']

I create a dataframe from this list.

         x
0     9_1_152
1     9_2_129
2      9_3_22
3     9_3_140
4     10_3_28
..        ...
310  166_2_30
311  175_6_17
312  175_6_31
313  176_6_26
314  180_1_26

I splitted this dataframe

x[['i','r','p']] = x['x'].str.split('_',expand=True)
x['i'] = pd.to_numeric(x['i'], downcast='integer')
x['r'] = pd.to_numeric(x['r'], downcast='integer')
x['p'] = pd.to_numeric(x['p'], downcast='integer')
print(x)

and obtain this one.

            x    i  r    p
0     9_1_152    9  1  152
1     9_2_129    9  2  129
2      9_3_22    9  3   22
3     9_3_140    9  3  140
4     10_3_28   10  3   28
..        ...  ... ..  ...
310  166_2_30  166  2   30
311  175_6_17  175  6   17
312  175_6_31  175  6   31
313  176_6_26  176  6   26
314  180_1_26  180  1   26

[315 rows x 4 columns]

What i would like to do that, create new dataframe. New elements are column 'i'. New columns are column 'r'. New indexes are column 'p'.

Like this

        1   2     3   4   5  6     
17                           175
22                9 
28                28                
129         9               
152     9

Answer 1

This might be what you're looking for.

x_pivot = x.pivot_table(index="p", columns="r", values="i", aggfunc="sum", fill_value="")