Book a Demo!
CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutPoliciesSign UpSign In
robertopucp
GitHub Repository: robertopucp/1eco35_2022_2
 Path: blob/main/Trabajo_final/grupo7/PREGUNTA_2_Spyder.py
2714 views
1
#!pip install openpyxl

2
#importar la librería pandas 

3
import pandas as pd

4
import os

5


6
user = os.getlogin()   # Username

7
#se cambia el directorio de trabajo 

8


9
os.chdir(f"C:/Users/{user}/Desktop/trabfinal") 	

10


11
#se exporta los dataframes para los años desde 2014 hasta 2021, para luego editarlos

12


13
#2014

14
    

15
det2014 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2014.xlsx", sheet_name='Hoja1')

16


17
det2014.drop([0],axis=0)

18
det2014=det2014.drop([0],axis=0)

19
print (det2014)

20


21
#renombrar columnas 

22
det2014.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

23


24
det2014.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

25
 

26
#se eliminan las columnas bimestrales, para solo quedarse con las anuales

27
det2014.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

28


29
#se eliminan las columnas de Establecimiento de Salud e Instituciones educativas  

30
det2014.drop([10, 11],axis=0)

31
det2014=det2014.drop([10, 11],axis=0)

32


33
det2014.drop([13],axis=0)

34
det2014=det2014.drop([13],axis=0)

35


36
det2014.drop([1],axis=0)

37
det2014=det2014.drop([1],axis=0)

38


39


40
det2014=det2014.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

41


