Convert multi column table into two column

May 21, 2023

let us suppose i want to analyze tourism visitors in different regions/cities of Georgia(Country), here is simple for getting data from the following site :
https://www.geostat.ge/en/modules/categories/101/domestic-tourism

import pandas as pd
data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
data.dropna(axis=0,inplace=True)
data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
data["Country"] ="Georgia"
print(data.head())

result is :

       Tbilisi  Adjara A/R     Imereti  ...  Kvemo Kartli  Shida Kartli  Country
0   314.835582  106.598779  239.673489  ...     67.557908     80.719441  Georgia
3   261.135399   89.256953  201.047910  ...     65.957077     62.978426  Georgia
7   250.610817  104.218447  217.913793  ...     69.989150     68.448837  Georgia
11  263.794270  102.331560  197.538537  ...     61.636733     71.306660  Georgia
15  272.579135  123.327306  190.075834  ...     68.220004     64.016689  Georgia

[5 rows x 10 columns]

now instead of having Regions in columns i want to create a two column called as Regions and Second column as Visitors and place them according given table, i know that there is function melt, but how can i use in this? please help me

Edited : Country column should stay

>Solution :

Use the following approach with pd.melt:
(note that Country column is assigned at the end)

data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
print(data)

                   Regions    Visitors  Country
0                  Tbilisi  314.835582  Georgia
1                  Tbilisi  261.135399  Georgia
2                  Tbilisi  250.610817  Georgia
3                  Tbilisi  263.794270  Georgia
4                  Tbilisi  272.579135  Georgia
5                  Tbilisi  298.764963  Georgia
6                  Tbilisi  305.112243  Georgia
7                  Tbilisi  261.974202  Georgia
8                  Tbilisi  331.928109  Georgia
9               Adjara A/R  106.598779  Georgia
10              Adjara A/R   89.256953  Georgia
11              Adjara A/R  104.218447  Georgia
12              Adjara A/R  102.331560  Georgia
13              Adjara A/R  123.327306  Georgia
14              Adjara A/R  129.820220  Georgia
15              Adjara A/R  115.448963  Georgia
16              Adjara A/R  140.962496  Georgia
17              Adjara A/R  122.815398  Georgia
18                 Imereti  239.673489  Georgia
19                 Imereti  201.047910  Georgia
20                 Imereti  217.913793  Georgia
21                 Imereti  197.538537  Georgia
22                 Imereti  190.075834  Georgia
23                 Imereti  213.599758  Georgia
24                 Imereti  178.503183  Georgia
25                 Imereti  210.046339  Georgia
26                 Imereti  211.687675  Georgia
27                 Kakheti   83.976312  Georgia
28                 Kakheti   82.903209  Georgia
29                 Kakheti   72.473283  Georgia
30                 Kakheti   69.910040  Georgia
31                 Kakheti   76.881032  Georgia
32                 Kakheti   76.714403  Georgia
33                 Kakheti   75.422254  Georgia
34                 Kakheti  101.719093  Georgia
35                 Kakheti  126.646269  Georgia
36       Mtskheta-Mtianeti   68.094149  Georgia
37       Mtskheta-Mtianeti   39.898430  Georgia
38       Mtskheta-Mtianeti   61.196603  Georgia
39       Mtskheta-Mtianeti   53.892373  Georgia
40       Mtskheta-Mtianeti   45.605619  Georgia
41       Mtskheta-Mtianeti   76.702671  Georgia
42       Mtskheta-Mtianeti   51.146409  Georgia
43       Mtskheta-Mtianeti  103.417359  Georgia
44       Mtskheta-Mtianeti  130.709472  Georgia
45  Samegrelo-Zemo Svaneti   78.144514  Georgia
46  Samegrelo-Zemo Svaneti   72.868002  Georgia
47  Samegrelo-Zemo Svaneti   73.109213  Georgia
48  Samegrelo-Zemo Svaneti   77.090360  Georgia
49  Samegrelo-Zemo Svaneti   74.340778  Georgia
50  Samegrelo-Zemo Svaneti   79.723074  Georgia
51  Samegrelo-Zemo Svaneti   58.731581  Georgia
52  Samegrelo-Zemo Svaneti   76.525402  Georgia
53  Samegrelo-Zemo Svaneti   88.756872  Georgia
54      Samtskhe-Javakheti   70.469410  Georgia
55      Samtskhe-Javakheti   62.890917  Georgia
56      Samtskhe-Javakheti   42.984470  Georgia
57      Samtskhe-Javakheti   46.061863  Georgia
58      Samtskhe-Javakheti   43.466759  Georgia
59      Samtskhe-Javakheti   54.557168  Georgia
60      Samtskhe-Javakheti   45.954961  Georgia
61      Samtskhe-Javakheti   52.756540  Georgia
62      Samtskhe-Javakheti   55.530282  Georgia
63            Kvemo Kartli   67.557908  Georgia
64            Kvemo Kartli   65.957077  Georgia
65            Kvemo Kartli   69.989150  Georgia
66            Kvemo Kartli   61.636733  Georgia
67            Kvemo Kartli   68.220004  Georgia
68            Kvemo Kartli   75.571344  Georgia
69            Kvemo Kartli   79.618624  Georgia
70            Kvemo Kartli  127.320872  Georgia
71            Kvemo Kartli  122.897431  Georgia
72            Shida Kartli   80.719441  Georgia
73            Shida Kartli   62.978426  Georgia
74            Shida Kartli   68.448837  Georgia
75            Shida Kartli   71.306660  Georgia
76            Shida Kartli   64.016689  Georgia
77            Shida Kartli   66.703733  Georgia
78            Shida Kartli   84.690400  Georgia
79            Shida Kartli  114.779793  Georgia
80            Shida Kartli  137.791063  Georgia