1

i had a data frame which i groupby() and aggregate() after the process i got the table i wanted merged. the only problem is that the column i aggregated with is above the others.

i did try reset_index() and it solves the columns problem but then the data frame with the same grouped values isnt merged. how cna i align the columns while keeping the same vlaues merged?

        sensors_location_arrived = self.data.loc[self.data.phone.isin(phones_location)].groupby(
            ['sensor', 'phone']).aggregate({'date': 'last'})

                                   date
sensor       phone
C03629361E50 941250 2019-04-09 02:01:39
C0362F365650 941250 2019-04-07 14:00:32
C03631362350 930576 2019-04-09 10:03:42
             940187 2019-04-09 22:04:20
C03631362650 941250 2019-04-09 10:03:00
C03632363A50 941250 2019-04-07 22:01:56
C03634363F50 941250 2019-04-07 18:00:23
C03821392E4D 941250 2019-04-09 14:00:27
C03829391B4D 941250 2019-04-08 10:00:21

the wanted result: ( reset index cancel the merging (like in line 3) , which i want to keep)

sensor       phone   date
C03629361E50 941250 2019-04-09 02:01:39
C0362F365650 941250 2019-04-07 14:00:32
C03631362350 930576 2019-04-09 10:03:42
             940187 2019-04-09 22:04:20
C03631362650 941250 2019-04-09 10:03:00
C03632363A50 941250 2019-04-07 22:01:56
C03634363F50 941250 2019-04-07 18:00:23
C03821392E4D 941250 2019-04-09 14:00:27
C03829391B4D 941250 2019-04-08 10:00:21
Amit Neuhaus
  • 183
  • 3
  • 14
  • Won't flattening hierarchy [like here](https://stackoverflow.com/questions/14507794/pandas-how-to-flatten-a-hierarchical-index-in-columns) work in your case? – Szymon Maszke Apr 13 '19 at 18:01

1 Answers1

0

It's an unwanted procedure but didn't find any other way.

Dump it into an excel file, then read the excel file again and it will merge columns.

file_name = 'middle.xlsx'
order_X.to_excel(file_name)
order = pd.read_excel
Peter Henry
  • 651
  • 5
  • 17
Shraddha Tarawade
  • 1
  • 1