Reindex a dataFrame on the missing columns of a second

Question

Reindex a dataFrame on the missing columns of a second

Navigation

#1 by (2 votes)

1

I have two dataFrame ratings_full and side_eclipse that contain subscriber_id,eclipse_id by ratings and eclipse_id,hashtag_id by side_eclipse. Have, on the one hand, an index, and on the other hand columns of different sizes, certain of their indexes are the same as the columns but certain are not. How can I reindex the columns of the first one over the indexes of the second one missing and fill in with 0 the new columns?

It is to say how to do:

df1 : (equivalent to your side_eclipse )

         Value1    Value2
Index
pub1            1          2                       
pub2           11         15
pub3            2        145
pub4           45         24

df2 : (equivalent to ratings_full )

           pub1     pub4     pub6     pub7
Index
Jorge          1       5        0    42             
Clara         28      14        4     1
Anton         23       4       14    12
Tirob         41      31       47     4

Exit:

              pub1       pub2    pub3     pub4    pub6   pub7
Index
Jorge            1          0       0        5       0     42     
Clara           28          0       0       14       4      1
Anton           23          0       0        4      14     12
Tirob           41          0       0       31      47     14

I try to use a .union but it did not work:

idx1 = pd.Index(ratings_full.columns.values)
idx2 = pd.Index(side_eclipse.index.values)
idx2.union(idx1)
print idx2
print idx2

Then I use, with the help of the answer of FJSevilla:

side_eclipse = side_eclipse.join(ratings_full, how ='outer')
side_eclipse.fillna(0, inplace=True)

But he tells me that there is a ValueError :

columns overlap but no suffix specified: Int64Index([    0, 10453, 11243, 11265, 11266, 11267, 11268, 11269, 11270,
            11271, 11275, 11276, 11277, 11278, 11279, 11281, 11282, 11284,
            11285, 11286, 11287, 11288, 11289, 11290, 11291, 11292, 11294,
            11295, 11296, 11297, 11301, 11304, 11305, 11306, 11307, 11308,
            11309, 11310, 11311, 11312, 11313, 11314, 11315, 11316],
           dtype='int64')

Data

Here are the data, you can see that they are missing in the second 11309 for example:

ratings_full.columns.values : 
[    0 10453 11243 11265 11266 11267 11268 11269 11270 11271 11275 11276
 11277 11278 11279 11281 11282 11284 11285 11286 11287 11288 11289 11290
 11291 11292 11294 11295 11296 11297 11301 11304 11305 11306 11307 11308
 11309 11310 11311]
