I'm struggling a bit with a new concept, which I just recently stumbled upon recently. I'm trying to work with a MultiIndex Dataframe. I have two rows of headers in this dataframe, and I really need both headers matching up, just as they do when I import the data from the CSV file.
Here is my MultiIndex DF:
index IDRSSD RCFD3531 \
Unnamed: 0_level_1 TRDG ASSETS-US TREAS SECS IN DOM OFF
0 0 37 0.0
1 1 242 0.0
2 2 279 0.0
3 3 354 0.0
4 4 457 0.0
... ... ...
21380 7080 4232881 0.0
21381 7081 4257594 0.0
21382 7082 4262534 0.0
21383 7083 4262543 0.0
21384 7084 4368351 0.0
RCFD3532 ... \
TRDG ASSETS-US GOV AGC CORP OBLGS ...
0 0.0 ...
1 0.0 ...
2 0.0 ...
3 0.0 ...
4 0.0 ...
... ...
21380 0.0 ...
21381 0.0 ...
21382 0.0 ...
21383 0.0 ...
21384 0.0 ...
file schedule_code year \
0 FFIEC CDR Call Schedule RCD 03312011.txt RCD 03/31/2011
1 FFIEC CDR Call Schedule RCD 03312011.txt RCD 03/31/2011
2 FFIEC CDR Call Schedule RCD 03312011.txt RCD 03/31/2011
3 FFIEC CDR Call Schedule RCD 03312011.txt RCD 03/31/2011
4 FFIEC CDR Call Schedule RCD 03312011.txt RCD 03/31/2011
... ... ...
21380 FFIEC CDR Call Schedule RCD 03312013.txt RCD 03/31/2013
21381 FFIEC CDR Call Schedule RCD 03312013.txt RCD 03/31/2013
21382 FFIEC CDR Call Schedule RCD 03312013.txt RCD 03/31/2013
21383 FFIEC CDR Call Schedule RCD 03312013.txt RCD 03/31/2013
21384 FFIEC CDR Call Schedule RCD 03312013.txt RCD 03/31/2013
qyear
0 2011Q1
1 2011Q1
2 2011Q1
3 2011Q1
4 2011Q1
...
21380 2013Q1
21381 2013Q1
21382 2013Q1
21383 2013Q1
21384 2013Q1
[21385 rows x 145 columns]
The import is done like this; I think this is correct.
df = pd.read_csv(f, delimiter='\t', skiprows=0, header=[0,1])
I think the issue is with the way I am doing the GroupBy and Sum. Here is the code that I am testing.
grouped_and_summed = df_append.stack().reset_index().groupby(['IDRSSD','qyear']).sum()
I want to GroupBy these two fields: 'IDRSSD','qyear' and Sum all the other fields.
When I run the code above and check the grouped_and_summed variable, I see only this.
Empty DataFrame
Columns: [level_0, RCFD3531, RCFD3532, RCFD3533, RCFD3541, RCFD3543, RCFD3545, RCFD3546, RCFD3547, RCFD3548, RCFDF610, RCFDF614, RCFDF615, RCFDF616, RCFDF618, RCFDF624, RCFDF632, RCFDF633, RCFDF634, RCFDF636, RCFDF639, RCFDF640, RCFDF643, RCFDF644, RCFDF645, RCFDF646, RCFDF647, RCFDF648, RCFDF651, RCFDF652, RCFDF653, RCFDF654, RCFDF655, RCFDF656, RCFDF657, RCFDF658, RCFDF659, RCFDF660, RCFDF790, RCFDG299, RCFDG332, RCFDG333, RCFDG334, RCFDG335, RCFDG379, RCFDG380, RCFDG381, RCFDG383, RCFDG384, RCFDG385, RCFDG386, RCFDG387, RCFDG388, RCFDG651, RCFDG652, RCFDK197, RCFDK198, RCFDK199, RCFDK200, RCFDK210, RCFDK211, RCON3531, RCON3532, RCON3533, RCON3541, RCON3543, RCON3545, RCON3546, RCON3547, RCON3548, RCONF604, RCONF605, RCONF606, RCONF607, RCONF611, RCONF612, RCONF613, RCONF614, RCONF615, RCONF616, RCONF618, RCONF624, RCONF625, RCONF626, RCONF627, RCONF628, RCONF629, RCONF630, RCONF631, RCONF632, RCONF633, RCONF634, RCONF636, RCONF639, RCONF640, RCONF643, RCONF644, RCONF645, RCONF646, RCONF647, ...]
Index: []
[0 rows x 141 columns]
I feel like the 'IDRSSD','qyear' are on level 0 and they are getting skipped, but I don't know for sure if that's right. Under IDRSSD I have this: Unnamed: 0_level_1. In the CSV file, this is totally blank. Under qyear I have another blank. Do I need to fill IDRSSD and qyear down, from level 0 to level 1? Or, do I need ot do the GroupBy and Sum based on level 0 and skip level 1? I don't really know how to deal with this setup.
I think I went off on a few tangents before, but I feel like I'm back on track now. I just need to get this last step working, and lock this thing down, once and for all.
Finally, I tried to import both headers, and then strip out level 0 or level 1, and played with that concept a bit, but I didn't make much progress there, as I couldn't easily recombine those two headers after I separated them.