I have a Pandas data frame which is MultiIndexed. The second level contains a year ([2014,2015]) and the third contains the month number ([1, 2, .., 12]). I would like to merge these two into a single level like - [1/2014, 2/2014 ..., 6/2015]. How could this be done?
I'm new to Pandas. Searched a lot but could not find any similar question/solution.
Edit: I found a way to avoid having to do this altogether with the answer to this question. I should have been creating my data frame that way. This seems to be the way to go for indexing by DateTime.
Consider the pd.MultiIndex and pd.DataFrame, mux and df
mux = pd.MultiIndex.from_product([list('ab'), [2014, 2015], range(1, 3)])
df = pd.DataFrame(dict(A=1), mux)
print(df)
A
a 2014 1 1
2 1
2015 1 1
2 1
b 2014 1 1
2 1
2015 1 1
2 1
We want to reassign to the index a list if lists that represent the index we want.
I want the 1st level the same
df.index.get_level_values(0)
I want the new 2nd level to be a string concatenation of the current 2nd and 3rd levels but reverse the order
df.index.map('{0[2]}/{0[1]}'.format)
df.index = [df.index.get_level_values(0), df.index.map('{0[2]}/{0[1]}'.format)]
print(df)
A
a 1/2014 1
2/2014 1
1/2015 1
2/2015 1
b 1/2014 1
2/2014 1
1/2015 1
2/2015 1
You can use a list comprehension to restructure your index. For example, if you have a 3 levels index and you want to combine the second and the third levels:
lst = [(i, f'{k}/{j}') for i, j, k in df.index]
df.index = pd.MultiIndex.from_tuples(lst)
This is just an explanation to the answer of piRSquared.
df.index.map('{0[2]}/{0[1]}'.format)
the map() method has one argument, which is a callback that is executed on each element of the index. In this example, the method happens to be the python built-in str.format function.
The format function is pretty mighty and has a lot of functionality (see the docs). One of those functions is to refer to positional arguments by specifying their position. This means that
"Hello {1}, I am {0}, how are you?".format("Bob", "Alice")
--> Hello Alice, I am Bob, how are you?
That's where the zero in piRSquared's answer comes from. Normally, it is not required if only one argument is replaced in the string:
"Hello {}".format("Bob")
--> Hello Bob
However, in this case, two additional features are required:
Since the map method will pass a single index entry as argument to the format function, "{0[2]}" refers to the third element of that index.
Now the index in the original questions has three levels, so each argument passed to the format function is a tuple containing the three elements corresponding to the row's index.
A more verbose, but equivalent solution would be:
df.index.map(lambda idx: str(idx[2]) + '/' + str(idx[1]))
or
df.index.map(lambda idx: f'{idx[2]}/{idx[1]}')