side_eclipse.index.values : 
[ 6521  6527  6530  6532  6547  6553  6557  6559  6577  6580  6583  6588
  6591  6606  6609  6612  6617  6620  6635  6638  6641  6649  6664  6667
  6670  6675  6679  6694  6697  6701  6706  6709  6724  6727  6730  6735
  6738  6753  6756  6758  6761  6766  6769  6784  6787  6790  6798  6813
  6816  6819  6824  6827  6842  6845  6848  6853  6856  6871  6874  6877
  6882  6885  6900  6903  6906  6911  6914  6929  6932  6935  6940  6943
  6958  6961  6964  6969  6972  6975  6990  6993  6996  7001  7004  7019
  7022  7025  7030  7033  7048  7051  7054  7059  7062  7077  7080  7083
  7089  7092  7107  7110  7115  7120  7952  8075  8820  9353  9360  9374
  9379  9389  9397  9419  9484  9534  9556  9559  9561  9565  9572  9576
  9598  9602  9606  9616  9621  9639  9646  9651  9657  9662  9680  9687
  9692  9698  9703  9721  9728  9733  9739  9744  9762  9774  9780  9785
  9803  9810  9815  9816  9817  9818  9819  9822  9823  9825  9836  9854
  9861  9866  9871  9875  9887  9890  9893  9896  9897  9900  9906  9910
  9922  9927  9935  9939  9951  9956  9960  9964  9968  9980  9985  9989
  9993  9997 10009 10013 10014 10016 10019 10021 10025 10041 10047 10054
 10058 10070 10075 10079 10080 10084 10088 10100 10101 10103 10104 10108
 10112 10113 10114 10116 10117 10118 10119 10121 10122 10123 10129 10141
 10146 10150 10170 10175 10179 10183 10187 10199 10204 10207 10209 10213
 10216 10217 10221 10233 10236 10237 10238 10240 10242 10246 10250 10266
 10279 10283 10295 10304 10310 10314 10331 10335 10343 10355 10360 10368
 10389 10393 10397 10418 10430 10442 10450 10452 10454 10458 10462 10474
 10479 10483 10485 10486 10487 10490 10494 10506 10511 10515 10519 10523
 10535 10540 10548 10552 10569 10573 10577 10581 10593 10606 10610 10631
 10632 10633 10634 10638 10642 10654 10659 10663 10664 10671 10672 10673
 10674 10675 10681 10686 10687 10688 10690 10694 10711 10735 10736 10738
 10739 10740 10741 10742 10743 10744 10746 10747 10748 10749 10751 10752
 10753 10754 10755 10756 10757 10758 10759 10760 10761 10762 10763 10764
 10765 10767 10798 10799 10800 10802 10803 10804 10805 10806 10807 10808
 10809 10810 10811 10812 10813 10814 10815 10816 10817 10818 10819 10820
 10821 10822 10823 10824 10825 10826 10827 10828 10829 10830 10831 10832
 10833 10834 10835 10836 10837 10838 10839 10840 10841 10842 10843 10844
 10845 10846 10847 10848 10849 10850 10851 10852 10853 10854 10855 10856
 10857 10858 10859 10860 10861 10862 10863 10864 10865 10866 10867 10868
 10869 10870 10871 10872 10873 10874 10875 10876 10877 10878 10879 10880
 10881 10882 10883 10884 10885 10886 10887 10888 10889 10890 10891 10892
 10893 10894 10895 10896 10897 10898 10899 10900 10901 10902 10903 10904
 10905 10906 10907 10908 10909 10910 10911 10912 10913 10914 10915 10916
 10917 10918 10919 10920 10921 10922 10923 10924 10925 10926 10927 10928
 10929 10930 10931 10959 10960 10961 10962 11048 11050 11051 11052 11053
 11054 11055 11056 11057 11058 11059 11060 11061 11062 11063 11064 11065
 11066 11067 11068 11069 11070 11071 11072 11073 11074 11075 11076 11077
 11078 11079 11080 11081 11082 11083 11084 11085 11086 11110 11113 11114
 11116 11117 11119 11120 11123 11124 11125 11126 11160 11161 11162 11163
 11164 11165 11166 11167 11168 11169 11170 11171 11172 11173 11174 11175
 11176 11177 11178 11179 11180 11181 11182 11183 11184 11185 11186 11187
 11188 11189 11190 11191 11192 11193 11194 11195 11196 11197 11198 11199
 11200 11201 11203 11204 11205 11206 11207 11208 11209 11210 11211 11212
 11213 11214 11215 11216 11217 11218 11219 11220 11221 11222 11223 11224
 11225 11226 11227 11228 11229 11230 11231 11232 11233 11234 11235 11236
 11237 11238 11239 11240 11241 11242 11243 11244 11245 11246 11247 11248
 11249 11250 11251 11260 11261 11263 11281 11282 11283 11293 11297 11306
 11307 11308 11310 11311]

python

asked by ThePassenger 21.06.2017 в 12:09

source

1 answer

Is it possible to initialize an Activity from a Fragment in Android? How to execute an sql file from Visual Basic script

score 2 · Accepted Answer

I think I have understood the problem, let's see it with an example:

import pandas as pd

