CODEWITHSUNDEEP
pythonpandasdictionarymerge
Bigboss01
Bigboss01

Reputation: 618

Make dictionary keys into rows and dict values as columns with one value as column name and one as column value

I have the following data:

print(tables['T10101'].keys())

dict_keys(['Y033RL', 'A007RL', 'A253RL', 'A646RL', 'A829RL', 'A008RL', 'A191RP', 'DGDSRL', 'A822RL', 'A824RL', 'A006RL', 'A825RL', 'A656RL', 'A823RL', 'Y001RL', 'DNDGRL', 'DDURRL', 'A021RL', 'A009RL', 'A020RL', 'DSERRL', 'A011RL', 'DPCERL', 'A255RL', 'A191RL'])

Each key has the following value, a list of tuples: The dates are the same but the values change.

[('-20.7', '1930'), ('-33.3', '1931'), ('-41.4', '1932'), ('2.5', '1933'), ('38.3', '1934'), ('36.3', '1935'), ('37.2', '1936'), ('16.2', '1937'), ('-30.4', '1938'), ('15.4', '1939'), ('29.6', '1940'), ('17.2', '1941'), ('-42.6', '1942'), ('-10.2', '1943'), ('33.7', '1944'), ('43.2', '1945'), ('24.7', '1946'), ('36.0', '1947'), ('-8.3', '1947Q2'),

I would like to create the following dataframe:

       1930 | 1931 | 1932 | 1933 | 1934 |
Y033RL|-20.7| -33.3| -41.4| 2.5  | 38.3 |
A007RL| data| data | data | data | data |

What's the best way to do this? I came up with this roundabout way of joining created dataframes but it is very inefficient as I have lots of data. I would like to have everything in a dictionary first and then convert it to one dataframe.

def dframeCreator(dataname):
    dframeList = list(tables[dataname].keys())
    df = tables[dataname][dframeList[0]]
    for x in range(len(dframeList[1:])):
        df = df.join(tables[dataname][dframeList[x+1]])
    return df

Upvotes: 1

Views: 1052

Answers (2)

Shubham Sharma
Shubham Sharma

Reputation: 71689

We can use dict comprehension to normalize the given dictionary in a standard format which is suitable for creating a dataframe

d  = tables['T10101']
df = pd.DataFrame({k: dict(map(reversed, v)) for k, v in d.items()}).T

print(df)

         1930   1931   1932 1933 ... 1945  1946  1947 1947Q2
Y033RL  -20.7  -33.3  -41.4  2.5 ... 43.2  24.7  36.0   -8.3

Upvotes: 4

Andrej Kesely
Andrej Kesely

Reputation: 195418

If tables is your dictionary, you can do:

df = pd.DataFrame(
    [{"idx": k, **dict([(b, a) for a, b in v])} for k, v in tables.items()],
).set_index("idx")
df.index.name = None
print(df)

Prints:

         1930   1931   1932 1933  1934  1935  1936  1937   1938  1939  1940  1941   1942   1943  1944  1945  1946  1947 1947Q2
T10101  -20.7  -33.3  -41.4  2.5  38.3  36.3  37.2  16.2  -30.4  15.4  29.6  17.2  -42.6  -10.2  33.7  43.2  24.7  36.0   -8.3
A007RL    xxx    xxx    xxx  xxx   xxx   xxx   xxx   xxx    xxx   xxx   xxx   xxx    xxx    xxx   xxx   xxx   xxx   xxx    xxx

Upvotes: 0

Related Questions