42
det2014.rename(columns={"INFORMACION BIMESTRAL 2014 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

43


44
det2014.rename(columns={"Anual": "2014"}, inplace=True)

45


46
#este proceso de replica para los siguientes años

47
#2015

48


49
det2015 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2015.xlsx", sheet_name='Hoja1')

50


51
det2015.drop([0],axis=0)

52
det2015=det2015.drop([0],axis=0)

53
print (det2015)

54


55
#renombrar columnas 

56
det2015.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

57


58
det2015.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

59
 

60


61
det2015.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

62
det2015=det2015.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

63


64
det2015.drop([10, 11],axis=0)

65
det2015=det2015.drop([10, 11],axis=0)

66


67
det2015.drop([13],axis=0)

68
det2015=det2015.drop([13],axis=0)

69


70
det2015.drop([1],axis=0)

71
det2015=det2015.drop([1],axis=0)

72


73


74
det2015.rename(columns={"INFORMACION BIMESTRAL 2015 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

75


76


77
det2015.rename(columns={"Anual": "2015"}, inplace=True)

78


79
#2016

80


81
det2016 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2016.xlsx", sheet_name='Hoja1')

82


83
det2016.drop([0],axis=0)

84
det2016=det2016.drop([0],axis=0)

85
print (det2016)

86


87
#renombrar columnas 

88
det2016.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

89


90
det2016.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

91
 

92


93
det2016.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

94
det2016=det2016.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

95


96
det2016.drop([10, 11],axis=0)

97
det2016=det2016.drop([10, 11],axis=0)

98


99
det2016.drop([13],axis=0)

100
det2016=det2016.drop([13],axis=0)

101


102
det2016.drop([1],axis=0)

103
det2016=det2016.drop([1],axis=0)

104


105
det2016.rename(columns={"INFORMACION BIMESTRAL 2016 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

106


107
det2016.rename(columns={"Anual": "2016"}, inplace=True)

108


109
#2017

110


111
det2017 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2017.xlsx", sheet_name='Hoja1')

112


113
det2017.drop([0],axis=0)

114
det2017=det2017.drop([0],axis=0)

115
print (det2017)

116


117
#renombrar columnas 

118
det2017.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

119


120
det2017.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

121
 

122


123
det2017.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

124
det2017=det2017.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

125


126
det2017.drop([10, 11],axis=0)

127
det2017=det2017.drop([10, 11],axis=0)

128


129
det2017.drop([13],axis=0)

130
det2017=det2017.drop([13],axis=0)

131


132
det2017.drop([1],axis=0)

133
det2017=det2017.drop([1],axis=0)

134


135


136
det2017.rename(columns={"INFORMACION BIMESTRAL 2017 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

137


138
det2017.rename(columns={"Anual": "2017"}, inplace=True)

139


140


141
#2018

142


143
det2018 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2018.xlsx", sheet_name='Hoja1')

144


145
det2018.drop([0],axis=0)

146
det2018=det2018.drop([0],axis=0)

147
print (det2018)

148


149
#renombrar columnas 

150
det2018.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

151


152
det2018.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

153
 

154


155
det2018.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

156
det2018=det2018.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

157


158
det2018.drop([10, 11],axis=0)

159
det2018=det2018.drop([10, 11],axis=0)

160


161
det2018.drop([13],axis=0)

162
det2018=det2018.drop([13],axis=0)

163


164
det2018.drop([1],axis=0)

165
det2018=det2018.drop([1],axis=0)

166


167
det2018.rename(columns={"INFORMACION BIMESTRAL 2018 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

168
det2018.rename(columns={"Anual": "2018"}, inplace=True)

169


170


171


172
#2019

173


174
det2019 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2019.xlsx", sheet_name='Hoja1')

175


176
det2019.drop([0],axis=0)

177
det2019=det2019.drop([0],axis=0)

178
print (det2019)

179


180
#renombrar columnas 

181
det2019.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

182


183
det2019.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

184
 

185


186
det2019.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

187
det2019=det2019.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

188


189
det2019.drop([10, 11],axis=0)

190
det2019=det2019.drop([10, 11],axis=0)

191


192
det2019.drop([13],axis=0)

193
det2019=det2019.drop([13],axis=0)

194


195
det2019.drop([1],axis=0)

196
det2019=det2019.drop([1],axis=0)

197


198
det2019.rename(columns={"INFORMACION BIMESTRAL 2019 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

199


200


201
det2019.rename(columns={"Anual": "2019"}, inplace=True)

202


203


204
#2020

205
det2020 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2020.xlsx", sheet_name='Hoja1')

206


207
det2020.drop([0],axis=0)

208
det2020=det2020.drop([0],axis=0)

209
print (det2020)

210


211
#renombrar columnas 

212
det2020.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

213


214
det2020.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

215
 

216


217
det2020.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

218
det2020=det2020.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

219


220
det2020.drop([10, 11],axis=0)

221
det2020=det2020.drop([10, 11],axis=0)

222


223
det2020.drop([13],axis=0)

224
det2020=det2020.drop([13],axis=0)

225


226
det2020.drop([1],axis=0)

227
det2020=det2020.drop([1],axis=0)

228


229


230
det2020.rename(columns={"INFORMACION BIMESTRAL 2020 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

231


232
det2020.rename(columns={"Anual": "2020"}, inplace=True)

233


234
#2021

235


236
det2021 = pd.read_excel(f"C:/Users/{user}/Desktop/trabfinal/VRAEM 2021.xlsx", sheet_name='Hoja1')

237


238
det2021.drop([0],axis=0)

239
det2021=det2021.drop([0],axis=0)

240
print (det2021)

241


242
#renombrar columnas 

243
det2021.rename(columns={"Unnamed: 7": "Anual"}, inplace=True)

244


245
det2021.rename(columns={"Unnamed: 1": "I BIMESTRE", "Unnamed: 2":"II BIMESTRE", "Unnamed: 3": "III BIMESTRE", "Unnamed: 4": "IV BIMESTRE", "Unnamed: 5": "V BIMESTRE", "Unnamed: 6": "VI BIMESTRE"}, inplace=True)

246
 

247


248
det2021.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

249
det2021=det2021.drop(['I BIMESTRE','II BIMESTRE', 'III BIMESTRE','IV BIMESTRE','V BIMESTRE', 'VI BIMESTRE'], axis=1)

250


251
det2021.drop([10, 11],axis=0)

252
det2021=det2021.drop([10, 11],axis=0)

253


254
det2021.drop([13],axis=0)

255
det2021=det2021.drop([13],axis=0)

256


257
det2021.drop([1],axis=0)

258
det2021=det2021.drop([1],axis=0)

259


260
det2021.rename(columns={"INFORMACION BIMESTRAL 2021 - VRAEM: InfoJUNTOS": "Reporte"}, inplace=True)

261


262
det2021.rename(columns={"Anual": "2021"}, inplace=True)

263


264


265


266
#utilizamos merge para poder agrupar los dataframe (de dos en dos)

267
det1514=pd.merge(det2014, det2015, on='Reporte', how='inner')

268
det1614=pd.merge(det1514, det2016, on='Reporte', how='inner')

269
det1714=pd.merge(det1614, det2017, on='Reporte', how='inner')

270
det1814=pd.merge(det1714, det2018, on='Reporte', how='inner')

271
det1914=pd.merge(det1814, det2019, on='Reporte', how='inner')

272
det1420=pd.merge(det1914, det2020, on='Reporte', how='inner')

273
det1421=pd.merge(det1420, det2021, on='Reporte', how='inner')

274


275


276
#finalmente, det1421 representa el dataframe que agrupa los dataframe para cada año desde 2014 hasta 2021 (en términos anuales) 

277
#trasponemos el dataframe 

278


279
dataframefinal=det1421.transpose()

280


281
print(dataframefinal)

282


283
#se obtiene de el dataframefinal 

284


285


286


287


288