alumnos = pd.DataFrame({'Alumno':['Juan', 'Laura', 'Luis', 'Pedro',  'Ana'],
                        'Curso':[1, 1, 2, 3, 1],
                        'Grupo':['A',  'B',  'A',  'A',  'C']})
alumnos.set_index('Alumno',  inplace=True)

notas = pd.DataFrame({'Alumno':['Juan', 'Luis',  'Ana'],
                        'Parcial_1':[7.5,  8, 9.5],
                        'Parcial_2':[8, 7, 8.5]})
notas.set_index('Alumno',  inplace=True)

Now we have two dataframes of the form:

----------------------------------------
ALUMNOS
----------------------------------------
        Curso Grupo
Alumno             
Juan        1     A
Laura       1     B
Luis        2     A
Pedro       3     A
Ana         1     C

----------------------------------------
NOTAS
----------------------------------------
        Parcial_1  Parcial_2
Alumno                      
Juan          7.5        8.0
Luis          8.0        7.0
Ana           9.5        8.5

Now we want to join the dataframe notes with students. For we can use:

pandas.DataFrame.join:
```
res = alumnos.join(notas, how='outer')
```

pandas.DataFrame.merje:

 res = alumnos.merge(notas, how='outer', left_index=True, right_index=True)

Exit:

         Curso Grupo  Parcial_1  Parcial_2
Alumno                                   
Ana         1     C        9.5        8.5
Juan        1     A        7.5        8.0
Laura       1     B        NaN        NaN
Luis        2     A        8.0        7.0
Pedro       3     A        NaN        NaN

If you want the NaN to be 0, just use DataFrame.fillna :

>>> res.fillna(0.0,  inplace = True)
>>> res

         Curso Grupo  Parcial_1  Parcial_2
Alumno                                   
Ana         1     C        9.5        8.5
Juan        1     A        7.5        8.0
Laura       1     B        0.0        0.0
Luis        2     A        8.0        7.0
Pedro       3     A        0.0        0.0

Edit:

In view of the new edition it seems that all you want is to add as new empty columns to your df2 those indexes of df1 that are not between the columns of df2 . Since it seems that you do not modify data or mix data between the dataframes in any way (you only add empty columns), what you can do is obtain the indexes that are not between the columns of df2 by joining sets | applied on df2.columns.values and df1.index.values .

For this, using reindex is the simplest thing. This changes the order of your columns, but it can be reordered if you need it.

Emulating your example:

import pandas as pd

df1 = pd.DataFrame({'Index':['pub1', 'pub2', 'pub3', 'pub4'],
                        'Value1':[1, 11, 2, 45],
                        'Value2':[2, 15, 145, 24]})
df1.set_index('Index',  inplace=True)

df2 = pd.DataFrame({'Index':['Jorge', 'Clara', 'Anton', 'Tirob'],
                    'pub1':[1,28,23,41],
                    'pub4':[5,14,4,31],
                    'pub6':[0,4,14,47],
                    'pub7':[42,1,12,4]})
df2.set_index('Index',  inplace=True)


new = df2.reindex(columns = set(df1.index.values) | set(df2.columns.values))
new.fillna(0.0, inplace = True)

print('='*41)
print(df1)
print('='*41)
print(df2)
print('='*41)
print(new)
print('='*41)

Exit:

=========================================
       Value1  Value2
Index                
pub1        1       2
pub2       11      15
pub3        2     145
pub4       45      24
=========================================
       pub1  pub4  pub6  pub7
Index                        
Jorge     1     5     0    42
Clara    28    14     4     1
Anton    23     4    14    12
Tirob    41    31    47     4
=========================================
       pub7  pub1  pub2  pub6  pub3  pub4
Index                                    
Jorge    42     1   0.0     0   0.0     5
Clara     1    28   0.0     4   0.0    14
Anton    12    23   0.0    14   0.0     4
Tirob     4    41   0.0    47   0.0    31
=========================================