pandas cannot delete columns

Question

I used pd.merge() to combine to two dataframes. So far it worked quite fine, but this time pd creates extra columns and I don't know why. Not big deal so far, but I can not delete unwanted columns and I don't know why

values_columns = ['LST_Day_1km', 'LST_Night_1km']
table = month_mean.pivot(index=['population', 'country'], columns='month', values=values_columns)
table = table.reset_index()

results.dropna(inplace=True)
table = pd.merge(table, results, on=['population', 'country'])
table.reset_index()

results = pd.Dataframe({'country': {0: 'uzbk', 1: 'uzbk', 2: 'uzbk', 3: 'uzbk', 6: 'uzbk'}, 'population': {0: 1, 1: 2, 2: 13, 3: 14, 6: 26}, 'n_plants': {0: 29.0, 1: 41.0, 2: 25.0, 3: 24.0, 6: 30.0}, 'n_dm3': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 6: 7.0}, 'perc_dm3': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 6: 23.33333333}})

pd.DataFrame({'population': {0: 1, 1: 2, 2: 13, 3: 14, 4: 26},
 'country': {0: 'uzbk', 1: 'uzbk', 2: 'uzbk', 3: 'uzbk', 4: 'uzbk'},
 ('population', ''): {0: 1, 1: 2, 2: 13, 3: 14, 4: 26},
 ('country', ''): {0: 'uzbk', 1: 'uzbk', 2: 'uzbk', 3: 'uzbk', 4: 'uzbk'},
 ('LST_Day_1km', 'April'): {0: 21.52598039215687,
  1: 27.82574879227056,
  2: 27.23307692307695,
  3: 28.99916256157639,
  4: 26.263700000000036},
 ('LST_Day_1km', 'August'): {0: 36.79163021868789,
  1: 39.95906666666667,
  2: 38.78652777777779,
  3: 46.96329983249583,
  4: 39.641850594227506},
 ('LST_Day_1km', 'December'): {0: 0.6611801242236314,
  1: 6.980344827586234,
  2: 10.035423728813587,
  3: 11.079760000000029,
  4: 6.721406250000029},
 ('LST_Day_1km', 'February'): {0: -0.5136666666666424,
  1: 9.338888888888919,
  2: 11.175762711864435,
  3: 10.999791666666695,
  4: 7.987187500000024},
 ('LST_Day_1km', 'January'): {0: -2.583491124260326,
  1: 5.6667441860465395,
  2: 6.85056603773588,
  3: 7.350000000000033,
  4: 2.717241379310376},
 ('LST_Day_1km', 'July'): {0: 36.44175337186901,
  1: 42.49046332046341,
  2: 41.024377104377116,
  3: 48.574181523500776,
  4: 40.77033167495858},
 ('LST_Day_1km', 'June'): {0: 31.96304123711347,
  1: 41.723317191283336,
  2: 39.96400000000003,
  3: 45.81320000000002,
  4: 38.27521384928721},
 ('LST_Day_1km', 'March'): {0: 12.131560283687973,
  1: 19.09487179487184,
  2: 20.453040000000026,
  3: 21.504645669291357,
  4: 19.016351351351386},
 ('LST_Day_1km', 'May'): {0: 25.559118773946373,
  1: 36.469663299663324,
  2: 33.800755287009075,
  3: 37.53063400576372,
  4: 31.62000000000006},
 ('LST_Day_1km', 'November'): {0: 10.985531914893652,
  1: 14.045539568345351,
  2: 17.69792270531405,
  3: 19.28898989898993,
  4: 17.179842931937205},
 ('LST_Day_1km', 'October'): {0: 22.78096551724142,
  1: 26.49048109965638,
  2: 27.541752021563386,
  3: 32.219545454545475,
  4: 26.592708333333377},
 ('LST_Day_1km', 'September'): {0: 31.7962770562771,
  1: 34.359823874755435,
  2: 34.65355805243449,
  3: 41.5586535008977,
  4: 34.987818181818206},
 ('LST_Night_1km', 'April'): {0: 7.428039215686304,
  1: 11.624009661835778,
  2: 10.993653846153878,
  3: 12.09098522167491,
  4: 9.67710000000003},
 ('LST_Night_1km', 'August'): {0: 17.047176938369812,
  1: 19.450419047619054,
  2: 19.302986111111167,
  3: 21.32366834170856,
  4: 18.71767402376913},
 ('LST_Night_1km', 'December'): {0: -5.529627329192519,
  1: -1.8968965517241128,
  2: -1.5199999999999723,
  3: -1.1770399999999708,
  4: -3.743749999999973},
 ('LST_Night_1km', 'February'): {0: -8.199999999999973,
  1: -0.4308080808080539,
  2: -0.6925423728813282,
  3: -0.3518749999999719,
  4: -3.641718749999971},
 ('LST_Night_1km', 'January'): {0: -8.32088757396447,
  1: -2.084186046511599,
  2: -3.6739622641509166,
  3: -2.578965517241353,
  4: -6.3384482758620395},
 ('LST_Night_1km', 'July'): {0: 18.013121387283277,
  1: 21.493127413127457,
  2: 21.020235690235747,
  3: 23.224975688816883,
  4: 20.515671641791084},
 ('LST_Night_1km', 'June'): {0: 15.94376288659797,
  1: 20.185447941888633,
  2: 19.214458333333337,
  3: 21.213320000000014,
  4: 18.589877800407365},
 ('LST_Night_1km', 'March'): {0: -0.0034751773049363833,
  1: 5.004102564102594,
  2: 4.989680000000029,
  3: 5.707322834645698,
  4: 2.8841891891892186},
 ('LST_Night_1km', 'May'): {0: 12.303180076628376,
  1: 16.889124579124594,
  2: 15.533564954682795,
  3: 17.093919308357382,
  4: 14.693125000000032},
 ('LST_Night_1km', 'November'): {0: -0.1641489361701835,
  1: 0.9734532374101026,
  2: 2.970289855072489,
  3: 3.347373737373767,
  4: 1.9593193717277761},
 ('LST_Night_1km', 'October'): {0: 6.293034482758647,
  1: 7.753298969072191,
  2: 8.515768194070107,
  3: 9.684141414141447,
  4: 7.5757812500000234},
 ('LST_Night_1km', 'September'): {0: 12.172943722943756,
  1: 13.638454011741713,
  2: 14.51179775280902,
  3: 16.17215439856377,
  4: 13.910836363636404},
 'n_plants': {0: 29.0, 1: 41.0, 2: 25.0, 3: 24.0, 4: 30.0},
 'n_dm3': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 7.0},
 'perc_dm3': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 23.33333333}})

