0

I am trying to convert values in the headers of the third and fourth columns as rows

df = pd.read_excel("file_name.xlsx", sheet_name = 0, header=[14,15,16,17,18])

df = df.melt(col_level=0, id_vars = ["Unnamed: 0_level_0", "Unnamed: 0_level_1"], var_name='Region', value_name='Value')
print(df.columns)

input file data

Country1 Country1 Country1 Country Country1 Country2
2000 2000 2000 2001 2002 2000
Type1 Type1 Type2 Type1 Type2 Type1
jan feb feb apr jan feb
unit1 unit2 unit2 unit1 unit1 unit1
xyz1 xyzdetails 1 2 3 4 5
xyz2 xyzdetails 2 3 4 5 6
xyz3 xyzdetails 3 4 5 6 7
xyz4 xyzdetails 4 5 6 7 8
xyz5 xyzdetails 5 6 7 8 9
xyz6 xyzdetails 6 7 8 9 10
xyz7 xyzdetails 7 8 9 10 11
xyz8 xyzdetails 8 9 10 11 12

expected output:

country Year Type Month Unit Value
xyz1 xyzdetails Country1 2000 Type1 jan unit1 1
xyz2 xyzdetails Country1 2000 Type1 jan unit1 2
xyz3 xyzdetails Country1 2000 Type1 jan unit1 3
xyz4 xyzdetails Country1 2000 Type1 jan unit1 4
xyz5 xyzdetails Country1 2000 Type1 jan unit1 5
xyz6 xyzdetails Country1 2000 Type1 jan unit1 6
xyz7 xyzdetails Country1 2000 Type1 jan unit1 7
xyz8 xyzdetails Country1 2000 Type1 jan unit1 8
Timus
  • 10,974
  • 5
  • 14
  • 28
D M
  • 1
  • 1
  • if you can, share the excel file, with your expected output dataframe. It is possible that you are not reading in the file properly. Alternatively, you can share a reproducible sample of your dataframe, along with an expected output – sammywemmy Dec 14 '21 at 08:08
  • Unfortunately I am unable to load an excel sample for better clarify . but the file has been read correctly . I have been trying to convert data from 15th to 19th row as a individual column keeping the values present from row 20 as it is. and also I am trying to skip first 14 rows and retain column1 and column2 as it is – D M Dec 14 '21 at 08:41
  • it's very hard to help with the information given. Can you supply sample in and output in proper formatting or rewrite the question indicating your desired behaviour? – julian Dec 14 '21 at 09:28
  • Thanks @Timus for formatting the table structure. – D M Dec 14 '21 at 18:01
  • 1
    Thanks to @sammywemmy. I was able to resolve the issue based on your answer for one of the other question. https://stackoverflow.com/a/59852608/8610293 – D M Dec 14 '21 at 18:01

0 Answers0