as you can see for population and country a new column was created, is pd.pivot the reason for that?

anyway when I try to delete the columns:

table.drop("('population', '')", axis=1, inplace=True)
# same results for table.drop("(population, )", axis=1, inplace=True)

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\merge.py:648: UserWarning: merging between different levels can give an unintended result (2 levels on the left,1 on the right)
  warnings.warn(msg, UserWarning)
Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 3437, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-6-02927c003eef>", line 37, in <module>
    table.drop("('population', '')", axis=1, inplace=True)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py", line 4308, in drop
    return super().drop(
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\generic.py", line 4153, in drop
    obj = obj._drop_axis(labels, axis, level=level, errors=errors)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\generic.py", line 4188, in _drop_axis
    new_axis = axis.drop(labels, errors=errors)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexes\base.py", line 5591, in drop
    raise KeyError(f"{labels[mask]} not found in axis")
KeyError: '["(\'population\', \'\')"] not found in axis'

Anyone has an Idea why (i) the columns are created in the first place (ii) I cannot drop the columns

Cheers

I think you might just want table = table.reset_index(drop=True) instead of table = table.reset_index(). The new index levels are created by pivot since you've specified the new index to be index=['population', 'country']. They become columns when you call reset_index — Henry Ecker
– Henry Ecker ♦, Commented Jan 13, 2022 at 21:51
@Henry Ecker but then I loose the columns complete, which I don't want since I need them to merge on population and country — Kuehlschrank
– Kuehlschrank, Commented Jan 14, 2022 at 9:21

Marco_CH · Accepted Answer · 2022-01-13 20:57:15Z

2

You can delete it with:

table.drop([["population", ""]], axis=1, inplace=True)

Or with:

table.drop(("population", ""), axis=1, inplace=True)

answered Jan 13, 2022 at 20:57

Marco_CH

3,30413 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kuehlschrank Over a year ago

so the bracket are not actually part of the column name as I see now, why is that?

Collectives™ on Stack Overflow

pandas cannot delete columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related